PHP imports Excel into the database and database data exported to Excel, Excel data export
This example describes how PHP imports Excel into the database and the database data exported to Excel. Share to everyone for your reference. The implementation method is as follows:
One. Import
Import needs to use the components that can read Excel, there are better components on the net, here to share I use: Download extract code: Vxyn. (Note that two files have a citation relationship)
<?php//the file name of the Excel to be imported function import_to_db ($filename) { require_once ' reader.php '; $data = new Spreadsheet_excel_reader (); Create an object that reads Excel $data->setoutputencoding (' utf-8 '); Sets the character encoding output after reading the contents of Excel $data->read ("data/excel/{$filename}.xls"); $db = mysql_connect (' localhost ', ' username ', ' password ') or Die ("Could does connect to database."); Connect 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 items ...)"; mysql_query ($sql); Can be wrongly judged */ }?>
In short, you can read the values of the corresponding column $data->sheets[0][[column] in each row in the table, and the insert operation will be done.
Two. Export
Export can easily export tabular files without relying on any components using the MIME protocol. You can export Excel by setting the header in the following format, while the browser downloads
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:
<?php 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 "1\t header 2\t header 3\n", while ($row =mysql_fetch_array ($result)) { echo $row [0]. " \ t ". $row [1]." \ t ". $row [2]." \ n ";}?" >
In fact, \ t is a change of grid, \ nthe line. Set up a link to this PHP file in a Web page, and the browser will automatically save the streamed stream as an Excel file when clicked.
I hope this article is helpful to everyone's PHP programming.
http://www.bkjia.com/PHPjc/1021084.html www.bkjia.com true http://www.bkjia.com/PHPjc/1021084.html techarticle PHP imports Excel into the database and database data exported to Excel method, Excel Data export This article describes how PHP imports Excel into the database and database data exported to Excel. Points ...