Performance issue in sheet creation.

Topics: Developer Forum
Aug 17, 2009 at 3:46 PM

Hi guys,

I'm doing some reports in PHPExcel, and one of them has to create a lot of sheets (about 120) and I noticed that this takes lots of time. More time in proportion than creating 10 or 20 sheets.

Every sheet contains about 200 lines of data and 15 rows in width.

When I push all the data on one sheet, performance is really fast. But this is not what the customer wants.

Is there some way I can speed up this process?

 

Developer
Aug 17, 2009 at 7:25 PM
Edited Aug 17, 2009 at 7:27 PM

Can you run the following test:

Take your generated file (containing 120 sheets). Do a plain save in MS Office Excel 2007 (or OpenOffice.org 3). Save the file as read.xls. Then run this code (plain read/write):

$tstartRead = time();

$file = 'read.xls';
$reader = PHPExcel_IOFactory::createReaderForFile($file);
$excel = $reader->load($file);

$tendRead = time();

$tstartWrite = time();

//$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
//$writer->save('write.xls');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
//$writer->setPreCalculateFormulas(false);
$writer->save('write.xlsx');

$tendWrite = time();
echo 'read took: ' . ($tendRead - $tstartRead) . ' seconds<br>';
echo 'write took: ' . ($tendWrite - $tstartWrite) . ' seconds<br>';
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
exit;



It will give you the fastest possible generation time that can be achieved with PHPExcel currently. If the "write" time it is lower than your current figures, then it means your code can be optimized. You can perhaps post the result here so we can discuss.