PHPExcel $objWriter->save() fails

Apache2PHPphpexcel

I am creating an Excel file based on records from a database with PHPExcel and am facing an odd problem when generating it on a large data set. I have narrowed it down to a single problem-causing line:

$objWriter->save('php://output');

It just spits out a blank file instead of the expected workbook. Also, the effect is similar if I try to save the result into a local file instead of redirecting to the browser – no file is ever created.

The kicker is that it works perfectly for a smaller data set (smaller = up to 200 rows, larger = up to 2000 rows, up to 20 columns in both cases). It also works fine on another (development) server with an EXACT SAME, 100% identical, large dataset.

I do have the save() call wrapped in a try-catch but no exception is raised. Executing memory_get_peak_usage() right before going for save() tells me that it was using 24MB, and I have ini_set('memory_limit', '-1') earlier in the script. Also, monitoring memory usage on the server while running the script does not show any significant increase either.

Any tips for debugging it?

Edit: there are no errors in apache logs, however I did notice that the relevant line in access logs says:

10.1.1.1 - - [01/Jun/2011:08:35:03 -0400] "GET /get_excel.php HTTP/1.1" 200 16386

The odd part is the last column – size. The file I actually get to download is always empty – exactly 0 bits, not 16386.

Best Answer

Based on the feedback you gave in the comments I think you do not meet all the requirements. You can find the list of the requirements here on the phpexcel website.

Related Solutions

PHPExcel Export for Larger Files fails

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++;
}

Php – TCPDF Memory Issue Allowed memory size of 134217728 bytes exhausted

Add this line in the your code

set_time_limit(0);

and this

ini_set('memory_limit', '-1');// its not the proper solution but it will do the trick.

Or

ini_set('memory_limit', '640M'); //Set Proper memory limit in your php file

Related Topic