memory allocation in function

Topics: Developer Forum, Project Management Forum, User Forum
May 7, 2010 at 10:26 AM

I have a function that create an Excel with data from a DB and save it to a file

I call this function for each item in a table

Now I see than memory usage increments on every cicle. While I expect that exiting from my function memory allocated for $objPHPExcel would be released.

If I have many items memory limit will soon be reached.


here is the code



foreach($item as $id)
    save_excel($id, $filename, true);





function save_excel($item_id, $filename="", $save=false)
    global $db;

    $objPHPExcel = new PHPExcel();

    // ... here I fill my excel with data from Mysql DB 

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        return true;




May 15, 2010 at 12:38 PM

Unfortunately, a PHPExcel object comprises a series of nested objects: workbook contains worksheets, which in turn contain cells, and each also contains a reference to its parent. PHP has problems when trying to unset (even when exiting a function where anything in local scope should be unset automatically unless it's static) objects that contain such cyclic references. Supposedly this problem has been resolved in PHP 5.3.x, although I haven't confirmed that this is the case. In the interim, this leads to memory leaks that do rapidly use up to PHP's memory limit.

The next release of PHPExcel adds a couple of new methods that break these cyclic references allowing a workbook or a worksheet to be unset after they have been called; so it will be possible to do:


While it isn't perfect, and doesn't yet free up every byte of memory that has been used, it should certainly help resolve this issue

May 19, 2010 at 4:50 PM

i'm using 1.7.3 version, i only can call $objPHPExcel->disconnectWorksheets();
 after i do :

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');


and the memory leaks out int the process of save.

when i'm doing the $objWorksheet2->setCellValue('A' . $i, 'value');

also i can see memory runnig out.

I'm talking about files 2* sheets 50K cells, of course when i try files width 300K rows * 15 columns...

I have tried all this methods:


$cacheMethod  = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
#$cacheMethod  = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
#$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
#$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; 

no sucess.


Is there a better way, im doing someting wrong?


May 24, 2010 at 12:10 PM


it seems to work. I doesen't free all memory allocated but it saves enought memory for my application



May 26, 2010 at 9:03 AM


I'm glad that it's improved things for now.

I'll try to ensure that it cleans up more memory leaks in subsequent releases. I know ,for example, that it isn't yet clearing up all of the shared styles, xf register, etc.

May 26, 2010 at 9:17 AM


The latest SVN code fixes some of the memory leaks in the writers, so you should see improvements using that, but it still isn't perfect. Cell caching isn't intended to fix memory leaks... it's purpose is to reduce the overall footprint in memory of a workbook/worksheet; but it can't eliminate the use of any memory at all no matter which caching method you use, just reduce it allowing you to work with larger workbooks than previously. The memory leaks are a separate issue, and appear to be a particular problem with the writers: this is something that I am still trying to resolve.

Our estimate with the 1.7.2 release was an average of 1k/cell memory required, so 2 sheets of 300K rows * 15 columns would require about 9GB of PHP memory. The different caching mechanisms in 1.7.3 allow you to reduce that, perhaps by as much as half, although the leaks in the writers make this less effective.