How to import Excel data into a database and export it to Excel using PHP

How to import Excel data into a database and export it to Excel using PHP

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

1. Import

To import data, you must use a component that can read Excel. There are also good components on the Internet. Here, I will share my use: Download extraction code: vxyn. (Note that there is a reference relationship between the two files)

?

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

<? Php // Input the file name of the Excel file 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 the Excel content $ Data-> read ("data/Excel/{$filename}.xls "); $ Db = mysql_connect ('localhost', 'username', 'Password') or die ("cocould not connect to database ."); // Connect to the database Mysql_query ("set names 'uft8 '"); // Output Chinese Characters Mysql_select_db ('database name '); // Select a 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]; // Insert the values of corresponding columns in each row to the database, for example: /* $ SQL = "insert" table name "values (corresponding item ...)"; Mysql_query ($ SQL ); Error judgment can be added */ } ?>

In short, you can read the value of the corresponding column $ data-> Sheet [0] [row] [column] in each row in the table, and the insert operation is easy.

Ii. Export

You can use the MIME protocol to easily export table files without relying on any components. Set the header in the following format to export the Excel file and download it in the browser.

?

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: filename1_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

<? Php Header ('content-type: text/html; charset = UTF-8 '); Header ("Content-type: application/vnd. ms-excel; charsets = UTF-8 "); Header ("Content-Disposition: filename1_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 1 \ t header 2 \ t header 3 \ n "; While ($ row = mysql_fetch_array ($ result )){ Echo $ row [0]. "\ t". $ row [1]. "\ t". $ row [2]. "\ n "; } ?>

Here \ t is actually a change, \ n is a line feed. Set the PHP file link in a webpage. When you click it, the browser automatically saves the uploaded stream as an Excel file.

I hope this article will help you with php programming.