How to export Excel to Excel from database and database data in PHP

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.