poi 自定义函数

参考链接 :https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java

averageifs: https://support.office.com/zh-cn/article/AVERAGEIFS-%E5%87%BD%E6%95%B0-48910C45-1FC0-4389-A028-F7C5C3001690

poi 函数

获取 poi 函数

1
2
3
4
public void getSupportFunction() {
System.out.println(FunctionEval.getSupportedFunctionNames());
System.out.println(FunctionEval.getNotSupportedFunctionNames());
}

支持

[ABS, ACOS, ACOSH, ADDRESS, AND, AREAS, 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, DGET, DMAX, DMIN, DOLLAR, DSUM, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FREQUENCY, FV, GEOMEAN, HLOOKUP, HOUR, HYPERLINK, IF, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MDETERM, MEDIAN, MID, MIN, MINA, MINUTE, MINVERSE, MIRR, MMULT, 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, TRANSPOSE, TREND, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, WEEKDAY, YEAR]

不支持

[ABSREF, APP.TITLE, ARGUMENT, ASC, AVERAGEA, BETADIST, BETAINV, BINOMDIST, CALL, CELL, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CORREL, COVAR, CRITBINOM, DATEDIF, DATESTRING, DATEVALUE, DAVERAGE, DB, DBCS, DCOUNT, DCOUNTA, DDB, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP, ENABLE.TOOL, END.IF, ERROR, EVALUATE, EXEC, EXPONDIST, FDIST, FINDB, FINV, FISHER, FISHERINV, FORECAST, FTEST, GAMMADIST, GAMMAINV, GAMMALN, GET.CELL, GET.DOCUMENT, GET.WINDOW, GET.WORKBOOK, GET.WORKSPACE, GETPIVOTDATA, GOTO, GROWTH, HARMEAN, HYPGEOMDIST, INFO, ISPMT, KURT, LAST.ERROR, LEFTB, LENB, LINEST, LOGEST, LOGINV, LOGNORMDIST, MIDB, N, NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV, NUMBERSTRING, PEARSON, PERCENTRANK, PERMUT, PHONETIC, PRESS.TOOL, PROB, QUARTILE, REGISTER.ID, RELREF, REPLACEB, RETURN, RIGHTB, RSQ, SAVE.TOOLBAR, SEARCHB, SKEW, SLN, STANDARDIZE, STDEVA, STDEVP, STDEVPA, STEP, STEYX, SYD, TDIST, TIMEVALUE, TINV, TRIMMEAN, TTEST, TYPE, USDOLLAR, VARA, VARPA, VDB, WEIBULL, WINDOW.TITLE, ZTEST]

自定义 poi 函数

INFO 函数

举个栗子:INFO 函数 poi 不支持

excel 是可用的:

测试 test 函数

测试代码:

1
2
3
4
5
6
7
8
9
public void registerFunctionOfINFO() {
Workbook workbook = getWorkbook();
workbook.setForceFormulaRecalculation(true);
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
Sheet sheet = workbook.getSheetAt(0);
Cell cell = sheet.getRow(0).getCell(0);
formulaEvaluator.evaluateInCell(cell);
System.out.println(cell.getStringCellValue());
}

错误信息:NotImplementedFunctionException: INFO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!A1
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:344)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:285)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:56)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell(BaseFormulaEvaluator.java:145)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:85)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:34)
at com.yangyang.java.UserDefinedFunctionPoi.registerFunctionOfINFO(UserDefinedFunctionPoi.java:45)
at com.yangyang.java.UserDefinedFunctionPoi.main(UserDefinedFunctionPoi.java:26)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: INFO
at org.apache.poi.ss.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:40)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:153)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
... 7 more

自定义实现 INFO 函数:

为了测试,就直接返回固定值 windwos

1
2
3
4
5
6
public class INFOFuncation implements Function {
@Override
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
return new StringEval("windwos");
}
}

使用 : FunctionEval.registerFunction("INFO", new INFOFuncation()); 注册。

结果:

AVERAGEIFS 函数

同样 自定义函数,返回固定值:

1
2
3
4
5
6
public class AVERAGEIFSuncation implements Function {
@Override
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
return new NumberEval(10);
}
}

错误信息:

1
Exception in thread "main" java.lang.IllegalArgumentException: AVERAGEIFS is a function from the Excel Analysis Toolpack. Use AnalysisToolpack.registerFunction(String name, FreeRefFunction func) instead.

有个 bug , 我也是找半天:提示使用 AnalysisToolpack.registerFunction(String name, FreeRefFunction func)方法。但是:查无此类。。。

AnalysisToolpack VS AnalysisToolpak . poi 的 bug ,拼写错误。

20190904173818_50d00c1a0ff4fd35997ee986b8ee59d9.png

注册:

1
AnalysisToolPak.registerFunction("AVERAGEIFS", new AVERAGEIFSFuncation());
1
2
3
4
5
6
7
public class AVERAGEIFSFuncation implements FreeRefFunction {

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
return new NumberEval(10);
}
}

运行:

上面都是为了测试注册函数可不可用:

自己实现的 AVERAGEIFS (选择区和条件区一样,条件都是 <> 0, 所以简单实现了,如果遇到其他 case 再更新)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
public class AverageifsFuncation implements FreeRefFunction {

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
String argStr0 = getMainContent(args[0]);
String argStr1 = getMainContent(args[1]);
String argStr2 = getMainContent(args[2]);

if (!argStr0.equals(argStr1)) {
throw new RuntimeException("暂时不支持");
}

XSSFEvaluationWorkbook workbook = (XSSFEvaluationWorkbook) ec.getWorkbook();

EvaluationSheet sheet = workbook.getSheet(ec.getSheetIndex());
List<Double> list = new ArrayList<>();
AreaReference areaReference = new AreaReference(argStr0, SpreadsheetVersion.EXCEL2007);
CellReference[] cellReferences = areaReference.getAllReferencedCells();
for (CellReference cellReference : cellReferences) {
EvaluationCell cell = sheet.getCell(cellReference.getRow(), cellReference.getCol());
if (cell == null) {
continue;
}
try {
Double value = Double.valueOf(getCellValue(cell).toString());
if (isInCondition(argStr2, value)) {
list.add(value);
}
} catch (NumberFormatException e) {
//不是数字类型的
}
}
double[] arrays = new double[list.size()];
for (int i = 0; i < arrays.length; i++) {
arrays[i] = list.get(i);
}
return new NumberEval(StatUtils.mean(arrays));
}

public Object getCellValue(final EvaluationCell cell) {
switch (cell.getCellType()) {
case BOOLEAN:
return cell.getBooleanCellValue();
case ERROR:
return cell.getErrorCellValue();
case NUMERIC:
return cell.getNumericCellValue();
case STRING:
case BLANK:
return cell.getStringCellValue();
case FORMULA:
//可能有坑
default:
throw new IllegalArgumentException("未知类型:" + cell.getCellType());
}
}

/**
* 判断是否满足条件
*
* @return
*/
public boolean isInCondition(String condition, double value) {
if (condition.startsWith("<>")) {
double conditionValue = Double.parseDouble(condition.substring(condition.indexOf("<>") + 2));
if (value == conditionValue) {
return false;
}
return true;
} else {
throw new IllegalArgumentException("AverageifsFuncation 暂时不支持此类格式");
}
}

public String getMainContent(ValueEval argStr) {
String str = argStr.toString();
return str.substring(str.indexOf("[") + 1, str.lastIndexOf("]"));
}

}

本文完。。。!

Look at your mood.