Bug 56688

Summary: EDate formula throws an exception for named argument
Product: POI Reporter: kirill.frolov77 <kirill.frolov77>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: Macintosh   
OS: All   
Attachments: EDate type test 1
EDate type test 2
EDate type test 3
EDate type test 4

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.