Original author: Podcast on the Iceberg When I saw this article, I was very surprised by the patience of the original author. Although we use some in daily life, there are not all listed by the author. When writing excel, I used pear. Library, I have also used the pack header, and I have also used those that use smarty to simply replace xml, not to mention csv. hehe. (I won’t talk about the COM method. There are too many of them that are readable. I have also written articles about using wps to generate word, etc.)
But when I was reading, I only used one. I forgot what it was, so I had to go back and read the code. Because it is based on the principle of borrowing, I can't remember it.
Original address: http://xinsync.xju.edu.cn/index.php/archives/3858
Original content:
Recently, due to project needs, I need to develop a module to export some data in the system to Excel, modify it, and then import it back to the system. I took the opportunity to do some research on this, and here are some summaries.
Basically, there are two types of exported files:
1: Excel-like format. This is actually not an Excel file in the traditional sense. It is just because Excel has strong compatibility and can be opened correctly. After modifying this kind of file and then saving it, you will usually be prompted whether you want to convert it to an Excel file.
Advantages: Simple.
Disadvantages: It is difficult to generate the format. If it is used for import, you need to write the corresponding program yourself.
2: Excel format, corresponding to Excel-like, the file generated by this method is closer to the real Excel format.
If garbled characters appear when exporting Chinese, you can try to convert the string to gb2312. For example, the following converts $yourStr from utf-8 to gb2312:
$yourStr = mb_convert_encoding(”gb2312″, “UTF-8″, $yourStr);
Several methods are listed below in detail.
1. PHP export to Excel
1: The first recommendation is the extremely popular PHPExcel, official website: http://www.codeplex.com/PHPExcel
It can be imported and exported, and can be exported to office 2007 format, which is also compatible with 2003.
The downloaded package contains documents and examples, which you can study on your own.
Here is an example of copying a paragraph:
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 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 have received 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 objectn”;
$objPHPExcel = new PHPExcel();
// Set properties
echo date('H:i:s') . ” Set propertiesn”;
$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ī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 datan”;
$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 sheetn”;
$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 formatn”;
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
// Echo done
echo date('H:i:s') . ” Done writing file.rn”;
2. Use pear’s Spreadsheet_Excel_Writer class download address: http://pear.php.net/package/Spreadsheet_Excel_Writer
This class depends on OLE, download address: http://pear.php.net/package/OLE
It should be noted that the format of the exported Excel file is relatively old. If you save it after modification, you will be prompted whether to convert it to a newer format.
But you can set the format, which is 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: Use smarty to generate XML or HTML file support formats that comply with Excel specifications, which is a perfect export solution. However, the exported file is essentially an XML file. If it is used for import, it needs to be processed separately.
For details, please see the post of rardge hero: http://bbs.chinaunix.net/viewthread.php?tid=745757
It should be noted that if the number of rows in the exported table is uncertain, it is best to put "ss:" in the template: Delete things like ExpandedColumnCount=”5″ ss:ExpandedRowCount=”21″”.
4. Use the pack function to print out the sentence break symbols that simulate the Excel format. This is closer to the Excel standard format. After being modified and saved in Office 2003, no prompt will pop up. This method is recommended.
The disadvantage is that it has 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 two: ");
xlsWriteLabel(2,1,"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, the author failed when using it on a 64-bit Linux system, and all the sentence-breaking symbols became garbled characters.
5. How to use tabs and line breaks
The tab character "t" allows users to separate columns in the same line, and the newline character "tn" can open 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 “tn”;
/*start of second line*/
echo “this is second line”.”t”;
echo “Hi, pretty girl”.”t”;
echo “tn”;
?>
6. Use com
If your PHP can open the com module, you can use it to export Excel files
PHP code
<?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_resultn”;
$Worksheet = $Workbook->Worksheets($sheet2);
$Worksheet->activate;
$excel_cell = $Worksheet->Range(”C4″);
$excel_cell->activate;
$excel_result = $excel_cell->value;
print “$excel_resultn”;
#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
1. PHP import into Excel
1: Still use PHPExcel, official website: http://www.codeplex.com/PHPExcel .
2: Use PHP-ExcelReader, download address: 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”;
}
?>
----------
This site adopts the Creative Commons copyright agreement, which requires attribution, non-commercial and consistency. This site welcomes any non-commercial reprint, but must indicate that it comes from "Uncle Fatty's Simple Life", retain the original link, and must also indicate the original title and Link.