PHPExcel not releasing memory

Question

I have a script which reads emails with Excel files attached.
I am using PHPExcel to parse these files.

The current problem I am having, is with an email which has five spreadsheets attached.
Each time the fifth file is loaded, PHP runs out of memory.

Initially I tried nullifying ($objPHPExcel = NULL;) and unsetting (unset($objPHPExcel);) the PHPExcel Object each time after I was through with it. That didn't help.

I then tried adding garbage collection to the process (gc_enable(); $cycles_collected = gc_collect_cycles();). That didn't help.

I printed the current php memory usage to my error log and I can see it grow with the loading of each Excel file into PHPExcel, I also can see that the memory usage does not decrease with each NULL, unset or gc_collect.

Here is some of the sample output to my error_log right before and after PHP runs out of memory:

[17-Aug-2017 16:04:29 America/Chicago] [Blackouts] Current Memory Usage at beginning of script before loading Excel file: 85575176

[17-Aug-2017 16:04:32 America/Chicago] [Blackouts] Current Memory Usage after loading Excel file: 104474632

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] Current Memory Usage after parsing of Excel file is complete: 104480416

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] Current Memory Usage after PHPExcel object set to null: 104480536

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] Current Memory Usage after PHPExcel object is unset: 104480256

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] gc collected cycles: 0

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] Current Memory Usage after gc_collect_cycles: 104480416

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] script completed: /path/to/file/Schedule Week of 14 Aug 2017 - 21 Aug 2017.xlsx

[17-Aug-2017 16:04:34 America/Chicago] [Blackouts] Current Memory Usage at beginning of script before loading Excel file: 104484072

[17-Aug-2017 16:04:36 America/Chicago] [Blackouts] Current Memory Usage after loading Excel file: 122069128

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] Current Memory Usage after parsing of Excel file is complete: 122114480

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] Current Memory Usage after PHPExcel object set to null: 122114600

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] Current Memory Usage after PHPExcel object is unset: 122114320

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] gc collected cycles: 0

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] Current Memory Usage after gc_collect_cycles: 122114480

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] script completed: /path/to/file/Schedules Week of 14 Aug 2017 - 21 Aug 2017.xlsx

[17-Aug-2017 16:04:38 America/Chicago] [Blackouts] Current Memory Usage at beginning of script before loading Excel file: 122118192

[17-Aug-2017 16:04:39 America/Chicago] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted at Zend/zend_vm_execute.h:22207 (tried to allocate 72 bytes) in /mnt/nas_001_www/Classes/PHPExcel/Cell.php on line 551

I once refactored a project using phpExcel. After increasing memory and using a very capable server, it still took very long to generate an excel (5k+ rows). I eventually optimized my data fetching and switched to generating a csv with sep=; metadata tag so it can be opened with excel. — R_Ice, Aug 17 '17 at 21:24
All of the sheets are about the same size. The last sheet is 9 columns and about 1500 rows. — Chris Utter, Aug 17 '17 at 21:27
@Rienk, I'd love to convert it to a csv, but unfortunately I need to be able to read the cell colors because they convey certain meanings to each cell. — Chris Utter, Aug 17 '17 at 21:28
@bassxzero No, see for example https://stackoverflow.com/questions/4817651/phpexcel-runs-out-of-256-512-and-also-1024mb-of-ram phpExcel is just loading a lot of objects in memory — R_Ice, Aug 17 '17 at 21:31
I just read this one https://github.com/infostreams/excel-merge — R_Ice, Aug 17 '17 at 21:33
Check answer here - https://stackoverflow.com/questions/11447918/php-import-excel-into-database-xls-xlsx/27798644#27798644 for `nuovo/spreadsheet-reader`. It is simple and reads simple styles. — shukshin.ivan, Aug 17 '17 at 22:40

score 7 · Answer 1 · answered Aug 17 '17 at 21:39

7

You cannot simply nullify or unset a PHPExcel object to remove it from memory, as explained in the documentation, because it contains cyclic references (the workbook contains a collection of worksheet objects, and each worksheet references the workbook; and similarly with worksheet and cell objects) that cannot simply be resolved by an unset. Instead, you need to break those references first

$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel)

answered Aug 17 '17 at 21:39

Mark Baker

209,507
32
346
385

Thanks for the help @Mark Baker. But why not just include the disconnectWorksheets() function in the destructor of the PHPExcel class so the developer doesn't have to call it explicitly? I'm sure there's a good reason for it or it's just a lack of my understanding of the functionality of the class. Thanks again! – Chris Utter Aug 17 '17 at 22:59
Theoretically it could be, although there are a couple of circumstances where it needs to be called independently; but my recollection when it was written x-year ago, is that it didn't work when called within the destructor.... my recollection is that it was related to the timing/order of execution of the destructors down the chain from workbook to worksheet to cellcollection to cells – Mark Baker Aug 17 '17 at 23:25
Fair enough. Thanks again Mark! – Chris Utter Aug 17 '17 at 23:26
I wanted to say a big THANKYOU for this answer. I regularly run PhpSpreadsheet against a directory full of 100-120 spreadsheets and my memory usage went from 2GB down to 100MB! – Jacob Mulquin Aug 25 '21 at 12:16

PHPExcel not releasing memory

1 Answers1