Multiple exporting & memory clearing

Topics: Developer Forum, User Forum
Nov 18, 2009 at 11:35 AM

Hi,

I've started using your library today and I'm really impressed with the features available. There's 1 thing though that's limiting it's use for me and that's the problems with the amount of memory it needs when creating larger files. I'm trying to export 70k records with 500MB available as the memory_limit and I'm still getting a fatal error.

I've looked over this forum and it seems the library is really designed for smaller xlsx files - which is fine. So given that, I'm trying to export my data in smaller chunks, say 10k records at a time. The problem is that the memory allocated via php is not being freed up after each file write.

The basics of the code are as follows:

 

$lStart = 0;
$lInc = 10000;
$lFinish = 70000;

for($i = 0; $i<$lFinish; $i=$i+$lInc)
{

 $objPHPExcel = new PHPExcel();

 $rowTracker = 1;
 $sQL = mysql_query("SELECT * FROM table LIMIT ".$lStart.", ".($lStart+$lInc));
 while ($row = mysql_fetch_assoc($sQL))
 {
  $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowTracker, $row[...]);
  ...
  $rowTracker++;
 }

 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 $objWriter->save("filename-".$lStart.".xlsx");

}

 

How can I clean up the memory used after each loop so the script doesn't fail? I've tried unset($objPHPExcel) & $objPHPExcel->distroy() without any luck.

Thanks in advance,
Adam.

Coordinator
Nov 18, 2009 at 12:51 PM

Try clearing down each instance of the $objWriter using unset($objWriter) after each call to $objWriter->save() as each call to PHPExcel_IOFactory::createWriter() creates a new instance of the writer

Nov 18, 2009 at 1:01 PM

Hi Mark,

Thanks for the reply.

I've tried that and it seems to have no impact. For testing purposes I'm looping 700 records in sets of 100 and logging the memory usage. These are the results both after and before the amendment:

AFTER EACH ROW 13.75
AFTER EACH ROW 16
AFTER EACH ROW 17
AFTER EACH ROW 18
AFTER EACH ROW 19.25
AFTER EACH ROW 20.25
AFTER EACH ROW 21.25
12:56:57 Peak memory usage: 21.5 MB

For info, this is the amended code section:

echo "AFTER EACH ROW ".(memory_get_usage(true) / 1024 / 1024)."<br>";

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save("filename-".$lStart.".xlsx"); unset($objWriter);

 

Nov 18, 2009 at 1:23 PM

I've managed to trim it down by clearing all the object properties via a destruct method:

PHPExcel.php:

	public function Destruct()
	{
		foreach($this->_workSheetCollection as $value)
		{
			$value->Destruct();
		}
	}

Worksheet.php:

	public function Destruct()
	{
		foreach($this as $key=>$value)
		{
			$this->$key = null;
		}
	}

Then call this after the $objWriter->save(); in the base code:

$objPHPExcel->Destruct();

It may not be the ideal solution however it does seem to use a lot less memory:

AFTER EACH ROW 13.75
AFTER EACH ROW 15.25
AFTER EACH ROW 15.25
AFTER EACH ROW 15.5
AFTER EACH ROW 15.5
AFTER EACH ROW 15.75
AFTER EACH ROW 15.75
13:18:47 Peak memory usage: 16 MB

Now to try it on a larger scale... :)

Nov 18, 2009 at 1:31 PM

Results for 3 loops of 30,000 rows:

AFTER EACH ROW 301
AFTER EACH ROW 317.75
AFTER EACH ROW 319.5
13:28:33 Peak memory usage: 359.25 MB

Although it still uses a lot of memory for each run, it's clearing up a lot better for the previous objects.

Developer
Nov 19, 2009 at 4:13 AM

This is unfortunately a known issue since long ago, but it is not forgotten. The problem is with recursive references between the workbook-worksheet, and worksheet-cell.

In the near future I'm planning to do some testing to see how a cell supervisor can be incorporated in PHPExcel. For a start, that alone will probably cut down memory usage to approximately one half, and as a side effect, the worksheet-cell recursive references would be eliminated. This should be a first step for eliminating the memory leaks you are experiencing.