1

I am using the following method inside a loop in order to read an excel file in chunks (using a custom read filter so that I don't run out of memory):

public function chunkToArray($file, $startRow, $chunkSize)
{

    $file = __DIR__ . '/../../' . $file;

    $objReader = PHPExcel_IOFactory::createReader('Excel2007');

    $chunkFilter = new ExcelChunkReadFilter();

    $objReader->setReadFilter($chunkFilter);
    $chunkFilter->setRows($startRow, $chunkSize);

    $objPHPExcel = $objReader->load($file);

    $array = $objPHPExcel->getActiveSheet()->rangeToArray('A' . $startRow . ':AT' . ($startRow + $chunkSize - 1));

    $objPHPExcel->disconnectWorksheets();

    unset($objPHPExcel);
    $objPHPExcel = null;

    return $array;
}

Despite calling "disconnectWorksheets" and unsetting and nulling the php excel object, I'm still getting a memory leak on each loop iteration.

I have used echo memory_get_usage(true) to isolate that the memory usage is going up on each iteration on the line $objPHPExcel = $objReader->load($file);, but the memory is not being cleared again before the next iteration.

I'm really struggling to identify what's going on here. Any help greatly appreciated.

James Flight
  • 1,424
  • 1
  • 13
  • 21
  • Some memory should be cleared when you disconnect worksheets and unset $objPHPExcel.... at what points inside this method are you actually checking memory usage? – Mark Baker Jul 30 '14 at 10:03
  • That's what I thought, but it still eventually runs out of memory after a few iterations. Would I be correct in saying that I should see a difference in the memory usage if i measure it immediately after "$objPHPExcel = $objReader->load($file);" and "$objPHPExcel = null;"? Because both values are coming out the same on each iteration at the moment. – James Flight Jul 30 '14 at 13:24
  • I wouldn't expect those figures to be exactly identical, because you're also populating $array in between – Mark Baker Jul 30 '14 at 13:27
  • "echo memory_get_usage(true)" seems to always give identical values unless one is at a point before "$objPHPExcel = $objReader->load($file);" and one is after. Could the array population be making no difference because in each iteration the new array is the same size as the one from the previous iteration, and the previous is being garbage collected? Thanks for your help. – James Flight Jul 30 '14 at 14:29
  • Well your array is being created each iteration by the `rangeToArray()` call, and is only marked for garbage collection when you exit the function having returned its content to your calling function.... so I'd certainly expect to see some difference between the two memory usage checks on your first call to `chunkToArray()` – Mark Baker Jul 30 '14 at 14:51
  • But also note that `memory_get_usage returns the amount of memory allocated to php, not necessarily the amount being used.` If you don't pass in the true argument, then you should get back the exact amount of memory that your code is currently using – Mark Baker Jul 30 '14 at 14:53
  • See the answer to [this question](http://stackoverflow.com/questions/14260517/php-memory-get-usage-on-empty-php-script) for a better explanation of `memory_get_usage()` – Mark Baker Jul 30 '14 at 14:57
  • Ah, that explains a lot: bit of a stupid mistake on my part. I'll measure all this again without the true argument – James Flight Jul 30 '14 at 15:00
  • Did you ever figure out why the memory kept increasing? – PriestVallon Apr 21 '15 at 08:25
  • No I don't think so, I think in the end the memory increase was only very slight, and I might have increased the memory limit with ini_set() and put up with it. – James Flight Apr 22 '15 at 08:44

1 Answers1

1

The PHPExcel library has a big problem with memory leaks so I advise you to switch to other libraries that are working with .xlsx files.

Have a look at this answer to the question "Alternative for PHP_excel". You can use PHP_XLSXWriter or Spout.

If you can't switch to another library then you can use the caching mechanism of PHPExcel. This answer can help you with that.

Community
  • 1
  • 1
Aleksandar Rusakov
  • 31
  • 5