PHPExcel 1.8.0 - Creating multiple sheets by cloning a template sheet becomes slower with each clone

Question

I am working on a reporting tool using PHPExcel 1.8.0. I need to constantly clone the template sheets and fill them with data. The first sheets are generated very quickly, but this process becomes slower and slower with each cloning of the sheet. Here is the code I use to clone sheets:

$resultSheet = clone $sourceSheet; // instance of PHPExcel_Worksheet $resultSheet->setTitle($newSheetName); $sourceSheet->getParent()->addSheet($resultSheet,0); $sourceSheet->getParent()->setActiveSheetIndex($sourceSheet->getParent()->getIndex($resultSheet)); return $resultSheet; 

Measuring the execution time in seconds using microtime () to create up to 24 clones on one sheet (2 samples for each clone) gives me the following:

 duplicateSheet (2 Samples) --- 0.046000003814697 duplicateSheet (2 Samples) --- Summarized difference: 0.046000003814697 duplicateSheet (4 Samples) --- 0.50999999046326 duplicateSheet (4 Samples) --- Summarized difference: 0.21099996566772 duplicateSheet (6 Samples) --- 0.69600009918213 duplicateSheet (6 Samples) --- Summarized difference: 0.39299988746643 ... duplicateSheet (46 Samples) --- 21.375 duplicateSheet (46 Samples) --- Summarized difference: 20.99299955368 duplicateSheet (48 Samples) --- 23.653000116348 duplicateSheet (48 Samples) --- Summarized difference: 23.266999483109 

The total difference is the time spent only on cloning the sheet.

Is there a reason for this behavior? How can I speed this up?

Answer 1

I investigated the problem further, and it seems that not the cloning itself causes everything to slow down, but the setTitle () method of the worksheet (lines 794-848) and updating the links to the formulas inside it (lines 843 and 844). I recorded the time around this call:

  $logger = Zend_Registry::get('microtimeLogger'); $logger->log("updateFormulaCellReferences"); if ($updateFormulaCellReferences) { PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->_parent, $oldTitle, $newTitle); } $logger->log("updateFormulaCellReferences"); 

Result:

 duplicateSheet (2 Samples) --- 0.041999816894531 duplicateSheet (2 Samples) --- Summarized difference: 0.041999816894531 updateFormulaCellReferences (24 Samples) --- 0.60699987411499 updateFormulaCellReferences (24 Samples) --- Summarized difference: 0.036999940872192 duplicateSheet (4 Samples) --- 0.48899984359741 duplicateSheet (4 Samples) --- Summarized difference: 0.19499969482422 updateFormulaCellReferences (26 Samples) --- 1.0539999008179 updateFormulaCellReferences (26 Samples) --- Summarized difference: 0.072999954223633 ... duplicateSheet (48 Samples) --- 23.306999921799 duplicateSheet (48 Samples) --- Summarized difference: 22.933000087738 updateFormulaCellReferences (70 Samples) --- 23.871999979019 updateFormulaCellReferences (70 Samples) --- Summarized difference: 18.763000011444 

SetTitle has a flag to disable this update.

Disabling it saves 18 seconds and is an acceptable solution for me because I do not use such links.