PHPExcel occupies huge amount of memory through Apache

Topics: Developer Forum, User Forum
Jun 10, 2014 at 9:22 AM
Hi,

i want to create around 130 xls files (xlsx is no option), mostly with one or two sheets but some have 6 or more sheets
i wrote a PHPExcel script and its working just fine - the only problem i have that the script takes 300 mb (for files with one sheet) till 2 gb (for files with 10+ sheets) of memory through the Apache webserver
that alone wouldn't be a problem because the machine has 8GB of memory, but after one file is created the PHPExcel-script or the Apache don't erase the data from the memory - its just stays there
so after 5-15 created files the memory is full and the script breaks up

how can i prevent this?

i tried unset of the variables in the PHPExcel script - no success
i tried PHPExcel_CachedObjectStorageFactory::cache_to_discISAM - no success

does somebody has other ideas?

i'm running a CentOS 6.5 with PHP 5.3.3 and Apache 2.2.15
Coordinator
Jun 10, 2014 at 9:37 AM
Edited Jun 10, 2014 at 9:39 AM
As described in section 4.3 of the developer documentation (entitled Clearing a Workbook from memory), you can't simply unset() a workbook,
The PHPExcel object contains cyclic references (e.g. the workbook is linked to the worksheets, and the worksheets are linked to their parent workbook) which cause problems when PHP tries to clear the objects from memory when they are unset(), or at the end of a function when they are in local scope. The result of this is “memory leaks”, which can easily use a large amount of PHP’s limited memory.
This can only be resolved manually: if you need to unset a workbook, then you also need to “break” these cyclic references before doing so. PHPExcel provides the disconnectWorksheets() method for this purpose.
So you have to call
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
But you shouldn't really be running scripts that call for large amounts of memory and take a long time to execute through a web request; I'd strongly recommend doing this as a CLI task
Jun 10, 2014 at 1:20 PM
thanks for your quick reply
i will use the terminal from now on

and many thanks for your great work!
Jun 19, 2014 at 3:39 PM
Where in the code do you place the steps to manually unset a workbook when you want to send the output to the user's browser?

I have tried after the $objWriter->save('php://output'); statement, but the apache process still has the memory after the spreadsheet is saved.