PHPExcel Performance: Extreme Execution Time

May 30, 2008 at 6:03 PM
Edited May 30, 2008 at 6:04 PM
I'll want to use PHPExcel to generate XLSX files from MySQL results. That part works fine and dandy, but the issue I've got is the execution time needed to create a large file. To test it I'm generating an array that simulates a MySQL result object and feeding that to PHPExcel. Using something like 10 columns by 100 or 1000 rows doesn't take a great deal of time, although it's quite a bit longer than the PEAR Excel option I've been using. While that's not really a huge deal the attraction of PHPExcel is it's ability to create a XLSX file containing more than 65,000 rows. The problem: trying to generate a 50 column by 75,000 row spreadsheet with up to 10 characters per cell take an enormous amount of time to generate.

Server Specs: 2x Dual Core AMD with 16GB Ram and 10000 rpm scsi drives.

Array object is built using:

$a = 50; //cols
$b = 75000; // rows
$result = array();
for($i=1;$i<=$b;$i++) {
    for($j=1;$j<=$a;$j++) {
        $result['Row'.$i.'']['Col '.$j.'']="data $j";
    }
}


Object is parsed by PHPExcel using:

$r=2; // Set row pointer
foreach ($result AS $row) {
    $c=0; // Set column pointer
    foreach ($row AS $cell) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c,$r,$cell);
    $c++;   
    }
   
$r++;   
}

Again, this is for simulation/proof of concept only at the moment, using an actual query I can't get past 1300 rows before it blows up but that could be for any number of other reasons. I have successfully generated a 30K row file using the above method but that took almost 5 minutes to create.  I'm up to 10 minutes on PHP's maximum execution limit and still can't create the 75K row file. Any ideas?
Coordinator
Jun 2, 2008 at 5:53 AM
This is a known issue, based on PHP's internal memory structure regarding objects. We are still searching for a good, easy-to-implement solution/workaround for this.
Coordinator
Jun 3, 2008 at 3:39 PM
Putting you read and write within the same loop might help.


$a = 50; //cols
$b = 75000; // rows
$r=2; // Set row pointer
for($i=1;$i<=$b;$i++) {
    $c=0; // Set column pointer
    for($j=1;$j<=$a;++$j) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c++,$r,"data $j");
    }
   $r++;   
}