org.apache.poi.ss.formula.eval.NotImplementedExcep

Question

I used a formula in excel sheet: =DATEDIF(TODAY(),E2,"y") & " years " & DATEDIF(TODAY(),E2,"ym") & " months " & DATEDIF(TODAY(),E2,"md") & " days" where E2 = 14-Aug-2015

On Execution of HSSFFormulaEvaluator.evaluateAllFormulaCells(wb) I am getting and Exception of

org.apache.poi.ss.formula.eval.NotImplementedFunctionException: DATEDIF

Please help.

Answer 1

As of Apache POI version 3.12, DATEDIF() isn't supported. You can check which functions are supported by running:

Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();

This link describes how to write a user defined function and register it with the formula evaluator:

Two base interfaces to start your implementation

All Excel formula function classes implement either org.apache.poi.hssf.record.formula.functions.Function or org.apache.poi.hssf.record.formula.functions.FreeRefFunction interface. Function is a common interface for the functions defined in the binary Excel format (BIFF8): these are "classic" Excel functions like SUM, COUNT, LOOKUP, etc. FreeRefFunction is a common interface for the functions from the Excel Analysis Toolpack and for User-Defined Functions. In the future these two interfaces are expected be unified into one, but for now you have to start your implementation from two slightly different roots.

Which interface to start from?

You are about to implement a function XXX and don't know which interface to start from: Function or FreeRefFunction. Use the following code to check whether your function is from the excel Analysis Toolpack:

if(AnalysisToolPack.isATPFunction(functionName)){
    // the function implements org.apache.poi.hssf.record.formula.functions.Function
} else {
    // the function implements org.apache.poi.hssf.record.formula.functions.FreeRefFunction
}

Walkthrough of an "evaluate()" implementation.

Here is the fun part: lets walk through the implementation of the excel function SQRT()

AnalysisToolPack.isATPFunction("SQRTPI") returns false so the base interface is Function. There are sub-interfaces that make life easier when implementing numeric functions or functions with fixed number of arguments, 1-arg, 2-arg and 3-arg function:

• org.apache.poi.hssf.record.formula.functions.NumericFunction
• org.apache.poi.hssf.record.formula.functions.Fixed1ArgFunction
• org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction
• org.apache.poi.hssf.record.formula.functions.Fixed3ArgFunction
• org.apache.poi.hssf.record.formula.functions.Fixed4ArgFunction

Since SQRTPI takes exactly one argument we start our implementation from org.apache.poi.hssf.record.formula.functions.Fixed1ArgFunction:

Function SQRTPI = new Fixed1ArgFunction() {
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
        try {
            // Retrieves a single value from a variety of different argument types according to standard
            // Excel rules.  Does not perform any type conversion.
            ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);

            // Applies some conversion rules if the supplied value is not already a number.
            // Throws EvaluationException(#VALUE!) if the supplied parameter is not a number
            double arg = OperandResolver.coerceValueToDouble(ve);

            // this where all the heavy-lifting happens
            double result = Math.sqrt(arg*Math.PI);

            // Excel uses the error code #NUM! instead of IEEE _NaN_ and _Infinity_,
            // so when a numeric function evaluates to Double.NaN or Double.Infinity,
            // be sure to translate the result to the appropriate error code
            if (Double.isNaN(result) || Double.isInfinite(result)) {
                throw new EvaluationException(ErrorEval.NUM_ERROR);
            }

            return new NumberEval(result);
        } catch (EvaluationException e){
            return e.getErrorEval();
        }
    }
}

Now when the implementation is ready we need to register it in the formula evaluator:

WorkbookEvaluator.registerFunction("SQRTPI", SQRTPI);

Voila! The formula evaluator now recognizes SQRTPI!

-- https://poi.apache.org/components/spreadsheet/eval-devguide.html