$objWriter->save($str_filename); takes a very long time. Is there a way to shorten the time consumption?

Topics: Developer Forum, Project Management Forum, User Forum
Sep 18, 2012 at 7:52 AM

I create a writer with an changed reader,

but i found the work of saving of the writer takes a very long time,so i wonder that if there is a way to help me to shorten the time...

Coordinator
Sep 18, 2012 at 12:12 PM

Please be slightly more specific in your questions.... reading and writing files aren't instantaneous, but as every reader and every writer is completely different your question doesn't really give me any clues on how to answer. Try reading some of the threads on this forum about performance.

Sep 19, 2012 at 2:48 AM
MarkBaker wrote:

Please be slightly more specific in your questions.... reading and writing files aren't instantaneous, but as every reader and every writer is completely different your question doesn't really give me any clues on how to answer. Try reading some of the threads on this forum about performance.

Thank u very much for replying !

what am doing is to get data in Mysql and set the data to the DropDown value of some of the many columns in the excel with 2001 rows.

1: I need create a reader with an existing excel file which has 20001 rows(one title row and 2000 empty rows) and 40 columns.

   $objReader = PHPExcel_IOFactory::createReader($excel_type);
   $objReaderTemplateExcel = $objReader->load($file_name);

2: I then set the data from Mysql to the DropDown value of some cells of the 40 cells of row with  row-index 2(the row with row-index 1 is the title row).

// this code below is in an loop with $k form 'A' to 'AQ'
//set row with row-index 2
$readerSheet = $objReaderTemplateExcel->getSheet($i);//
$objValidation=$readerSheet->getCell($k."2")->getDataValidation();            // A2,B2,C2....
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST )  		  
			               ->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION )
			               ->setAllowBlank(true)  
			               ->setShowInputMessage(true)  
			               ->setShowErrorMessage(true)  
			               ->setShowDropDown(true)
$str_len = strlen($list);  //$list is a string with comma delimiter
if($str_len>=255){
	$sheetName = $readerSheet->getTitle();            					
		$str_list_arr = explode(',', $list);$list=null;unset($list);   
		if($str_list_arr)   
			  foreach($str_list_arr as $i =>$d){  
					 $c = $this->spaceColumn.($i+2);  
					 $readerSheet->setCellValue($c,$d);				                       
			   }   
		 $endcell = '$'.$this->spaceColumn.'$'.($i+2);  
		 $objValidation->setFormula1($sheetName."!$".$this->spaceColumn."$2:".$endcell);				        					 
		 $readerSheet->getColumnDimension($this->spaceColumn)->setVisible(false);
		 $str_list_arr=null;unset($str_list_arr);		 
} 
else $objValidation->setFormula1('"' . $list . '"');  	

3:I clone the row with row-index 2 1999 times.

//this code below is in a loop with $k from 'A' to 'AQ'
//row 3 to  row 2001(title row and 2000 data rows )
for($j=3;$j<=$this->highestRow;$j++)
{   
	$readerSheet->getCell("$k$j")->setDataValidation(clone $objValidation);			    	
}

4:I create a witer to write the changed ojbReader to excel file to the Apache server.

	$objWriter = PHPExcel_IOFactory::createWriter ( $objReaderTemplateExcel,'Excel5');//writer	
      $objWriter->save($str_file_store_name);//save  file to server disk

5:At last,a will get an excel file with 2000 rows(one title row and the other 1999 rows  with just DropDown value).

 

 

And i have found that it would take a very long time in progress 4 in the 

		$objWriter->save($str_file_store_name);//save  file to server disk

 code.

 

Can i get any suggestions ?

 

Coordinator
Sep 19, 2012 at 11:29 AM

Critical information is that you're talking about the Excel5 Writer.

Use

$objWriter->setPreCalculateFormulas(FALSE);

to prevent the writer from testing to see if there are any formula cells and executing their calculation when it saves.

 

Otherwise, you'll have to live with the speed: PHPExcel needs time to build the xls binary streams and insert them into the OLE container that it builds.

This is why larger workbooks are best generated in the background rather than within a user request

Sep 20, 2012 at 3:20 AM
MarkBaker wrote:

Critical information is that you're talking about the Excel5 Writer.

Use

$objWriter->setPreCalculateFormulas(FALSE);

to prevent the writer from testing to see if there are any formula cells and executing their calculation when it saves.

 

Otherwise, you'll have to live with the speed: PHPExcel needs time to build the xls binary streams and insert them into the OLE container that it builds.

This is why larger workbooks are best generated in the background rather than within a user request

but i do use formula:

 

 $objValidation->setFormula1($sheetName."!$".$this->spaceColumn."$2:".$endcell);

 

Coordinator
Sep 20, 2012 at 10:52 AM

It doesn't matter whether you use formulas or not... When an Excel workbook is opened in MS Excel, there is a "dirty" flag that tells Excel whether it needs to recalculate formulas itself or not. If PHPExcel calculates the formulas on save, then this flag is set to false so MS Excel doesn't need to calculate them; if PHPExcel doesn't calculate the formulas, then the flag is set to true and MS Excel will calculate them. It means that opening the file in MS Excel is slightly slower, but makes saving the file in PHPExcel faster.

Sep 20, 2012 at 11:14 AM
MarkBaker wrote:

It doesn't matter whether you use formulas or not... When an Excel workbook is opened in MS Excel, there is a "dirty" flag that tells Excel whether it needs to recalculate formulas itself or not. If PHPExcel calculates the formulas on save, then this flag is set to false so MS Excel doesn't need to calculate them; if PHPExcel doesn't calculate the formulas, then the flag is set to true and MS Excel will calculate them. It means that opening the file in MS Excel is slightly slower, but makes saving the file in PHPExcel faster.

Thank u very much!! I have learned a lot