How to import and export Excel files using PHP

Next I will introduce you to the two excel instances in php. one is to import and output excel files using PHP-ExcelReader, and the other is to directly input and export excel files. The following is an example. With PH

Next I will introduce you to the two excel instances in php. one is to import and output excel files using PHP-ExcelReader, and the other is to directly input and export excel files. The following is an example.

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

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

2. Require_once 'Excel/reader. php ';
3. $ Data = new Spreadsheet_Excel_Reader ();
4. $ Data-> setOutputEncoding ('gbk ');
5. $ Data-> read('test.xls ');
6. For ($ I = 1; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++ ){
7. For ($ j = 1; $ j <= $ data-> sheets [0] ['numcols']; $ j ++ ){
8. Echo ". $ data-> sheets [0] ['cells '] [$ I] [$ j]." ",";
9. }
10. Echo "n ";
11. }
12. ?>

For example, export PHP excel to excel,1. test. php,The code is as follows:

1. Require_once 'reader. php ';
2. // ExcelFile ($ filename, $ encoding );
3. $ Data = new Spreadsheet_Excel_Reader ();
4. // Set output Encoding.
5. $ Data-> setOutputEncoding ('gbk ');
6. // ”Data.xls refers to the excel file to be imported to mysql.
7. $ Data-> read('data.xls ');
8. @ $ Db = mysql_connect ('localhost', 'root', '123') or
9. Die ("cocould not connect to database."); // connect to the database
10. Mysql_query ("set names 'gbk'"); // output Chinese
11. Mysql_select_db ('mydb'); // select a database
12. Error_reporting (E_ALL ^ E_NOTICE );
13. For ($ I = 1; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++ ){
14. // Print the excel table data in a for loop with the following comments
15. /*
16. For ($ j = 1; $ j <= $ data-> sheets [0] ['numcols']; $ j ++ ){
17. Echo ". $ data-> sheets [0] ['cells '] [$ I] [$ j]." ",";
18. }
19. Echo "n ";
20. // PHP open source code
21.  
22. */
23. // 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!
24. $ SQL = "INSERT INTO test VALUES ('".
25. $ Data-> sheets [0] ['cells '] [$ I] [1]. "', '".
26. $ Data-> sheets [0] ['cells '] [$ I] [2]. "', '".
27. $ Data-> sheets [0] ['cells '] [$ I] [3]. "')";
28. Echo $ SQL .'
29. ';
30. $ Res = mysql_query ($ SQL );
31. }
32. ?>

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 directly use the header: header ("Content-type: application/vnd. ms-excel ");

Take a look at the following code:

2. Header ("Content-type: application/vnd. ms-excel ");
3. Header ("Content-Disposition: attachment?filename=export_test.xls ");
4. $ Tab = "t"; $ br = "n ";
5. $ Head = "no.". $ tab. "Remarks". $ br;
6. // The output content is as follows:
7. Echo $ head. $ br;
8. Echo "test321318312". $ tab;
9. Echo "string1 ";
10. Echo $ br;
11. Echo "330181199006061234". $ tab; // The direct output is recognized as a number by Excel.
12. Echo "number ";
13. Echo $ br;
14. Echo "=" 330181199006061234 "". $ tab; // The original output needs to be processed.
15. Echo "string2 ";
16. Echo $ br;
17. ?>

An error will be found after export. if the data is a number, unexpected situations may occur. for example, "012345" may change 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, and the last four digits will be deviated, the displacement is 0000, etc, 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:

1. Header ("Content-Type: application/msword ");
2. Header ("Content-Disposition: attachment?filename=doc.doc ");