PhpExcel: getCalculatedValue () returns #VALUE

Question

Can someone help me with phpExcel code:

These codes are:

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

Return:

 #VALUE! #VALUE! =AE19*I19 

Thank you in advance!:)

Answer 1

To debug the problem, do 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 result of this should help diagnose the problem.

Answer 2

If you are not sure about the contents of the cell (including the value or formula), I recommend that you first check if the cell has a formula, and then copy and paste accordingly. getOldCalculatedValue () is very useful in this case. Here is an example of this:

 $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 datasets, the getCalculatedValue () function is really cumbersome and will require a lot of memory to work properly.

Answer 3

I ran into a similar problem, getCalculatedValue () returned #VALUE! on a cell that referenced another cell that contained the formula.

The reason turned out to be one of the mentioned cells containing a NULL value, although Excel did the job correctly. All I had to do was add the value 0 to this cell and it calculated perfectly.

Answer 4

You should try to open the source Excel file and look at cell AF19. Most likely, even MS Excel itself has problems calculating the value due to the fact that something is wrong with the formula located in AF19.

Maybe that

This formula tries to multiply two cells that do not contain a number

= AE19 * I19

AE19 contains a comma and is not formatted as a number, so PHPExcel sees this as a string

in this case, you can try to format the data in excel first by right-clicking (in excel) and specify the number format.

Answer 5

// you can set this

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

// because php excel defines the code in the string, so you get an error. because in phpexcel you have to use excel formula. can not do such A1 + A2 or ocher.

You can propagate in php, for example,

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

// function is multiplied.

 $total=$bil1*$bil2; 

after that you can set the total quantity in the cell

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

This works on my php.

Answer 6

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

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

To handle this "#VALUE!" the problem is, in a small project, I set empty cells to NULL instead of '' .

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