exporting large files to excel using PHPExcel

Question

I am trying to export around 40,000 rows of Mysql data in PHP(Laravel4) using PHPExcel library. Below is my code:

($patList is an array of result columns)

set_time_limit ( 3000 );

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;

$cacheSettings = array( 'memoryCacheSize' => -1);

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objPHPExcel = new PHPExcel();
$i = 1;
$patList = $result[0];

for ($r = 0; $r < count($patList); $r++) {

    $objPHPExcel->setActiveSheetIndex(0)

         ->setCellValue("A$i", $patList[$r][0])
         ->setCellValue("B$i", $patList[$r][1])
         ->setCellValue("C$i", $patList[$r][2])
         ->setCellValue("D$i", $patList[$r][1])
         ->setCellValue("E$i", $patList[$r][2])
         ->setCellValue("F$i", $patList[$r][1])
         ->setCellValue("G$i", $patList[$r][2])
         ->setCellValue("H$i", $patList[$r][2])
         ->setCellValue("I$i", $patList[$r][1])
         ->setCellValue("J$i", $patList[$r][2])
         ->setCellValue("K$i", $patList[$r][5]);
     $i++;
}

$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');

header('Content-Type: application/vnd.ms-excel'); 

header('Content-Disposition: attachment;filename="result.xls"');

header('Cache-Control: max-age=0');

ob_clean();

flush();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

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

The above code runs fine if there are 3-4 columns in the excel and 15,000 rows. However, if I increase the no. of rows to 30,000 or the no. of columns to 10, the excel doesn't get generated.

Are you getting any errors? Is error reporting enabled? Make sure your script doesn't run out of memory, because you are keeping pretty big variables in memory. — Bogdan
– Bogdan, Commented Oct 24, 2014 at 9:52
Try setting ini_set('memory_limit', '1024M'); or whatever you feel would be enough memory for the script. — Bogdan
– Bogdan, Commented Oct 24, 2014 at 9:55
Yes that's working now! It was consuming 228 MB Memory which is why it was failing. Thanks a lot:) — pallavi-Mindfire Solutions
– pallavi-Mindfire Solutions, Commented Oct 24, 2014 at 12:03

Mark Baker · Accepted Answer · 2014-10-24 11:53:29Z

1

$cacheSettings = array( 'memoryCacheSize' => -1);

isn't sensible.... I don't even know if using a value of -1 will work; but if it does, it will mean that you're storing everything in memory and nothing in php://temp

The memoryCacheSize value tells the cache stream how much data should be stored in memory before switching data out to php://temp ; so

$cacheSettings = array( 'memoryCacheSize' => '8MB');

would tell the cache stream to use 8MB of memory, and then if additional data needed to be stored to use php://temp instead

answered Oct 24, 2014 at 11:53

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pallavi-Mindfire Solutions Over a year ago

Oh, that's helpful. I had increased the cache size also to 500M, still it was failing. Because the memory limit itself was not sufficient. So, now after I set the memory_limit to 500M or 1024M, it works !

Collectives™ on Stack Overflow

exporting large files to excel using PHPExcel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related