php 如何修复 PHPExcel 耗尽的内存？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3537604/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix memory getting exhausted with PHPExcel?
提问by Shubham
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1078799 bytes) in D:\xampplite\htdocs\Scraper\PHPExcel\Reader\Excel2007.php on line 269
致命错误：第 269 行 D:\xampplite\htdocs\Scraper\PHPExcel\Reader\Excel2007.php 中允许的内存大小为 134217728 字节（试图分配 1078799 字节）
My 128M PHP memory limit quickly gets exhausted even when I am only trying to open a small excel file of ~350 KB with PHPExcel.
即使我只是想用 PHPExcel 打开一个大约 350 KB 的小型 excel 文件，我的 128M PHP 内存限制也会很快耗尽。
Although, I can increase the memory limit in the configuration but it'll be great to see if there are any alternatives to fix this.
虽然，我可以增加配置中的内存限制，但很高兴看到是否有任何替代方法可以解决此问题。
回答by Mark Baker
File size isn't a good measure for workbook files when working with PHPExcel. The number of rows and columns (ie cells) is more important.
使用 PHPExcel 时，文件大小不是衡量工作簿文件的好方法。行数和列数（即单元格）更为重要。
The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.
PHPExcel 代码本身的占用空间在 10 到 25MB 之间，具体取决于正在访问的组件。
At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so (for example) a worksheet of 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB.
目前，工作簿中的每个单元格平均需要 1k 内存（没有任何缓存）或 1.6k 在 64 位 PHP 上 - 我暂时假设是 32 位 PHP - 所以（例如）8000 行的工作表31 列（248,000 个单元格）大约为 242MB。使用单元缓存（例如 php://temp 或 DiskISAM），可以减少到大约三分之一，因此 8000 行乘 31 列将需要大约 80MB。
There are a number of options available to help you reduce the memory usage:
有许多选项可帮助您减少内存使用：
Are you using cell caching with PHPExcel?
您是否在 PHPExcel 中使用单元格缓存？
require_once './Classes/PHPExcel.php';
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("test.xlsx");
If you only need to access data in your worksheets, and don't need access to the cell formatting, then you can disable reading the formatting information from the workbook:
如果您只需要访问工作表中的数据，而不需要访问单元格格式，则可以禁用从工作簿中读取格式信息：
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");
If you only need to access some, but not all of the worksheets in the workbook, you can load only those worksheets:
如果您只需要访问工作簿中的部分而不是全部工作表，则可以仅加载这些工作表：
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setLoadSheetsOnly( array("Worksheet1", "Worksheet2") );
$objPHPExcel = $objReader->load("test.xlsx");
if you only want to read certain cells within worksheets, you can add a filter:
如果您只想读取工作表中的某些单元格，您可以添加一个过滤器：
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
public function readCell($column, $row, $worksheetName = '') {
// Read title row and rows 20 - 30
if ($row == 1 || ($row >= 20 && $row <= 30)) {
return true;
}
return false;
}
}
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("test.xlsx");
All of these techniques can significantly reduce the memory requirements.
所有这些技术都可以显着降低内存需求。
回答by shamittomar
PHPExcel is known for memory leaks. I advise you to use the following which need a FRACTION of the memory that PHPExcel uses.:
PHPExcel 以内存泄漏而闻名。我建议您使用以下需要PHPExcel 使用的内存的分数。：
1) For Reading: PHP-Excel-Reader
1）阅读：PHP-Excel-Reader
2) For Writing: Pear Spreadsheet Excel Writer
回答by TravisO
Just because the data file is only X bytes, doesn't mean it uses X bytes of ram. For example only 4K of data in a $_SESSION array uses 64K of ram when loaded up. It just depends what the code is doing with that data. The correct answer is to increase the amount of ram.
仅仅因为数据文件只有 X 字节，并不意味着它使用 X 字节的 ram。例如，$_SESSION 数组中只有 4K 的数据在加载时使用 64K 的内存。这仅取决于代码如何处理这些数据。正确答案是增加ram的数量。
Also if this is a XLSX file, they are ZIP'd XML documents. Text files zip up far tighter than 1/2, so your 350K XLSX file is easily a 1MB Excel file.
此外，如果这是一个 XLSX 文件，则它们是 ZIP 格式的 XML 文档。文本文件的压缩比 1/2 小得多，因此您的 350K XLSX 文件很容易成为 1MB 的 Excel 文件。
回答by Chris
Xdebug is profiler/debugger for php and can help you trace through memory usage and functional calls to figure out where the problem lies. And its easy to install, most linux distributions have it in repository, "yum install xdebug", "apt-get install xdebug".
Xdebug 是 php 的分析器/调试器，可以帮助您跟踪内存使用情况和函数调用以找出问题所在。而且它易于安装，大多数 linux 发行版都在存储库中，“yum install xdebug”，“apt-get install xdebug”。
回答by greg0ire
回答by frostymarvelous
One thing you should note too is empty cells.
您还应该注意的一件事是空单元格。
I had the same issue, only 800 rows of data and the script was not even able to read. Both timeouts and out of memory errors when either was fixed.
我有同样的问题，只有 800 行数据，脚本甚至无法读取。修复超时和内存不足错误。
Something @Mark Baker said about cells got me thinking. I noticed that I had loads of empty cells and copying only the cells with data to a new workbook got it to run in a second.
@Mark Baker 关于细胞的一些话让我想到了。我注意到我有很多空单元格，并且只将带有数据的单元格复制到新工作簿中，让它在一秒钟内运行。
Hope this helps someone.
希望这可以帮助某人。
回答by Annie Chandel
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");
This much code is enough
这么多代码就够了
回答by Sixtina Aquafina
This post talks about how to close a PHPExcel reader: How to close excel file in php-excel-readerIf you're opening multiple Excel files simultaneously, but actually not need them all at the same time, then you could try to open and close(i.e. unset) the readers for each file one by one. Btw, using the same variable name for readers would suffice for the "unsetting" operation.
这篇文章讨论了如何关闭 PHPExcel 阅读器： 如何在 php-excel-reader 中关闭 excel 文件如果您同时打开多个 Excel 文件，但实际上并不需要同时打开它们，那么您可以尝试打开并一一关闭（即取消设置）每个文件的阅读器。顺便说一句，为读者使用相同的变量名就足以进行“取消设置”操作。
