Timeout Issue

Topics: Developer Forum
Apr 19, 2011 at 2:34 PM

Hi there,

OS: CentOS 5
PHP Version: 5.2.1
PHPExcel: 1.7.6

I can't seem to get past 700 rows without timing out. Code is below

$query = $this->paged_results->GetQuery();
		$query->SetLimit(null);
		$query->SetOffset(0);
		$query->Run();

		//header("Cache-Control: ");
		//header("Pragma: ");
		//header("Expires: ");
		//header('Content-type: text/csv');
		//header(sprintf('Content-disposition: attachment; filename="clients-%s.csv"', $this->paged_results->GetId()));


		$flat_assigns = array(
			'client1_first_name' => 'first_name',
			'client1_last_name' => 'last_name',
			'application_type','sales_stage', 'property_value', 'total_financing', 'down_payment', 'payment', 'payment_frequency',
			'date_entered', 'maturity_date', 'closing_date', 'condition_of_finance_date',
			'sales_stage',
			'agent',			
			'client2_first_name','client2_last_name',
			'client3_first_name','client3_last_name',
			'client_address1',
			'client_postal_code',
			'client_city',
			'client_province',
			'email',
		);

		/** PHPExcel */
		LibAxiom::ImportByPath('/PHPExcel/PHPExcel.php');
		/** PHPExcel */
		
		$objPHPExcel = new PHPExcel();

		// Set properties
		$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
		$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
		$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
		$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
		$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
		
		LibAxiom::WriteToLog('about to create object');
		// Add some data
		$objPHPExcel->setActiveSheetIndex(0);
		//Set column headings
		$col='a';
		$styleArray = array(
	'font' => array(
		'bold' => true,
	),
	'alignment' => array(
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
	),
	'borders' => array(
		'bottom' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN,
		),
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
		'rotation' => 90,
		'startcolor' => array(
			'argb' => '00000000',
		),
		'endcolor' => array(
			'argb' => 'FFFFFFFF',
		),
	),
);
		//Format Header column
		foreach ($flat_assigns as $k => $v){ 
			$objPHPExcel->getActiveSheet()->SetCellValue($col."1", $v);
			$col++;
		}
		$objPHPExcel->getActiveSheet()->getStyle("a1:".$col."1")->applyFromArray($styleArray);
			
		$row=1;		
		foreach ($query as $result) {
			//var_dump($result);exit;
			$col = 'a';
			$row++;
			foreach ($flat_assigns as $k => $v) {
				
				if (intval($k) == $k){
					$objPHPExcel->getActiveSheet()->SetCellValue("$col$row", $result[$v]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue("$col$row", $result[$v]);
				}
				$column_list[] = $col;
				$col++;	
			}
			
			

		}
			$subtotal = $row+1;
			$objPHPExcel->getActiveSheet()->SetCellValue("F$subtotal", "=SUBTOTAL(9,F1:F$row)");
					
			//Once all the rows are created set column auto size true
			//foreach($column_list as $column)
				//$objPHPExcel->getActiveSheet()->getColumnDimension("$column")->setAutoSize(true);
			LibAxiom::WriteToLog('Finished Loop');

			header('Content-Type: application/excel');
			header('Content-Disposition: attachment;filename="test.xls"');
			header('Cache-Control: max-age=0');
			
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output'); 
	}
Any thoughts on where to start debugging?

Apr 19, 2011 at 2:38 PM

In general, when my app tries to output a spreadsheet and it times out, PHPExcel does not give a lot of clues as to why.

So I just arbitrarily reduce the output until I do get a spreadsheet, and go from there.

Apr 19, 2011 at 2:43 PM

It just seems that 700ish rows is a fairly small recordset.  I use the same function to create a csv with 13,000 records easily.  I was hoping to create spreadsheets that are a little more robust using PHPExcel.

Coordinator
Apr 19, 2011 at 10:05 PM
mcameron123 wrote:

It just seems that 700ish rows is a fairly small recordset.  I use the same function to create a csv with 13,000 records easily.  I was hoping to create spreadsheets that are a little more robust using PHPExcel.


700 rows isn't a particularly meaningful measure. A row can contain just one column, or it can contain 256 columns... and the difference in memory requirements between those is about 256 times as much. Each cell in your workbook is a cell object, taking up about 1k of PHP's memory (on a 32-bit server; about 1.6k on a 64-bit server). The Excel Writers double that requirement, because they build the complete binary in memory as well, while the CSV writer writes each line as it is generated.

A lot has been written on how to use memory more efficiently on this forum, and there are some links to specific discussion threads in the FAQ. I'm not going to repeat it all; but cell caching is one tool, that can reduce the memory requirements for each cell.

Coordinator
Apr 19, 2011 at 10:09 PM
MarkBaker wrote:
mcameron123 wrote:

It just seems that 700ish rows is a fairly small recordset.  I use the same function to create a csv with 13,000 records easily.  I was hoping to create spreadsheets that are a little more robust using PHPExcel.


700 rows isn't a particularly meaningful measure. A row can contain just one column, or it can contain 256 columns... and the difference in memory requirements between those is about 256 times as much. Each cell in your workbook is a cell object, taking up about 1k of PHP's memory (on a 32-bit server; about 1.6k on a 64-bit server). The Excel Writers double that requirement, because they build the complete binary in memory as well, while the CSV writer writes each line as it is generated.

A lot has been written on how to use memory more efficiently on this forum, and there are some links to specific discussion threads in the FAQ. I'm not going to repeat it all; but cell caching is one tool, that can reduce the memory requirements for each cell. The costs is slower performance. There are also threads about improving execution speed, such as using the worksheet fromArray() method to set a bock of cells at a time, or disabling calculation when saving using a writer's setPreCalculateFormulas(false) method.

 

Apr 19, 2011 at 10:27 PM

Thanks Mark

I will investigate your suggestions.  I am not getting an out of memory error but an execution timeout.  My data is 24 columns wide. 

The exact error is 

Fatal error:  Maximum execution time of 30 seconds exceeded in /home/mike/env/lib/PHPExcel/PHPExcel/Cell/DefaultValueBinder.php on line 77 

Apr 20, 2011 at 2:56 PM

mcameron,

I am getting the same error, so I would be interested in knowing if you find a solution. I have been able to generate an Excel file with 3300 rows, but as soon as I add borders to the cells, it times out on me. I timed the step by step execution of the script and total time is 1:30 - 1:00 for database queries (an issue that will be resolved with a new server) and 30 seconds for the rest of the script to manipulate the data annd output the Excel. I noticed when I get the timeout error the file path I'm given is different every time, so it's not specific to a particular method or script in PHPExcel, but more the generally the overall execution time for conversion to Excel. I'll look at other posts as suggested by MarkBaker, but wanted to make my info known so it might help you solve this.

 

Coordinator
Apr 20, 2011 at 8:36 PM

@mcameron123

Using the fromArray() method will improve speed of setting the cell values

@denewey

When you set your border styling, use the applyFromArray() method, and apply to ranges rather than individual cells, as mcameron123 is doing

@both of you

Especially if you're not using formulae (though it can still help even if you are), set the writer setPreCalculateFormulas(false) before saving.