How to import and export Excel files using PHP

With the open-source PHP-ExcelReader class, we can easily import Excel file data. The sample code is as follows:

PHP-ExcelReader download address: http://sourceforge.net/projects/phpexcelreader/

Example: import an Excel file

The code is as follows:	Copy code
<? Php Require_once 'Excel/reader. Php '; $ Data = new Spreadsheet_Excel_Reader (); $ Data-> setOutputEncoding ('gbk '); $ Data-> read('test.xls '); For ($ I = 1; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++ ){ For ($ j = 1; $ j <= $ data-> sheets [0] ['numcols']; $ j ++ ){ Echo ". $ data-> sheets [0] ['cells '] [$ I] [$ j]." ",";     } Echo "n "; } ?>

Example. Export from phpexcel to excel

1. test. php

The code is as follows:

Copy code

Require_once 'reader. Php '; // ExcelFile ($ filename, $ encoding ); $ Data = new Spreadsheet_Excel_Reader (); // Set output Encoding. $ Data-> setOutputEncoding ('gbk '); // ”Data.xls refers to the excel file to be imported to mysql. $ Data-> read('data.xls '); @ $ Db = mysql_connect ('localhost', 'root', '123') or Die ("cocould not connect to database."); // connect to the database Mysql_query ("set names 'gbk'"); // output Chinese Mysql_select_db ('mydb'); // select a database Error_reporting (E_ALL ^ E_NOTICE ); For ($ I = 1; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++ ){ // Print the excel table data in a for loop with the following comments /* For ($ j = 1; $ j <= $ data-> sheets [0] ['numcols']; $ j ++ ){ Echo ". $ data-> sheets [0] ['cells '] [$ I] [$ j]." ",";            } Echo "n "; // PHP open source code */ // The following code inserts [3 fields] of the excel table data into mysql. Rewrite the following code based on the number of fields in your excel table! $ SQL = "INSERT INTO test VALUES ('". $ Data-> sheets [0] ['cells '] [$ I] [1]. "', '". $ Data-> sheets [0] ['cells '] [$ I] [2]. "', '". $ Data-> sheets [0] ['cells '] [$ I] [3]. "')"; Echo $ SQL .' '; $ Res = mysql_query ($ SQL ); } ?>

Example: export an excel file

For example, if I need a php excel export program, I only need to export the relevant data to an excel table. In this simple operation, I don't need to use the class libraries or anything. You can use the header directly: header ("Content-type: application/vnd. ms-excel ");

Look at the code:

The code is as follows:

Copy code

<? Php Header ("Content-type: application/vnd. ms-excel "); Header ("Content-Disposition: attachment?filename=export_test.xls "); $ Tab = "t"; $ br = "n "; $ Head = "no.". $ tab. "Remarks". $ br; // The output content is as follows: Echo $ head. $ br; Echo "test321318312". $ tab; Echo "string1 "; Echo $ br; Echo "330181199006061234". $ tab; // The direct output is recognized as a number by Excel. Echo "number "; Echo $ br; Echo "=" 330181199006061234 "". $ tab; // The original output needs to be processed. Echo "string2 "; Echo $ br; ?>

After export, you will find a problem. If the data is a number, unexpected situations may occur. For example, "012345" can be changed to "12345" in excel. If you enter a long number such as an ID card number, it will be represented by scientific notation in excel, in addition, the last four digits may see deviations, 0000 displacement, and so on. In this case, you need to set the cell to the text format by using the following method:
Echo "=" 330181199006061234 "" If the program is UTF-8 encoded, you also need to use the iconv function for transcoding. Otherwise, it will be garbled.

In addition, if the word format is similar, you can specify the header:

The code is as follows:	Copy code
Header ("Content-Type: application/msword "); Header ("Content-Disposition: attachment; filename=doc.doc ");