PHPExcel - memory leak when I go through all rows

Question

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();


}

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:

Script started 1835008 -1,75 Mb

Reader Initiliazed 19660800 - 18,75 Mb

Went through each row 47972352 - 45,75 Mb

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

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

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

---

UPD 2

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

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.

Answer 1

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;

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.

The output that this generates is:

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

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.