Apache POI - How to register a function

I read a tutorial on the Apache POI website on how to register custom functions in FormulaEvaluator and I wanted to use it to define a MINVERSE function for which the POI does not provide support. So, firstly, I created a class defining MINVERSE (for testing only, I defined MINVERSE to always return 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);
    }
}

Subsequently, I tried something really simplistic: I created the following excel sheet:

Screenshot from Excel

A1 is the 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, when 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? Thank you very much in advance!

+2
3

, , . Excel, POI.

org/apache/poi/ss/formula/function/functionMetadata.txt, Excel, . , , - . (, , .xls ,.xlsx ). .

, FunctionEval. getNotSupportedFunctionNames() , , . ( , Metadata.txt)

, POI :

  • -
  • FunctionEval ID
  • ( POI, )
  • ! . POI.

, , POI , , :)

+3

, , Excel, Java-. excel, POI , FunctionEval. , :

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

( - ). Minverse org.apache.poi.ss.formula.functions.Function .

Function :

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

. , , , ( /). ...

+1

I don’t understand, but you need to put the VBA code with the function definition (maybe even empty) in your worksheet - put the function in the module.

Function MINVERSE(principal As Double)
End Function
0
source

All Articles