php导出导入excel格式数据文件的几种方法
模块需求：php将数据导成excel格式供编辑修改后再导入数据库
一、导出
1.PHPExcel (导入导出均可)
官网：http://phpexcel.codeplex.com/
下载：http://phpexcel.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=10718
Requirements
* PHP version 5.2.0 or higher
* PHP extension php_zip enabled *)
* PHP extension php_xml enabled
* PHP extension php_gd2 enabled (if not compiled in)
PHPExcel currently features:
* Create an in-memory spreadsheet representation
* Set spreadsheet meta data (author, title, description, …)
* Add worksheets to spreadsheet
* Add data and formulas to individual cells
* Merge cells
* Protect ranges of cells with a password
* Supports setting cell width and height
* Supports different fonts and font styles
* Supports formatting, styles, cell borders, fills, gradients, …
* Supports hyperlinks
* Supports different data types for individual cells
* Supports cell text wrapping
* Supports conditional formatting
* Supports column auto-sizing
* Supports rich-text strings
* Supports autofilter
* Supports “freezing” cell panes
* Supports cell-level security
* Supports workbook-level security
* Supports worksheet-level protection
* Group rows/columns
* Cell data validation
* Insert/remove rows/columns
* Named ranges
* Worksheet references
* Calculate formula values
* Add comments to a cell
* Add images to your spreadsheet
* Set image styles
o Positioning
o Rotation
o Shadow
* Set printing options
o Header
o Footer
o Page margins
o Paper size
o orientation
o Row and column breaks
o Repeat rows at header / columns at left
o Print area
* Output your spreadsheet object to different file formats
o Excel 2007 (spreadsheetML)
o BIFF8 (Excel 97 and higher)
o PHPExcel Serialized Spreadsheet
o CSV (Comma Separated Values)
o HTML
o PDF
* Read different file formats into your spreadsheet object
o Excel 2007 (spreadsheetML)
o BIFF5 (Excel 5.0 / Excel 95), BIFF8 (Excel 97 and higher)
o PHPExcel Serialized Spreadsheet
o Excel 2003 XML format
o Symbolic Link (SYLK)
o CSV (Comma Separated Values)
* … and lots of other things!
2.使用pear的Spreadsheet_Excel_Writer类
下载地址： http://pear.php.net/package/Spreadsheet_Excel_Writer
此类依赖于OLE,下载地址：http://pear.php.net/package/OLE
注意：导出的Excel文件格式比较老，修改后保存会提示是否转换成更新的格式。不过可以设定格式，很强大。
<?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.利用smarty，生成符合Excel规范的XML或HTML文件
支持格式，非常完美的导出方案。不过导出来的的本质上还是XML文件，如果用来导入就需要另外处理了。
参考：http://bbs.chinaunix.net/viewthread.php?tid=745757
注意：如果导出的表格行数不确定时，最好在模板中把”ss:ExpandedColumnCount=”5″ ss:ExpandedRowCount=”21″”之类的东西删掉。
4.利用pack函数打印出模拟Excel格式的断句符号，这种更接近于Excel标准格式，用office2003修改后保存，还不会弹出提示，推荐用这种方法。
缺点是无格式。
<?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”, 0x0A, 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;
}
?>
笔者在64位linux系统中使用时失败了，断句符号全部变成了乱码。
5.使用制表符、换行符的方法
制表符”\t”用户分割同一行中的列，换行符”\t\n”可以开启下一行。
<?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.使用com
如果你的PHP可以开启com模块，就可以用它来导出Excel文件
<?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);
?>
例子： http://blog.chinaunix.net/u/16928/showart_387171.html
二、导入
1.PHPExcel
2.PHP-ExcelReader,
地址: http://sourceforge.net/projects/phpexcelreader
本文来源：http://www.niutian365.com/blog/article.asp?id=331
