Any advice on how to optimize this code?

Topics: User Forum
Oct 15, 2010 at 1:43 AM

I get a "Allowed memory size of 33554432 bytes exhausted" error after my script has been running for about 15 seconds. I wil check if the apache/php timeout/limit settings can be changed, but anyways I will occationally have to render very big files. Efficiency is a key.

I am not very experienced in PHP and PHPEcxel, so my code may be cumbersome. In that case I hope someone can give me some advice on how it can be done more resource-friendly!

Here goes:

print '<pre>';

echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Tester");
$objPHPExcel->getProperties()->setCreator("John Doe")
	->setLastModifiedBy("John Doe")
	->setTitle("Test file");
	  
echo date('H:i:s') . " Set active sheet index\n";
$objPHPExcel->setActiveSheetIndex(0);


echo date('H:i:s') . " Set row 1 - headers\n";
$objPHPExcel->getActiveSheet()
	->setCellValue('A1', 'ClaimNo')
	->setCellValue('B1', 'Date Incident')
	->setCellValue('C1', 'Date Recieved')
	->setCellValue('D1', 'Date Processed')
	->setCellValue('E1', 'Days I->R')
	->setCellValue('F1', 'Days R->P')
	->setCellValue('G1', 'Name')
	->setCellValue('H1', 'District')
	->setCellValue('I1', 'Club')
	->setCellValue('J1', 'SportNo')
	->setCellValue('K1', 'Licence')
	->setCellValue('L1', 'Area')
	->setCellValue('M1', 'Side')
	->setCellValue('N1', 'Type')
	->setCellValue('O1', 'Sport')
	->setCellValue('P1', 'Location')
	->setCellValue('Q1', 'Surface')
	->setCellValue('R1', 'Status')
	->setCellValue('S1', 'Date Closed')
	->setCellValue('T1', 'Reserve')
	->setCellValue('U1', 'Paid')
	->setCellValue('V1', 'Paid Projection')
;


echo date('H:i:s') . " Set sheet title\n";
$objPHPExcel->getActiveSheet()->setTitle('Test file');


echo date('H:i:s') . " Starting iteration for cell population\n";
for ($i = 2; $i <= 2000; $i += 1) {

$celltype_plain_text = PHPExcel_Cell_DataType::TYPE_STRING;

echo date('H:i:s') . " Setting cell values for row $i\n";
$objPHPExcel->getActiveSheet()
		->setCellValueExplicit('A'.$i, '12345', $celltype_plain_text)
		->setCellValue('B'.$i, '2010-10-15')
		->setCellValue('C'.$i, '2010-10-15')
		->setCellValue('D'.$i, '2010-10-15')
		->setCellValue('E'.$i, '1,11')
		->setCellValue('F'.$i, '10,50')
		->setCellValueExplicit('G'.$i, 'John Doe', $celltype_plain_text)
		->setCellValueExplicit('H'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('I'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('j'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('K'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('L'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('M'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('N'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('O'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('P'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('Q'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValueExplicit('R'.$i, 'Lorem ipsum', $celltype_plain_text)
		->setCellValue('S'.$i, '2010-10-15')
		->setCellValue('T'.$i, '100,00')
		->setCellValue('U'.$i, '200,50')
		->setCellValue('V'.$i, '300,80')
;

echo date('H:i:s') . " Set cell formats for row $i\n";
$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->getStyle('C'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->getStyle('D'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->getStyle('E'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle('F'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle('G'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('H'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('I'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('J'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('K'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('L'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('M'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('N'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('O'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('P'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('Q'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('R'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('S'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
$objPHPExcel->getActiveSheet()->getStyle('T'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle('U'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle('V'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
  
}
		  

echo date('H:i:s') . " Starting iteration for all columns auto-size\n";
$columnID = 'A';
$lastColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
do {
	echo date('H:i:s') . " Set auto-size for column $columnID\n";
	$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
	$columnID++;
} while ($columnID != $lastColumn);
		
$objPHPExcel->getActiveSheet()->setTitle('Testing testing');

$objPHPExcel->setActiveSheetIndex(0);	  

// save to file

echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('test' . time() . '.xlsx');

// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

print '</pre>';
Oct 15, 2010 at 9:30 PM

RTFM! ;)

I solved my issues by:

  • Moving the cell formatting from each iteration to after the loop using cell ranges:

    $objPHPExcel->getActiveSheet()->getStyle('A2:A'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objPHPExcel->getActiveSheet()->getStyle('B2:D'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
    $objPHPExcel->getActiveSheet()->getStyle('E2:F'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
    $objPHPExcel->getActiveSheet()->getStyle('G2:R'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objPHPExcel->getActiveSheet()->getStyle('S2:S'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
    $objPHPExcel->getActiveSheet()->getStyle('T2:V'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

  • Caching using phpTemp for storage exceeding memory size:

    $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
     $cacheSettings = array( ' memoryCacheSize ' => '30MB'); 
     if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings)) die('PHPExcel caching error');

Generating the files still takes a lot of time, but that's no problem since this is a report-generator for internal use. The important part is that it works.

Thanks to the developers for a great product!