Excel 2007 Writer Processing Time

Topics: Developer Forum
Oct 20, 2011 at 5:15 PM

Hi,  I am was using version 1.7.5 and have a portion of my application were I need to create an excel file from a template, but add data to it from a MySQL database.  The issue is that, once I get over 200 rows I get a timeout from the browser.  Does anyone else create something that has maybe 1,000 new rows added to it without problems?  I have been thinking of running it on a separate process, but before I try that, I wanted to see if maybe I was missing something.  Here is the main function that I use:

I recently tried to chunk (do{...}while() block) the query to see if that was the problem, but it also did not work.  Any thoughts or suggestions? 

function run_excel($quoteId)
	{
		set_time_limit(0);
		
		$this->phpexcel->objReader = PHPExcel_IOFactory::createReader('Excel2007');
		$this->phpexcel->objPHPExcel = $this->phpexcel->objReader->load("templates/quotetemplate.xlsx");
		$sheet = $this->phpexcel->objPHPExcel->getActiveSheet();
		
		$rowCount = $this->get_quote_items_count($quoteId);
		
		
		//----------------------------------
		//Header info
		$sheet->setCellValue('G4', $data['info']->quote_id);
		$sheet->setCellValue('B8', $data['info']->company_name);
		$sheet->setCellValue('B9', $data['info']->project_name);
		$fullName = $data['info']->first_name . ' ' . $data['info']->last_name;
		$sheet->setCellValue('B10', $fullName);
		$sheet->setCellValue('A15', $data['info']->project_rep);
		$sheet->setCellValue('B15', $data['info']->project_number);
		$sheet->setCellValue('G6', $data['info']->customer_id);
		$sheet->getStyle('A14:G14')->applyFromArray(
					array(
						'fill' 			=> array(
							'type'			=> PHPExcel_Style_Fill::FILL_SOLID,
							'color'			=> array('argb' => '000000')
						),
						'font'			=> array(
							'color'			=> array('argb' => 'ffffff'),
							'bold'			=> true
						),
					  	'borders' 		=> array(
							'bottom'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
							'right'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
							'left'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
							'top'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
						)
					)
				);
		
		$sheet->getStyle('A17:G17')->applyFromArray(
					array(
						'fill' 			=> array(
							'type'			=> PHPExcel_Style_Fill::FILL_SOLID,
							'color'			=> array('argb' => '000000')
						),
						'font'			=> array(
							'color'			=> array('argb' => 'ffffff'),
							'bold'			=> true
						),
					  	'borders' 		=> array(
							'bottom'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
							'right'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
							'left'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
							'top'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
						)
					)
				);
		
		
		//Install & Shipping
		($data['info']->ovr_install == 1) ? $sheet->setCellValue('G22', $data['info']->install_val) : $sheet->setCellValue('G22', '=G21 * '.$data['info']->install_perc.'%');
		
		($data['info']->ovr_ship == 1) ? $sheet->setCellValue('G23', $data['info']->ship_val) : $sheet->setCellValue('G23', '=G21 * '.$data['info']->ship_perc.'%');
		
		//--------------------------------------------------
		//Process data
		$baseRow = 19;	
		$row = $baseRow;
		$group = '';
		$continue = TRUE;
		$rowLimit = 50;
		
		do {
			
			$data = $this->get_quote_items_xls($quoteId, 0, $rowLimit);
			
			foreach($data['rows'] as $r => $dataRow)
			{
				set_time_limit(30);
				if($data['combined'] == 0)
				{
					if($group != $dataRow->groups)
					{
						$group = $dataRow->groups;
						$sheet->insertNewRowBefore($row,2);
						
						$sheet->setCellValue('A'.$row, strtoupper($dataRow->groups));
						$sheet->mergeCells('A'.$row.':G'.$row);
						
						$sheet->getStyle('A'.$row.':G'.$row)->applyFromArray(
							array(
								'alignment' 	=> array(
									'horizontal'	=> PHPExcel_Style_Alignment::HORIZONTAL_LEFT
								),
								'fill' 			=> array(
									'type'			=> PHPExcel_Style_Fill::FILL_SOLID,
									'color'			=> array('argb' => '000000')
								),
								'font'			=> array(
									'color'			=> array('argb' => 'ffffff'),
									'bold'			=> true
								),
								'borders' 		=> array(
									'bottom'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
									'right'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
									'left'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
									'top'			=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
								)
							)
						);
						$row = $row + 2;
					}
				}
			
				$sheet->insertNewRowBefore($row,1);
				
				$sheet->setCellValue('A'.$row, $dataRow->qty);
				$sheet->setCellValue('B'.$row, $dataRow->catalog_number);
				$sheet->setCellValue('C'.$row, $dataRow->long_description);
				$sheet->mergeCells('C'.$row.':D'.$row);
				$sheet->setCellValue('E'.$row, '=M'.$row);
				
				$lineTotal = '=E'.$row.'*A'.$row;
				$sheet->setCellValue('G'.$row, $lineTotal);
				$sheet->setCellValue('J'.$row, $dataRow->base_cost);
				$sheet->setCellValue('K'.$row, 1.0); //escalation
				$sheet->setCellValue('L'.$row, $dataRow->margin);
				
				$sell = '=((J'.$row.'*K'.$row.')/(1-L'.$row.'))';
				$sheet->setCellValue('M'.$row, $sell);
				$sheet->setCellValue('N'.$row, ($dataRow->commission_prc <= 0)? 0 : '='.$dataRow->commission_prc.'/100');
				
				$finMargin = '=(M'.$row.'-((M'.$row.'*N'.$row.')+J'.$row.'))/M'.$row;
				$sheet->setCellValue('O'.$row, $finMargin);
				
				//increment row
				$row = $row + 1;
			
			}
			
			if($rowLimit > $rowCount)
			{
				$continue = FALSE;
			}
			
			$rowLimit = $rowLimit + 50;
			
			
		} while ($continue == TRUE);
		
			
		$sheet->removeRow(18,1);
		
		$printRow = $row + 8;
		$sheet->getPageSetup()->setPrintArea("A1:G".$printRow);
		$sheet->getPageSetup()->setFitToWidth(1, true);
		
		
		$fileName = $quoteId.'-'.date('Y-m-d').'.xlsx';
		$file = 'uploads/quotes/'.$fileName;
		if(file_exists($file))
		{
			unlink($file);
		}
			
		$this->phpexcel->objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel->objPHPExcel, 'Excel2007');
		$this->phpexcel->objWriter->save($file);
		$this->phpexcel->objPHPExcel->disconnectWorksheets();
		
		unset($this->objWriter);
		unset($this->objReader);
		unset($this->objPHPExcel);
		
		$response = array(
			'success' => TRUE,
			'file' => $fileName
		);
		
		return $response;
		
	}

Jun 19, 2014 at 12:50 PM
Adding borders to cells can significantly increase the time to generate the Excel file. I had a script that was generating a file containing 15 column and 1500 rows - it took 150sec to build with borders on the cells, only 25 secs without the borders.
Coordinator
Jun 19, 2014 at 1:48 PM
It's also possible to benefit by applying allborders to a range of cells, rather than applying borders to individual cells