Memory leak?

Topics: Developer Forum, User Forum
Nov 27, 2007 at 8:59 PM
Hello everybody,

first of all, I have to say that this project was of great value in one of my projects. But while working with some really big bunch of data, I permanently ran out of memory, though my server setting was 64 MB. So I digged a bit into the code and took a look around on the net and found something about problems with circular references in PHP along with a solution (link not available right now, can post it later). I applied these hints to PHPExcel and reduced my memory usage significantly, so now I wonder, if this is of interest for anybody. If so, I could help in applying the improvements.

Thanks in advance for all answers!
Nov 28, 2007 at 7:00 PM
Please check your e-mail or contact me.
Jun 12, 2008 at 5:37 PM
Edited Jun 15, 2008 at 6:29 AM
Hi maartenba,

After experiencing several memory issues myself and looking around the discussion board this particular discussion seems the most appropriate to follow up on. I'll suffice to say that I run into memory problems too in sheets with only a few hundred cells.

Was there any follow up on retiolum's suggestion?


edit: a few thousand cells, not a few hundred
Jun 12, 2008 at 7:58 PM
Retiolum's solution was indeed a good one, but was breaking object references in some occasions. Still looking for a good shot on memory usage...
Jun 12, 2008 at 9:12 PM
Thanks for the info Maarten,

Is it possible for you to give me a bit of guidance as to where (say, a class / method reference) object reference breaking would occur or a specific section where you are looking at improving memory usage? After spending some time on the net your package seems to outperform anything else I've seen and between building something of my own (ouch!) and helping something good get better I would rather pick the latter. I'll be happy to spend some time looking for solutions and any directions into localizing the issue would be very helpful.

11 columns with 500 rows with border style and fill style still fail here on memory issues. Without the styles it works fine (or at least doesn't hit a ceiling yet) so it helps some and localizes somewhat. Is styles and the creation of circular references where I need to be looking for improving memory management?


Jun 13, 2008 at 6:43 AM
You can write some __destruct() functions on classes like Worksheet, Cell and Style which destroy parent/child relations. This helps memory management (a few kilobytes on 05featuredemo.php).
Jun 13, 2008 at 6:55 AM
Thanks for the pinpoint. Makes sense. I'll look into it and see I can produce some results.

Jun 14, 2008 at 10:16 PM
Edited Jun 14, 2008 at 10:24 PM

Hi Maarten,

I set out for some research on memory consumption in PHPExcel. I’ll share with you what I found assuming that some of it must be old news to you. Appologies for that. Consider it catching up.

I followed your lead and looked for circular references. Before I did that however I made sure that I was actually getting memory errors that I could search for. The following code does just that:

$objPHPExcel = new PHPExcel();

echo memory_get_usage()."\n";

   array('fill'    => array(
                   'type'    => PHPExcel_Style_Fill::FILL_SOLID,
                   'color'   => array('argb' => 'FFCCFFCC')),
         'borders' => array(
                   'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                   'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                   'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN))

If I run this I get:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 288 bytes) in C:\xampp\htdocs\libraries\PHPExcel\Style\Border.php on line 340

I ran through the code, constructs and methods, and found two circular references:

- PHPExcel->_workSheetCollection references PHPExcel_Worksheet objects and they all reference back to the PHPExcel object through their _parent property.
- PHPExcel_Worksheet->_cellCollection references PHPExcel_Cell objects and they all reference back to the PHPExcel_Worksheet object through their _parent property.

So, basically, if we were to do the following:

$obj = new PHPexcel();
... fill lots of cells or make lots of worksheets ...

then the whole Workbook would stay in memory because of the circular references that still exist. Bad.

We could ofcourse work around this by introducing a Destroy method in PHPExcel which we call ourselves, much like C++'s delete (I know it's yuk and not PHP style but I'm trying to figure out what works first, then what works best)

PHPExcel::Destroy() {
   foreach($this->_workSheetCollection as $index => $dummy) {
      $this->_workSheetCollection[$index] = null;

PHPExcel_Worksheet::Destroy() {
   foreach($this->_cellCollection as $index => $dummy) {
      $this->_cellCollection[$index] = null;

I made a test file to verify this and it appears to work. It makes sense for it to work as we ask the owner of a collection to break the circle for each child. Sure, it's not in line with PHP's garbage collection (not needing programmers intervention and all) but it may give some folks an exit strategy for their memory problems and it does not interfere with the existing API. Also, even if PHP's GC ever gets better this code may become useless, but never invalid. (BTW, after doing a few tests it seems to me that doing something in __destruct() may be an additional option but the real issue at hand seems to be that either something (PHP's GC) or somebody (the programmer) has to initiate the destruct sequence to begin with. Only then will __destruct() be called.)

My problem with this solution, however, is that it fixes a problem that is not causing my memory failure :( After all, this only applies if we want to get rid of the Workbook and my code is nowhere near trying to get rid of the Workbook. It already fails at duplicateStyleArray(). I actually removed the circular references from the PHPExcel and PHPExcel_Workbook code (and commented out code that required the reference) and I still get the error. The trace goes PHPExcel::getActiveSheet()->PHPExcel_Worksheet::duplicateStyleArray()->PHPExcel_Worksheet::duplicateStyleArray()->PHPExcel_Worksheet::getStyle() and that's where the memory consumptions appears to come into display. The lines...

$newStyle = clone $this->getDefaultStyle();

...basically cause a whole lot of objects to be created through constructors and the cloning of the default style. Nothing gets deleted, nowhere, so we don't want any refcount to get to 0 so I'm not worried about circular references here. What I do see is that A1:K500 is 5500 cells in total which amounts to:

- 5.500 PHPExcel_Cell class objects
- 5.500 PHPExcel_Style class objects
- 5.500 PHPExcel_Style_Fill class objects
- 5.500 PHPExcel_Style_Font class objects
- 5.500 PHPExcel_Style_Borders class objects
- 38.500 PHPExcel_Style_Border class objects
- 5.500 PHPExcel_Style_Alignment class objects
- 5.500 PHPExcel_Style_NumberFormat class objects
- 5.500 PHPExcel_Style_Protection class objects
- 55.000 PHPExcel_Style_Color class objects

Equals 137.500 class objects to populate 5500 cells. With available memory taken into account each object should be no more than (33554432 - 3435512) / 137.500 = 219 bytes each on average including overhead.

So, one thing would be to increase memory. I tuned a bit and found that the above code works fine if the memory_limit directive is set to 57M. So effectively each object consumes (59768832 - 3435512) / 137.500 = 410 bytes each on average including overhead.

And... that doesn't sound too odd, given the wealth of options one can configure for each cell.

All in all, it seems to me that:

A) The problem of circular references appears solvable. What I suggest is not necessarily beautiful, however practical. Truth is, I believe 99% of PHPExcel users will not have multiple iterations of creation and destruction of PHPExcel or PHPExcel_Workbook objects within the scope of a single HTTP Request. So, I am suggestion that they may not even be running the consequences of poor GC by PHP at all.

B) We all need to get more memory or alternatively we find a way to reduce the number of PHPExcel_Style_* objects or make each of them smaller in size. I do have some ideas that could work like on-demand-creation and so forth but before I elaborate I rather fully understand the issue we're dealing with.

That's all I have for now.

Compliments on the code. I enjoyed reading it and I'm amazed by the functionality.

I'm looking forward to hearing your thoughts.


PS: Is this the proper place to continue this discussion?

Jun 16, 2008 at 6:20 AM
This is the proper place, but I suggest we continue this by e-mail.
Feb 6, 2009 at 2:22 AM
How do I implement this stuff? I coded an app that creates a sheet from a template, and while testing (5 sheet) it worked decent, but trying real data (15) it takes for ever :( Whats the best way to increase performance on something like my code below. The php.ini memory is set for 750mb, but the server memory doesn't seem to be the prob, its 100% CPU usage.

Heres the output first number is the time through the loop, second is elapsed:
Starting Build
This run 1 Running: 1
This run 6 Running: 7
This run 13 Running: 21
This run 18 Running: 39
This run 23 Running: 62
This run 28 Running: 91
This run 32 Running: 123
This run 38 Running: 161
This run 43 Running: 204
This run 50 Running: 254
This run 56 Running: 310
This run 62 Running: 372
This run 68 Running: 440
This run 72 Running: 512
This run 75 Running: 587

Fatal error: Maximum execution time of 600 seconds exceeded in PHPExcel\Classes\PHPExcel\Writer\Excel5\Parser.php on line 914

foreach ($quotes as $k=>&$worksheet) {
                 $fname = explode(" ", &$worksheet['Quote Factory']);
                 $name = trim($fname[0]) . ' ' .trim(&$worksheet['box_name']);
                     $row = 1;
                       while ($max_rows >= $row) {
                         $this->excel_row($templates[$row], &$worksheet);
            $factory = $worksheet['Factory Name'];

I tried:

function destroy

PHPExcel::Destroy() {
   foreach($this->_workSheetCollection as $index => $dummy) {
      $this->_workSheetCollection[$index] = null;

PHPExcel_Worksheet::Destroy() {
   foreach($this->_cellCollection as $index => $dummy) {
      $this->_cellCollection[$index] = null;

function __destroy(){

foreach($this->_workSheetCollection as $index => $dummy) {

      $this->_workSheetCollection[$index] = null;

Feb 6, 2009 at 1:24 PM
Got it down to 30 seconds by
First doing a loop to

The sheets, then just going throug the loop above without create and set title?

Strange, bug in one of those?

Feb 6, 2009 at 8:12 PM
setTitle() bug is fixed in the source code release tomorrow morning.
Feb 14, 2009 at 5:10 AM
Thanks for all your hard work. I used your base to build a template system and parser. You can import all the sheets into mysql, it looks for $$Vars and then replaces those with DB columns and data. I automated a sheet that would have taken weeks to recreate in about 10 minutes with the final code :) Ive spit out hundreds of cases with the template already.

Its certainly not as elegant as yours, but just wanted to say thanks the help :)
Jul 9, 2009 at 11:30 AM

Hi everybody,

I had the same problem, because for a project I need to open an excel file hundreds of different times (5MB of more memory each time).

My solution is a bit rude (and could be optimized), because I had no time, but very effective.

I placed the next function in a few key classes (PHPExcel, Worksheet, and few others), and called it manually (once) from PHPExcel, when I needed it no more


public function Destroyer() {
			$ClassVars = get_object_vars($this);
			foreach ($ClassVars as $VarName => $VarValue) {
				if ($VarName == '_parent') continue;
				if (is_object($VarValue)) {
					if (in_array('Destroyer',get_class_methods(get_class($VarValue))))
				if (is_array($VarValue)) {
					foreach ($VarValue as $ArrayID => $ArrayValue) {
						if (is_object($ArrayValue)) {
							if (in_array('Destroyer',get_class_methods(get_class($ArrayValue))))
						if (is_array($ArrayValue)) {
							foreach ($ArrayValue as $ArrayID2 => $ArrayValue2) {
								if (is_object($ArrayValue2)) {
									if (in_array('Destroyer',get_class_methods(get_class($ArrayValue2))))

Jul 9, 2009 at 1:08 PM

@WhiteCubes: Thank you for posting your solution. I linked to this thread from this related issue

In the long run I hope that we can avoid custom destroyers because many will probably find it difficult to implement these, but until then, your solution is very valuable.

Apr 30, 2010 at 3:33 PM
Edited Apr 30, 2010 at 3:39 PM
I faced this memory leak issue while I was creating spreadsheets in a loop and writing them. Indeed, as mentioned in this thread, there are a lot of circular references.
@whisper's solution does help but only to some extent, coz the circular references extend beyond the worksheet collection. But he was the first to see the direction!
@WhiteCubes's solution saved my day. Many thanks to you! Now I am able to generate 100s of excel sheets at constant memory.
For information's sake I'm mentioning the classes where I included the 'Destroyer' function mentioned in @Whitecube's post.
Using version 1.7.2 here
Apr 30, 2010 at 8:56 PM
Edited May 1, 2010 at 3:47 PM

I'm currently implementing the destroy() method as part of the changes that I'm doing for cell caching: mainly because it needs to work rather differently when the different caching methods are in use.

May 3, 2010 at 8:47 AM

I'm glad it turned to be usefull ;-)

Jun 9, 2010 at 2:20 PM

@WhiteCubes: It looks like this is what I need. I have a similar issue where I open an Excel file, paste several thousand lines of cells into it using a loop, and save it when done. Every time I run this line, memory used is increased by about 95kB:

$objPHPExcel->setActiveSheetIndex(0)->setCellValue("$column$row", "$value");

I added the Destroyer function to Worksheet.php, but what do you mean by "and called it manually (once) from PHPExcel, when I needed it no more"? I'm not sure where to call it from.