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)
<?php
//Incoming file name
function import_to_db ($filename) {
require_once ' reader.php ' of Excel to be imported;
$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];
Here you can insert the value of the corresponding column in each row into 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
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:
<?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 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, \ n is a line break. 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.