PHP implementation to import Excel files into MySQL

Recently due to project requirements, to implement the Excel file through the PHP page into the MySQL database. Search on the Internet a lot of this information, found that the Excel file is saved as a CSV file, and then imported from the CSV file. Here is an example of importing an Excel file directly into MySQL. I spent a night testing, regardless of the introduction of simple traditional will not appear garbled, very useful.

Description

Test environment: MySQL database is UTF8 encoded. The import Excel document is XLS format and, after testing, xlsx format [Excel 2007] is OK.

The red note in the text as a need to pay attention to the place, please replace the data you have configured, such as database configuration. Run http://localost/test.php implementation Import.

Here is the detailed code I posted, where test.php the test files that I wrote, reader.php and oleread.inc files are downloaded from the URLs provided above.

1. test.php

The following are the referenced contents:

Require_once ' reader.php ';

Excelfile ($filename, $encoding);

$data = new Spreadsheet_excel_reader ();

Set output Encoding.

$data->setoutputencoding (' GBK ');

"Data.xls" refers to an Excel file that you want to import into MySQL

$data->read (' Data.xls ');

@ $db = mysql_connect (' localhost ', ' root ', ' 123456 ') or

Die ("Could not connect to database."); /Connection Database

mysql_query ("Set names ' GBK '")//Output Chinese

mysql_select_db (' mydb '); Select Database

Error_reporting (e_all ^ e_notice);

for ($i = 1; $i <= $data->sheets[0][' numrows '; $i + +) {

The For loop for the following comment prints Excel table data

/*

for ($j = 1; $j <= $data->sheets[0][' numcols '; $j + +) {

echo "". $data->sheets[0][' cells ' [$i] [$j]. "", ";

}

echo "n";

*/

The following code is to insert the Excel table Data "3 fields" into MySQL, according to your Excel table fields, the number of rewrite the following code it!

$sql = "INSERT into Test VALUES ('".

$data->sheets[0][' cells ' [$i][1]. "', '".

$data->sheets[0][' cells ' [$i][2]. "', '".

$data->sheets[0][' cells ' [$i][3].

echo $sql. '

';

$res = mysql_query ($sql);

}

?>