Back to home

[翻译]Apache POI 如何添加一个自定义函数

[翻译]Apache POI 如何添加一个自定义函数

原文地址:http://stackoverflow.com/questions/10298512/apache-poi-how-to-register-a-function

Pantelis Sopasakis 问

我想要添加一个POI还不支持的标准函数MINVERSE,在了解Apache POI官网上的教程如何给FormulaEvalutor注册一个自定义函数之后,我实现了一个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表: Xnpj5.png

其中A1是一个常量,A2是一个包含MINVERSE的表达式。

下面是我的主要类代码:

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

但是,无论我如何去执行他,我仍然会得到如下的错误:

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

各位有什么建议么?谢谢指教。

Gagravarr 回答

你读过的自定义函数教程只适合真正的自定义函数,他不支持重写一些POI还不支持的Excel内置函数。

如果你去看看org/apache/poi/ss/formula/function/functionMetadata.txt,你会发现他给出一个Excel内置函数列表。任何一个列表中的函数都不能通过自定义函数覆盖,那个列表中的自定义函数,都被以不同的文件格式存储起来,都不能被作为自定义函数直接重载。(当然,对.xsl和.xlsx文件来说,情况会有些不同。)当你浏览这些文件时,最好记录下你你要用的函数的ID。

如果你实现的函数是Excel内建函数,那么你应该看一下FunctionEval。你可以使用getNotSupportedFunctionNames()看看是函数否已经实现(这个数组索引就是你从functionMetadata.txt得到的函数ID)。

如果你想要的函数没有被实现,你可以自行改造POI源码,并且:

  • 添加你的函数实现
  • 列出函数ID
  • 测试(使用POI公式单元测试)
  • 将你的代码作为补丁提交!详见POI构建指导

在你提交补丁之后,POI会包含你的实现,整个社区都将会维护他,以助你走得更远。

Pantelis Sopasakis 回答

手册是关于如何通过Java类重写自定义函数。如果想要重当前POI不支持的Excel函数,你必须在FunctionEval注册这个函数的实现。比如:

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

(但是这会变得复杂,因为缺少相关文档)。这个Minverse类应该实现org.apache.poi.ss.formula.functions.Function接口或者它的一些抽象类。

Fuction 定义了一个方法:

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

我们需要实现这个函数,但是有了这些还是不清楚如何去处理接受的类型以及返回什么样的类型。为此我创建了另外一个新的问题。