DO NOT REPLY [Bug 48404] New: in getting 3-D formula gives 2-D formula

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=48404

           Summary: in getting 3-D formula gives 2-D formula
           Product: POI
           Version: 3.5-FINAL
          Platform: Macintosh
        OS/Version: Linux
            Status: NEW
          Severity: major
          Priority: P2
         Component: HSSF
        AssignedTo: dev@poi.apache.org
        ReportedBy: vesa.akerman@dans.knaw.nl


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)'.

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

DO NOT REPLY [Bug 48404] in getting 3-D formula gives 2-D formula

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=48404

--- Comment #1 from Josh Micich <jo...@gildedtree.com> 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.

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

DO NOT REPLY [Bug 48404] in getting 3-D formula gives 2-D formula

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=48404

--- Comment #2 from Vesa Akerman <ve...@dans.knaw.nl> 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

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org