如何修复用PHPExcel耗尽的内存?

问题描述

即使我仅尝试使用PHPExcel打开约350 KB的小excel文件，我的128M PHP内存限制也很快耗尽.

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的内存(不进行任何缓存)，或者在64位PHP上平均需要1.6k的内存-我现在假设使用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.

这篇关于如何修复用PHPExcel耗尽的内存?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！