PHP Excel Import and Export method summary

Recently, due to project requirements, a module needs to be developed to export some data in the system to Excel, and then export the data back to the system after modification. We will take the opportunity to make a summary of this study.
Basically, the exported files are divided into two types:
1: Excel-like format. This is not an Excel file in the traditional sense. It is only because of its strong compatibility and ability to open it correctly. After you modify the file and save it, you are usually prompted to convert it into an Excel file.
Advantage: simple.
Disadvantage: It is difficult to generate a format. If it is used for import, you need to write the corresponding program separately.
2: Excel format, which corresponds to Excel-like files. The files generated by this method are closer to the real Excel format.

If garbled characters occur during Chinese export, you can convert the string to gb2312. For example, convert $ yourStr from UTF-8 to gb2312:
$ YourStr = mb_convert_encoding ("gb2312", "UTF-8", $ yourStr );

The following describes several methods.
1. Export Excel in PHP

1: The first recommended cool PHPExcel, Official Website: http://www.codeplex.com/PHPExcel
Both import and export are supported. The office2007 format can be exported and 2003 compatible.
The downloaded package contains documents and examples, which you can study on your own.
Example:
<? Php
/**
* PHPExcel
*
* Copyright (C) 2006-2007 PHPExcel
*
* This library is free software; you can redistribute it and/or
* Modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* Version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* But without any warranty; without even the implied warranty
* MERCHANTABILITY or fitness for a special PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You shoshould have written ed a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @ Category PHPExcel
* @ Package PHPExcel
* @ Copyright Copyright (c) 2006-2007 PHPExcel (http://www.codeplex.com/PHPExcel)
* @ License http://www.gnu.org/licenses/lgpl.txt LGPL
* @ Version 1.5.0, 2007-10-23
*/

/** Error reporting */
Error_reporting (E_ALL );

/** Include path **/
Set_include_path (get_include_path (). PATH_SEPARATOR. '../Classes /');

/** PHPExcel */
Include 'phpexcel. php ';

/** PHPExcel_Writer_Excel2007 */
Include 'phpexcel/Writer/excel2007.php ';

// Create new PHPExcel object
Echo date ('H: I: s'). "Create new PHPExcel object \ n ";
$ ObjPHPExcel = new PHPExcel ();

// Set properties
Echo date ('H: I: s'). "Set properties \ n ";
$ ObjPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw ");
$ ObjPHPExcel-> getProperties ()-> setLastModifiedBy ("Maarten Balliauw ");
$ ObjPHPExcel-> getProperties ()-> setTitle ("Office 2007 XLSX Test Document ");
$ ObjPHPExcel-> getProperties ()-> setSubject ("Office 2007 XLSX Test Document ");
$ ObjPHPExcel-> getProperties ()-> setDescr resume ption ("Test document for Office 2007 XLSX, generated using PHP classes .");
$ ObjPHPExcel-> getProperties ()-> setKeywords ("office 2007 openxml php ");
$ ObjPHPExcel-> getProperties ()-> setCategory ("Test result file ");

// Add some data
Echo date ('H: I: s'). "Add some data \ n ";
$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('a1', 'Hello ');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('b2', 'World! ');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('c1', 'Hello ');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('d2 ', 'World! ');

// Rename sheet
Echo date ('H: I: s'). "Rename sheet \ n ";
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ('simple ');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$ ObjPHPExcel-> setActiveSheetIndex (0 );

// Save Excel 2007 file
Echo date ('H: I: s'). "Write to Excel2007 format \ n ";
$ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel );
$ ObjWriter-> save (str_replace ('. php', '.xlsx', _ FILE __));

// Echo done
Echo date ('H: I: s'). "Done writing file. \ r \ n ";

2. Use the Spreadsheet_Excel_Writer class of pear.
: Http://pear.php.net/package/Spreadsheet_Excel_Writer
This class depends on OLE,: http://pear.php.net/package/OLE
Note that the exported Excel file format is relatively old. After modification, the system will prompt whether to convert it to the updated format.
However, you can set the format, which is very powerful.
<? Php
Require_once 'spreadsheet/Excel/Writer. php ';
 
// Creating a workbook
$ Workbook = new Spreadsheet_Excel_Writer ();

// Sending HTTP headers
$ Workbook-> send('test.xls ');

// Creating a worksheet
$ Worksheet = & $ workbook-> addWorksheet ('My first worksheet ');

// The actual data
$ Worksheet-> write (0, 0, 'name ');
$ Worksheet-> write (0, 1, 'age ');
$ Worksheet-> write (1, 0, 'johnsmith ');
$ Worksheet-> write (1, 1, 30 );
$ Worksheet-> write (2, 0, 'johann Schmidt ');
$ Worksheet-> write (2, 1, 31 );
$ Worksheet-> write (3, 0, 'juan Herrera ');
$ Worksheet-> write (3, 1, 32 );

// Let's send the file
$ Workbook-> close ();
?>

3: Use smarty to generate html xml files that comply with Excel specifications
Supported formats, perfect export solution. However, the imported XML file is essentially an XML file. If it is used for import, additional processing is required.
For more information, see rardge's post: http://bbs.chinaunix.net/viewthread.php? Tid = 745757

Note that if the number of rows of the exported table is unknown, you are advised to delete "ss: ExpandedColumnCount =" 5 "ss: ExpandedRowCount =" 21 "" in the template.

4. Use the pack function to print out the broken sentence symbols in the simulated Excel format, which is closer to the standard Excel format. If you use office2003 to modify and save it, no prompt is displayed. This method is recommended.
The disadvantage is that there is no format.
<? Php
// Send Header
Header ("Pragma: public ");
Header ("Expires: 0 ");
Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 ");
Header ("Content-Type: application/force-download ");
Header ("Content-Type: application/octet-stream ");
Header ("Content-Type: application/download ");;
Header ("Content-Disposition: attachment?filename=test.xls ");
Header ("Content-Transfer-Encoding: binary ");
// XLS Data Cell

XlsBOF ();
XlsWriteLabel (1, 0, "My excel line one ");
XlsWriteLabel (2, 0, "My excel line two :");
XlsWriteLabel (2, 1, "Hello everybody ");

XlsEOF ();

Function xlsBOF (){
Echo pack ("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0 );
Return;
}
Function xlsEOF (){
Echo pack ("ss", 0x0A, 0x00 );
Return;
}
Function xlsWriteNumber ($ Row, $ Col, $ Value ){
Echo pack ("sssss", 0x203, 14, $ Row, $ Col, 0x0 );
Echo pack ("d", $ Value );
Return;
}
Function xlsWriteLabel ($ Row, $ Col, $ Value ){
$ L = strlen ($ Value );
Echo pack ("ssssss", 0x204, 8 + $ L, $ Row, $ Col, 0x0, $ L );
Echo $ Value;
Return;
}
?>
However, I failed to use it in a 64-bit linux system, and all the broken sentence symbols became garbled characters.

5. Use tabs and line breaks
The tab "\ t" is used to separate columns in the same row. The line break "\ t \ n" can be used to open the next row.
<? Php
Header ("Content-Type: application/vnd. ms-execl ");
Header ("Content-Disposition: attachment; filename=myExcel.xls ");
Header ("Pragma: no-cache ");
Header ("Expires: 0 ");
/* First line */
Echo "hello". "\ t ";
Echo "world". "\ t ";
Echo "\ t \ n ";

/* Start of second line */
Echo "this is second line". "\ t ";
Echo "Hi, pretty girl". "\ t ";
Echo "\ t \ n ";
?>

6. Use com
If your PHP can enable the com module, you can use it to export Excel files.
<? PHP
$ Filename = "c:/spreadhseet/test.xls ";
$ Sheet1 = 1;
$ Sheet2 = "sheet2 ";
$ Excel_app = new COM ("Excel. application") or Die ("Did not connect ");
Print "Application name: {$ excel_app-> Application-> value} \ n ";
Print "Loaded version: {$ excel_app-> Application-> version} \ n ";
$ Workbook = $ excel_app-> Workbooks-> Open ("$ filename") or Die ("Did not open $ filename $ Workbook ");
$ Worksheet = $ Workbook-> Worksheets ($ sheet1 );
$ Worksheet-> activate;
$ Excel_cell = $ Worksheet-> Range ("C4 ");
$ Excel_cell-> activate;
$ Excel_result = $ excel_cell-> value;
Print "$ excel_result \ n ";
$ Worksheet = $ Workbook-> Worksheets ($ sheet2 );
$ Worksheet-> activate;
$ Excel_cell = $ Worksheet-> Range ("C4 ");
$ Excel_cell-> activate;
$ Excel_result = $ excel_cell-> value;
Print "$ excel_result \ n ";
# To close all instances of excel:
$ Workbook-> Close;
Unset ($ Worksheet );
Unset ($ Workbook );
$ Excel_app-> Workbooks-> Close ();
$ Excel_app-> Quit ();
Unset ($ excel_app );
?>
A better example: http://blog.chinaunix.net/u/16928/showart_387171.html

I. Import Excel files in PHP

1: PHPExcel is still used. Official Website: http://www.codeplex.com/phpexcel.

2: Use PHP-ExcelReader,: http://sourceforge.net/projects/phpexcelreader
Example:
<? Php
Require_once 'excel/reader. php ';

// ExcelFile ($ filename, $ encoding );
$ Data = new Spreadsheet_Excel_Reader ();

// Set output Encoding.
$ Data-> setOutputEncoding ('utf8 ');

$ Data-> read ('jxlrwtest.xls ');

Error_reporting (E_ALL ^ E_NOTICE );

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 ";
}

?>

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/binger819623/archive/2009/06/22/4289544.aspx