Maby setTitle bug

Topics: Developer Forum
Apr 11, 2012 at 7:05 PM

HI.

I'm creating workbook with about 100 worksheets, and setTitle takes 0.001 seconds on first sheet, and 0.5 seconds on last sheet.

If I remove setTitle, everything is OK.

Here is code

$objPHPExcel = new PHPExcel();
$start0 = (float) array_sum(explode(' ',microtime()));
$dummy=array();
for($i=0;$i<30;$i++) $dummy[]=array(1,2,"some text","more text",4,5,6,7,8,"text");
for($sheet=0;$sheet<100;$sheet++)
{

	$objWorksheet1 = $objPHPExcel->createSheet();
		$start = (float) array_sum(explode(' ',microtime()));  // start time
	$objWorksheet1 ->setTitle("Worksheet_".$sheet);  // this line is problem
		$end = (float) array_sum(explode(' ',microtime())); // end time
	$objWorksheet1->fromArray($dummy, null, "A3");
	
	$objWorksheet1->setCellValueByColumnAndRow(0,1,($end-$start)); // write time to cell
}
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,1,($end-$start0));
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('phpexcel.xlsx');

Coordinator
Apr 11, 2012 at 7:45 PM
Edited Apr 11, 2012 at 7:53 PM

It's a bug if it doesn't work as expected, a performance issue if it's slow.

When you change a worksheet title using setTitle(), the code checks all existing cells on all existing sheets to see if there are any formulae that reference the worksheet that you're renaming, and modify the formula as well. Obviously, the more worksheets you have, the longer this will take.

When you're creating a new sheet and setting the title for the first time, this is clearly an unnecessary overhead: this was a known issue, so the latest SVN code supports a flag in the setTitle() method that suppresses this reference check (although it still performs standard title validation).

 P.S.

Your use of

$start = (float) array_sum(explode(' ',microtime())); 

isn't sensible... the two parts returned by microtime() are measured in microseconds and seconds respectively, so you're adding values in different units.

use

$start = microtime(true);

instead (and the equivalent for $end)