writing one sheet at a time to save memory

Topics: User Forum
May 23, 2011 at 6:29 AM

There does not seem to be a way for me to get around the memory limit problem.

I have set memory for PHP to 1024MB but still I run out.  I am writing 20,000 rows to as many as 6 sheets inside one workbook.  I'm not doing anything very complex.  just writing strings to each column. no formulas and no formatting.

I'm hoping there are some creative ways to use phpexcel to get around the memory problem.

What about writing one sheet at a time, closing that sheet in some way and writing to the next one?  I don't need for the entire thing to be in memory since once I'm done with the one sheet I won't be making any changes to it.  I just need to save it at that point and then create the next sheet inside the workbook.

There seems to be a lot of mention of the memory limit problem but not a lot of creative ways to get around it.  Yes I am using cell caching.  I have tried all the different options.  But in the end the memory problem renders this entire package useless for my needs.  A shame since I am only writing text into these spread sheets.  I can generate a csv version in a fraction of the time which will open in Excel also.  In fact the only reason I'm using phpexcel is so that I can get the several worksheets in one file - can't do this with csv files…

Can anyone point me in the right direction?


May 23, 2011 at 8:26 AM

I'm having the same problem and now looking for alternatives that work with Excel 2007.

Would appreciate to hear some answers as well.

May 24, 2011 at 10:32 PM
Edited May 24, 2011 at 10:34 PM

Unfortunately not. While I'm still spending inordinate amounts of time trying to squeeze every byte of extra memory from the code (while trying to avoid any degredation in performance); the problem is that PHPExcel is a pure PHP script, with no external dependencies, which means that it is susceptible to PHP's memory limitations. Cell caching goes some way to bypassing this limitation, but it still needs to maintain indexes to each cell in the cache controller.

You will not find any pure PHP solution that works in this way; and to provide the fuunctionality to work this in PHPExcel would require a complete rewrite from scratch. Your options for alternatives are

  • To use PHP's COM extension with a copy of MS Excel itself on the server.
  • Ilia Alshanetsky's Excel extension (xls only, and requires a commercial component).
  • The PHP wrapper around Open Office's alternative to COM (PUNO) (requires Open Office installed on the server with Java support enabled).


May 24, 2011 at 11:52 PM
Thanks Mark,

It's an amazing tool and provides some great functionality. I'm going to keep using it. I've just upped it's memory allocation to 2gb for this script which does the trick.