POI实现Excel公式的计算 & POI对Excel函数的支持

一、需求描述

接到一个大任务，其中有一块儿有点意思（之前没做过），转换成需求就是：

前端输入多个参数，用这些参数代入用户配置好的表达式中进行计算。

如：配置的公式为CEILING(La-Lb-0.1,0.05)，用户输入的La为6.67、Lb为4.43，期望计算得到CEILING(6.67-4.43-0.1,0.05)=2.15。

参数1	参数2	公式
La	Lb	CEILING(La-Lb-0.1,0.05)
6.67	4.43	2.15

二、捋清思路

一开始想着，如果只包含简单的四则运算和CEILING，那么枚举一下就可以搞定。但仔细询问后发现，需支持基本的Excel函数，也就意味着必须找一个Java API组件来实现了。多番查找，锁定了POI，开干！

大致的实现思路如下：

1.先进行一个替换，将表达式中各参数替换成值

2.再使用POI，进行计算

代码如下：

public String calculateTheFormula(Map<String, String> map) {
        // 初始的公式
        String initFormula = "CEILING(La-Lb-0.1,0.05)";
        // 计算结果
        String resultValue = null;

        /**1.先进行一个替换，将表达式中各参数替换成值**/
        for (Map.Entry<String, String> entry : map.entrySet()) {
            initFormula = initFormula.replace(entry.getKey(), entry.getValue());
        }

        /**2.再使用POI，进行计算**/
        // 创建工作簿，对应整个xls文件
        Workbook workbook = new HSSFWorkbook();
        // 创建sheet，对应excel的单个sheet
        Sheet sheet = workbook.createSheet("sheet1");
        // 创建行，对应excel中的一行
        Row row = sheet.createRow(0);
        // 创建单元格，对应row中的一格
        Cell cell = row.createCell(0);
        // 单元格设置公式
        cell.setCellFormula(initFormula);
        // 计算公式
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        try {
            resultValue = String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
        } catch (IllegalStateException e) {
            // 抛出异常：表达式中的参数不存在，无法计算！
        }

        return resultValue;
    }

使用postman测试，通过√

三、注意，有坑！

第一个坑：意外的浮点值

当输入La为6.6、Lb为0.43时，返回的结果为6.1000000000000005，正确的结果应为6.1。

解决办法：

使用NumberFormat解决，代码如下：

public String calculateTheFormula(Map<String, String> map) {
        String initFormula = "CEILING(La-Lb-0.1,0.05)";
        String resultValue = null;

        /**1.先进行一个替换，将表达式中各参数替换成值**/
        for (Map.Entry<String, String> entry : map.entrySet()) {
            initFormula = initFormula.replace(entry.getKey(), entry.getValue());
        }

        /**2.再使用POI，进行计算**/
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellFormula(initFormula);
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        try {
            NumberFormat numberFormat = NumberFormat.getInstance(); // 重点在这儿~
            resultValue = String.valueOf(numberFormat.format(formulaEvaluator.evaluate(cell).getNumberValue()));
        } catch (IllegalStateException e) {
            // 抛出异常：表达式中的参数不存在，无法计算！
        }

        return resultValue;
    }

结果正确√

第二个坑：POI对Excel函数的支持

目前POI支持了部分函数，尚有部分函数未提供支持。可以看一下官网：Formula Support (apache.org)

打印下支持/不支持的函数

import org.apache.poi.ss.formula.eval.FunctionEval;

System.out.println("POI支持的函数：\n" + FunctionEval.getSupportedFunctionNames());
System.out.println("POI不支持的函数：\n" + FunctionEval.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, DGET, DMIN, DOLLAR, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FV, 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, 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]

POI不支持的函数：
[AREAS, ASC, AVERAGEA, BETADIST, BETAINV, BINOMDIST, CELL, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CORREL, COVAR, CRITBINOM, DATEDIF, DATESTRING, DATEVALUE, DAVERAGE, DB, DBCS, DCOUNT, DCOUNTA, DDB, DMAX, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP, EXPONDIST, FDIST, FINDB, FINV, FISHER, FISHERINV, FORECAST, FREQUENCY, FTEST, GAMMADIST, GAMMAINV, GAMMALN, GEOMEAN, GETPIVOTDATA, GROWTH, HARMEAN, HYPGEOMDIST, INFO, 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]