Hi all,
I receive problems when registering a not supported Excel function, in
this case EDATE (or, in German, EDATUM).
(1) When I register this function with the name "EDATE", it won't work:
WorkbookEvaluator.registerFunction("EDATE", new EDate());
I receive the following error messages:
Exception in thread "main"
org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating
cell Tabelle1!B2
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
com.xls.UserDefinedFunctionExample.main(UserDefinedFunctionExample.java:55)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: EDATUM
at
org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59)
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
... 4 more
(2) When I try to fix this and to register the function with Germen name
"EDATUM", it won't work:
I get the following error messages, because EDATUM is, of course, not a
function that Excel or POI knows, since this must be the English name
"EDATE":
Exception in thread "main" java.lang.IllegalArgumentException: EDATUM is
not a function from the Excel Analysis Toolpack.
at
org.apache.poi.ss.formula.atp.AnalysisToolPak.registerFunction(AnalysisToolPak.java:237)
at
org.apache.poi.ss.formula.WorkbookEvaluator.registerFunction(WorkbookEvaluator.java:712)
at
com.xls.UserDefinedFunctionExample.main(UserDefinedFunctionExample.java:44)
(3) When I register this function via the UDFFinder with German name
"EDATUM", it will work:
FileInputStream fis = new FileInputStream(workbookFile);
Workbook workbook = WorkbookFactory.create(fis);
fis.close();
String[] functionNames = { "EDATUM" } ;
FreeRefFunction[] functionImpls = { new EDate() } ;
UDFFinder udfToolpack = new DefaultUDFFinder(
functionNames, functionImpls ) ;
//register the user-defined function in the workbook
workbook.addToolPack(udfToolpack);
POI knows the registered function and is able to evaluate the formula in
the Excel sheet.
But this is not what I want, because this will register EDATUM a
user-defined function. What I want is to give POI the code for the not
supported function EDATE (or EDATUM), and I think I have to use
WorkbookEvaluator.registerFunction() in this case.
(4) Again, when I try to register this function via the UDFFinder with
English name "EDATE":
I get error messages:
Exception in thread "main"
org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating
cell Tabelle1!B2
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
com.xls.UserDefinedFunctionExample.main(UserDefinedFunctionExample.java:55)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: EDATUM
at
org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59)
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
... 4 more
This seems to be due to the German EDATUM, which is written in the Excel
file, of course. How else could POI know about EDATUM, since everywhere
in my code I used EDATE in this case? So EDATUM must come from the Excel
file??
==> Does Excel not work internally with the English function names? How
can I solve this problem and register EDATE as EDATE (English name)??
Thanks for any help....
Christian
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
