[PhpExcel] Continous writing/memory optimization

Topics: Developer Forum
May 16, 2011 at 9:44 AM

Hi everyone,


I am new into PhpExcel, and try to tweak a little bit the code in order to generate an Excel5 file.

I read several threads regarding Cell Caching and memory set in PHP in order to write big files. However, none of those methods

allowed me to save (even generate) a file of about 30,000 rows in 3 sheets. I only have 256Mo of RAM, so cannot excess the 64Mo of allowed

memory for PHP.


So I try something, and I would like to have, if possible, a little bit of support :

In a PHP file, I set the header in order to save the generate file into a .xls file and permit the user to save it.

I have to extract approximately 30,000 rows from a DB and have to make a treatment and insert each of them in 3 differents sheets with Style (line affectation, in order to optimize a little bit).

So the thing we thought about was to call for each iteration the "save" method of the writer (here : Excel5) in order to get the file in real time and  not let the object grow in memory.

We end up with a file, using 42Mo of disk space, but in which we had only one line printed in it : the first row in the first sheet.


Is this possible to write continuously in the .xls file ? If yes, what would be the good way ? The thing is to make all the possible not to let the phpExcel object grow in the memory.


Please let me know !


Thank You in Advance for your help



May 16, 2011 at 10:03 AM



I don't think your proposed solution will help in reducing the memory footprint. PHPExcel keeps an internal representation of the entire spreadsheet in memory. Once you call the save() method of a specific writer, the entire structure is copied to the writer object.


How far do you get? What happens if you up the mem limit to for instance 128MB, and leave out styling... Are you running on a 32 bits or 64 bits platform?

May 16, 2011 at 10:09 AM

Thanks for your answer !


Well, the file is well created, when opening the file in Excel nothing appears but the first row in the first sheet.

However, opening the file in Notepad++, we can see (hardly but we can see) some datas that have to be inside the excel file in the other sheets...


I am running on a 32bits platforms under CentOS.


How would you empty this spreadsheet internal representation in memory ? I thought the cleanup happening at the end of the save method would have helped in some way...

May 16, 2011 at 10:14 AM

I don't think it is possible. :( First you create a representation (this structure is PHPExcel's own structure) of the entire spreadsheet in memory (every cell is represented by multiple php objects). When you call save(), the entire structure is copied, meaning you need atleast twice the memory of the initial object for a save() operation to be possible at all. And then I'm not even taking memory overhead of the actual save() operation itself into consideration.

May 16, 2011 at 10:26 AM
Edited May 16, 2011 at 10:26 AM

But, before, when we let the save method at the end, we couldn't get the file, the memory was already full and we got the Memory Fail Message.

Now, we can at least get the file, but without information printed "inside" the excel file, but the datas are in the file (a little bit weird I admit...)


Do you happen to know any process to continuously write inside a file in a good way ? I think that would be my last step to succeed ...

I will then take care of the PHPExcel way to handle the datas...


Thanks again for your help ;)

Jun 9, 2011 at 1:53 PM


I think that somethig is still fishy with the in-memory presentation of the spreadsheet.

I am the project lead of the LimeSurvey project and in the latest version (1.91) we tried to swap the old (but working) PEAR PHP_Excel_Writer class for phpExcel.

In general it worked fine but it turns out that phpExcel's memorry footprint is about 110% bigger than it was previously with the PEAR library, even when using the Cache_to_disk caching methods.

So for example exporting an excel file (no formattings) of ~ 1500 rows and 40 columns needs a max memory footprint of 20 MB with the old PEAR library and more then 55 MB with phpExcel.

Usually the datasets we are exporting to Excel are bigger. Since most hosting provider won't allow a memory usage of 128 MB (some even less) this is no acceptable and we decided to revert back to the PEAR PHP_Excel_Writer class. (all Exports were Excel5)

Please don't misunderstand me: There are good reasons to change to phpExcel and it is an awesome library - but for bigger datasets this is currently a no-go. I am sure there are ways to optimize this.

If there are changes regarding this we will be happy to test.