Apache POI - 如何注册一个函数 [英] Apache POI - How to register a function
问题描述
我在 您将看到以文件格式定义的内置 Excel 函数的列表.该列表中的任何内容都不能作为自定义函数覆盖，因为它们以不同的文件格式存储.(嗯，当然对于 .xls 文件，.xlsx 有点不同).查看该文件时，记下您的函数 ID.
如果你的公式函数是内置的，那么你应该看看FunctionEval.您可以使用 getNotSupportedFunctionNames() 或者只是查看代码以查看该功能是否已实现.(数组由函数 ID 索引，该 ID 来自 functionMetadata.txt)
如果您的功能没有实现，您需要获取 POI 源代码，并且:
- 在某处添加您的函数实现
- 列出具有正确 ID 的 FunctionEval 中的函数
- 测试(您可以使用 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屋！
