PHPEXCEL：如何从excel文件中读取每一行并插入到数据库中

Question

I want to upload an excel file, read row by row and insert the data into the database. How may I do so?

This is what I have tried so far:

for ($row = 1; $row <= $highestRow; $row++) {

        //  Read a row of data into an array

        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, 
        NULL, TRUE, FALSE);
        // This line works as $sheet->rangeToArray('A1:E1') that is selecting all the cells in that row from cell A to highest column cell

        echo "<tr>";

        //echoing every cell in the selected row for simplicity. You can save the data in database too.
        foreach($rowData[0] as $k=>$v)
         //   echo "<td>".$v."</td>";
       // foreach( $xlsx->rows() as $r ) 


        $sql = "INSERT into temperature (datetime, MODE_Value, MV_Value,PV_Value, PV_HighEng_Value, PV_LowEng_Value,SV_Value)
        values('$datetime', '$MODE_Value','$MV_Value','$PV_Value','$PV_HighEng_Value','$PV_LowEng_Value','$SV_Value')";
        //we are using mysql_query function. it returns a resource on true else False on error
        $result = mysqli_query( $conn, $sql );
        //echo "</tr>";
    }

Any tips, suggestions and helps are appreciated.

Answer 1

Try something like this.

    for ($row = 2; $row <= $highestRow; $row++) {
        $column_1 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('A' . $row)->getValue()));
        $column_2 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('B' . $row)->getValue()));
        $column_3 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('C' . $row)->getValue()));
        $column_4 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('D' . $row)->getValue()));
        $column_5 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('E' . $row)->getValue()));
        $column_6 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('F' . $row)->getValue()));

        $values = "('$column_1','$column_2','$column_3','$column_4','$column_5','$column_6')";
        if ($values != '') {
            $sqlInsert = "INSERT INTO table (column_1,column_2,column_3,column_4,column_5,column_6) VALUES $values";
            mysql_query($sqlInsert) or die(mysql_error());
        }
    }