Apache POI - 如何注册一个函数 [英] Apache POI - How to register a function

问题描述

我在，您将看到以文件格式定义的内置Excel函数的列表。该列表中的任何内容都不能作为自定义函数进行覆盖，因为它们以文件格式存储不同。 （嗯，当然是.xls文件，.xlsx有点不同）。在查看该文件时，请记下您的功能ID。

如果您的公式功能是内置的，那么您应该看看 FunctionEval 。您可以使用 getNotSupportedFunctionNames（ ）或只是查看代码，看看功能是否实现。 （该数组由函数ID索引，您从functionMetadata.txt获取）

如果您的函数未实现，则需要获取POI源代码，和：

• 在您的函数实现中添加


• 在FunctionEval中列出右侧的函数ID


• 测试（您可以使用POI公式测试来帮助）


• 将其作为补丁提交！有关详细信息，请参阅 POI贡献指南

在您的补丁提交后不久，POI将包含您的缺少的功能，社区将帮助维护它，以便您赢得未来：）

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!

解决方案

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 :)

这篇关于Apache POI - 如何注册一个函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！