php phpExcel: getCalculatedValue() 返回 #VALUE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16708471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
phpExcel: getCalculatedValue() returns #VALUE
提问by Jonas Ivy V. Imperial
Can someone help me with phpExcel Code:
有人可以帮助我使用 phpExcel 代码：
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! :)
先感谢您！:)
采纳答案by Mark Baker
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
这个输出应该有助于诊断问题
回答by pancy1
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:
如果您不确定单元格的内容（包括值或公式），我建议您首先检查单元格是否有公式，然后相应地复制粘贴。getOldCalculatedValue() 在这种情况下非常有用。这是一个例子：
$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.
对于大型数据集，getCalculatedValue() 函数非常麻烦，并且需要大量内存才能正确执行。
回答by Aaron Brown
I came across a similar problem, getCalculatedValue() returned #VALUE! on a cell which referenced another cell which contained a formula.
我遇到了类似的问题，getCalculatedValue() 返回 #VALUE！在引用另一个包含公式的单元格的单元格上。
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.
原因最终是引用的包含 NULL 值的单元格之一，即使 Excel 正确处理了这一点。我所要做的就是向该单元格添加 0 值，并且计算得很好。
回答by 40Plot
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.
您应该尝试打开原始 Excel 文件并查看单元格 AF19。很可能即使是 MS Excel 本身也无法计算值，因为位于 AF19 的公式有问题。
It could be that
可能是这样
This formula is trying to multiply two cells that doesnt contain number
此公式试图将两个不包含数字的单元格相乘
=AE19*I19
=AE19*I19
AE19 is contains a comma and isn't formatted as a number so PHPExcel sees it as a string
AE19 包含逗号且未格式化为数字，因此 PHPExcel 将其视为字符串
in such case, you could try formatting the data in excel first by right clicking (in excel) and specify numeric format.
在这种情况下，您可以首先通过右键单击（在 excel 中）并指定数字格式来尝试在 excel 中格式化数据。
回答by Land Rain Hard Siregar
//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.
//因为php excel定义字符串中的代码所以你会得到错误。因为在 phpexcel 中你必须使用公式 excel。不能像这样 A1+A2 或其他。
You can multiply in php like this
你可以像这样在php中乘以
$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.
这在我的 php.ini 中有效。
回答by Gavin
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")
=TEXT(A1, "mm/dd/yyyy")
In order to deal with this "#VALUE!" problem, on a small project I'm setting empty cells to NULLinstead of ''.
为了处理这个“#VALUE！” 问题，在一个小项目中，我将空单元格设置NULL为''.
$target = ($target == '')? NULL: $target; // without this, empty cells end up being #VALUE! after some formulas
$target = ($target == '')? NULL: $target; // without this, empty cells end up being #VALUE! after some formulas
