PHP imports the excel (xls) file into the mysql database

I have sorted out two ways to import an excel file to a mysql database. One is to use PHP-ExcelReader for import, and the other is to convert an excel file into a csv file and directly use php-related functions for import.

A customer contacted me for a website last night and asked me to import the data in the excel file provided by the customer to the mysql database, the most common method is to export the xls file as a csv file, and then import the file to the mysql database after parsing the csv file. The method is redundant and can be divided into several steps, which is inconvenient. One method described today is to directly skip the csv intermediate link and directly import the excel file to the mysql database.

First, we need to download PHP-ExcelReader. This is an open-source project, mainly used to parse the excel file: Compile (the default file is oleread. inc. I don't know why, a bunch of e files. I don't know, just change the name ).

Find the following similar code in the reader. php file (the first line is both) and change it to the correct oleread. php path: require_once 'oleread. php ';

Create a new php file and introduce reader. php. The Code is as follows:

The Code is as follows:

Copy code

<? Php Require_once 'excel/reader. php '; $ Data = new Spreadsheet_Excel_Reader (); $ Data-> setOutputEncoding ('gbk'); // encoding is set here, generally in the gbk mode. $ Data-> read('Book1.xls '); // file path bKjia. c0m Error_reporting (E_ALL ^ E_NOTICE ); // Here, I will output the content of the excel file cyclically. to store the content to the database, just write a mysql statement where the output is located ~ For ($ I = 1; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++ ){ For ($ j = 1; $ j <= $ data-> sheets [0] ['numcols']; $ j ++ ){ Echo ". $ data-> sheets [0] ['cells '] [$ I] [$ j]." ","; } Echo "n "; } ?>

Note: Please do not use the xls in the PHP-ExcelReader compressed package for testing. The file cannot be opened even in excel, so it is wrong.

The method above is used to parse a m data, and the data is displayed normally, so you can use it with confidence.

Phpexcel compares charged resources, but not all excel files can be read. We can convert them to csv files for operation.

First, confirm the encoding of your database. Take UTF-8 as an example,
Open the excel file, save it, and select ". CSV" as the file.
Then open the. csv file with the compiler and save it as a UTF-8 csv file.
Then, you can use php's getcsv to open the php file (this ensures that some of your fields are included, resulting in parsing errors), and then import the parsed results to the database.

If it is in csv format, I do not need to be so troublesome.

The Code is as follows:

Copy code

<? // Connect to the database file www. bKjia. c0m $ Connect = mysql_connect ("localhost", "admin", "admin") or die ("failed to connect to the database! "); // Connect to the database (test) Mysql_select_db ("testcg", $ connect) or die (mysql_error ()); $ Temp = file ("test.csv"); // connect to the excelfile, in the format of .csv For ($ I = 0; $ I <count ($ temp); $ I ++) { $ String = explode (",", $ temp [$ I]); // obtain the value of each record in an EXCEL file in a loop // Insert the values of each record in the EXCEL file into the database $ Q = "insert into ceshi (name, num, dom) values ('$ string [0]', '$ string [1]', '$ string [2]'); "; Mysql_query ($ q) or die (mysql_error ()); If (! Mysql_error ()); { Echo "data imported successfully! "; } Echo $ string [4]. "n "; Unset ($ string ); } ?>