問題描述

致命錯誤:允許的內存大小為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.

這篇關于如何修復 PHPExcel 耗盡的內存?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！