如何修复 PHPExcel 耗尽的内存?

2021-12-24 00:00:00 memory-management php phpexcel

致命错误:允许的内存大小为134217728 字节已用完(试图分配 1078799 字节)在D:xampplitehtdocsScraperPHPExcelReaderExcel2007.php在线 269

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1078799 bytes) in D:xampplitehtdocsScraperPHPExcelReaderExcel2007.php on line 269

我的 128M PHP 内存限制很快就会耗尽，即使我只是想用 PHPExcel 打开一个 ~350 KB 的小型 excel 文件.

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.

虽然，我可以在配置中增加内存限制，但很高兴看到是否有任何替代方法可以解决此问题.

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.

推荐答案

在使用 PHPExcel 时，文件大小不是衡量工作簿文件的好方法.行数和列数(即单元格)更重要.

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 代码本身的占用空间在 10 到 25MB 之间，具体取决于正在访问的组件.

The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.

目前，工作簿中的每个单元格平均需要 1k 内存(没有任何缓存)或 1.6k 在 64 位 PHP 上 - 我暂时假设是 32 位 PHP - 所以(例如)工作表8000 行 31 列(248,000 个单元格)大约为 242MB.使用单元缓存(例如 php://temp 或 DiskISAM)，这可以减少到大约三分之一，因此 8000 行乘 31 列将需要大约 80MB.

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.

有许多选项可以帮助您减少内存使用:

There are a number of options available to help you reduce the memory usage:

您是否在 PHPExcel 中使用单元格缓存?

Are you using cell caching with 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.

相关文章