Apache POI - How to register a function

问题:

I read the tutorial on the website of Apache POI on how to register custom functions into a FormulaEvaluator and I wanted to use it to define the function MINVERSE for which POI does not provide support. So, first off I created a class that defines MINVERSE (For testing purposes only, I defined MINVERSE to return always the value 10). So here is MINVERSE.java:

package simpleboxapi;

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;

public class MINVERSE implements FreeRefFunction{

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

Afterwards I tried something really simplistic: I created the following excel sheet:

A1 is a given constant and A2 is A2=MINVERSE(A1)

Here is my main class code:

package simpleboxapi;

import java.io.*;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.*;


public class SimpleBoxAPI {

    static String fileName = "workbook.xls";
    static Workbook wb;

    private static double updateInputVal(String cell, double val) throws IOException, InvalidFormatException{
        InputStream inp = new FileInputStream(fileName);
        wb = WorkbookFactory.create(inp);
        CellReference crInput = new CellReference(cell);
        Sheet sheet = wb.getSheetAt(0);
        Row rowInput = sheet.getRow(crInput.getRow());
        Cell cellInput = rowInput.getCell(crInput.getCol());
        cellInput.setCellValue(val);
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        double cellContents = cellInput.getNumericCellValue();
        inp.close();
        return cellContents;
    }


    private static void registerMINVERSE(){
       String[] functionNames = {"MINVERSE"};
        FreeRefFunction[] functionImpls = {new MINVERSE()};
        UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
        UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);
        wb.addToolPack(udfToolpack); 
    }


    public static void main(String[] args) throws Exception {

        double updatedValue = updateInputVal("A1",55);
        System.out.println(updatedValue);
        registerMINVERSE();

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        CellReference cr = new CellReference("A2");
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(cr.getRow());
        Cell cell = row.getCell(cr.getCol());
        System.out.println(evaluator.evaluate(cell).getNumberValue());
    }
}

However, whenever I try to execute it I get the following error:

org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'new sheet'!A2
    at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:356)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:297)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:354)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:185)
    at simpleboxapi.SimpleBoxAPI.main(SimpleBoxAPI.java:56)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: MINVERSE
    at org.apache.poi.ss.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)
    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
    ... 4 more

Any suggestions? Thanks a lot in advance!

回答1:

The Custom Functions tutorial you've been looking at is only for real custom functions. It won't let you override built-in Excel functions that don't have a POI implementation yet

If you look at org/apache/poi/ss/formula/function/functionMetadata.txt you'll see the list of the built-in Excel functions defined in the file format. Anything in that list can't be overriden as a custom function, as they get stored differently in the file format. (Well, certainly for .xls files, .xlsx is a little different). While looking at that file, make a note of the ID of your function.

If your formula function is a built in one, then you should take a look at FunctionEval. You can use getNotSupportedFunctionNames() or just look in the code to see if the function is implemented yet. (The array is indexed by function ID, which you got from functionMetadata.txt)

If your function isn't implemented, you'll need to grab the POI source code, and:

• Add in your function implementation somewhere
• List the function in FunctionEval with the right ID
• Test (you can use the POI formula tests to help)
• Submit it as a patch! See the POI contribution guidelines for details

Shortly after your patch is submitted, then POI will include your missing function, and the community will help maintain it so you win going forward :)

回答2:

This tutorial is about how to override custom function defined on the excel side using Java Classes. In order to override an existing excel function for which POI does not provide support you need to register it to FunctionEval. This is as simple as:

FunctionEval.registerFunction("MINVERSE", new Minverse());

(but it becomes difficult because of lack of proper documentation). The class Minverse should implement the interface org.apache.poi.ss.formula.functions.Function or extend some abstract class of the same package.

Function defines the method :

public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)

which one needs to override to provide the desired functionality. It is not still clear to me how to deal with functions that admit areas of data and return also areas (not vectors/arrays). I'll start a new question on that...

回答3:

I don't why but you have to put VBA code with function definition (can be even empty) in your worksheet - put function in module.

Function MINVERSE(principal As Double)
End Function