Bug 46989 - Support Array formulas
Summary: Support Array formulas
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.12-dev
Hardware: PC Windows Vista
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 48292
  Show dependency tree
Reported: 2009-04-07 17:37 UTC by Trejkaz (pen name)
Modified: 2015-11-25 10:11 UTC (History)
2 users (show)

hssf-array-ptg.xls (13.50 KB, application/octet-stream)
2009-04-07 17:37 UTC, Trejkaz (pen name)
hssf-array-plus-memarea-ptg.xls (13.50 KB, application/octet-stream)
2009-04-07 18:15 UTC, Trejkaz (pen name)
correct hssf-array-ptg.xls (13.50 KB, application/vnd.ms-excel)
2015-03-25 00:23 UTC, Trejkaz (pen name)

Note You need to log in before you can comment on or make changes to this bug.
Description Trejkaz (pen name) 2009-04-07 17:37:09 UTC
Created attachment 23453 [details]

The following test case is in our set of "things which POI cannot do":

    public void testArrayPtg() throws Exception
        File file = new File("hssf-array-ptg.xls");

        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(file)));
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFCell cell;

        // All in one row
        cell = sheet.getRow(0).getCell((short) 0);
        assertEquals("Wrong formula string for numeric 4x1 array", "{1,2,3,4}",
        assertEquals("Wrong numeric value for numeric 4x1 array", 1.0,
                     cell.getNumericCellValue(), 0.0);

        // All in one column
        cell = sheet.getRow(0).getCell((short) 1);
        assertEquals("Wrong formula string for numeric 1x4 array", "{1;2;3;4}",
        assertEquals("Wrong numeric value for numeric 1x4 array", 1.0,
                     cell.getNumericCellValue(), 0.0);

        // 2 x 2
        cell = sheet.getRow(0).getCell((short) 2);
        assertEquals("Wrong formula string for numeric 2x2 array", "{1,2;3,4}",
        assertEquals("Wrong numeric value for numeric 2x2 array", 1.0,
                     cell.getNumericCellValue(), 0.0);

        // Strings
        cell = sheet.getRow(1).getCell((short) 0);
        assertEquals("Wrong formula string for string array", "{\"a\",\"b\",\"c\"}",
        assertEquals("Wrong string value for string array", "a",

        // Booleans
        cell = sheet.getRow(2).getCell((short) 0);
        assertEquals("Wrong formula string for boolean array", "{TRUE,FALSE}",
        assertTrue("Wrong boolean value for boolean array",

In POI 3.1 it failed with RecordFormatException.  In POI trunk it appears to fail with IllegalStateException instead.

This was a test case constructed myself using Excel 2003 to reproduce something we saw in real user data.
Comment 1 Trejkaz (pen name) 2009-04-07 18:14:42 UTC
Another of our "wait to see if it gets fixed" test cases which probably belongs under the same umbrella:

    public void testArrayPlusMemAreaPtg() throws Exception
        File file = new File("hssf-array-plus-memarea-ptg.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(file)));
        HSSFCell cell = workbook.getSheetAt(0).getRow(3).getCell((short) 0);
        assertEquals("Wrong cell value", 5.0, cell.getNumericCellValue(), 0.0);
        assertEquals("Wrong cell formula", "{1}+A1:A2 A2:A3+{2}", cell.getCellFormula());
Comment 2 Trejkaz (pen name) 2009-04-07 18:15:39 UTC
Created attachment 23454 [details]
Comment 3 Dugas du Villard 2011-12-08 15:17:45 UTC

I have a problem with POI due probably to array formula. It's why I'm posting the probleme in this bug.

I have attach 2 xls files. One contain array formula (in cell B37..), the other not. 
I get an error "java.lang.RuntimeException: Failed to find a matching shared formula record" when I save the file containing array formula using POI (method: workbook.write) If I save the file using Excel 2010, and then pass it to POI, it's ok. But if it is saved from Excel 2003, there is a problem.

Is there a fix for this? Is it link to the bug describe here?

Comment 4 Dominik Stadler 2015-03-22 14:39:21 UTC
Similar to bug 48292, there was no update on this for a long time, likely some things already work nowadays, so I think it best to report fresh bugs for anything that is still missing.

The provided test-case does not fail with any exception, but rather "java.lang.IllegalStateException: Cannot get a formula value from a numeric formula cell", which sounds like a test-case problem.
Comment 5 Trejkaz (pen name) 2015-03-23 02:33:32 UTC
Well then, can you suggest a test case which passes and shows that the feature works? Because it's still failing here.
Comment 6 Dominik Stadler 2015-03-23 19:51:43 UTC
Can you then please update and combine the failing tests so we have an up-to-date set? At least the first unit test seems to fail in a strange unrelated way, maybe the attached document is incorrect as it only has a formula in cell 0,3, but the test expects formulas in more cells.
Comment 7 Trejkaz (pen name) 2015-03-25 00:19:56 UTC
What I'm finding:

- the first example now works (but somehow the attached .xls was the wrong file, which is probably a source of confusion.)

- the second example still fails and I didn't have to update the code. Stack trace:

java.lang.RuntimeException: Unknown grbit value (64)
	at org.apache.poi.ss.formula.constant.ConstantValueParser.readAConstantValue(ConstantValueParser.java:76)
	at org.apache.poi.ss.formula.constant.ConstantValueParser.parse(ConstantValueParser.java:52)
	at org.apache.poi.ss.formula.ptg.ArrayPtg$Initial.finishReading(ArrayPtg.java:261)
	at org.apache.poi.ss.formula.ptg.Ptg.readTokens(Ptg.java:70)
	at org.apache.poi.ss.formula.Formula.getTokens(Formula.java:82)
	at org.apache.poi.hssf.record.FormulaRecord.getParsedExpression(FormulaRecord.java:311)
	at org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate.getFormulaTokens(FormulaRecordAggregate.java:197)
	at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:626)
	at TestHssf.testArrayPlusMemAreaPtg(TestHssf.java:189)
Comment 8 Trejkaz (pen name) 2015-03-25 00:23:30 UTC
Created attachment 32609 [details]
correct hssf-array-ptg.xls

Corrected version of hssf-array-ptg.xls in case anyone is curious.
Comment 9 Trejkaz (pen name) 2015-03-25 00:28:33 UTC
Updating version to reflect that it still occurs on the 3.12 beta. It also occurs on 3.10 (which is what we were using before I updated to check it out) and on 3.5 (which is what we were on when we first discovered it.)
Comment 10 Tom Chiverton 2015-11-25 10:11:57 UTC
As of 3.13, certainly syntax like "IF({1,0}... throws "Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)" errors.

There was some discussion on the mailing list :