PHPExcel运行256,512和1024MB的RAM

I don't understand it. The XSLX table is about 3MB large yet even 1024MB of RAM is not enough for PHPExcel to load it into memory?

我不明白。 XSLX表大约3MB，甚至1024MB的RAM还不足以让PHPExcel将其加载到内存中？

I might be doing something horribly wrong here:

我可能在这里做了一些可怕的错误：

function ReadXlsxTableIntoArray($theFilePath)
{
    require_once('PHPExcel/Classes/PHPExcel.php');
    $inputFileType = 'Excel2007';
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($theFilePath);
    $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
    $arrayData = $arrayOriginalColumnNames = $arrayColumnNames = array();
    foreach($rowIterator as $row){
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
        if(1 == $row->getRowIndex ()) {
            foreach ($cellIterator as $cell) {
                $value = $cell->getCalculatedValue();
                $arrayOriginalColumnNames[] = $value;
                // let's remove the diacritique
                $value = iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $value);
                // and white spaces
                $valueExploded = explode(' ', $value);
                $value = '';
                // capitalize the first letter of each word
                foreach ($valueExploded as $word) {
                    $value .= ucfirst($word);
                }
                $arrayColumnNames[] = $value;
            }
            continue;
        } else {
            $rowIndex = $row->getRowIndex();
            reset($arrayColumnNames);
            foreach ($cellIterator as $cell) {
                $arrayData[$rowIndex][current($arrayColumnNames)] = $cell->getCalculatedValue();
                next($arrayColumnNames);
            }
        }
    }
    return array($arrayOriginalColumnNames, $arrayColumnNames, $arrayData);
}

The function above reads data from an excel table to an array.

上面的函数将数据从excel表读取到数组。

Any suggestions?

有什么建议么？

At first, I allowed PHP to use 256MB of RAM. It was not enough. I then doubled the amount and then also tried 1024MB. It still runs out of memory with this error:

起初，我允许PHP使用256MB的RAM。这还不够。然后我把数量增加了一倍，然后尝试了1024MB。它仍然因内存不足而出现此错误：

Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 50331648 bytes) in D:\data\o\WebLibThirdParty\src\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php on line 688

Fatal error (shutdown): Allowed memory size of 1073741824 bytes exhausted (tried to allocate 50331648 bytes) in D:\data\o\WebLibThirdParty\src\PHPExcel\Classes\PHPExcel\Reader\Excel2007.php on line 688

7 个解决方案

#1

There's plenty been written about the memory usage of PHPExcel on the PHPExcel forum; so reading through some of those previous discussions might give you a few ideas. PHPExcel holds an "in memory" representation of a spreadsheet, and is susceptible to PHP memory limitations.

在PHPExcel论坛上有很多关于PHPExcel的内存使用情况的文章;因此阅读之前的一些讨论可能会给你一些想法。 PHPExcel包含电子表格的“内存”表示，并且易受PHP内存限制。

The physical size of the file is largely irrelevant... it's much more important to know how many cells (rows*columns on each worksheet) it contains.

文件的物理大小在很大程度上是无关紧要的...知道它包含多少个单元格（每个工作表上的行*列）更为重要。

The "rule of thumb" that I've always used is an average of about 1k/cell, so a 5M cell workbook is going to require 5GB of memory. However, there are a number of ways that you can reduce that requirement. These can be combined, depending on exactly what information you need to access within your workbook, and what you want to do with it.

我一直使用的“经验法则”平均约为1k / cell，因此5M单元工作簿需要5GB内存。但是，有许多方法可以减少该要求。这些可以组合在一起，具体取决于您需要在工作簿中访问哪些信息，以及您要对其执行的操作。

If you have multiple worksheets, but don't need to load all of them, then you can limit the worksheets that the Reader will load using the setLoadSheetsOnly() method. To load a single named worksheet:

如果您有多个工作表，但不需要加载所有工作表，则可以使用setLoadSheetsOnly（）方法限制Reader将加载的工作表。要加载单个命名工作表：

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #2'; 
/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

Or you can specify several worksheets with one call to setLoadSheetsOnly() by passing an array of names:

或者，您可以通过传递一组名称来指定几个工作表，其中一次调用setLoadSheetsOnly（）：

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetnames = array('Data Sheet #1','Data Sheet #3'); 
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader of which WorkSheets we want to load **/ 
$objReader->setLoadSheetsOnly($sheetnames); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

If you only need to access part of a worksheet, then you can define a Read Filter to identify just which cells you actually want to load:

如果您只需要访问工作表的一部分，那么您可以定义一个读取过滤器来识别您实际要加载的单元格：

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #3'; 

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
class MyReadFilter implements PHPExcel_Reader_IReadFilter {
    public function readCell($column, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only 
        if ($row >= 1 && $row <= 7) {
           if (in_array($column,range('A','E'))) { 
              return true;
           }
        } 
        return false;
    }
}

/**  Create an Instance of our Read Filter  **/ 
$filterSubset = new MyReadFilter(); 
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load 
     It's more efficient to limit sheet loading in this manner rather than coding it into a Read Filter  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
echo 'Loading Sheet using filter';
/**  Tell the Reader that we want to use the Read Filter that we've Instantiated  **/ 
$objReader->setReadFilter($filterSubset); 
/**  Load only the rows and columns that match our filter from $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

Using read filters, you can also read a workbook in "chunks", so that only a single chunk is memory-resident at any one time:

使用读取过滤器，您还可以在“块”中读取工作簿，以便任何时候只有一个块驻留在内存中：

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example2.xls';

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
class chunkReadFilter implements PHPExcel_Reader_IReadFilter {
    private $_startRow = 0;
    private $_endRow = 0;

    /**  Set the list of rows that we want to read  */ 
    public function setRows($startRow, $chunkSize) { 
        $this->_startRow    = $startRow; 
        $this->_endRow      = $startRow + $chunkSize;
    } 

    public function readCell($column, $row, $worksheetName = '') {
        //  Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow 
        if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) { 
           return true;
        }
        return false;
    } 
}

/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Define how many rows we want to read for each "chunk"  **/ 
$chunkSize = 20;
/**  Create a new Instance of our Read Filter  **/ 
$chunkFilter = new chunkReadFilter(); 
/**  Tell the Reader that we want to use the Read Filter that we've Instantiated  **/ 
$objReader->setReadFilter($chunkFilter); 

/**  Loop to read our worksheet in "chunk size" blocks  **/ 
/**  $startRow is set to 2 initially because we always read the headings in row #1  **/
for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) { 
    /**  Tell the Read Filter, the limits on which rows we want to read this iteration  **/ 
    $chunkFilter->setRows($startRow,$chunkSize); 
    /**  Load only the rows that match our filter from $inputFileName to a PHPExcel Object  **/ 
    $objPHPExcel = $objReader->load($inputFileName); 
    //    Do some processing here 

    //    Free up some of the memory 
    $objPHPExcel->disconnectWorksheets(); 
    unset($objPHPExcel); 
}

If you don't need to load formatting information, but only the worksheet data, then the setReadDataOnly() method will tell the reader only to load cell values, ignoring any cell formatting:

如果您不需要加载格式化信息，只需要加载工作表数据，那么setReadDataOnly（）方法将告诉读者只加载单元格值，忽略任何单元格格式：

$inputFileType = 'Excel5';
$inputFileName = './sampleData/example1.xls';
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader that we only want to load cell data, not formatting **/ 
$objReader->setReadDataOnly(true);
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

Use cell caching. This is a method for reducing the PHP memory that is required for each cell, but at a cost in speed. It works by storing the cell objects in a compressed format, or outside of PHP's memory (eg. disk, APC, memcache)... but the more memory you save, the slower your scripts will execute. You can, however, reduce the memory required by each cell to about 300bytes, so the hypothetical 5M cells would require about 1.4GB of PHP memory.

使用单元格缓存。这是一种减少每个单元所需的PHP内存的方法，但速度很快。它的工作原理是以压缩格式存储单元格对象，或者存储在PHP内存之外（例如磁盘，APC，内存缓存）......但是保存的内存越多，脚本执行的速度就越慢。但是，您可以将每个单元所需的内存减少到大约300字节，因此假设的5M单元将需要大约1.4GB的PHP内存。

Cell caching is described in section 4.2.1 of the Developer Documentation

单元缓存在开发人员文档的4.2.1节中描述

EDIT

编辑

Looking at your code, you're using the iterators, which aren't particularly efficient, and building up an array of cell data. You might want to look at the toArray() method, which is already built into PHPExcel, and does this for you. Also take a look at this recent discussion on SO about the new variant method rangeToArray() to build an associative array of row data.

查看代码，您将使用迭代器，这些迭代器效率不高，并且构建了一组单元数据。您可能希望查看已经内置到PHPExcel中的toArray（）方法，并为您执行此操作。另外，请看一下最近有关新变种方法rangeToArray（）的SO的讨论，以构建行数据的关联数组。

#2

I had the same memory issue problem with PHPExcel and actually all the other libraries. Reading the data in chunks, as Mark Baker suggested could fix the issue (caching works too), but it turned out that the memory issue became a time issue. The reading and writing time was exponential so for large spreadsheets, it was not a good fit.

我有与PHPExcel相同的内存问题，实际上所有其他库。正如Mark Ba​​ker建议的那样，可以解决这个问题（缓存也可以解决），但是事实证明内存问题成了时间问题。读写时间是指数级的，因此对于大型电子表格来说，它并不合适。

PHPExcel and others are not meant to handle large files so I created a library that solves this problem. You can check it out here: https://github.com/box/spout

PHPExcel和其他人不打算处理大文件，所以我创建了一个库来解决这个问题。你可以在这里查看：https：//github.com/box/spout

Hope that helps!

希望有所帮助！

#3

There are plenty of measures you can take to reserve less memory when working with PHPExcel. I recommend you to take the following actions to optimize memory usage before modifying your server's memory limit in Apache.

使用PHPExcel时，可以采取许多措施来保留较少的内存。我建议您在修改Apache中服务器的内存限制之前采取以下操作来优化内存使用。

/* Use the setReadDataOnly(true);*/
    $objReader->setReadDataOnly(true);

/*Load only Specific Sheets*/
    $objReader->setLoadSheetsOnly( array("1", "6", "6-1", "6-2", "6-3", "6-4", "6-5", "6-6", "6-7", "6-8") );

/*Free memory when you are done with a file*/
$objPHPExcel->disconnectWorksheets();
   unset($objPHPExcel);

Avoid using very large Exel files, remember it is the file size that makes the process run slowly and crash.

避免使用非常大的Exel文件，请记住文件大小会使进程运行缓慢并崩溃。

Avoid using the getCalculatedValue(); function when reading cells.

避免使用getCalculatedValue（）;阅读细胞时的功能。

#4

Ypu can try PHP Excel http://ilia.ws/archives/237-PHP-Excel-Extension-0.9.1.html Its an C extension for php and its very fast. (Also uses less memory than PHP implementations)

Ypu可以尝试PHP Excel http://ilia.ws/archives/237-PHP-Excel-Extension-0.9.1.html它是一个PHP的C扩展，它非常快。 （也使用比PHP实现更少的内存）

#5

In my case, phpexcel always iterated through 19999 rows. no matter, how many rows actually were filled. So 100 rows of data always ended up in a memory error.

就我而言，phpexcel总是迭代19999行。无论如何，实际填充了多少行。因此，100行数据总是在内存错误中结束。

Perhaps you just have to check, if the cells in the current row are empty and then "continue" oder break the loop, that iterates the rows.

也许您只需检查，如果当前行中的单元格为空，然后“继续”或者中断循环，则迭代行。

#6

Just reposting my post from another thread. It describes different approach to serverside generating or editing of Excel spreadsheets that should be taken in account. For large amount of data I would not recommend tools like PHPExcel or ApachePOI (for Java) because of their memory requirements. There is another quite convenient (although maybe little bit fiddly) way to inject data into spreadsheets. Serverside generation or updating of Excel spreadsheets can be achieved thus simple XML editing. You can have XLSX spreadsheet sitting on the server and every time data is gathered from dB, you unzip it using php. Then you access specific XML files that are holding contents of worksheets that need to be injected and insert data manually. Afterwards, you compress spreadsheet folder in order to distribute it as an regular XLSX file. Whole process is quite fast and reliable. Obviously, there are few issues and glitches related to inner organisation of XLSX/Open XML file (e. g. Excel tend to store all strings in separate table and use references to this table in worksheet files). But when injecting only data like numbers and strings, it is not that hard. If anyone is interested, I can provide some code.

只是从另一个帖子重新发布我的帖子。它描述了服务器端生成或编辑应该考虑的Excel电子表格的不同方法。对于大量数据，我不建议使用像PHPExcel或ApachePOI（Java）这样的工具，因为它们有内存要求。还有另一种非常方便（尽管可能有点繁琐）的方式将数据注入电子表格。可以实现Serverside生成或更新Excel电子表格，从而实现简单的XML编辑。您可以将XLSX电子表格放在服务器上，每次从dB收集数据时，都可以使用php解压缩它。然后，您将访问特定的XML文件，这些文件包含需要注入的工作表的内容并手动插入数据。之后，您压缩电子表格文件夹，以便将其作为常规XLSX文件进行分发。整个过程非常快速可靠。显然，与XLSX / Open XML文件的内部组织相关的问题和故障很少（例如，Excel倾向于将所有字符串存储在单独的表中并在工作表文件中使用对该表的引用）。但是当只注入数字和字符串等数据时，并不难。如果有人有兴趣，我可以提供一些代码。

#7

I ran into this problem and unfortunately none of the suggested solutions could help me. I need the functionality that PHPExcel provides (formulas, conditional styling, etc) so using a different library was not an option.

我遇到了这个问题，不幸的是，没有一个建议的解决方案可以帮助我。我需要PHPExcel提供的功能（公式，条件样式等），因此使用不同的库不是一个选项。

What I eventually did was writing each worksheet to an individual (temporary) file, and then combining these separate files with some special software I wrote. This reduced my memory consumption from >512 Mb to well under 100 Mb. See https://github.com/infostreams/excel-merge if you have the same problem.

我最终做的是将每个工作表写入单个（临时）文件，然后将这些单独的文件与我编写的一些特殊软件组合在一起。这使我的内存消耗从> 512 Mb减少到远低于100 Mb。如果您遇到同样的问题，请参阅https://github.com/infostreams/excel-merge。