PHPExcel 是一个处理Excel,CVS文件的开源框架,基于微软的OpenXML标准。可以使用它来读取、写入不同格式的电子表格,是PHP至今最通用的Excel处理工具,但它有一个非常致命的缺点: 特别占内存,对大批量效率低。PHPExcel官方已不再维护了这个项目了。
官方团队在github上又起了一个新项目,叫PhpSpreadsheet,使用了大量的php新特性,性能也比PHPExcel高了不少,不过该项目还是开发状态,还不稳定,bug会比较多。
使用以下替代方案
XLS/XLSX写入方案
利用PHP_XLSXWriter写入
https://github.com/mk-j/PHP_XLSXWriter
利用simplexlsx读取XLSX文件
https://github.com/shuchkin/simplexlsx
利用phpExcelReader读取XLS文件
https://github.com/derhasi/phpExcelReader
还有spreadsheet-reader可以解决 XLSX, ODS 和 text/CSV的读取问题
https://github.com/nuovo/spreadsheet-reader
PHP_XLSXWriter例子:
include_once("xlsxwriter.class.php");
$header = array(
'c1-text'=>'string',//text
'c2-text'=>'@',//text
'c3-integer'=>'integer',
'c4-integer'=>'0',
'c5-price'=>'price',
'c6-price'=>'#,##0.00',//custom
'c7-date'=>'date',
'c8-date'=>'YYYY-MM-DD',
);
$rows = array(
array('x101',102,103,104,105,106,'2018-01-07','2018-01-08'),
array('x201',202,203,204,205,206,'2018-02-07','2018-02-08'),
array('x301',302,303,304,305,306,'2018-03-07','2018-03-08'),
array('x401',402,403,404,405,406,'2018-04-07','2018-04-08'),
array('x501',502,503,504,505,506,'2018-05-07','2018-05-08'),
array('x601',602,603,604,605,606,'2018-06-07','2018-06-08'),
array('x701',702,703,704,705,706,'2018-07-07','2018-07-08'),
);
$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', $header);
foreach($rows as $row)
$writer->writeSheetRow('Sheet1', $row);
//$writer->writeSheet($rows,'Sheet1', $header);//or write the whole sheet in 1 call
$writer->writeToFile('xlsx-simple.xlsx');
simplexlsx例子:
require_once __DIR__ . '/simplexlsx.class.php';
if ( $xlsx = SimpleXLSX::parse('countries_and_population.xlsx')) {
echo '<table cellpadding="10">
<tr><td valign="top">';
// output worsheet 1
list( $num_cols, $num_rows ) = $xlsx->dimension();
echo '<h1>Sheet 1</h1>';
echo '<table>';
foreach ( $xlsx->rows( 1 ) as $r ) {
echo '<tr>';
for ( $i = 0; $i < $num_cols; $i ++ ) {
echo '<td>' . ( ( ! empty( $r[ $i ] ) ) ? $r[ $i ] : ' ' ) . '</td>';
}
echo '</tr>';
}
echo '</table>';
echo '</td><td valign="top">';
// output worsheet 2
list( $num_cols, $num_rows ) = $xlsx->dimension( 2 );
echo '<h1>Sheet 2</h1>';
echo '<table>';
foreach ( $xlsx->rows( 2 ) as $r ) {
echo '<tr>';
for ( $i = 0; $i < $num_cols; $i ++ ) {
echo '<td>' . ( ( ! empty( $r[ $i ] ) ) ? $r[ $i ] : ' ' ) . '</td>';
}
echo '</tr>';
}
echo '</table>';
echo '</td></tr></table>';
} else {
echo SimpleXLSX::parse_error();
}
phpExcelReader例子:
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('jxlrwtest.xls');
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";
}
spreadsheet-reader例子:
$Reader = new SpreadsheetReader('example.xlsx');
$Sheets = $Reader -> Sheets();
foreach ($Sheets as $Index => $Name)
{
echo 'Sheet #'.$Index.': '.$Name;
$Reader -> ChangeSheet($Index);
foreach ($Reader as $Row)
{
print_r($Row);
}
}