PHPExcel致命错误:未捕获的异常“异常”有消息“公式错误:意外,”
问题描述:
我会搜索谷歌,但我找不到这方面的任何解决方案。
我想要做的是从一个报告使用PHP制作excel5导出文件。PHPExcel致命错误:未捕获的异常“异常”有消息“公式错误:意外,”
每当我试图做一个平均的时候,我发现了以下错误:
Fatal error: Uncaught exception 'Exception' with message 'myexcelsheet!F38 -> Formula Error: Unexpected ,'
这是给出了错误的公式:
=SUM(F9:F36)/COUNTIF(F9:F36, "*" & "<>00:00:00" & "*")
细胞的数据类型是时间。使用COUNT它可以正常工作,但它需要忽略00:00:00时间。
这是包括:
require_once("PHPExcel/Classes/PHPExcel.php");
require_once("PHPExcel/Classes/PHPExcel/Writer/Excel5.php");
require_once 'PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php';
所有错误:
Fatal error: Uncaught exception 'Exception' with message 'myexcelsheet!F38 -> Formula Error: Unexpected ,' in /path/to/PHPExcel/Classes/PHPExcel/Cell.php:293 Stack trace: #0 /path/to/PHPExcel/Classes/PHPExcel/Worksheet.php(702): PHPExcel_Cell->getCalculatedValue() #1 /path/to/PHPExcel/Classes/PHPExcel/Writer/Excel5/Worksheet.php(295): PHPExcel_Worksheet->calculateColumnWidths() #2 /path/to/PHPExcel/Classes/PHPExcel/Writer/Excel5.php(194): PHPExcel_Writer_Excel5_Worksheet->close() #3 /path/to/rep-functions.php(985): PHPExcel_Writer_Excel5->save('/path/t...') #4 /path/to/report.php(462): export_excel(Array, Array, 'report.ph...', Array, Array, Array) #5 {main} thrown in /path/to/PHPExcel/Classes/PHPExcel/Cell.php on line 293
这是我从testFormula功能得到
Formula Value is=SUM(F9:F36)/COUNTIF(F9:F36,"*" & "00:00:00" & "*") Expected Value is UNKNOWN Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => F9 [reference] => F9 ) [1] => Array ( [type] => Cell Reference [value] => F36 [reference] => F36 ) [2] => Array ( [type] => Binary Operator [value] => : [reference] => ) [3] => Array ( [type] => Operand Count for Function SUM() [value] => 1 [reference] => ) [4] => Array ( [type] => Function [value] => SUM( [reference] => ) [5] => Array ( [type] => Cell Reference [value] => F9 [reference] => F9 ) [6] => Array ( [type] => Cell Reference [value] => F36 [reference] => F36 ) [7] => Array ( [type] => Binary Operator [value] => : [reference] => ) [8] => Array ( [type] => Value [value] => "*" [reference] => ) [9] => Array ( [type] => Value [value] => "00:00:00" [reference] => ) [10] => Array ( [type] => Binary Operator [value] => & [reference] => ) [11] => Array ( [type] => Value [value] => "*" [reference] => ) [12] => Array ( [type] => Binary Operator [value] => & [reference] => ) [13] => Array ( [type] => Operand Count for Function COUNTIF() [value] => 2 [reference] => ) [14] => Array ( [type] => Function [value] => COUNTIF( [reference] => ) [15] => Array ( [type] => Binary Operator [value] =>/ [reference] => ) ) CALCULATION ENGINE ERROR: Worksheet!F38 -> Formula Error: Unexpected , Evaluation Log: Array ( )
让我知道如果你需要了解更多信息。
在此先感谢!
答
使用此功能调试计算引擎:
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;
PHPExcel_Calculation::getInstance()->writeDebugLog = true;
$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;
}
}
}
您需要将工作表对象和小区ID作为参数,它产生的词法和语法分析器如何评估公式
详细的日志
我已经添加了该功能,但是我无法从中做出某些功能。 – 2013-02-28 09:27:31
输出是在问题中。 – 2013-02-28 09:31:58
SOrry,在功能中错过了一条线 – 2013-02-28 09:35:09