POI使⽤公式的问题，POI对excel函数的⽀持⼀、问题
最近在使⽤POI操作excel⽂件时，会报如下错误：
org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'AAA'!E5
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:332)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:273)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
at org.apache.poi.hssf.urmodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:374)
at org.apache.poi.hssf.urmodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:234)
st.PoiTest.main(PoiTest.java:40)
Caud by: org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'WinProbE
st - Table 1-1'!J5
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:332)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:273)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
at org.apache.poi.ss.EvalForCell(SheetRefEvaluator.java:48)
at org.apache.poi.ss.EvalForCell(SheetRangeEvaluator.java:74)
at org.apache.poi.ss.InnerValueEval(LazyRefEval.java:43)
at org.apache.poi.ss.formula.eval.OperandResolver.chooSingleElementFromRef(OperandResolver.java:179)
at org.apache.poi.ss.formula.SingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.WorkbookEvaluator.dereferenceResult(WorkbookEvaluator.java:570)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:521)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263)
感恩节作文
... 4 more
Caud by: org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'BBB'!I5
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:332)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:273)
锤子怎么画at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
at org.apache.poi.ss.EvalForCell(SheetRefEvaluator.java:48)
at org.apache.poi.ss.EvalForCell(SheetRangeEvaluator.java:74)
气质的近义词at org.apache.poi.ss.InnerValueEval(LazyRefEval.java:43)
at org.apache.poi.ss.formula.eval.OperandResolver.chooSingleElementFromRef(OperandResolver.java:179)
at org.apache.poi.ss.formula.SingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29) at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:36)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:503)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263)
... 13 more
Caud by: org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'BBB'!E5
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:332)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:273)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
at org.apache.poi.ss.EvalForCell(SheetRefEvaluator.java:48)
at org.apache.poi.ss.EvalForCell(SheetRangeEvaluator.java:74)
at org.apache.poi.ss.InnerValueEval(LazyRefEval.java:43)
at org.apache.poi.ss.formula.eval.OperandResolver.chooSingleElementFromRef(OperandResolver.java:179)
at org.apache.poi.ss.formula.SingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29) at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:503)董其昌书法欣赏
路由器如何桥接at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263)
... 25 more
清汤面怎么做
Caud by: org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'BBB'!F52
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:332)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:273)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
at org.apache.poi.ss.EvalForCell(SheetRefEvaluator.java:48)公园
at org.apache.poi.ss.EvalForCell(SheetRangeEvaluator.java:74)
at org.apache.poi.ss.EvalForCell(SheetRangeEvaluator.java:74)
at org.apache.poi.ss.RelativeValue(LazyAreaEval.java:51)
at org.apache.poi.ss.RelativeValue(LazyAreaEval.java:45)
at org.apache.poi.ss.formula.Value(AreaEvalBa.java:128)
at org.apache.poi.ss.formula.functions.Sumproduct.evaluateAreaSumProduct(Sumproduct.java:157)
at org.apache.poi.ss.formula.functions.Sumproduct.evaluate(Sumproduct.java:77)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
聪明的小鸭子at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:503)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263)
... 37 more
Caud by: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: NORMDIST
at org.apache.poi.ss.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:40)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:503)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263)
... 48 more
原来，源错误在于最近⼀个异常：
NotImplementedFunctionException: NORMDIST
⼆、查找资料
刚开始时看不太明⽩这个异常的意思，⼤概是英⽂领悟能⼒⽋佳吧。
The methods you need are available on org.apache.poi.ss.formula.eval.FunctionEval. To find which f
unctions your copy of Apache POI supports, u getSupportedFunctionNames() to get a list of the implemented function names. For the list of functions that POI knows the name of, but doesn't currently implement, u getNotSupportedFunctionNames()
意思是说，⽬前POI⽀持了部分函数，尚有部分函数未提供⽀持，试着把它们打印出来，如下：
⽀持的函数：
[ABS, ACOS, ACOSH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DAY, DAYS360, DEGREES, DEVSQ, DOLLAR, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MIRR, MOD, MODE, MONTH, NA, NOT, NOW, NPER, NPV, ODD, OFFSET, OR, PERCENTILE, PI, PMT, POISSON, POWER, PPMT, PRODUCT, PROPER, PV, RADIANS, RAND, RANK, RATE, REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH,
SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH, TEXT, TIME, TODAY, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, WEEKDAY, YEAR]
不⽀持的函数：
[AREAS, ASC, AVERAGEA, BETADIST, BETAINV, BINOMDIST, CELL, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CORREL, COVAR, CRITBINOM, DATEDIF, DATESTRING, DATEVALUE, DAVERAGE, DB, DBCS, DCOUNT, DCOUNTA, DDB, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP, EXPONDIST, FDIST, FINDB, FINV, FISHER, FISHERINV, FORECAST, FREQUENCY, FTEST, GAMMADIST, GAMMAINV, GAMMALN, GEOMEAN, GETPIVOTDATA, GROWTH, HARMEAN, HYPGEOMDIST, INFO, ISERR, ISPMT, KURT, LEFTB, LENB, LINEST, LOGEST, LOGINV, LOGNORMDIST, MDETERM, MIDB, MINVERSE, MMULT, N, NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV, NUMBERSTRING, PEARSON, PERCENTRANK, PERMUT, PHONETIC, PROB, QUARTILE, REPLACEB, RIGHTB, RSQ, SEARCHB, SKEW, SLN, STANDARDIZE, STDEVA, STDEVP, STDEVPA, STEYX, SYD, TDIST, TIMEVALUE, TINV, TRANSPOSE, TREND, TRIMMEAN, TTEST, TYPE, USDOLLAR, VARA, VARPA, VDB, WEIBULL, ZTEST]
三、总结
如果操作的excel需要使⽤到不被⽀持的函数，就需要⾃⾏添加相关函数的⽀持，或者考虑使⽤其它技术插件了。
POI注册⼀个函数⼤概是使⽤这个⽅法：isterFunction(name, func);
