How to unload the excel file loaded before?

Topics: Developer Forum
Sep 16, 2014 at 6:31 PM
I have a VERY BIG EXCEL FILE, and need to read it into an php array, then format it using my rules, finnaly I need write the formatted data into a new excel file.
I implemented the action into 3 functions, as I said just now. But the problem is: when I successfully loaded the source excel file, read all data from it, and exit the function, the memory allocated while I load it does not released; then when I try to write the data into a new file, the memory usage doubled! and crached at CacheBase.php!
So I guess the memory is used by the cache system, and not released when the file unloaded. Then I have 2 choices: one is to release all unused cached memory, the second is disable the cache function.
Can you give me some advises?
Regards,
Dany.
Coordinator
Sep 16, 2014 at 7:11 PM
Edited Sep 16, 2014 at 7:12 PM
Disable the cache function, and nothing at all will work.... try using some of the alternatives to the default "cache in memory" that are described in the manual, because these should the reduce memory usage of PHPExcel. The whole point of the cache is to provide options to reduce memory, but you have to specify which caching method to use before loading the file.

Memory used when the file is loaded is released, the loader does clear its memory usage... but the PHPExcel object holds data in memory (the default caching method).

Don't transfer data to an array: work with it directly in the PHPExcel object. Transferring data to an array will use a lot of memory, because you'll be duplicating data between the PHPExcel object and the array, which will take a lot more memory.
Sep 17, 2014 at 3:31 AM
Edited Sep 17, 2014 at 4:44 AM
So glad for your help, many thanks.
I'm using the method cache_in_memory_gzip now, and the memory usage is reduced 1/2 more, but accordingly, the speed reduced too :)
I know this is not the best solution, or I can split the excel file into more smaller pieces.
What I'm still confused is, I created the PHPExcel object in a function, as a local class instance, It should automatically destructed and release it's memory as soon as the function returned. But I could see the memory usage is still keep at the top value. How can I free all the memory used by the destroyed instance?
My code works with the following logic, and I would not change it in order to reducing the coupling degree:
Load some useful fileds from the excel file into an array and return it (funcation 1) -> format loaded fields and insert some new fields (function 2) (before do this I would like to release all memory holded by the phpexcel object in function 1) -> write the array into a new excel file (function 3).

Regards,
Dany.
Coordinator
Sep 17, 2014 at 8:15 AM
A PHPExcel object can't easily be destroyed because the hierarchy of spreadsheet->worksheets->cells contains cyclic references in that the cell also references the worksheet, and the worksheet references the spreadsheet, so special methods are needed to clear it from memory (as described in section 4.3 of the developer documentation, Clearing a Workbook from memory).
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
This should free up a more memory from the PHPExcel object for you
Marked as answer by DanyDeng on 9/17/2014 at 2:47 AM
Sep 17, 2014 at 9:54 AM
Edited Sep 17, 2014 at 10:47 AM
Thanks for your kindness! I'll try it later.

Regards,
Dany.

It works! Great!