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.
Ah, sorry, I meant "CONCATENATE(1,2,...125,CONCATENATE(126,127,....x))" in the workaround, as written it would duplicate the 125th value.
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?
(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.
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.
Fixed using https://svn.apache.org/viewvc?view=revision&revision=1800949
added test case using https://svn.apache.org/viewvc?view=revision&revision=1801307
Will be included in POI 3.17 beta 2