phpExcel: getCalculatedValue() returns #VALUE

Question

Can someone help me with phpExcel Code:

This Codes:

$objPHPExcel->getActiveSheet()->getCell("AF19")->getCalculatedValue();
$objPHPExcel->getActiveSheet()->getCell("AF19")->getFormattedValue();
$objPHPExcel->getActiveSheet()->getCell("AF19")->getValue();

Returns:

#VALUE!
#VALUE!
=AE19*I19

Thank you in advance! :)

Answer 1

To help debug the problem, run the calculation in DEBUG mode:

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()->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) {
        try {
            $cellValue = $sheet->getCell($cell)->getCalculatedValue();
            echo 'Calculated Value is ' , $cellValue , PHP_EOL;

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

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

$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;

testFormula($sheet,'AF19');

The output from this should help diagnose the problem

Answer 2

If you are unsure about the content of a cell (value or formula included), I recommend you to primarily do a check if the cell has a formula and then copy - paste accordingly. getOldCalculatedValue() is very helpful in this case. Here is an example of that:

$code = $sheet->getCell('A'.$y)->getValue();
if(strstr($code,'=')==true)
{
    $code = $sheet->getCell('A'.$y)->getOldCalculatedValue();
}
$objPHPExcel4->setActiveSheetIndex(0)
             ->setCellValue('A'.$l, $code);

For large data sets, getCalculatedValue() function is really cumbersome and lots of memory will be required to perform correctly.

Answer 3

I came across a similar problem, getCalculatedValue() returned #VALUE! on a cell which referenced another cell which contained a formula.

The cause ended up being one of the cells referenced containing a NULL value, even though Excel handled this correctly. All I had to do was add a value of 0 to that cell and it calculated fine.