PHP Excel Out of Memory

Sep 2, 2010 at 10:44 PM

Trying to write a spreadsheet that will have 28,000 rows or more.  Getting an out of memory error.  The "largescale" test works just fine and dumps more rows than that, so I don't know what the issue is.  Can anyone see what below would cause this to crash?  I am simply looping through SQL results and writing out the data.

$rownum = 1;
$cummulative_donors = 0;

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue("A$rownum", 'Zip Code');
$objPHPExcel->getActiveSheet()->setCellValue("B$rownum", 'State');
$objPHPExcel->getActiveSheet()->setCellValue("C$rownum", 'City');
$objPHPExcel->getActiveSheet()->setCellValue("D$rownum", 'Donors');
$objPHPExcel->getActiveSheet()->setCellValue("E$rownum", 'Residents');
$objPHPExcel->getActiveSheet()->setCellValue("F$rownum", 'Donations');
$objPHPExcel->getActiveSheet()->setCellValue("G$rownum", 'Total Revenue');
$objPHPExcel->getActiveSheet()->setCellValue("H$rownum", 'Predicted Donors');
$objPHPExcel->getActiveSheet()->setCellValue("I$rownum", 'Donors less Predicted');

$this->positionFirst();

while ($row = $this->nextResult()){
    $rownum += 1;
    $newdata = $row;

    $act_pred_diff = $newdata['DONORS'] - $newdata['PROJDONOR'];

    $cummulative_donors += $newdata['DONORS'];

    $objPHPExcel->getActiveSheet()->setCellValue("A$rownum", $newdata['ZIPCOD']);
    $objPHPExcel->getActiveSheet()->setCellValue("B$rownum", $newdata['STATE']);
    $objPHPExcel->getActiveSheet()->setCellValue("C$rownum", $newdata['CITY']);
    $objPHPExcel->getActiveSheet()->setCellValue("D$rownum", $newdata['DONORS']);
    $objPHPExcel->getActiveSheet()->setCellValue("E$rownum", $newdata['RESID']);
    $objPHPExcel->getActiveSheet()->setCellValue("F$rownum", $newdata['DONATIONS']);
    $objPHPExcel->getActiveSheet()->setCellValue("G$rownum", $newdata['AMOUNT']);
    $objPHPExcel->getActiveSheet()->setCellValue("H$rownum", $newdata['AVERAGE']);
    $objPHPExcel->getActiveSheet()->setCellValue("I$rownum", $newdata['PROJDONOR']);
    $objPHPExcel->getActiveSheet()->setCellValue("J$rownum", $act_pred_diff);
    $objPHPExcel->getActiveSheet()->setCellValue("K$rownum", $cummulative_donors);
}

$this->output("Writing to spreadsheet...");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$this->output("Complete!");

Coordinator
Sep 2, 2010 at 11:08 PM

I've written on this several times before:

28,000 rows and 11 columns = 308,000 cells,  with an average memory requirement of 1k/cell (dependent on string content), and double that figure when writing, plus a code footprint of 10MB-25MB, gives a memory requirement of 308MB * 2 + 25 = 641MB.

The 06largescale.php test has 5 columns/row: I don't know what you've increased it to, but the distributed version is 5,000 rows, giving 25,000 cells.... 25MB * 2 + 25 = 75MB.

Try using cell caching, or increasing your memory, or both.