3
votes

I've got an Excel file (97-2003) which I need to be able to import via PHP and have it convert just one sheet in the file to CSV. These are uploaded by users so there's no option for doing it manually. It needs to be with PHP.

I'm currently using PHPExcel, it was working fine until we got a file with over 33,000 rows... which is giving a fatal PHP error: Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 97 bytes) in [pathtophpexcel]\PHPExcel\Worksheet.php on line 11231

Is there any way I can optimise this further, or is PHPExcel just not the right thing to use when dealing with huge sheets? The code I'm using is fairly simple, so not sure if it'll be possible to optimise it further, but fingers crossed! Here's the code I'm using:

$reader = PHPExcel_IOFactory::createReader('Excel5');
$reader->setReadDataOnly(true);
$reader->setLoadSheetsOnly('Details');

$excel = $reader->load($filename);

$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->save($filename_fixed);

If PHPExcel isn't right for the job, what am I best to use?

Edit - This is the working code after Mark's suggestion

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '2GB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$reader = PHPExcel_IOFactory::createReader('Excel5');
$reader->setReadDataOnly(true);
$reader->setLoadSheetsOnly('Details');

$excel = $reader->load($filename);

$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->save($filename_fixed);
1
Have you tried using PHP's Cell Caching to reduce its memory usage?Mark Baker
Cheers @MarkBaker, that worked perfectly! Not sure if you want to post it as a proper answer and I'll mark it as answered?BT643

1 Answers

4
votes

PHPExcel 1.7.3 introduced cell caching as a mechanism to reduce the memory usage of maintaining large workbooks within the limits of PHP memory. Cell caching offers a trade-off reducing memory usage either by serializing the cell data to reduce its size, or allowing cell data to be held outside of PHP memory with only an index of cells maintained "in memory"; but at a cost in performance.

By default, cell caching will store all cells in memory for the best speed; but other caching options include:

  • zipped in memory
  • serialized in memory
  • igbinary serialized in memory
  • red on disk
  • APC
  • memcache
  • wincache
  • phptemp
  • sqlite or sqlite3 database

speed and memory usage vary between them and will also vary depending on operating platform and other setings, so you'd need to work out which was the best option to use in your own situation; but as a general guideline, the more efficient the use of memory, the slower performance. The SQLite options were the most recently added, and provide the best memory usage because even the "cell index" isn't maintained in memory, and I'm currently testing some changes to see if I can improve the sqlite execution speed.

Usage is covered in section 4.2.1 of the developer documentation