PHPExcel: Avoid Exceeding Maximum Execution Time CodeUnit 12 APR 2010

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/");
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->getActiveSheet()->setCellValue('A1','Hello World');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

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)
    //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!

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.

  • Mark Baker

    Chaining method calls can also improve speed.


    ->setCellValue(‘A1′,’Hello World’);

    As can setting styles using ranges and/or the applyFromArray methods

  • juratul

    thx cuyy..
    it works when i put the script “set_time_limit(0);” in my loop code

  • Thanks, you just helped me. I used set_time_limit(0) before start of the loop though.

  • Daniel

    Thank you!!!!

  • ar nguyenthinh

    Thanks a lot !

  • jorge_mt

    I got it to 240 but then it collapsed because of lack of memory. Alas!