PHPExcel 1.8.0-通过克隆模板工作表创建许多工作表，每个克隆操作都会变慢 [英] PHPExcel 1.8.0 - Creating many sheets by cloning a template sheet gets slower with each clone

问题描述

我正在使用PHPExcel 1.8.0开发报表工具.我需要不断地克隆模板表，并用数据填充它们.最初的工作表生成速度非常快，但是克隆每个工作表的过程变得越来越慢.这是我用于克隆工作表的代码:

I am working on a reporting tool using PHPExcel 1.8.0. I need to constantly clone template sheets and fill them with data. The first sheets are generated very fast, but this process gets slower and slower with each sheet cloned. Here is the code i use for cloning 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;

使用microtime()测量执行时间(以秒为单位)可以从一张纸上创建多达24个克隆(每个克隆有2个样本)为我提供了这一点:

Measuring the execution time in seconds using microtime() for creating up to 24 clones off of one sheet (2 Samples for every clone) gives me this:

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 summarized difference is the time spend only for cloning a sheet.

是否有这种现象的原因?我如何加快速度?

推荐答案

我进一步研究了这个问题，似乎不是克隆本身会导致一切变慢，而是工作表的setTitle()方法(第794-848行) &更新其中的公式引用(第843和844行).我记录了这次通话的时间:

I investigated the problem further and it seems that not the cloning itself causes everything to get slower but the setTitle() method of the worksheet (Lines 794-848) & the update of the formula references inside it (Line 843 & 844). I logged 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");

结果:

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有一个禁用此更新的标志.

The setTitle has a flag to disable this update.

禁用它可以节省18秒的时间，这对我来说是可以接受的解决方案，因为我不使用此类引用.

这篇关于PHPExcel 1.8.0-通过克隆模板工作表创建许多工作表，每个克隆操作都会变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！