PHPExcel-当我遍历所有行时发生内存泄漏 [英] PHPExcel - memory leak when I go through all rows

问题描述

我正在使用PHPExcel库读取xls和xlsx文件.下面是一个示例函数，用于演示我遇到的问题:

I'm using PHPExcel library for reading xls and xlsx files. Below is a sample function for demonstrating problem that I have:

public function memoryAction()
{
    $filename = "example.xlsx";

    echo "<br>Script started<br>";
    echo memory_get_usage(true);

    $inputFileType = PHPExcel_IOFactory::identify($filename);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);
    $objReader->setLoadSheetsOnly(array('OTMS','Printing'));

    $excelReader = $objReader->load($filename);

    echo "<br>Reader Initiliazed<br>";
    echo memory_get_usage(true);

    foreach ($excelReader->setActiveSheetIndex(0)->getRowIterator() as $row) {
        if ($row->getRowIndex() == 1) {
            continue;
        }

        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);

        $excelRow = array();

        foreach ($cellIterator as $cell) {
            $columnIndex = $cell->getColumn();
            $cellValue = $cell->getCalculatedValue();
            $excelRow[$columnIndex] = $cellValue;

        }

        if (empty($excelRow)) {
            continue;
        }

        echo "<br>Row ".$row->getRowIndex()."<br>";
        echo memory_get_usage(true);



    }

    echo "<br>Went through each row<br>";
    echo memory_get_usage(true);

    die();


}

因此，基本上，我遍历了Excel表中的每一行并输出了内存使用情况.事实是，每使用20到30行，内存使用量就会增加.

So, basically I go through each row in Excel table and output memory usage. The thing is that memory usage increases after each 20-30 rows.

以下是我得到的价值观:

Here are values that I get:

脚本开始1835008 -1,75 Mb

Script started 1835008 -1,75 Mb

读取器初始化19660800-18,75 Mb

Reader Initiliazed 19660800 - 18,75 Mb

通过每一行47972352-45,75 Mb

Went through each row 47972352 - 45,75 Mb

我在Internet上阅读了几篇有关PHPExcel内存问题的文章.是的，它消耗大量内存.可以看到，我使用了setReadDataOnly()函数，并且仅上载了特定的工作表.但是我仍然不明白为什么仅循环浏览行会消耗内存.

I read several posts on Internet about PHPExcel memory problem. Yes, it consumes a lot of memory. You can see, that I use setReadDataOnly() function and that I uploaded only specific worksheets. But I still don't understand why just looping through rows consumes memory.

是否有任何方法可以在循环中取消设置行/单元格对象并释放内存?感谢任何帮助.

Is there any way to unset row/cell objects in the loop and free up memory? Appreciate any help.

UPD

我已经运行了Mark的代码，这是我文件的结果:

I have run Mark's code and here is a result for my file:

Base Memory: 1835008
Reader Initialised/File Loaded
19660800
Row 256 memory usage: 24903680
Row 512 memory usage: 33030144
Row 768 memory usage: 38797312
Went through each row
Final memory usage: 48234496

我正在使用PHPExcel版本1.7.8.和PHP 5.5.11.也许值得更新PHPExcel库.

I'm using PHPExcel version 1.7.8. and PHP 5.5.11. Probably it's worth to update PHPExcel library.

UPD 2

我已经安装了PHPExcel库的1.8.0版本.这是标记代码的结果:

I have installed version 1.8.0 of PHPExcel library. Here are results of the Mark's code:

Base Memory: 1835008
Reader Initialised/File Loaded
15990784
Row 256 memory usage: 20185088
Row 512 memory usage: 27262976
Row 768 memory usage: 31719424
Went through each row
Final memory usage: 40108032

任何想法为什么会发生?我正在使用Zend Framework，并在动作控制器中调用了此代码.测试文件包含4个标签，文件大小-619 KB.代码仅适用于包含1000行的第一个标签.

Any ideas why it happens? I'm using Zend Framework and called this code in action controller. Test file contains 4 tabs, file size - 619 KB. Code works with first tab only which contains 1000 rows.

推荐答案

作为测试，我已经创建了一个包含8192行和32列的工作表文件.我正在阅读的脚本基于您上面发布的脚本:

I've created a single worksheet file with 8192 rows and 32 columns just as a test. The script that I'm running to read this is based on the script you've posted above:

echo "Base Memory: ", memory_get_usage(true), PHP_EOL;

$inputFileType = PHPExcel_IOFactory::identify('volumeTest.xlsx');
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);

$excelReader = $objReader->load('volumeTest.xlsx');

echo "Reader Initialised/File Loaded", PHP_EOL;
echo memory_get_usage(true), PHP_EOL;

foreach ($excelReader->setActiveSheetIndex(0)->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    foreach ($cellIterator as $cell) {
        $columnIndex = $cell->getColumn();
        $cellValue = $cell->getCalculatedValue();
    }
    if (($row->getRowIndex() % 256) == 0) {
        echo "Row ".$row->getRowIndex(), ' memory usage: ', memory_get_usage(true), PHP_EOL;
    }
}

echo "Went through each row", PHP_EOL;
echo "Final memory usage: ", memory_get_usage(true), PHP_EOL;

所以我以与您完全相同的方式遍历行和列，唯一真正的区别是我仅在每256行迭代中显示内存使用情况，而不是每行显示一次内存使用情况，我没有建立一个单元格值数组.

so I'm iterating through the rows and columns in exactly the same way you are, and the only real differences are that I'm only displaying the memory usage every 256 rows of iteration rather than every row, and that I'm not building an array of cell values.

此生成的输出是:

Base Memory: 524288 Reader Initialised/File Loaded 105119744 Row 256 memory usage: 105119744 Row 512 memory usage: 105119744 Row 768 memory usage: 105119744 Row 1024 memory usage: 105119744 Row 1280 memory usage: 105119744 Row 1536 memory usage: 105119744 Row 1792 memory usage: 105119744 Row 2048 memory usage: 105119744 Row 2304 memory usage: 105119744 Row 2560 memory usage: 105119744 Row 2816 memory usage: 105119744 Row 3072 memory usage: 105119744 Row 3328 memory usage: 105119744 Row 3584 memory usage: 105119744 Row 3840 memory usage: 105119744 Row 4096 memory usage: 105119744 Row 4352 memory usage: 105119744 Row 4608 memory usage: 105119744 Row 4864 memory usage: 105119744 Row 5120 memory usage: 105119744 Row 5376 memory usage: 105119744 Row 5632 memory usage: 105119744 Row 5888 memory usage: 105119744 Row 6144 memory usage: 105119744 Row 6400 memory usage: 105119744 Row 6656 memory usage: 105119744 Row 6912 memory usage: 105119744 Row 7168 memory usage: 105119744 Row 7424 memory usage: 105119744 Row 7680 memory usage: 105119744 Row 7936 memory usage: 105119744 Row 8192 memory usage: 105119744 Went through each row Final memory usage: 105119744

Base Memory: 524288 Reader Initialised/File Loaded 105119744 Row 256 memory usage: 105119744 Row 512 memory usage: 105119744 Row 768 memory usage: 105119744 Row 1024 memory usage: 105119744 Row 1280 memory usage: 105119744 Row 1536 memory usage: 105119744 Row 1792 memory usage: 105119744 Row 2048 memory usage: 105119744 Row 2304 memory usage: 105119744 Row 2560 memory usage: 105119744 Row 2816 memory usage: 105119744 Row 3072 memory usage: 105119744 Row 3328 memory usage: 105119744 Row 3584 memory usage: 105119744 Row 3840 memory usage: 105119744 Row 4096 memory usage: 105119744 Row 4352 memory usage: 105119744 Row 4608 memory usage: 105119744 Row 4864 memory usage: 105119744 Row 5120 memory usage: 105119744 Row 5376 memory usage: 105119744 Row 5632 memory usage: 105119744 Row 5888 memory usage: 105119744 Row 6144 memory usage: 105119744 Row 6400 memory usage: 105119744 Row 6656 memory usage: 105119744 Row 6912 memory usage: 105119744 Row 7168 memory usage: 105119744 Row 7424 memory usage: 105119744 Row 7680 memory usage: 105119744 Row 7936 memory usage: 105119744 Row 8192 memory usage: 105119744 Went through each row Final memory usage: 105119744

在行迭代期间的任何时候都没有显示内存使用情况更改

Showing no memory usage changes at any point during the row iterations

我现在已经使用PHPExcel版本1.7.9、1.8.0和针对PHP版本5.2.17、5.3.27、5.4.7、5.4.21、5.5.5和5.6的最新开发分支运行了这些测试. .0，并且没有发现PHPExcel/PHP的任何组合在迭代器中的内存使用情况都有变化.

I've run these tests now using the PHPExcel versions 1.7.9, 1.8.0 and the latest development branch against PHP versions 5.2.17, 5.3.27, 5.4.7, 5.4.21, 5.5.5 and 5.6.0 and have seen no variation of memory usage within the iterators with any combination of PHPExcel/PHP.

这篇关于PHPExcel-当我遍历所有行时发生内存泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！