How to import Excel data into the database and export database data to Excel in PHP? _ PHP Tutorial

PHP exports the Excel data into the database and database data to Excel. PHP exports Excel data to the database and database to Excel. This document describes how PHP exports excel data to the database and database to Excel. Import Excel data into the database and export the database data to Excel in PHP, and export the excel data

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)

<? Php // input the name of the Excel file to be imported. function import_to_DB ($ filename) {require_once 'reader. php '; $ data = new Spreadsheet_Excel_Reader (); // Create an Excel reading object $ data-> setOutputEncoding ('utf-8 '); // set the character encoding $ data-> read ("data/Excel/{$$filename}.xls") after reading the Excel content; $ db = mysql_connect ('localhost', 'username ', 'password') or die ("cocould not connect to database. "); // connect to the 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 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.

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 .xls"); // The name of the output table

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

Examples in this article describes how PHP imports data from an Excel database and from a database to an Excel file. Points...