Out of Memory Loading Multiple CSV Files

Topics: User Forum
Nov 23, 2009 at 7:39 PM
Edited Nov 23, 2009 at 7:40 PM

I am running the code below to combine multiple CSV files into 1 Excel file with multiple tabs. I am not doing any styling or other advanced operations. However, I am running into a PHP memory limit of 512MB when I have multiple sheets with a few thousand rows each. I have read other posts regarding PHPExcel's memory utilization issues. Is there an optimization I could make to the code below to help my issue? Possibly write to disk after each tab or something similar. Thanks.

 


$sheetIdx = 0; $excel = new PHPExcel(); foreach($csvFiles as $csvFile) { if($sheetIdx > 0) { $excel->createSheet(); } $excel->setActiveSheetIndex($sheetIdx); $excel->getActiveSheet()->setTitle('Report'.$sheetIdx); $reader = PHPExcel_IOFactory::createReaderForFile($csvFile); $reader->setSheetIndex($sheetIdx); $reader->loadIntoExisting($csvFile, $excel); $sheetIdx++; } $excel->setActiveSheetIndex(0); $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5'); $writer->save($xlsFile);

 

Developer
Nov 24, 2009 at 6:06 AM

The problem is that PHPExcel uses too much memory per cell. Around 1KB per cell. There is some indication that this can be cut down to around one half ~ 500B by introducing a cell supervisor. This is the next thing on the to-do list for the weeks to come. Once this is in place it will be easier to introduce automatic disk caching like you are suggesting. And this would nearly wipe out all memory usage by PHPExcel.