使用PHPExcel将XLS文件中的单张纸转换为CSV-内存耗尽

问题描述

我有一个Excel文件(97-2003)，我需要该文件能够通过PHP导入，并将其仅将文件中的一张纸转换为CSV.这些是由用户上传的，因此没有手动选择的选项.它必须与PHP一起使用.

我目前正在使用PHPExcel，直到我们得到一个包含33,000行以上的文件为止，它都可以正常工作...这给出了致命的PHP错误:Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 97 bytes) in [pathtophpexcel]\PHPExcel\Worksheet.php on line 11231

有什么办法可以进一步优化它，还是处理大型工作表时不适合使用PHPExcel?我正在使用的代码非常简单，因此不确定是否可以进一步对其进行优化，但是请耐心等待！这是我正在使用的代码:

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

如果PHPExcel不适合该职位，那么我最好使用什么?

编辑-这是Mark建议后的工作代码

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

解决方案

PHPExcel 1.7.3引入了cell caching作为减少在PHP内存范围内维护大型工作簿的内存使用量的机制.单元缓存提供了一个折衷方案，可以通过序列化单元数据以减小其大小，或允许单元数据保留在PHP内存之外而仅在内存"中保留单元索引来减少内存使用.但要付出一定的性能代价.

默认情况下，单元缓存将所有单元存储在内存中，以获得最佳速度；但其他缓存选项包括:

• 压缩在内存中
• 在内存中序列化
• 二进制在内存中序列化
• 在磁盘上红色
• APC
• 内存缓存
• wincache
• phptemp
• sqlite或sqlite3数据库

速度和内存使用情况在它们之间会有所不同，并且还会因操作平台和其他设置而异，因此您需要确定哪种方法最适合您自己的情况；但是作为一般准则，内存使用效率越高，性能就越慢.SQLite选项是最近添加的，并且提供了最佳的内存使用率，因为即使在内存中也不保留单元索引"，而且我目前正在测试一些更改，以查看是否可以提高sqlite的执行速度. >

开发人员文档的第4.2.1节涵盖了用法

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

解决方案

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

这篇关于使用PHPExcel将XLS文件中的单张纸转换为CSV-内存耗尽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！