如何使用PHPExcel改善配方计算？ [英] How to improve formula calculation with PHPExcel?

问题描述

我是PHPExcel的新手，而且我已经挖掘了谷歌，但是在这里找不到我的具体问题。我发现更新/评估我的工作表中的公式是真的慢。

I'm new to PHPExcel, and I've scoured the Googles, but not finding much for my specific problem here. I'm finding that updating/evaluating the formulas in my sheet is being really slow.

我有一个很小的excel文件我正在做一些非常基本的目标寻求（PHP中的目标寻求，Excel表格中的最终结果计算）。我已经准确地工作，但速度绝对是杀死我的。似乎公式计算是责怪 - 如何加快公式计算/更新？

I've got a pretty small excel file on which I'm doing some very basic goal-seeking (goal seeking done in PHP, end result calculation done in excel sheet). I've got it working accurately, but the speed is absolutely killing me. It appears that the formula calculation is to blame -- how can formula calculations/updates be sped up?

不幸的是，我不能发布作为内容的excel文件的副本是我公司的商业秘密，但这并不是普通的。公式中非常简单的算术。我可以想到的唯一的一个可能在这里可能产生的影响是，一些细胞依赖关系链可能有点长（15-ish依赖）。

Unfortunately, I can't post a copy of the excel file as the contents are a trade secret for my company, but it's nothing out of the ordinary. Very simple arithmetic in the formulas. The only thing I can think of that might have an effect here is that some of the cell-dependency chains can be somewhat long (15-ish dependencies).

当你可以从下面的输出中看出，我们只执行11次迭代，用于寻找目标，总共需要4-5秒。因为这将是一个AJAX服务，我真的需要它比这更快。

As you can see from the output below, we're only executing 11 iterations for the goal seeking, and taking a total of 4-5 seconds. Since this will be an AJAX service, I really need it to be faster than that.

是非常快速和肮脏的概念验证码，请与我联系：

This is very quick and dirty proof-of-concept code, please bear with me:

<?php

Stopwatch::start();

$inputFileType = PHPExcel_IOFactory::identify( './example.xlsx' );
var_dump( 'FileType: '.$inputFileType );
Stopwatch::rel( 'identify filetype' );

$objReader = PHPExcel_IOFactory::createReader( $inputFileType );
$objReader->setReadDataOnly( true );
$filterSubset = new ReadFilter( 1, 35, range( 'A', 'J' ));
$objReader->setReadFilter( $filterSubset );
Stopwatch::rel( 'create reader' );

$objPHPExcel = $objReader->load( $inputFileName );
Stopwatch::rel( 'load file' );

$data = $objPHPExcel->getSheetByName( 'Data' );
$inputCell  = $data->getCell( 'B9' );
$outputCell = $data->getCell( 'B35' );
Stopwatch::rel( 'get cells' );

goalSeek( $inputCell, $outputCell, '0.10', 1, 5 );







function goalSeek( $inputCell, $outputCell, $targetValue ) {
    $cellValue = function() use ( &$outputCell, $precision ) {
        return round( $outputCell->getCalculatedValue(), $precision );
    };

    $setValue = function( $value ) use ( &$inputCell, &$objPHPExcel, $cellValue ) {
        $inputCell->setValue( $value );
        PHPExcel_Calculation::getInstance( $objPHPExcel )->clearCalculationCache(); // -- clear cache so updates are calculated
        Stopwatch::rel( 'goal-seek' );
    };

    // -- very basic goal seeking psuedo-code
    while( $stillHunting ) { // -- outside tolerance
        $setValue( $newInputValue );
    }
};

class ReadFilter implements PHPExcel_Reader_IReadFilter {
    private $_startRow = 0;
    private $_endRow   = 0;
    private $_columns  = [];

    public function __construct( $startRow, $endRow, $columns ) {
        $this->_startRow = $startRow;
        $this->_endRow   = $endRow;
        $this->_columns  = $columns;
    }

    public function readCell( $column, $row, $worksheetName = '' ) {
        if( $row >= $this->_startRow && $row <= $this->_endRow ) { // -- valid row
            if( in_array( $column, $this->_columns )) { // -- valid column
                return true;
            }
        }
        // else (implicit)

        return false;
    }
}

输出

Output

string 'FileType: Excel2007' (length=19)

array (size=2)
  'rel' => 
    array (size=17)
      'identify' => float 0.008597135543823242
      'create reader' => float 0.0001199245452880859
      'load file' => float 0.387645959854126
      'get cells' => float 5.292892456054688E-5
      'goal-seek' => float 0.4194750785827637
      'goal-seek2' => float 0.3829901218414307
      'goal-seek3' => float 0.3478608131408691
      'goal-seek4' => float 0.3471150398254395
      'goal-seek5' => float 0.3569440841674805
      'goal-seek6' => float 0.378180980682373
      'goal-seek7' => float 0.3683559894561768
      'goal-seek8' => float 0.3778479099273682
      'goal-seek9' => float 0.3664979934692383
      'goal-seek10' => float 0.4503841400146484
      '_avg' => float 0.2794940630594889
      '_untilStop' => float 0.5339441299438477
  'total' => float 4.726345062255859

推荐答案

如果您重新计算相同的公式，但相关单元格中的值不同，则需要解析公式一次，只能解析一次，但执行多次。

OK, one possible solution that might speed things up if you're recalculating the same formula, but with different values in related cells is to parse the formula once, and only once, but execute multiple times.

getCalculatedValue（）调用两个方法;第一个是 parseFormula（），它接受公式作为一个字符串，并构建一个解析器堆栈（作为一个数组）执行该公式的步骤;第二个（私有方法，因此您需要在Calculation.php中将其更改为public）是 processTokenStack（），它接受3个参数，由调用 parseFormula（），单元格ID（作为字符串）和单元格对象。

getCalculatedValue() calls two methods; the first is parseFormula() which accepts the formula as a string, and builds a parser stack (as an array) of steps for the execution of that formula; the second (a private method, so you'd need to change that to public in Calculation.php) is processTokenStack() which accepts 3 arguments, the token stack generated by the call to parseFormula(), the cell ID (as a string) and the cell object.

可能是可能只能执行parseFormula（）步骤一次，然后为每次迭代调用 processTokenStack（），这将消除除第一次迭代之外的所有解析步骤

It might be possible for you to execute the parseFormula() step only once, and then call the processTokenStack() for each iteration, which would eliminate the parse step for all but the first iteration

这篇关于如何使用PHPExcel改善配方计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！