How to import Excel into database and export database data to Excel with PHP_PHP tutorial

How to import Excel into a database and export database data to Excel with PHP

This article describes the example of how to import Excel into a database and export database data to Excel with PHP. Share it with everyone for your reference. The specific implementation method is as follows:

1. Import

Importing requires the use of a component that can read Excel. There are also better components on the Internet. Here I share the one I use: Download extraction code: vxyn. (Note that the two files are referenced)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

//传入要导入的Excel的文件名 function import_to_DB($filename) { require_once'reader.php'; $data = new Spreadsheet_Excel_Reader(); //创建读取Excel的对象 $data->setOutputEncoding('utf-8'); //设置读取Excel内容后输出的字符编码 $data->read("data/Excel/{$filename}.xls"); $db = mysql_connect('localhost', '用户名', '密码') or die("Could not connect to database."); //连接数据库 mysql_query("set names 'uft8'"); //输出中文 mysql_select_db('数据库名'); //选择数据库 error_reporting(E_ALL ^ E_NOTICE); for ($i = 1; $i <= $data->sheets[0]['numRows']; $i ) { echo $data->sheets[0]['cells'][$i][列数]; //这里可以把每一行相应列的值插到数据库中，如： /* $sql="insert "表名" values(对应项...)"; mysql_query($sql); 可加上错误判断 */ } ?>

1 2

3

4

5

6

7

8

1 2 3 4

header('Content-type: text/html; charset=utf-8'); header("Content-type:application/vnd.ms-excel;charset=UTF-8"); //application/vnd.ms-excel指定输出Excel格式 header("Content-Disposition:filename=表格文件名.xls"); //输出的表格名称

9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

<🎜>//Input the Excel file name to be imported<🎜> <🎜>function import_to_DB($filename) {<🎜> <🎜>require_once'reader.php';<🎜> <🎜>$data = new Spreadsheet_Excel_Reader();<🎜> <🎜>//Create an object for reading Excel<🎜> <🎜>$data->setOutputEncoding('utf-8'); //Set the character encoding output after reading Excel content $data->read("data/Excel/{$filename}.xls"); $db = mysql_connect('localhost', 'username', 'password') or die("Could not connect to database."); //Connect to database mysql_query("set names 'uft8'"); //Output Chinese mysql_select_db('database name'); //Select database error_reporting(E_ALL ^ ​​E_NOTICE); for ($i = 1; $i <= $data->sheets[0]['numRows']; $i ) { echo $data->sheets[0]['cells'][$i][number of columns]; //Here you can insert the value of the corresponding column of each row into the database, such as: /* $sql="insert "table name" values ​​(corresponding item...)"; mysql_query($sql); Can add wrong judgment */ } ?>

In short, if you can read the value of the corresponding column $data->sheets[0][row][column] in each row of the table, the insertion operation will be easy. 2. Export Export can use the MIME protocol to easily export table files without relying on any components. Set the header in the following format to export to Excel, and the browser will download it at the same time ?

1 2 3 4

header('Content-type: text/html; charset=utf-8'); header("Content-type:application/vnd.ms-excel;charset=UTF-8"); //application/vnd.ms-excel specifies the output Excel format header("Content-Disposition:filename=table file name.xls"); //Output table name

The complete code is as follows:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

header('Content-type: text/html; charset=utf-8'); header("Content-type:application/vnd.ms-excel;charset=UTF-8"); header("Content-Disposition:filename=表格文件名.xls"); $conn = mysql_connect("localhost","root","数据库密码") or die("不能连接数据库"); mysql_select_db("数据库名", $conn); mysql_query("set names 'UTF-8'"); $sql="select * from 表名 where 条件"; $result=mysql_query($sql); echo "表头1t表头2t表头3n"; while($row=mysql_fetch_array($result)){ echo $row[0]."t".$row[1]."t".$row[2]."n"; } ?>

1 2

3

4

5

6

7 8 9 10 11 12

13 14

<🎜>header('Content-type: text/html; charset=utf-8');<🎜> <🎜>header("Content-type:application/vnd.ms-excel;charset=UTF-8");<🎜> <🎜>header("Content-Disposition:filename=table file name.xls");<🎜> <🎜>$conn = mysql_connect("localhost","root","database password") or die("Cannot connect to database");<🎜> <🎜>mysql_select_db("database name", $conn);<🎜> <🎜>mysql_query("set names 'UTF-8'");<🎜> <🎜>$sql="select * from table name where condition";<🎜> <🎜>$result=mysql_query($sql);<🎜> <🎜>echo "Header 1t Header 2t Header 3n";<🎜> <🎜>while($row=mysql_fetch_array($result)){<🎜> <🎜>echo $row[0]."t".$row[1]."t".$row[2]."n";<🎜> <🎜>}<🎜> <🎜>?>

Here, t is actually a format change, and n is a line break. Set the link to this php file in a web page, and when clicked, the browser will automatically save the passed stream as an Excel file. I hope this article will be helpful to everyone’s PHP programming design. http://www.bkjia.com/PHPjc/1020890.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1020890.htmlTechArticleHow to import Excel into database and export database data to Excel using PHP. This article explains how to import Excel into database and database using PHP. How to export data to Excel. Share it with everyone for everyone...