PHPExcel GetCalculatedValue() 返回错误值

Question

我有一个包含 6 张纸的 .xlsx 文件，我用它加载

$objPHPExcel = PHPExcel_IOFactory::load('./FINAL SEB.xlsx');

当我想从单元格中获取计算值时，我得到了错误的值。这个单元格有一个公式

(IF(COMPARATEUR!B6=20,EMPRUNT20ANS!F32,IF(COMPARATEUR!B6=25,EMPRUNT25ANS!F37,IF(COMPARATEUR!B6=15,EMPRUNT15ANS!F27,"Erreur années"))))-B35

在我的 PHP 代码中我这样做

echo $worksheet->getCell('B38')->getCalculatedValue().'<br />';

调试时得到

Formula Value is=(IF(COMPARATEUR!B6=20,EMPRUNT20ANS!F32,IF(COMPARATEUR!B6=25,EMPRUNTANS!F37,IF(COMPARATEUR!B6=15,EMPRUNT15ANS!F27,"Erreur années"))))-B35
Expected Value is 59045.580877165
Parser Stack :-
array (size=21)
  0 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'COMPARATEUR!B6' (length=14)
      'reference' => string 'COMPARATEUR!B6' (length=14)
  1 => 
    array (size=3)
      'type' => string 'Value' (length=5)
      'value' => int 20
      'reference' => null
  2 => 
    array (size=3)
      'type' => string 'Binary Operator' (length=15)
      'value' => string '=' (length=1)
      'reference' => null
  3 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'EMPRUNT20ANS!F32' (length=16)
      'reference' => string 'EMPRUNT20ANS!F32' (length=16)
  4 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'COMPARATEUR!B6' (length=14)
      'reference' => string 'COMPARATEUR!B6' (length=14)
  5 => 
    array (size=3)
      'type' => string 'Value' (length=5)
      'value' => int 25
      'reference' => null
  6 => 
    array (size=3)
      'type' => string 'Binary Operator' (length=15)
      'value' => string '=' (length=1)
      'reference' => null
  7 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'EMPRUNTANS!F37' (length=14)
      'reference' => string 'EMPRUNTANS!F37' (length=14)
  8 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'COMPARATEUR!B6' (length=14)
      'reference' => string 'COMPARATEUR!B6' (length=14)
  9 => 
    array (size=3)
      'type' => string 'Value' (length=5)
      'value' => int 15
      'reference' => null
  10 => 
    array (size=3)
      'type' => string 'Binary Operator' (length=15)
      'value' => string '=' (length=1)
      'reference' => null
  11 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'EMPRUNT15ANS!F27' (length=16)
      'reference' => string 'EMPRUNT15ANS!F27' (length=16)
  12 => 
    array (size=3)
      'type' => string 'Value' (length=5)
      'value' => string '"Erreur années"' (length=16)
      'reference' => null
  13 => 
    array (size=3)
      'type' => string 'Operand Count for Function IF()' (length=31)
      'value' => int 3
      'reference' => null
  14 => 
    array (size=3)
      'type' => string 'Function' (length=8)
      'value' => string 'IF(' (length=3)
      'reference' => null
  15 => 
    array (size=3)
      'type' => string 'Operand Count for Function IF()' (length=31)
      'value' => int 3
      'reference' => null
  16 => 
    array (size=3)
      'type' => string 'Function' (length=8)
      'value' => string 'IF(' (length=3)
      'reference' => null
  17 => 
    array (size=3)
      'type' => string 'Operand Count for Function IF()' (length=31)
      'value' => int 3
      'reference' => null
  18 => 
    array (size=3)
      'type' => string 'Function' (length=8)
      'value' => string 'IF(' (length=3)
      'reference' => null
  19 => 
    array (size=3)
      'type' => string 'Cell Reference' (length=14)
      'value' => string 'B35' (length=3)
      'reference' => string 'B35' (length=3)
  20 => 
    array (size=3)
      'type' => string 'Binary Operator' (length=15)
      'value' => string '-' (length=1)
      'reference' => null
Calculated Value is 9860500
Evaluation Log:
null

你能帮帮我吗？

附言对不起我的英语。

Answer 1

如果您使用我之前在此处发布的标准化 testFormula() 调试代码来转储您显示的跟踪，那么当计算引擎从单例修改为 1.7.9 版时，这已发生变化一种多模式，以避免在一次处理多个工作簿文件时出现问题。该代码的更新版本如下所示:

function testFormula($sheet,$cell) {
    $formulaValue = $sheet->getCell($cell)->getValue();
    echo 'Formula Value is' , $formulaValue , PHP_EOL;
    $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
    echo 'Expected Value is '  , ((!is_null($expectedValue)) ? $expectedValue : 'UNKNOWN') , PHP_EOL;


    $calculate = false;
    try {
        $tokens = PHPExcel_Calculation::getInstance(
            $sheet->getParent()
        )->parseFormula(
            $formulaValue,
            $sheet->getCell($cell)
        );
        echo 'Parser Stack :-' , PHP_EOL;
        print_r($tokens);
        echo PHP_EOL;
        $calculate = true;
    } catch (Exception $e) {
        echo 'PARSER ERROR: ' , $e->getMessage() , PHP_EOL;

        echo 'Parser Stack :-' , PHP_EOL;
        print_r($tokens);
        echo PHP_EOL;
    }

    if ($calculate) {
        PHPExcel_Calculation::getInstance(
            $sheet->getParent()
        )->getDebugLog()
        ->setWriteDebugLog(true);
        try {
            $cellValue = $sheet->getCell($cell)->getCalculatedValue();
            echo 'Calculated Value is ' , $cellValue , PHP_EOL;

            echo 'Evaluation Log:' , PHP_EOL;
            print_r(
                PHPExcel_Calculation::getInstance(
                    $sheet->getParent()
                )->getDebugLog()
                ->getLog()
            );
            echo PHP_EOL;
        } catch (Exception $e) {
            echo 'CALCULATION ENGINE ERROR: ' , $e->getMessage() , PHP_EOL;

            echo 'Evaluation Log:' , PHP_EOL;
            print_r(
                PHPExcel_Calculation::getInstance(
                    $sheet->getParent()
                )->debugLog
                ->getLog()
            );
            echo PHP_EOL;
        }
    }
}


$sheet = $objPHPExcel->getActiveSheet();
testFormula($sheet,'A1');

请尝试使用以上帮助诊断问题