我有一个.xlsx文件,里面有6张纸,我用加载
$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 />';
当我去Bug时,我获得
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
你能帮我吗?
附言:对不起我的英语。
如果您使用我之前在这里发布的标准化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');
请尝试使用以上内容来帮助诊断问题