PHPExcel: Avoid Exceeding Maximum Execution Time

The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

Recognise this?

Fatal error: Maximum execution time of 20 seconds exceeded in…

Today’s little hint is on how one can go about avoiding hitting the dreaded maximum execution time fata error that PHP so loves spitting out at us while generating a PHPExcel excel worksheet.

So let’s go then.

As we know, generating an excel spreadsheet using PHPExcel is remarkably simple. The following code below shows you just how simple it really is:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Example');
$objPHPExcel->getActiveSheet()->setCellValue('A1','Hello World');

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

And that’s it, all done. However, generating a big Excel file takes a lot of time and memory and can quite often cause either Apache or PHP to simply timeout or run out of resources and spit a half-baked spreadsheet back at you.

Now to fix the memory allocation problem we can skip through by using PHP’s script cheat ini_set(‘memory_limit’, ‘-1′); which basically frees our script from the normal constraints in terms of memory usage and allocation enforced upon it.

In order to solve the maximum execution time exceeded problem we do pretty much the same thing.

Using the incredibly useful cheat of set_time_limit(20);, we basically identify the big looping structures that are involved in the generation of our spreadsheet and attach the time limit reset function inside each loop, basically now forcing the script to keep adding time to it’s execution timer and thus allowing it to essentially run forever until the loops complete their work.

In practise, you code would look like this:

foreach ($dataset as $dataitem)
{
    set_time_limit(20);
    //create the necessary worksheet cells for this particular item
}

And that is pretty much that. Implementing this approach should now free you from all those nasty, unexpected timeout fatal errors!