POI 导入带公式的EXCEL 精度出问题

用POI读EXCEL时，一定要用支持POI的函数。。。。

poi只对excle中部分函数支持。

1.支持的函数：
[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]

2.不支持的函数：
[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]

当我们用到不支持的函数时需要手动注册下面说下怎么手动注册函数。

最近在解析excle中遇到了NORMINV(正态分布)这个函数：

首先注册时我们只需要注册一遍（注册重复则会报错）所以我写了个监听在bean加载完后注册函数

FunctionEval.registerFunction(“TRANSPOSE”, new RegisterFunction());

public class RegisterFunction implements Function{

@Override
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
    System.out.println("---------------");
    System.out.println(srcRowIndex);
    System.out.println(srcColumnIndex);
    for(ValueEval v: args){
        System.out.println(v);
        AreaEval ae = (AreaEval)v;
        try {
            System.out.println(ae.getFirstColumn());
            System.out.println(ae.getFirstRow());
            System.out.println(ae.getFirstSheetIndex());
            System.out.println(ae.getHeight());
            System.out.println(ae.getLastColumn());
            System.out.println(ae.getLastRow());
            System.out.println(ae.getLastSheetIndex());
            System.out.println(ae.getWidth());
            ValueEval tEval= OperandResolver.chooseSingleElementFromArea(ae, ae.getFirstRow(), ae.getFirstColumn());
            System.out.println(tEval);
            System.out.println("------------------------------------------------------");
        } catch (EvaluationException e) {
            e.printStackTrace();
        }
    }
    //new NormalDistribution(mean,standard_dev)     
  //  NormalDistribution normalDistributioin = new NormalDistribution(Double.valueOf(list.get(1)), Double.valueOf(list.get(2))); 
    //double S1 = normalDistributioin.cumulativeProbability(Double.valueOf(list.get(0)));
    //normalDistributioin.inverseCumulativeProbability(probability)
    //double S2 = normalDistributioin.inverseCumulativeProbability(Double.valueOf(list.get(0)));

    System.out.println("-------111111111111111111111111111-----------------------------------------------");
    return new NumberEval(2);
}

}

资料来源：
https://blog.csdn.net/u014677702/article/details/84836517

https://blog.csdn.net/wengengeng/article/details/52664683

http://poi.apache.org/components/spreadsheet/formula.html