PHPExcel 内存问题 - 还有更多想法吗? [英] PHPExcel Memory Problems - any more ideas?

问题描述

我正在尝试使用 Zend Framework 和 PHPExcel 编写 Excel2007 文件.我知道我可以增加 PHP 内存限制..但是没有其他方法吗?

到目前为止，这是我的代码，它适用于 5000 行和 77 列，但我需要 12000 行:-) 内存设置为 128MB

 $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite;PHPExcel_Settings::setCacheStorageMethod($cacheMethod);$db = Zend_Db_Table_Abstract::getDefaultAdapter();$phpExcel = new PHPExcel();//设置标题$select = $db->select();$select->from('IMPORT')->limit(1);$data = $select->query()->fetchAll();$columns = array_keys($data[0]);$phpExcel->setActiveSheetIndex(0);$colCNT = 1;foreach ($columns 作为 $column) {$letter = Nc_Utils::getNameFromNumber($colCNT);$phpExcel->getActiveSheet()->SetCellValue($letter .'1', $column);$colCNT++;}未设置($选择)；未设置($数据)；$sql = 'SELECT * FROM IMPORT LIMIT 7000';$stmt = $db->query($sql);$rowCNT = 2;while($rows = $stmt->fetch()){$phpExcel->getActiveSheet()->fromArray($rows, null, 'A'.$rowCNT);$rowCNT++;}未设置($rowCNT)；未设置($选择)；未设置($db)；//内存使用量 = 4 MB！$phpExcelWrite = new PHPExcel_Writer_Excel2007($phpExcel);$phpExcelWrite->setUseDiskCaching(true);$phpExcelWrite->save(str_replace('.php', '.xls', __FILE__));

<块引用>

PHP 致命错误:允许的内存大小为 134217728 字节已用完(试图分配 13878925 个字节)在/Applications/MAMP/htdocs/phpexcel/library/PHPExcel/Shared/XMLWriter.php在线 102

解决方案

我曾尝试使用 PhpExcel 但发现与您相同的问题.我已经尝试了所有减少内存消耗的建议(Klinky 在他的回答中发布的链接)，但只有 25-30% 的改进.我什至想过创建 CSV 输出而不是 excel.

现在我正在使用 PEAR 的 Spreadsheet_Excel_Writer，它对我来说效果很好，大致相同数据量(11k 行 62 列)，虽然生成文件需要相当长的时间.

它不如 PhpExcel 强大，但正如我在您的示例中看到的，您没有使用它来设置单元格样式和编写函数，对吗?

示例:

$workbook = new Spreadsheet_Excel_Writer();$workbook->send($filename .'.xls');$workbook->setVersion(8);//优秀 8//创建工作表$工作表=&$workbook->addWorksheet('submissions');$worksheet->setInputEncoding('UTF-8');//在 Sheet 1 上写一些数据$行= 0;$worksheet->write($row, 0, '海报#ID');$worksheet->write($row, 1, 'Surname');$worksheet->write($row, 2, 'Firstname');$worksheet->write($row, 3, 'Country');$worksheet->write($row, 4, 'E-mail');$worksheet->write($row, 5, '所有作者');$worksheet->write($row, 6, '机构');$worksheet->write($row, 7, '抽象主题');$worksheet->write($row, 8, '抽象标题');foreach ($records as $rec_id => $rec_data) {$行++;$worksheet->write($row, 0, 'P-' . $rec_data['id']);$worksheet->write($row, 1, $rec_data['submitter_surname']);$worksheet->write($row, 2, $rec_data['submitter_firstname']);$worksheet->write($row, 3, $rec_data['submitter_country']);$worksheet->write($row, 4, $rec_data['submitter_email']);$worksheet->write($row, 5, $rec_data['authors']);$worksheet->write($row, 6, $rec_data['institutions']);$worksheet->write($row, 7, $rec_data['abstract_topic']);$worksheet->write($row, 8, $rec_data['abstract_title']);}$workbook->close();//输出

Iam trying to write an Excel2007 File with Zend Framework and PHPExcel. I know i could increase the PHP Memory Limit.. but is there no other way?

This is my Code so far, it works well with 5000 Rows and 77 Columns but i need 12000 rows :-) Memory is set to 128MB

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite;
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

    $db = Zend_Db_Table_Abstract::getDefaultAdapter();
    $phpExcel = new PHPExcel();

    // set headers
    $select = $db->select();
    $select->from('IMPORT')->limit(1);
    $data = $select->query()->fetchAll();
    $columns = array_keys($data[0]);
    $phpExcel->setActiveSheetIndex(0);
    $colCNT = 1;

    foreach ($columns as $column) {
        $letter = Nc_Utils::getNameFromNumber($colCNT);
        $phpExcel->getActiveSheet()->SetCellValue($letter . '1', $column);
        $colCNT++;
    }

    unset($select);
    unset($data);

    $sql = 'SELECT * FROM IMPORT LIMIT 7000';
    $stmt = $db->query($sql);

    $rowCNT = 2;
    while($rows = $stmt->fetch()){
        $phpExcel->getActiveSheet()->fromArray($rows, null, 'A'.$rowCNT);
        $rowCNT++;
    }  

    unset($rowCNT);
    unset($select);
    unset($db);

    // MEMORY USAGE = 4 MB!

    $phpExcelWrite = new PHPExcel_Writer_Excel2007($phpExcel);
    $phpExcelWrite->setUseDiskCaching(true);
    $phpExcelWrite->save(str_replace('.php', '.xls', __FILE__));

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 13878925 bytes) in /Applications/MAMP/htdocs/phpexcel/library/PHPExcel/Shared/XMLWriter.php on line 102

解决方案

I have tried to use PhpExcel but found out the same problems as you. I've tried all the proposals to reduce memory consumption (the link Klinky posted in his answer) but got only 25-30% improvement. I even thought about creating CSV output instead of excel.

Now I am using PEAR's Spreadsheet_Excel_Writer and it works fine for me with roughly the same amount of data (11k rows 62 columns), though it takes quite some time to generate the file.

It is not as powerful as PhpExcel, but as I see in your example you are not using it to style cells and write functions, right?

Example:

$workbook = new Spreadsheet_Excel_Writer();
$workbook->send($filename . '.xls');
$workbook->setVersion(8); // excel 8
// Create worksheet
$worksheet =& $workbook->addWorksheet('submissions');
$worksheet->setInputEncoding('UTF-8');
// Write some data on Sheet 1
$row = 0;
$worksheet->write($row, 0, 'Poster #ID');
$worksheet->write($row, 1, 'Surname');
$worksheet->write($row, 2, 'Firstname');
$worksheet->write($row, 3, 'Country');
$worksheet->write($row, 4, 'E-mail');
$worksheet->write($row, 5, 'All authors');
$worksheet->write($row, 6, 'Institutions');
$worksheet->write($row, 7, 'Abstract topic');
$worksheet->write($row, 8, 'Abstract title');
foreach ($records as $rec_id => $rec_data) {
    $row++;
    $worksheet->write($row, 0, 'P-' . $rec_data['id']);
    $worksheet->write($row, 1, $rec_data['submitter_surname']);
    $worksheet->write($row, 2, $rec_data['submitter_firstname']);
    $worksheet->write($row, 3, $rec_data['submitter_country']);
    $worksheet->write($row, 4, $rec_data['submitter_email']);
    $worksheet->write($row, 5, $rec_data['authors']);
    $worksheet->write($row, 6, $rec_data['institutions']);
    $worksheet->write($row, 7, $rec_data['abstract_topic']);
    $worksheet->write($row, 8, $rec_data['abstract_title']);
}
$workbook->close(); // output

这篇关于PHPExcel 内存问题 - 还有更多想法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！