This project has moved. For the latest updates, please go here.
1
Vote

Out of memory when writing huge file

description

Hi all.
I create file like:
 
<?
ini_set('memory_limit','512M');
...
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTe$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '32MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
 
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(' ... ')
->setTitle(' ... ')
->setDescription(' ... '));
 
$objPHPExcel->getActiveSheet()->getStyle('3')->getFont()->setBold(true);
 
//$columns_meta - array with meta information about columns (title, width, etc.)
foreach(self::$columns_meta as $col => $meta) {
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($meta['num']-1))->setWidth($meta['width']);
$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($meta['num']-1) . '3', $meta['title']);
$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($meta['num']-1) . '4', $meta['num']);
}
 
$row = 1;
 
// count($recordList) ~ 3000
foreach ($recordList as $rec) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row , $rec-> ... );
...
$objPHPExcel->getActiveSheet()->setCellValue('AK' . $row, $rec-> ... );
 
++$row;
}
echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB"; // 217.25
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setUseDiskCaching(true, '/path/to/temp/');
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('fullFileName.xlsx');
...
?>
 
And have "Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 84 bytes) in /path/to/PHPExcel/Classes/PHPExcel/CachedObjectStorage/CacheBase.php on line 155"
 
I use PHPExcel 1.7.7.
 
How can I solve this problem?

comments

mdryan wrote Jun 15, 2012 at 7:59 AM

Your memory limit is still set to 256M, which means your ini.set is having no effect. Try changing the memory limit in php.ini directly.

You can reduce the memory usage significantly by using

$objPHPExcel->getActiveSheet()->fromArray($data, null, "A$row");

where $data is a 2D array of data to dump. Whether this is better a line at a time or the whole 80k cells probably depends on exactly how you're getting it out of the database and how much processing is required for each record. Generally though I've found that outputting a line at a time takes longer but use less memory.

Dukat wrote Jun 15, 2012 at 10:14 AM

I add ini_set('memory_limit','512M'); to CacheBase.php and ini_set('max_execution_time','60'); to Worksheet.php , it works.

With

$data[] = $rec-> ...;
...
$objPHPExcel->getActiveSheet()->fromArray($data, null, "A$row", true);

already before "echo 'Peak memory usage: ' ..." i get "Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 74 bytes) in /path/to/PHPExcel/Classes/PHPExcel/CachedObjectStorage/PHPTemp.php on line 68" ...

gigtech wrote Jul 17, 2012 at 9:02 PM

Try this as well:
  • Assign $objPHPExcel->getActiveSheet() to an object which can be called
  • Use Explicit Cell Values
  • Style cells using a set of which can be applied to specific areas with rules
  • If you are creating multiple sheets then save each sheet separately and then combine them once processing is complete.

wrote Feb 22, 2013 at 2:01 AM