Created attachment 23453 [details] hssf-array-ptg.xls 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}", cell.getCellFormula()); 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}", cell.getCellFormula()); 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}", cell.getCellFormula()); 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\"}", cell.getCellFormula()); assertEquals("Wrong string value for string array", "a", cell.getRichStringCellValue().toString()); // Booleans cell = sheet.getRow(2).getCell((short) 0); assertEquals("Wrong formula string for boolean array", "{TRUE,FALSE}", cell.getCellFormula()); assertTrue("Wrong boolean value for boolean array", cell.getBooleanCellValue()); } 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.
Another of our "wait to see if it gets fixed" test cases which probably belongs under the same umbrella: @Test 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()); }
Created attachment 23454 [details] hssf-array-plus-memarea-ptg.xls
Hi, 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? Thanks. Christophe.
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.
Well then, can you suggest a test case which passes and shows that the feature works? Because it's still failing here.
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.
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)
Created attachment 32609 [details] correct hssf-array-ptg.xls Corrected version of hssf-array-ptg.xls in case anyone is curious.
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.)
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 : http://mail-archives.apache.org/mod_mbox/poi-user/201511.mbox/%3CCAAwi-j_kWOZ-x+s-e5AQvZ9DCGj6iYktwLEOnL3vOL+pWk_9UQ@mail.gmail.com%3E