Bug 45865

Summary: Formula Parser doesn't handle external file references
Product: POI Reporter: Pierre Lavignotte <pierre.lavignotte>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
URL: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-td19622168.html
Attachments: Sample code and workbook to reproduce bug

Description Pierre Lavignotte 2008-09-23 05:59:32 UTC
When a formula contains a reference to an external file/sheet/cell, the parser ignores the external file reference and only parses the sheet/cell reference.

If the index of the original sheet is greater than the maximum index of the sheets of the current workbook, an IndexOutOfBoundException occurs.

Exemple :
WorkbookA has two sheets named Sheet1 & Sheet2
WorkbookB has only one sheet named Sheet1

In workbook B :
The formula '[WorkbookA]Sheet1!$A$1' will be correctly parsed but will point to cell A1 in WorkbookB.
the formula '[WorkbookA]Sheet2!$A$1' will fail to parse because it will point to the second sheet of WorkbookB, wich doesn't exist.

If WorkbookB contains 2 formulas :
A2 = '[WorkbookA]Sheet1!$A$1'
A3 = '[WorkbookA]Sheet2!$A$1'

Debuging the A2 cell record shows :
Ptg(0)=org.apache.poi.hssf.record.formula.Ref3DPtg [sheetIx=2 ! $A$1]
So it makes evaluation to fail also.

The attached sample shows a formula that evaluate in the wrong workbook.
Comment 1 Pierre Lavignotte 2008-09-23 06:00:30 UTC
Created attachment 22625 [details]
Sample code and workbook to reproduce bug
Comment 2 Josh Micich 2008-09-27 19:13:20 UTC
Fixed in svn r699761.

Quite a lot of stuff needed to be done to get your test code working.  

Formula rendering was fixed so that external references get rendered with workbook name (as Excel does)

The formula parser can now parse external references properly (as long as the same workbook+sheet combination already exists in the LinkTable). HSSFWorkbook probably still needs modifying to allow creation of new external references.

The formula evaluator was extended to allow evaluation of formulas across multiple workbooks (see TestWorkbookEvaluator.testEvaluateMultipleWorkbooks()).  We probably need to add examples for this functionality to the POI documentation.

junits added