PHP-Excel & Memory Usage

[timstring]

I have a script put together with PHP-Excel. The problem I'm having is that the memory usage increases by 500 KB an iteration. In a world measured by gigabytes, 500 k doesn't seem like much, but the data has over 800 iterations. The resulting memory usage is 1.45 GB. Running on a machine that only has 2 GB, the script bogs down. So, i'm wondering if there's a way to reclaim the memory that PHP-Excel is using? Attached is the code that I have largely borrowed. Where can I cut?

				$EndCol = 'A'. chr($NumMonth+43);
				$strRow = 'A' . $iRow  . ':' . $EndCol . $iRow;

			$fontRow = 'A' . $iRow;




			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow, $LongSystem);

			$objPHPExcel->getActiveSheet()->mergeCells($strRow);
			$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($fontRow)->getFont()->setSize(14);



$iRow++;
$strRow = 'A' . $iRow  . ':' . $EndCol . $iRow;
$fontRow = 'A' . $iRow;



			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow, $EndMO);



	$objPHPExcel->getActiveSheet()->mergeCells($strRow);
	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle($fontRow)->getFont()->setSize(14);

	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $iRow) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $iRow) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow+1, 'Tech Number');


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $iRow+1, 'Name');

$iRow++;

$fontRow = 'A' . $iRow;



	for ($counter = 0; $counter <$NumMonth; $counter++)
		{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($counter+3, $iRow, $W[$counter]);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($counter+3, $iRow+1, $D[$counter]);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow+1) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	

$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($counter, $iRow+1) ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);		

}


if ($SubCallCount > 0 || $SubWorkOrders > 0) {

	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$ColorCount = $ColorCount + .5;
	if ($ColorCount == (int) ($ColorCount)) {

		$objPHPExcel->getActiveSheet()->getStyle($strRow)->getFill()->getStartColor()->setARGB('FFA9F5A9');
	}
else {
	$objPHPExcel->getActiveSheet()->getStyle($strRow)->getFill()->getStartColor()->setARGB('FFFFFFFF');
}


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $iRow, $TechNum);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $iRow, $TechName);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $iRow, 'Calls');

			$GBMemory = memory_get_usage()/1024/1024;
			echo 'Memory&nbsp&nbsp' . $GBMemory . "<br><br>";



$CellNum = $row["CellNum"];
$AreaCode = substr($CellNum,0,3);
$Exchange = substr($CellNum,3,3);
$PhNum = substr($CellNum,5,4);
$FormatCell = '(' . $AreaCode . ') ' . $Exchange . '-' . $PhNum;


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $iRow+1, $FormatCell);
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $iRow+1) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $iRow+1) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $iRow + 1, 'Work Orders');
	$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $iRow + 1 ) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $iRow + 1) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+3, $iRow, $CallCount);


$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+3, $iRow+1, $WorkOrders);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow+1) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);	
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Count+3, $iRow+1) ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);			

	}

}		
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+3, $iRow, $SubCallCount);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Count+4, $iRow+1, $SubWorkOrders);


			$SubCallCount = 0;
			$SubWorkOrders = 0;

			$GBMemory = memory_get_usage() /1024/1024;
			echo 'Mem_2&nbsp&nbsp' . $GBMemory . "<br>";

[dalecosp]

I have the documentation for this @work, but won't be there for several hours. IIRC, there are some tips in the handbook for handling such things; some sort of configuration knob or somesuch... I'll try and remember to look it up tomorrow.

[dalecosp]

See the docs, section 4.2.1, "Configuration Settings / Cell Caching". After reading this, my response in my project was:

if ($count($data)>200) {
         $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
         PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
}

HTH,

[timstring]

I found the same thing this morning. That cut the memory usage of the script from 1.4 gig to 800 meg, and the speed increased,too. I have tinkered with the memory given to the cache, and there's not much of a bump in performance from 32 M to 64 M to 128 M.

I am having another problem: The script will process 120 or so rows and pause for what seems to be an insufferable amount of time and go on to the next bunch rows. I have error reporting turned on and there are no errors. Is there anything I can do about this, or should I take a walk around the building while the script is running?

[dalecosp]

I don't know about that one. It does take a while. Did you take out the "get memory usage" code? I can't see any real reason for that to stay, but I'm not sure it would have to go, either. I've not tested my app quite so thoroughly, and the most it ever does is about 10K rows.....