memory exeeds while creating big xls-file

Topics: Developer Forum, User Forum
Aug 28, 2012 at 8:52 AM

Hi there,

I encounter some problems while creating a big xls(5)-file.
After a while I receive a 500-error. No error-info is written to the logfile.
Sometimes I get a timeout or memory exeeded error.

What I am doing:
Doing some SQL-querys to get some data. The data will be approx. 5000 rows long and about 50 columns wide. There will be numbers and text in the xls-doc.

printing the data with print_r() will output the data within 10 seconds.

Putting this data into an excel-doc will fail (see above).

Almost every cell is styled with an format-information

$xls->getActiveSheet()->getStyle('A1')->applyFromArray($style['borders'])

Are there any options to write data direct to harddrive, and therefore no memory errors will occur. How is it done creating big xls-files? Am I doing something wrong.

See below some snippets of my code.

require_once '../PHPExcel.php';
$xls = new PHPExcel();
$xls->getProperties()->setCreator("mycreator")
 ->setLastModifiedBy("mycreator")
 ->setTitle("title")
 ->setSubject("title")
 ->setDescription("title")
 ->setKeywords("title");
$xls->setActiveSheetIndex(0);
$xls->getActiveSheet()->setTitle('title');	
$orientation = array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
);

[...]

$xls->getActiveSheet()->mergeCells('B1:B2');
$xls->getActiveSheet()->getStyle('B1')->applyFromArray($style['bold'])->getAlignment()->applyFromArray($orientation);
$xls->getActiveSheet()->setCellValueByColumnAndRow(1, 1, $months[$month-1].' '.$year);

[...]

$xls->getActiveSheet()->getStyle($cols[$col].$row)->applyFromArray($style['normal'])->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$xls->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $number);

[...]

for ($i=0;$i<$breite;$i++) $xls->getActiveSheet()->getColumnDimension($cols[$i])->setAutoSize(true);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myfile.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($xls, 'Excel5');
$objWriter->save('php://output');

Thanks a lot for your help
Jens