This example describes how PHP imports Excel into the database and database data into Excel. Share to everyone for your reference. The implementation methods are as follows:
I. Import
The import needs to use the component that can read Excel, also has the good component on the net, here share I use: Download extract code: Vxyn. (Note that two files have a reference relationship)
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 The 25 26 |
<?php//Incoming file name of Excel to import 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 Excel content $data->read ("data/excel/{$filename}.xls"); $db = mysql_connect (' localhost ', ' username ', ' password ') or Die ("could not connect to database."); Connection 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];//You can insert the value of the corresponding column in each row To the database, such as:/* $sql = Insert Table name values (corresponding ...); mysql_query ($sql); Can be added error judgement/}?> |
In summary, the insert operation can be done by reading the value of the corresponding column in each row in the table $data->sheets[0][the row [column].
Two. Export
Exports can easily export a table file using the MIME protocol without relying on any component. You can export Excel by setting the header in the following format while the browser downloads
?
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 header for Excel format ("content-disposition:filename= table file name. xls"); The table name of the output |
The complete code is as follows:
?
1 2 3 4 5 6 7 8 9 10 11 12 13-14 |
<?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 the 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 "Table Head 1t Header 2t header 3n"; while ($row =mysql_fetch_array ($result)) {echo $row [0]. " T ". $row [1]." T ". $row [2]." n "; }?> |
Here in fact, T is to change the grid, n is to change lines. Set up a link to this PHP file in a Web page, and the browser will automatically save the stream as an Excel file when clicked.
I hope this article will help you with your PHP program design.