Bug 45865 - Formula Parser doesn't handle external file references
Summary: Formula Parser doesn't handle external file references
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL: http://www.nabble.com/IndexBound-Exce...
Depends on:
Reported: 2008-09-23 05:59 UTC by Pierre Lavignotte
Modified: 2008-09-27 19:13 UTC (History)
0 users

Sample code and workbook to reproduce bug (4.08 KB, application/x-zip)
2008-09-23 06:00 UTC, Pierre Lavignotte

Note You need to log in before you can comment on or make changes to this 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