You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2008/07/07 18:52:25 UTC
DO NOT REPLY [Bug 45354] New: Complex formula calcuation does not
support named cells
https://issues.apache.org/bugzilla/show_bug.cgi?id=45354
Summary: Complex formula calcuation does not support named cells
Product: POI
Version: unspecified
Platform: PC
OS/Version: Linux
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: bsneade+apache@gmail.com
Created an attachment (id=22223)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=22223)
Example referenced in the description
I reported something similar in
https://issues.apache.org/bugzilla/show_bug.cgi?id=45353 but the stack trace is
totally different, so I thought to make a new ticket. Sorry if its a dupe.
When using named cells with the following formula (where tappab, tapp, pfy1 and
totall are named cells):
=IF(tappab<0;-21;IF(tapp+pfy1<>totall;-11;0))
I get the error:
Exception in thread "main" java.lang.IllegalArgumentException: Specified colIx
(1012) is out of range
at
org.apache.poi.hssf.record.formula.RefPtgBase.setColumn(RefPtgBase.java:144)
at
org.apache.poi.hssf.record.formula.RefPtgBase.<init>(RefPtgBase.java:61)
at org.apache.poi.hssf.record.formula.RefPtg.<init>(RefPtg.java:35)
at
org.apache.poi.hssf.model.FormulaParser.parseIdentifier(FormulaParser.java:277)
at
org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:229)
at
org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468)
at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433)
at
org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420)
at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628)
at
org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:725)
at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693)
at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650)
at
org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:405)
at
org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308)
at
org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:227)
at
org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468)
at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433)
at
org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420)
at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628)
at
org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709)
at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693)
at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650)
at
org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:405)
at
org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308)
at
org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:227)
at
org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468)
at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433)
at
org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420)
at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628)
at
org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709)
at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693)
at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650)
at
org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:751)
at
org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:113)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateCell(HSSFFormulaEvaluator.java:346)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:338)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:183)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:278)
at
NamedCellComplicatedFormulaTest.main(NamedCellComplicatedFormulaTest.java:20)
In the example I have attached, the formula is in A2, and the named cells are
in the first row.
Note: this is in 3.1Final.
--
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 45354] Complex formula calcuation does not support
named cells
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45354
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED
--- Comment #1 from Josh Micich <jo...@gildedtree.com> 2008-07-08 14:20:19 PST ---
Fixed in svn r674953
Excel allows named ranges to have names which look like cell references. They
seem to be distinguished only by the allowable values for row and column. For
instance, "FY2008" is definitely a cell reference and cannot be a named range.
"A70123" definitely not a cell reference and might be a named range.
POI was interpreting 'pfy1' as a cell reference (also incorrectly calculating
the column index to be 1012 - it would really be 10997). The fixed code now
checks the apparent column ('pfy') and row ('1') for maximum BIFF8 range ('IV'
and '65536') to decide whether to treat the identifier as a named range.
There are all sorts of complexities related to this issue. For example dots
are legal in named range names, but also may be used instead of the colon range
operator. In BIFF12 (Excel 2007) the sheet boundaries change, so 'pfy1'
becomes a valid cell reference, and presumably an invalid named range name.
--
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 45354] Complex formula calcuation does not support
named cells
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=45354
--- Comment #2 from Nick Burch <ni...@torchbox.com> 2008-07-10 13:32:03 PST ---
I've back-ported some fixes for column number <-> letter conversion from the
ooxml branch to trunk. Hopefully this helps
--
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