php - PHPExcel very slow - ways to improve?

Question

I am generating reports in .xlsx using PHPExcel. It was okay in the initial testing stages with small data sets (tens of rows, 3 sheets), but now when using it on a real production data with over 500 rows in each sheet, it becomes incredibly slow. 48 seconds to generate a file, and when running a report that combines more information, the whole thing fails with Fatal error: Maximum execution time of 30 seconds exceeded in PHPExcel/Worksheet.php on line 1041. Sometimes it's in another PHPExcel file, so I doubt the exact location is that relevant.

Ideally, I would want to speed it up somehow, if possible. If not, then at least increase the execution limit for this script.

The only suggestions I have seen so far was to style in ranges instead of individual cells. Unfortunately, I already do my styling in ranges and it is rather minimal too. Any other suggestions?

Answer 1

Is it populating the worksheet? or saving? that you find too slow?

How are you populating the spreadsheet with the data?

• Using the fromArray() method is more efficient than populating each individual cell, especially if you use the Advanced Value Binder to set cell datatypes automatically.

• If you're setting values for every individual cell in a sheet using

  $objPHPExcel->getActiveSheet()->setCellValue('A1',$x);
  $objPHPExcel->getActiveSheet()->setCellValue('B1',$y);
  

  use

  $sheet = $objPHPExcel->getActiveSheet();
  $sheet->setCellValue('A1',$x);
  $sheet->setCellValue('B1',$y);
  

  so that you're only accessing the getActiveSheet() method once; or take advantage of the fluent interface to set multiple cells with only a single call to $objPHPExcel->getActiveSheet()

  $objPHPExcel->getActiveSheet()->setCellValue('A1',$x)
                                ->setCellValue('B1',$y);
  

You've commented on applying styles to ranges of cells:

• You also have the option to use applyFromArray() to set a whole variety of style settings in one go.
• It's a lot more efficient if you can apply styles to a column or a row rather than simply to a range

If you're using formulae in your workbook, when saving:

• Use

  $objWriter->setPreCalculateFormulas(false)
  

  to disable calculating the formulae within PHPExcel itself.

Those are just a few hints to help boost performance, and there's plenty more suggested in the forum threads. They won't all necessarily help, too much depends on your specific workbook to give any absolutes, but you should be able to improve that slow speed. Even the little notebook that I use for development can write a 3 worksheet, 20 column, 2,000 row Excel 2007 file faster than your production server.

EDIT

If it was possible to simply improve the speed of PHPExcel itself, I'd have done so long ago. As it is, I'm constantly performance testing to see how its speed can be improved. If you want faster speeds than PHPExcel itself can give, then there's a list of alternative libraries here.