PHP Excel import and export method

The PHP Excel import and export method, reading the PHP Excel import and export method, Original Author: When I saw this article on the iceberg podcast, I was surprised by the patience of the original author, although we usually use some of them, they are not fully listed by the Author. when I was writing an excel file, I used the pear Library and the pack header, similarly, for simple replicas using smarty and other functions, <LINKhref = "http ://

Original Author: podcasts on the iceberg
When I saw this article, I was surprised by the patience of the original author. Although we used some of them at ordinary times, they did not list all of them. when I was writing an excel file, I have used pear libraries and pack headers, and I have also used simple xml replacement by using smarty and so on. I don't need to talk about csv. Haha. (Don't talk about the COM method. this is too readable. I have also written articles such as generating word using wps)
However, I only used one method when I was reading it. I forgot what it was, and I had to go back to the code. This is because the app is a tailism and cannot be remembered.
Address: http://xinsync.xju.edu.cn/index.php/archives/3858
Original article content:

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 code
/**
* 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 encryption 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 code
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 XML or HTML files conforming to 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 code
// 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", 0 × 0A, 0 × 00 );
Return;
}
Function xlsWriteNumber ($ Row, $ Col, $ Value ){
Echo pack ("sssss", 0 × 203, 14, $ Row, $ Col, 0 × 0 );
Echo pack ("d", $ Value );
Return;
}
Function xlsWriteLabel ($ Row, $ Col, $ Value ){
$ L = strlen ($ Value );
Echo pack ("ssssss", 0 × 204, 8 + $ L, $ Row, $ Col, 0 × 0, $ 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 character "\ t" is used to separate columns in the same row. The line break "\ t \ n" can be used to open the next row.
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 code
$ 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 code
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 site uses the shared copyright agreement, requiring signatures, non-commercial and consistent. this site welcomes reprinting of any non-commercial applications, but it must be noted that the original link is retained, and the original title and link must also be noted.