Bug 56688 - EDate formula throws an exception for named argument
Summary: EDate formula throws an exception for named argument
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-FINAL
Hardware: Macintosh All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-07-01 11:38 UTC by kirill.frolov77@gmail.com
Modified: 2014-07-22 12:33 UTC (History)
0 users

EDate type test 1 (8.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-03 15:22 UTC, kirill.frolov77@gmail.com
EDate type test 2 (8.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-03 15:22 UTC, kirill.frolov77@gmail.com
EDate type test 3 (8.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-03 15:23 UTC, kirill.frolov77@gmail.com
EDate type test 4 (8.72 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-03 15:24 UTC, kirill.frolov77@gmail.com

Note You need to log in before you can comment on or make changes to this bug.
Description kirill.frolov77@gmail.com 2014-07-01 11:38:14 UTC
It is obviously missing check for named reference before cast in EDate.java.

Here is the stacktrace

Caused by: java.lang.ClassCastException: org.apache.poi.ss.formula.LazyRefEval cannot be cast to org.apache.poi.ss.formula.eval.NumberEval
	at org.apache.poi.ss.formula.functions.EDate.evaluate(EDate.java:39)
	at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:64)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:315)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:252)
<< omitted >>

I believe that substituting 

			NumberEval offsetInYearsValue = (NumberEval) args[1];


			if (args[1] instanceof RefEval) {
				args[1] = ((RefEval) args[1]).getInnerValueEval();
			NumberEval offsetInYearsValue = (NumberEval) args[1];

resolves the issue
Comment 1 Nick Burch 2014-07-01 14:12:05 UTC
Any chance of a small unit test that demonstrates the problem? We can then use that to test the fix, and to ensure it stays fixed into the future!
Comment 2 kirill.frolov77@gmail.com 2014-07-03 15:22:02 UTC
Created attachment 31784 [details]
EDate type test 1

EDate should return 0
Comment 3 kirill.frolov77@gmail.com 2014-07-03 15:22:54 UTC
Created attachment 31785 [details]
EDate type test 2

EDate should return #VALUE
Comment 4 kirill.frolov77@gmail.com 2014-07-03 15:23:26 UTC
Created attachment 31786 [details]
EDate type test 3

EDate should return #VALUE
Comment 5 kirill.frolov77@gmail.com 2014-07-03 15:24:23 UTC
Created attachment 31787 [details]
EDate type test 4

EDate should return today() + 1 month but now it throws an exception
Comment 6 kirill.frolov77@gmail.com 2014-07-03 15:25:10 UTC
Code to reproduce exceptions:
public class Test {

	public static void main(String[] args) {
		try {
			FileInputStream is = new FileInputStream("edate_test1.xlsx");
			XSSFWorkbook excel = new XSSFWorkbook(is);
			XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(excel);
		} catch (Exception e) {
Comment 7 Dominik Stadler 2014-07-22 12:33:58 UTC
I have fixed this as far as possible in r1612557, however the case with blank cells does not work in POI currently because of the way dates below "1" are handled. 

Currently POI sees these dates as invalid and thus always returns -1.0 as value from DateUtil methods, so this is what the emtpy-case results in. 

Changing this would have side-effects in other places, so I kept it this way for now.