Memory Issues

Topics: Developer Forum, User Forum
Jan 28, 2010 at 10:48 PM

I'm generating a fairly small XLS file, about 32KB or so, and am reaching 32MB memory limit errors during my database loop to write to the Excel file. Here's the bulk of my code:

 

ini_set('include_path', ini_get('include_path').PATH_SEPARATOR.$_SERVER['DOCUMENT_ROOT'].'/'.APPPATH.'libraries/php-excel/');

/** PHPExcel */
include 'PHPExcel.php';

/** Writer_Excel2007 */
require_once 'PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator('Brian DiChiara')
				->setLastModifiedBy('Brian DiChiara')
				->setTitle('Hibbett Vendor Report')
				->setSubject('Hibbett Vendor')
				->setDescription('Report generated online at HibbettVendor.com');


$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Report');

/* document styles */
$default = array(
	'font' => array(
		'name' => 'Lucida Sans Unicode',
		'size' => 9
	)
);
$headers = $default;
$headers['font']['bold'] = true;

$alphabet = range('A','Z');

$c = 'A';
$r = 1;
$total_cols = count($cols);

$objPHPExcel->getActiveSheet()->getStyle('A1:'.$alphabet[$total_cols].'1')->applyFromArray($headers);

foreach($cols as $col){
	$cell = $c.$r;
	$objPHPExcel->getActiveSheet()->SetCellValue($cell, $col['label']);
	$objPHPExcel->getActiveSheet()->getColumnDimension($c)->setAutoSize(false)
							      ->setWidth($col['width']);
	$c++;
}
$r++;

$coop = 0;
$initiative = 0;
$grand_total = 0;

$c = 'A';
$total = $query->num_rows();
$range = 'A2:'.$alphabet[$total_cols].$total+1;
$objPHPExcel->getActiveSheet()->getStyle($range)->applyFromArray($default);

// set cols 9-16 to currency
$objPHPExcel->getActiveSheet()->getStyle('I:K')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

foreach($query->result_array() as $item){
	foreach($item as $k=>$value){
		$cell = $c.$r;
		
		if($k == 'cat_id'){
			$cat_id = $value;
			continue;
		}
		if($k == 'due'){
			$due = $value;
			continue;
		}
		
		$value = trim($value);
		$value = str_replace('"', '""', $value);
		$value = str_replace('&', '&', $value);
		$value = strip_tags($value);
		
		if($k == 'category'){
			$root_cat = $this->cms->get_root_category($cat_id);
			$objPHPExcel->getActiveSheet()->SetCellValue($cell, $root_cat);
			$c++;
			$cell = $c.$r;
		} elseif($k == 'due_date'){
			if(strtotime($value) === false){
				$value = $due;
			}
		}
		$objPHPExcel->getActiveSheet()->SetCellValue($cell, $value);
		$c++;
	}
	$c='A';
	$r++;
}

// Save Excel 2007 file
$filename = ($data['filename']) ? $data['filename'] : 'master_report-'.date('YmdHis');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

I had to up my memory_limit to 64 MB but seem to still be having memory issues. What exactly am I doing wrong here? Why does it take forever to generate this report when it didn't take near as long with Pear's Spreadsheet Writer? Please help! Thanks.

 

Coordinator
Jan 29, 2010 at 12:17 PM

Excessive memory usage is a known problem with PHPExcel, and one that we are working hard to overcome. There are a number of threads here on the board discussing that very issue, which recommend a number of techniques to reduce the memory usage within your scripts: for example, using shared styles