Apache的POI - 如何注册功能 [英] Apache POI - How to register a function

问题描述

我阅读教程的的Apache POI 上的如何注册自定义函数的成FormulaEvaluator，我想用它来定义函数MINVERSE为其POI不提供支持。所以，首先，我创建了一个类定义MINVERSE（仅用于测试目的，我定义MINVERSE始终返回值10）。因此，这里是MINVERSE.java：

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);
    }
}

后来我试过的东西真的简单：我创建了下面的Excel表：

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

A1是一个给定的常量和A2是A2 = MINVERSE（A1）

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

下面是我的主类code：

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!

推荐答案

在自定义功能教程中，你一直在看只有真正的自定义函数。它不会让你覆盖没有实现POI还内置Excel函数

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

如果你看一下<一个href=\"http://svn.apache.org/repos/asf/poi/trunk/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt\"相对=nofollow>组织/阿帕奇/ POI / SS /公式/功能/ functionMetadata.txt 你会看到列表中内置的文件格式定义Excel函数。凡是在该列表中不能被覆写为自定义的功能，因为他们得到不同的存储在文件格式。 （嗯，当然，对于.xls文件，.XLSX稍有不同）。而在寻找这个文件，请记下你的函数的ID的。

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.

如果您的公式函数是一个内置于一身，那么你应该看看<一个href=\"http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java\"相对=nofollow> FunctionEval 。您可以使用<一个href=\"http://poi.apache.org/apidocs/org/apache/poi/ss/formula/eval/FunctionEval.html#getNotSupportedFunctionNames%28%29\"相对=nofollow> getNotSupportedFunctionNames（）或只是看在code，看该功能尚未实现。 （该阵列由功能ID，你从functionMetadata.txt了索引）

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)

如果您的功能没有实现，你需要抓住POI源$ C ​​$ c和：

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

• 添加在你的函数实现的地方


• 在FunctionEval功能用正确的ID列表


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


• 提交它作为一个补丁！请参阅 POI贡献指南详情

• 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

不久你的补丁被提交后，再POI将包括丢失的功能，而社会将帮助它让你赢未来：）

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屋！