Bug 53954 - Cross-worksheet references not parsed correctly
Summary: Cross-worksheet references not parsed correctly
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-10-02 14:58 UTC by Andreas Goetz
Modified: 2016-06-12 17:39 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Goetz 2012-10-02 14:58:22 UTC
I'm using evaluator.evaluateFormulaCell(c) to reevaluate an xlsx workbook. 

Upon evaluating a cell with cross-sheet, fixed index reference, the evaluation breaks in getDynamicReference although all functions used should be supported by POI:

=SUM(INDIRECT("'Test H0'!$H:$H"))

This is the stack trace:

Exception in thread "main" java.lang.NumberFormatException: For input string: "$H"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at org.apache.poi.ss.formula.OperationEvaluationContext.parseColRef(OperationEvaluationContext.java:229)
	at org.apache.poi.ss.formula.OperationEvaluationContext.getDynamicReference(OperationEvaluationContext.java:200)
	at org.apache.poi.ss.formula.functions.Indirect.evaluateIndirect(Indirect.java:124)
	at org.apache.poi.ss.formula.functions.Indirect.evaluate(Indirect.java:76)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:127)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:653)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
	at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:653)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:36)
	at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:653)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
	at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:653)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.WorkbookEvaluator.dereferenceResult(WorkbookEvaluator.java:543)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:506)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:653)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
	at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
	at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:653)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
	at org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
	at org.apache.poi.ss.formula.functions.Sumif.accumulate(Sumif.java:95)
	at org.apache.poi.ss.formula.functions.Sumif.sumMatchingCells(Sumif.java:83)
	at org.apache.poi.ss.formula.functions.Sumif.eval(Sumif.java:72)
	at org.apache.poi.ss.formula.functions.Sumif.evaluate(Sumif.java:65)
	at org.apache.poi.ss.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36)
	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)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
	at POITest.main(POITest.java:40)
Comment 1 Andreas Goetz 2012-10-02 15:10:58 UTC
I'm sorry- the stack trace was unnecessarily complicated:

Exception in thread "main" java.lang.NumberFormatException: For input string: "$H"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at org.apache.poi.ss.formula.OperationEvaluationContext.parseColRef(OperationEvaluationContext.java:229)
	at org.apache.poi.ss.formula.OperationEvaluationContext.getDynamicReference(OperationEvaluationContext.java:200)
	at org.apache.poi.ss.formula.functions.Indirect.evaluateIndirect(Indirect.java:124)
	at org.apache.poi.ss.formula.functions.Indirect.evaluate(Indirect.java:76)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:127)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
	at POITest.main(POITest.java:40)
Comment 2 Andreas Goetz 2012-10-02 15:58:45 UTC
Seems that OperationEvaluationContext.getDynamicReference identifies NameType=COLUMN and then calls OperationEvaluationContext.parseColRef which treats the passed column as integer- which it isn't.

I believe parseColRef needs more logic in this place (or is the wrong function to call- not being a POI expert).
Comment 3 Andreas Goetz 2012-10-02 16:05:31 UTC
The proposed fix would be to enhance parseColRef like this:

	private static int parseColRef(String refStrPart) {
		try {
			return Integer.parseInt(refStrPart) - 1;
		} catch (NumberFormatException e) {
			return CellReference.convertColStringToIndex(refStrPart);
		}
	}
Comment 4 Yegor Kozlov 2012-10-03 13:33:55 UTC
Any chance you upload a unit test for the proposed fix?
Comment 5 Dominik Stadler 2016-06-12 17:39:21 UTC
There was a change done for Bug 54720: Support for Row/Col Area Range like 8:8 or H:H, r1514812, which seems to have fixed this by not calling parseColRef() any more in this case. 

Therefore I am closing this as FIXED for now, please report any other issues that you might encounter.