Bug 48404 - Support formulas with multi-sheet 3D references
Summary: Support formulas with multi-sheet 3D references
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-FINAL
Hardware: Macintosh Linux
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-12-17 02:07 UTC by Vesa Akerman
Modified: 2016-04-05 16:45 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Vesa Akerman 2009-12-17 02:07:53 UTC
When I read (using hssf.usermodel) a cell formula, where the formula is a 3-D reference, I get a 2-D reference, referring to the first sheet.
For instance, if the formula is 'SUM($Sheet1.A1:$Sheet3.B1)', I get 'SUM(Sheet1!A1:B1)'.
Comment 1 Josh Micich 2009-12-17 12:20:24 UTC
This feature (multi-sheet 3D references) is not yet supported by POI.  Incidentally, the syntax of your formula looks wrong.  My Excel(win, 2007) seems to require "SUM(Sheet1:Sheet3!A1:B1)".

There are three aspects that will need fixing:
 1 - Rendering
 2 - Parsing
 3 - Evaluation

I'm not sure if you care about 2 or 3.

The rendering fix will probably involve ExternSheetNameResolver.prependSheetName() and perhaps some code in LinkTable.

The parsing fix may be a little more difficult with all the existing complexity regarding distinguishing sheet names, defined names, function names and cell names.   This will probably affect FormulaParser and LinkTable.checkExternSheet(int)

I'm not sure about the evaluation fix.  It may be simple because it seems like multi-sheet references are nowhere near as usable as single sheet ones.  Besides the aggregate functions (sum, stdev, etc) do you know of any other function or operator which can use multi-sheet references? 

Contributions always welcome.
Comment 2 Vesa Akerman 2009-12-21 00:39:48 UTC
Thanks for the prompt reply.

That's correct that the syntax I gave for the formula was wrong. I had namely opened the excel file with open office and copied the syntax from there to the bug message.

You asked if I know any other than aggregate functions used in 3-D references.  No, I don't.  I am using POI in a project where we convert files from different (older) file formats into XML format.  In testing I noticed that 3-D formulas gave wrong result.

I reported this as a bug, because in POI website, in 'Formula support' under 'Supported Features' it says: 'References: single cell & area, 2D & 3D, relative & absolute'.

I don't know if it is simple or difficult to recognize a 3-D reference.  I think that POI should give anyway an error message, instead of giving erroneous 2-D reference.

By the way, at the moment POI supports approximately 60 of the 346 functions implemented in Excel 2003.  Do you know if there are any plans to add support to more functions?

Thanks,  Vesa
Comment 3 Javen O'Neal 2016-04-05 16:45:11 UTC
As of POI 3.14, POI still does not support multi-sheet 3D references. If you still need this or found a solution, please submit a patch with a working unit test.