Memory limit exhausted

Topics: User Forum
May 12, 2010 at 10:13 AM

Hi,

Using PHPExcel 1.7.2 I have to export "big" amount of data (Up to 3 sheets of each about 8000 rows of up to 14 columns each).  My current memory_limit is set to 128M and do not seem enough. 

Here is how I do something like the following (a little bit simplified to avoid overhead):

 

$objPHPExcel = new PHPExcel();

// for each sheet I do the following

$currentSheet = $objPHPExcel->createSheet();
$currentSheet->setTitle('My title');
// then I set the headers for all columns
$currentSheet->SetCellValue($col++.'1', 'id');
$currentSheet->SetCellValue($col++.'1', 'name');
// etc...
// then I loop through resources fetched from DB into $resource_list
$row = 2;
foreach ( $resource_list as $res ) {
$col = 'A';
foreach ( $res AS $data ) {
if ( !empty($data) ) {
$currentSheet->SetCellValue($col . $row, $data);
}
$col++;
}

$row++;
}

// end of the loop done to fill in all the sheets (up to 3 sheets)

// then I adjust size of all columns on each sheet (assuming sheetIndex is the index of latest sheet)
for ($sheet = 0; $sheet < $sheetIndex; $sheet++ ) {
$objPHPExcel->setActiveSheetIndex($sheet);
// sets size of colums depending on their content
$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn(); //e.g., 'G'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6

for ($col = 0; $col < $highestColumnIndex; $col++) {
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true);
}
}

// finally...
// I set back first page as active sheet
$objPHPExcel->setActiveSheetIndex(0);

// and I start the download of the file
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");

header("Pragma: public");
header("Expires: 0");

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="filename.xls"');

header("Cache-Control: max-age=0, must-revalidate, post-check=0, pre-check=0");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');




I'm not sure it's the best way to create excel sheets with PHPExcel but I'm currently stuck with this problem so any help or hint will be appreciated.

Alternatively I was thinking about using a batch process to write up the file by iterations of 1000 rows before starting the download of the "concatenated" file.
Is this a a good solution ? Is it possible to add 1000 rows to an existing sheet without loading it (as it will not help resource usage...)
Thanks for any help :)
Regards,
Seb

 

Coordinator
May 15, 2010 at 12:14 AM

The next release (which should be coming to a download near you early next week) includes a number of features to reduce the memory footprint, including cell caching. This allows PHPExcel to work with much larger workbooks, because the cell objects themselves can be configured to be held on disk or APC or as smaller serialized values rather than taking up all the php memory