Bug 64986 - Cannot evaluate formulas with extra comma before missing optional parameter
Summary: Cannot evaluate formulas with extra comma before missing optional parameter
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.1.2-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-12-14 19:19 UTC by Matti Kannala
Modified: 2020-12-30 21:42 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Matti Kannala 2020-12-14 19:19:44 UTC
Cannot evaluate formulas with extra comma before missing optional parameter.
In Microsoft Excel following formula is valid and evaluated: =MATCH("VAL",B3:B11,)
In POI 4.1.2 evaluation throws RuntimeException.
In POI 3.9 evaluation works.


Steps to Reproduce: 

public static void main(String[] args) {
	XSSFWorkbook w = XSSFWorkbookFactory.createWorkbook();
	XSSFSheet s = w.createSheet();
	XSSFRow r = s.createRow(0);
	XSSFCell c = r.createCell(0);
	c.setCellFormula("MATCH(\"VAL\",B3:B11,)");
	FormulaEvaluator evaluator = w.getCreationHelper().createFormulaEvaluator();
	evaluator.evaluate(c);
}

Actual Results:

Exception in thread "main" java.lang.RuntimeException: Unexpected match_type type (org.apache.poi.ss.formula.eval.MissingArgEval)
	at org.apache.poi.ss.formula.functions.Match.evaluateMatchTypeArg(Match.java:182)
	at org.apache.poi.ss.formula.functions.Match.evaluate(Match.java:77)
	at org.apache.poi.ss.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:153)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
	at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:56)
	at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:110)

Expected Results:
Formula should be evaluated without Exceptions.
Comment 1 Dominik Stadler 2020-12-30 21:42:21 UTC
This should be fixed via r1884959, we now handle missing or blank match_type as default value "1".