dongpenggan6812
dongpenggan6812
2014-11-11 05:34

如何将excel数据导入MySQL数据库?

  • php
  • mysql

I have used the following code to read the data of exceltodb.xlsx file and import it into the table city of database world. The library to read the file is PHPExcel which is very common library.The code I found is as follows but the code is executing but the row is not added into the database.

<?php
include 'PHPExcel-develop/Classes/PHPExcel/IOFactory.php';

$inputFileName = 'exceltodb.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load('exceltodb.xlsx');
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    //  Insert row data array into your database of choice here
    mysql_connect('localhost','root','');
    mysql_select_db('world');
    mysql_query('insert into city("city","id","stateid","countryid") values("$rowData")');
}
?>
  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答