PHP Excel Import and Export Method

Introduction: This is a detailed page for PHP to import and export Excel methods. It introduces PHP, related knowledge, skills, experience, and some PHP source code.

Class = 'pingjiaf' frameborder = '0' src = 'HTTP: // biancheng.dnbc?info/pingjia.php? Id = 334782 'rolling = 'no'>
Original Author: podcasts on the iceberg

See this articleArticleI was surprised by the patience of the original author. Although we usually use some of them, they didn't list all of them. When I was writing excel, I used the pear library, I have also used the pack header, and I have also used the simple replacement of XML 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)

But when I was reading, I only used one of them. What did I forget?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 correspondingProgram.

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 appear during Chinese export, you can try to 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

<? 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 Y warranty; without even the implied warranty of * merchantability or fitness for a particle 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 pH Pexcel * @ package phpexcel * @ 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_excel 2007 */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 (" FICE 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 ')." Dd 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, 'John Smith '); $ 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 the sentence symbols in the Excel format.

This method is more similar to the standard Excel format. If you save it after modification with office2003, no prompt is displayed. This method is recommended.

The disadvantage is that there is no format.

<? PHP // send 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 d ATA cell xlsbof (); xlswritelabel (, "My Excel line one"); xlswritelabel (, "My Excel line two:"); xlswritelabel, "Hello everybody"); xlseof (); function xlsbof () {echo pack ("Ssssss", 0 × 809, 0 × 8, 0 × 0, 0 × 10, 0 × 0, 0 × 0); 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.

 
<? 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-> Worksheet S ($ 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; u NSET ($ 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" ;}?>
 

More articles about "php Excel Import and Export method"

Love J2EE follow Java Michael Jackson video station JSON online tools

http://biancheng.dnbcw.info/php/334782.html pageno: 10