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
Is it populating the worksheet? or saving? that you find too slow?
How are you populating the spreadsheet with the data?
If you're setting values for every individual cell in a sheet using
$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()
You've commented on applying styles to ranges of cells:
If you're using formulae in your workbook, when saving:
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.
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.