PHP Import Export Excel method

See this article, is very surprised the original author's patience, although we have some in peacetime, but not the author listed the whole, when writing Excel, I used Pear Library, also used the pack pressure pack head, also those who use smarty and other simple substitution of XML also used, The CSV doesn't have to be talked about. Oh. (COM does not speak, this can be read too much, I also wrote the use of WPS, such as word generation and other articles)
But in the reading, only used one, specifically what forget, to go back to the code. Because of the use of take doctrine, can't remember.
Original address: http://xinsync.xju.edu.cn/index.php/archives/3858
Original content:

Recently, due to project needs, a module needs to be developed to export some of the data in the system to excel, and then back to the system after modification. Take the opportunity to study this, some of the following summary.
Basically the exported files are divided into two types:
1: Excel format, this is not in the traditional sense of Excel file, just because Excel compatibility ability, can be opened correctly. After you modify this file and then save it, you will usually be prompted whether you want to convert to an Excel file.
Pros: Simple.
Disadvantage: It is difficult to generate a format, if you need to write the appropriate program for the import.
The 2:excel format, which corresponds to class Excel, produces files that are closer to the real Excel format.

If garbled in the export of Chinese, you can try to convert the string to gb2312, for example, the following $yourstr from Utf-8 to gb2312:
$YOURSTR = mb_convert_encoding ("Gb2312″," Utf-8″, $YOURSTR);

Several methods are listed below.
First, PHP export Excel

1: First recommendation Matchless Phpexcel, official website: http://www.codeplex.com/PHPExcel
Import and export, you can export office2007 format, and compatible with 2003.
The downloaded package has documentation and examples that you can study on your own.
To copy the paragraph example out:

PHP code
<?php
/**
* Phpexcel
*
* Copyright (C) 2006-2007 Phpexcel
*
* This library was 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 was distributed in the hope that it'll be useful,
* but without any WARRANTY; Without even the implied warranty of
* merchantability or FITNESS for A particular PURPOSE. See the GNU
* Lesser general public License for more details.
*
* You should has received a copy of the GNU Lesser general public
* License along with this library; If not, write to the free software
* Foundation, Inc., Wuyi 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" XLSX Test Document ");
$objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setdescrīption ("Test document for Office-XLSX, generated using PHP classes.");
$objPHPExcel->getproperties ()->setkeywords ("Office 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 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, the use of Pear Spreadsheet_excel_writer class
: Http://pear.php.net/package/Spreadsheet_Excel_Writer
This class relies on the Ole,:http://pear.php.net/package/ole
It is important to note that the exported Excel file format is older, and the modified save will prompt you to convert to an updated format.
But you can set the format, very powerful.

PHP code
<?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: Generate an XML or HTML file that conforms to the Excel specification using Smarty
Support format, very perfect export scheme. However, the nature of the derivative is the XML file, if used to import it needs to be processed separately.
For more information, please see Rardge's post: http://bbs.chinaunix.net/viewthread.php?tid=745757

It is important to note that if the number of table rows exported is uncertain, it is best to delete "ss:expandedcolumncount=" 5″ss:expandedrowcount= "21″" in the template.

4, using the Pack function to print the analog Excel format of the segmentation symbol, which is closer to the standard format of Excel, with office2003 modified to save, will not pop up hints, recommend this method.
The disadvantage is no format.

PHP code
<?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:");
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, $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, the author in the 64-bit Linux system used in the failure, the segmentation symbol has all become garbled.

5, the use of tabs, line break method
The tab "\ T" user splits the column in the same row, and the line break "\t\n" opens the next line.
<?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 was second line". " \ t ";
echo "Hi,pretty girl". " \ t ";
echo "\t\n";
?>

6. Using COM
If your PHP can open a COM module, you can use it to export Excel files

PHP code
<? Php
$filename = "C:/spreadhseet/test.xls";
$sheet 1 = 1;
$sheet 2 = "Sheet2″;
$excel _app = new COM ("Excel.Application") or Die ("Do 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 ("Do not Open $filename $Workbook");
$Worksheet = $Workbook->worksheets ($sheet 1);
$Worksheet->activate;
$excel _cell = $Worksheet->range ("C4″");
$excel _cell->activate;
$excel _result = $excel _cell->value;
Print "$excel _result\n";
$Worksheet = $Workbook->worksheets ($sheet 2);
$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

First, PHP import Excel

1: Still use Phpexcel, official website: http://www.codeplex.com/PHPExcel.

2: Using Php-excelreader,: Http://sourceforge.net/projects/phpexcelreader
Example:

PHP code
<?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";
}

?>

PHP Import Export Excel method