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 2016/02/05 15:31:47 UTC
[Bug 58975] New: Function update fails on functions with more than
127 arguments
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
Bug ID: 58975
Summary: Function update fails on functions with more than 127
arguments
Product: POI
Version: 3.13-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: wellwishingmax@gmail.com
I recently encountered a problem when trying to rename a sheet.
Exception in thread "main" java.lang.NegativeArraySizeException
at
org.apache.poi.ss.formula.FormulaRenderer.getOperands(FormulaRenderer.java:118)
at
org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:100)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateFormula(XSSFFormulaUtils.java:101)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:79)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1571)
After some poking around, I found that that the issue is that one of the cells
previously added had 176 arguments in a CONCATENATE function (don't ask...)
which was causing the byte value of _numberOfArgs in
org.apache.poi.ss.formula.ptg.AbstractFunctionPtg to overflow. Excel does not
actually limit the number of arguments in CONCATENATE itself, and the sheet
works fine in POI unless you try to parse the formula. Unfortunately, renaming
a sheet parses all function cells to see if they need to be renamed. A very
similar cell with 108 arguments does not cause this issue.
A workaround for anyone encountering this bug is to simply turn any call to
CONCATENATE(1,2,...,x) to CONCATENATE(1,2,...125,CONCATENATE(125,126,....x)),
repeating this split as many times as required. This causes the function to
parse properly and returns the same result.
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
--- Comment #6 from PJ Fanning <fa...@yahoo.com> ---
added test case using
https://svn.apache.org/viewvc?view=revision&revision=1801307
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
--- Comment #3 from wellwishingmax@gmail.com ---
(In reply to Javen O'Neal from comment #2)
> Excel limits functions to 255 arguments [1]
>
> > Calculation specifications and limits
> > Feature Maximum limit
> > Arguments in a function 255
>
> POI XSSF should at least be able to support this many function arguments.
>
> [1]
> https://support.office.com/en-us/article/Excel-specifications-and-limits-
> 1672b34d-7043-467e-8e27-269d656771c3
>
> 128 sounds like a signed byte data type is being used when we should either
> be using an unsigned byte (unwrap a signed byte) or short.
>
> Do you have time to look through the code to try to find where this might be
> happening?
At line 99 of FormulaRenderer, the object relating to the formula is cast as an
OperationPtg. At line 100, getNumberOfOperands() is called on it. Examining the
type hierarchy, getNumberOfOperands() is abstract in OperationPtg, and the only
subclass that implements it that doesn't return a constant is
AbstractFunctionPtg, which returns _numberOfArgs, a private byte. The
constructor sets _numberOfArgs on creation by casting an int argument nParams
as a byte.
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |michal@michal.waw.pl
--- Comment #8 from Dominik Stadler <do...@gmx.at> ---
*** Bug 61385 has been marked as a duplicate of this bug. ***
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
--- Comment #7 from Javen O'Neal <on...@apache.org> ---
Will be included in POI 3.17 beta 2
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
--- Comment #2 from Javen O'Neal <on...@apache.org> ---
Excel limits functions to 255 arguments [1]
> Calculation specifications and limits
> Feature Maximum limit
> Arguments in a function 255
POI XSSF should at least be able to support this many function arguments.
[1]
https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
128 sounds like a signed byte data type is being used when we should either be
using an unsigned byte (unwrap a signed byte) or short.
Do you have time to look through the code to try to find where this might be
happening?
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |RESOLVED
Resolution|--- |FIXED
--- Comment #5 from PJ Fanning <fa...@yahoo.com> ---
Fixed using https://svn.apache.org/viewvc?view=revision&revision=1800949
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #4 from Dominik Stadler <do...@gmx.at> ---
Can you provide sample code which allows to reproduce this? Ideally as
standalone unit-test together with any required sample-file?
This way we can reproduce it and also ensure that it stays fixed in the future.
--
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
[Bug 58975] Function update fails on functions with more than 127
arguments
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975
wellwishingmax@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from wellwishingmax@gmail.com ---
Ah, sorry, I meant "CONCATENATE(1,2,...125,CONCATENATE(126,127,....x))" in the
workaround, as written it would duplicate the 125th value.
--
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