Fatal error: Out of memory (allocated 1979711488)

Question

I have integrating xlsx file for writing from database using phpexcel. I want to write 3,00,000 records in xlsx file. But it till through Fatal error: Out of memory (allocated 1979711488) (tried to allocate 131072 bytes)

My PHP Version 5.3.28

Also i set php ini and cell cache see my code below

ini_set('max_execution_time',-1);
ini_set('memory_limit', '-1'); 
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_in_memory_gzip;
$cacheSettings = array( ' memoryCacheSize ' => '-1');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

I'm looking forward for your reply.

Thanks

Answer 1

Because you are trying to write a very large amount of records, it may be possible that even if you use the caching techniques PHPExcel provides, you'd still encounter OutOfMemory errors. Or that your script would take too long to finish (too long to be acceptable).

If this is something you see happening, I can recommend you taking a look at Spout: https://github.com/box/spout. It's an alternative to PHPExcel that was created to solve exactly your problem. You can throw it as many records as you want, the library will be able to write them without needing any extra config.

Writing a XLSX file is that easy:

$writer = WriterFactory::create(Type::XLSX);
$writer->openToFile($filePath);

while (...) {
    $writer->addRow($singleRow);
}

$writer->close();