dongmieqiao3152
dongmieqiao3152
2017-04-18 15:21
浏览 192
已采纳

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

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dt3674
    dt3674 2017-04-18 15:28
    已采纳

    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());
            }
        }
    
    点赞 评论

相关推荐