Reputation: 125
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
Reputation: 1
//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
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
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
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
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
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