Bug 58975

Summary:	Function update fails on functions with more than 127 arguments
Product:	POI	Reporter:	wellwishingmax
Component:	XSSF	Assignee:	POI Developers List <dev>
Status:	RESOLVED FIXED
Severity:	normal	CC:	michal
Priority:	P2
Version:	3.13-FINAL
Target Milestone:	---
Hardware:	PC
OS:	All

Description wellwishingmax 2016-02-05 14:31:47 UTC

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.

Comment 1 wellwishingmax 2016-02-05 14:41:41 UTC

Ah, sorry, I meant "CONCATENATE(1,2,...125,CONCATENATE(126,127,....x))" in the workaround, as written it would duplicate the 125th value.

Comment 2 Javen O'Neal 2016-02-05 18:31:26 UTC

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?

Comment 3 wellwishingmax 2016-02-08 10:12:13 UTC

(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.

Comment 4 Dominik Stadler 2016-02-14 20:54:35 UTC

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.

Comment 5 PJ Fanning 2017-07-05 22:58:33 UTC

Fixed using https://svn.apache.org/viewvc?view=revision&revision=1800949

Comment 6 PJ Fanning 2017-07-08 18:17:02 UTC

added test case using https://svn.apache.org/viewvc?view=revision&revision=1801307

Comment 7 Javen O'Neal 2017-08-06 18:36:17 UTC

Will be included in POI 3.17 beta 2

Comment 8 Dominik Stadler 2017-09-13 20:02:29 UTC

*** Bug 61385 has been marked as a duplicate of this bug. ***