Excel文件快速写入读取问题

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 ] : '&nbsp;' ) . '</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 ] : '&nbsp;' ) . '</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);
    }
}

标签: 无

发表评论: