How to import Excel to MySQL with PHP_PHP Tutorial-PHP Tutorial-php.cn

How to import Excel to MySQL with PHP_PHP Tutorial

WBOY

Release： 2016-07-21 15:30:18

Original

878 people have browsed it

After some research ~ there are many methods ~ I finally decided to use Excel to import ~ I searched a lot of information on this on the Internet and found that they all save the excel file as a csv file and then import from the csv file. Here is an example of directly importing excel files into mysql. I spent one night testing it, and there was no garbled code when importing simplified or traditional Chinese. It was very easy to use.
PHP-ExcelReader, download address: http://sourceforge.net/projects/phpexcelreader
Instructions:
PHP imports EXCEL into the MYSQL test environment: the MYSQL database uses utf8 encoding. The imported EXCEL document is in xls format , after testing, the xlsx format [excel 2007] is also OK.
Please replace it with your configured data, such as database configuration, etc. Run http://localost/test.php to import.
The following is the detailed code I posted, in which test.php is the test file I wrote, and the reader.php and oleread.inc files are downloaded from the URL provided above.
1. PHP code example test.php for importing EXCEL into MYSQL

Copy code The code is as follows:

 
< ?php 
require_once 'reader.php'; // ExcelFile($filename, $encoding); $data = new Spreadsheet_Excel_Reader(); // Set output Encoding. $data->setOutputEncoding('gbk'); 
//"data.xls" refers to the excel file to be imported into mysql 
$data->read('data.xls'); 
@ $db = mysql_connect('localhost', ' root', '123456') or 
die("Could not connect to database.");//Connect to the 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 following commented for loop 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 inserts excel table data [3 fields] into mysql. 
Rewrite the following code according to the number of fields in your excel table! 
$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.'< br />'; 
$res = mysql_query($sql); 
} 
?> 

The above is an introduction to the relevant methods of importing EXCEL into MYSQL with PHP. I hope that friends who need it can be helpful.
But I found that there was something wrong with the files in his class. After modifying it, it became normal~ You can go to

Download phpexcelreader here