I have tried using the debug tool posted in reply to similar questions but was unable to find a solution to my problem. My excel sheet looks as follows: G36 = SUM(G24:G28). G24->G28 = SUM(B24:G24) and so on.
It returns formula with getValue() and #Value with getCalculatedValue()
Following is the output of the debug tool.
Formula Value is=SUM(G28:G34) Expected Value is UNKNOWN Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => G28 [reference] => G28 ) [1] => Array ( [type] => Cell Reference [value] => G34 [reference] => G34 ) [2] => Array ( [type] => Binary Operator [value] => : [reference] => ) [3] => Array ( [type] => Operand Count for Function SUM() [value] => 1 [reference] => ) [4] => Array ( [type] => Function [value] => SUM( [reference] => ) ) Calculated Value is 0 Evaluation Log: Array ( [0] => Testing cache value for cell Worksheet!G36 [1] => Retrieving value for cell Worksheet!G36 from cache )
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,'G36');
Following is the output after I implemented the changes recommended by @MarkBaker in his answer below.
Formula Value is=SUM(G28:G34) Expected Value is UNKNOWN Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => G28 [reference] => G28 ) [1] => Array ( [type] => Cell Reference [value] => G34 [reference] => G34 ) [2] => Array ( [type] => Binary Operator [value] => : [reference] => ) [3] => Array ( [type] => Operand Count for Function SUM() [value] => 1 [reference] => ) [4] => Array ( [type] => Function [value] => SUM( [reference] => ) ) Calculated Value is 0 Evaluation Log: Array ( [0] => Testing cache value for cell Worksheet!G36 [1] => Worksheet!G36 => Evaluating Cell G28 in current worksheet [2] => Worksheet!G36 => Testing cache value for cell Worksheet!G28 [3] => Worksheet!G36 -> Worksheet!G28 => Evaluating Cell D28 in current worksheet [4] => Worksheet!G36 -> Worksheet!G28 => Testing cache value for cell Worksheet!D28 [5] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluating Cell B28 in current worksheet [6] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluation Result for cell Worksheet!B28 is a floating point number with a value of 10000 [7] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluating Cell C28 in current worksheet [8] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluation Result for cell Worksheet!C28 is a floating point number with a value of 140 [9] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluating 10000 * 140 [10] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluation Result is a floating point number with a value of 1400000 [11] => Worksheet!G36 -> Worksheet!G28 => Evaluation Result for cell Worksheet!D28 is a floating point number with a value of 1400000 [12] => Worksheet!G36 -> Worksheet!G28 => Evaluating Cell F28 in current worksheet [13] => Worksheet!G36 -> Worksheet!G28 => Testing cache value for cell Worksheet!F28 [14] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluating Cell D28 in current worksheet [15] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Testing cache value for cell Worksheet!D28 [16] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Retrieving value for cell Worksheet!D28 from cache [17] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluation Result for cell Worksheet!D28 is a floating point number with a value of 1400000 [18] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluating Cell E28 in current worksheet [19] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluation Result for cell Worksheet!E28 is a string with a value of "3%" [20] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluating 1400000 * "3%" [21] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluation Result is a a #VALUE! error [22] => Worksheet!G36 -> Worksheet!G28 => Evaluation Result for cell Worksheet!F28 is a #VALUE! error [23] => Worksheet!G36 -> Worksheet!G28 => Evaluating 1400000 + "#VALUE!" [24] => Worksheet!G36 -> Worksheet!G28 => Evaluation Result is a #VALUE! error [25] => Worksheet!G36 => Evaluation Result for cell Worksheet!G28 is a #VALUE! error [26] => Worksheet!G36 => Evaluating Cell G34 in current worksheet [27] => Worksheet!G36 => Evaluation Result for cell Worksheet!G34 is a NULL value [28] => Worksheet!G36 => Evaluating Range "Worksheet!G28" : "Worksheet!G34" [29] => Worksheet!G36 => Testing cache value for cell Worksheet!G28 [30] => Worksheet!G36 => Retrieving value for cell Worksheet!G28 from cache [31] => Worksheet!G36 => Testing cache value for cell Worksheet!G29 [32] => Worksheet!G36 -> Worksheet!G29 => Evaluating Cell D29 in current worksheet [33] => Worksheet!G36 -> Worksheet!G29 => Testing cache value for cell Worksheet!D29 [34] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluating Cell B29 in current worksheet [35] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluation Result for cell Worksheet!B29 is a floating point number with a value of 5000 [36] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluating Cell C29 in current worksheet [37] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluation Result for cell Worksheet!C29 is a floating point number with a value of 102 [38] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluating 5000 * 102 [39] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluation Result is a floating point number with a value of 510000 [40] => Worksheet!G36 -> Worksheet!G29 => Evaluation Result for cell Worksheet!D29 is a floating point number with a value of 510000 [41] => Worksheet!G36 -> Worksheet!G29 => Evaluating Cell F29 in current worksheet [42] => Worksheet!G36 -> Worksheet!G29 => Testing cache value for cell Worksheet!F29 [43] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluating Cell D29 in current worksheet [44] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Testing cache value for cell Worksheet!D29 [45] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Retrieving value for cell Worksheet!D29 from cache [46] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluation Result for cell Worksheet!D29 is a floating point number with a value of 510000 [47] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluating Cell E29 in current worksheet [48] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluation Result for cell Worksheet!E29 is a string with a value of "3%" [49] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluating 510000 * "3%" [50] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluation Result is a a #VALUE! error [51] => Worksheet!G36 -> Worksheet!G29 => Evaluation Result for cell Worksheet!F29 is a #VALUE! error [52] => Worksheet!G36 -> Worksheet!G29 => Evaluating 510000 + "#VALUE!" [53] => Worksheet!G36 -> Worksheet!G29 => Evaluation Result is a #VALUE! error [54] => Worksheet!G36 => Evaluating Function SUM() with 1 argument [55] => Worksheet!G36 => Evaluating SUM( { "#VALUE!"; "#VALUE!"; ; ; ; ; } ) [56] => Worksheet!G36 => Evaluation Result for SUM() function call is an integer number with a value of 0 ) 14:16:49 Write to Excel2007 format