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);