PHP import excel (xls) file into mysql database_PHP tutorial-PHP Tutorial-php.cn

PHP import excel (xls) file into mysql database_PHP tutorial

WBOY

Release： 2016-07-13 10:46:48

Original

1063 people have browsed it

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

Last night a customer contacted me to build a website. The request was to import the data in the excel file provided by the customer into the mysql database. The most common method is to first export the xls file into a csv format file and then parse it. Files in csv format are imported into the mysql database. The method is relatively redundant and is divided into several steps, which is very inconvenient. Today Broken Bridge Canxue introduced a method that directly skips the intermediate link of csv and directly imports the excel file into the mysql database.

First we need to download PHP-ExcelReader, which is an open source project. It is mainly used to parse excel files. Download address: http://sourceforge.net/projects/phpexcelreader. After downloading, unzip it and mainly use the excel folder. There are two files inside, reader.php and oleread.php (the default of this file is oleread.inc, I don’t know why, it’s a bunch of e-texts, I haven’t read them, just change the name).

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

Then create a new php file and import reader.php, the code is as follows:

echo "n"; }

The code is as follows

代码如下

复制代码

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('gbk');//此处设置编码，一般都是gbk模式

$data->read('Book1.xls');//文件路径bKjia.c0m

error_reporting(E_ALL ^ E_NOTICE);
//这里我就只循环输出excel文件的内容了，要入库，只要把输出的地方，写一段mysql语句即可~
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";
}
?>

Copy code

require_once 'Excel/reader.php';

$data = new Spreadsheet_Excel_Reader();

$data->setOutputEncoding('gbk');//Set the encoding here, usually in gbk mode

$data->read('Book1.xls');//File path bKjia.c0m

error_reporting(E_ALL ^ E_NOTICE);

//Here I will only loop the contents of the excel file. To store it in the database, just write a mysql statement in the output place~

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]."",";

代码如下

复制代码

//连接数据库文件 www.bKjia.c0m
$connect=mysql_connect("localhost","admin","admin") or die("链接数据库失败！");
//连接数据库(test)
mysql_select_db("testcg",$connect) or die (mysql_error());

$temp=file("test.csv");//连接EXCEL文件,格式为了.csv
for ($i=0;$i {
$string=explode(",",$temp[$i]);//通过循环得到EXCEL文件中每行记录的值
//将EXCEL文件中每行记录的值插入到数据库中
$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 " 成功导入数据!";
}
echo $string[4]."n";
unset($string);
}
?>

}

?> Note: Please do not use the xls in the PHP-ExcelReader compressed package for testing. Broken Bridge Canxue found that the file cannot be opened even with excel, so it is wrong. Broken Bridge Canxue used the above method to parse a 1.4M data, and it all showed normal, so everyone can use it with confidence phpexcel is relatively resource intensive, but not all excel can be read. We can convert it to csv for operation First confirm what encoding your database is in, take utf-8 as an example, You first open the excel file, then save it and choose to save as a .csv file. Then open the .csv file with a text editor and save it as utf-8 csv Then when you write PHP, you can use PHP's getcsv to open it (this ensures that the fields you have contain it, which will cause parsing errors), and then import the parsed results into the database. If it were in csv format, I wouldn’t have to go through so much trouble

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 database!"); <🎜> //Connect to database (test) <🎜> mysql_select_db("testcg",$connect) or die (mysql_error());<🎜> <🎜>$temp=file("test.csv");//Connect the EXCEL file, the format is .csv <🎜> for ($i=0;$i { <🎜> $string=explode(",",$temp[$i]);//Get the value of each row record in the EXCEL file through loop <🎜> //Insert the value of each row of records 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); <🎜> } <🎜> ?>