PHPExcel Export for Larger Files fails

Question

Hi All,

I am using PHPExcel library to generate excel data based on mysql datbase. MySql query results in the 1,34,000 rows. And Excel Supports 65,536 Rows on one worksheet. So made logic like

foreach($result as $value)
{
    $val = array_values($value);

    if($rowscounter < 65000)
    {
        $objPHPExcel->addRow($val,$rowscounter);
    }
    else
    {
        $active_sheet++;
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex($active_sheet);
        $rowscounter = 1;
    }
    $rowscounter++;
}
// deliver header   
header("Content-Type: $mtype; charset=" . $objPHPExcel->sEncoding);
header("Content-Type:application/octet-stream");
header("Content-Disposition: inline; filename=\"" . $filename . ".$ext\"");

// Save it as an excel 2003 file
$objWriter = IOFactory::createWriter($objPHPExcel,$objPHPExcel->sFileFormat);
//echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB";exit;
$objWriter->save('php://output');

to create new worksheet after 65000 records are reached for one woeksheet.

But it doesn't work; not giving any output or error. Initially I thought its because of memory limit. But when echoed it shows peak memory to 1400.5 MB and I have set my memory limit up to 3500MB using ini_set('memory_limit', '3500M');

Could you please suggest something or any alternative?

Answer 1

You might be exceeding the 65,000 limit, what do you set the $rowscounter initial value at? 1 or 0 (zero)? The reason I ask it that the array results starts at index 0 (zero), you're adding the row and then incrementing the counter after the add. So if you start the counter at 0 (zero) than you might have more rows that what you have counted. Also you are missing a row in the else statement, you loop through a value but don't add it to the sheet

try this

$rowscounter = 1;

foreach($result as $value)
{
    $val = array_values($value);

    if($rowscounter < 65000)
    {
        $objPHPExcel->addRow($val,$rowscounter);
    }
    else
    {
        $active_sheet++;
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex($active_sheet);
        $rowscounter = 1;

        // add missing row
        $objPHPExcel->addRow($val,$rowscounter);
    }
    $rowscounter++;
}
// deliver header   
header("Content-Type: $mtype; charset=" . $objPHPExcel->sEncoding);
header("Content-Type:application/octet-stream");
header("Content-Disposition: inline; filename=\"" . $filename . ".$ext\"");

// Save it as an excel 2003 file
$objWriter = IOFactory::createWriter($objPHPExcel,$objPHPExcel->sFileFormat);
//echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB";exit;
$objWriter->save('php://output');

a simple example of what I'm trying to explain, if $i is set to 0 (zero) the else condition is not met. So you will have an extra row in your results. If $i is set to 1 the else condition is met

$count = array(1,2,3,4,5,6,7,8,9,10);
$i=1; // set this to 0 (zero) and test, set to 1 and test

foreach($count as $cnt) {
    if($i < 10) {
        echo "If condition - Count value: ".$cnt." i value:".$i."<br />";
    } else {
        echo "Else condition - Count value: ".$cnt." i value:".$i."<br />";
    }
    $i++;
}