Summary: | Support Array formulas | ||
---|---|---|---|
Product: | POI | Reporter: | Trejkaz (pen name) <trejkaz> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | NEW --- | ||
Severity: | enhancement | CC: | onealj, tc |
Priority: | P2 | ||
Version: | 3.12-dev | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | Windows Vista | ||
Bug Depends on: | 48292 | ||
Bug Blocks: | |||
Attachments: |
hssf-array-ptg.xls
hssf-array-plus-memarea-ptg.xls correct hssf-array-ptg.xls |
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 |
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.