Jonas Ivy V. Imperial
Jonas Ivy V. Imperial

Reputation: 125

phpExcel: getCalculatedValue() returns #VALUE

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! :)

Upvotes: 8

Views: 30940

Answers (6)

//you can set like this

$objPHPExcel->getActiveSheet()->setCellValue('A20','=AE19 * I19');

//because php excel define the code in string so you will get error. because in phpexcel you must use the formula excel. can't make like this A1+A2 or ohters.

You can multiply in php like this

$bil1 = $objPHPExcel->getActiveSheet()->getCell("AE19")->getValue()

$bl2 = $objPHPExcel->getActiveSheet()->getCell("I19")->getValue()

//the function multiply.

$total=$bil1*$bil2;

after that you can set the total in you cell

$objPHPExcel->getActiveSheet()->setCellValue('A20',$total);

This is working in my php.

Upvotes: 0

Gavin
Gavin

Reputation: 762

I think the root of this problem comes when a cell is empty. When a cell is set to an empty string, some formulas can fail. Here is an example formula which fails:

=TEXT(A1, "mm/dd/yyyy")

In order to deal with this "#VALUE!" problem, on a small project I'm setting empty cells to NULL instead of ''.

$target = ($target == '')? NULL: $target; // without this, empty cells end up being #VALUE! after some formulas

Upvotes: 0

pancy1
pancy1

Reputation: 511

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.

Upvotes: 4

Aaron Brown
Aaron Brown

Reputation: 11

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.

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212452

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

Upvotes: 6

40pro
40pro

Reputation: 1281

You should try opening up the original Excel file and look at cell AF19. Most likely even MS Excel itself is having trouble calculating the value because of there is something wrong with the formula located at AF19.

It could be that

This formula is trying to multiply two cells that doesnt contain number

=AE19*I19

AE19 is contains a comma and isn't formatted as a number so PHPExcel sees it as a string

in such case, you could try formatting the data in excel first by right clicking (in excel) and specify numeric format.

Upvotes: 0

Related Questions