Memory issue

Topics: Developer Forum
Jul 20, 2009 at 8:06 AM

Hello,

After using the PHPExcel library for a little while, i had to create a quite big excel export (over 20000 rows). When trying to create this excel file, i ran into the php memory limit after about 5000 rows (memory-limit set at 256MB).
I thought this was quite strange because the exported file so far was only a few MB's.

This was quite strange, since i was not using any styles or images in the excel (just plain text actually).

 

After a little bit of searching and browsing through the source code, i noticed that the writer functionality, that writes to the output file, does not remove the exported rows from the PHPExcel object.

I think the reason for this is because the formula support in PHPExcel. When not using this formula support (maybe a new setting?) it would be possible to keep the memory usage low.

Developer
Jul 21, 2009 at 2:55 AM

>> After a little bit of searching and browsing through the source code, i noticed
>> that the writer functionality, that writes to the output file, does not remove the
>> exported rows from the PHPExcel object.


Yes, but if you for example need to export to several different formats then you would need those rows after the first export. Therefore, removing those rows may not be a good idea.

We have been discussing some caching solutions which could drastically reduce memory usage. This may be the best solution.

Have you been trying with PHPExcel 1.6.7 or latest source code?

Jul 21, 2009 at 6:20 AM

Perhaps if the removal of exported rows is an option you can set in the writer classes, there will not be an issue if someone wants to use the same PHPExcel object for multiple exports.

I have been using both PHPExcel 1.6.7 and the latest source code and the problem occured in both versions.

Developer
Jul 22, 2009 at 5:51 AM

>> Perhaps if the removal of exported rows is an option you can set in the writer classes,
>> there will not be an issue if someone wants to use the same PHPExcel object for multiple
>> exports.


I follow what you are saying, but I don't think it is the solution. The reason is as follows. At best it will reduce memory usage by approximately 50%, say from 100MB to 50MB. This is of course good, but this just postpones the memory problem until you need to generate a workbook that is twice as big.

A caching mechanism would solve all problems once and for all. Memory usage would stay constant at around say 10-20MB no matter how many cells you have.

Jul 22, 2009 at 6:27 AM

That is true and i would really like to have such a feature,
but how will the memory usage be when exporting all the data at once to the excel export file?

Developer
Jul 25, 2009 at 2:59 AM

>> but how will the memory usage be when exporting all the data at once to the excel export file?

With a caching mechanism (disk or database) in PHPExcel, PHP memory usage could probably drop to 10-20MB independent of workbook size, whereas today, without a caching mechanism, PHP memory usage is typically around 100MB per 100,000 cells in the workbook.