Created attachment 27865 [details] The spreadsheetfile used. I created a simple .xsf test spreadsheet (see attachment) that uses refernce intersection: (C2:D3 D3:E4) When parsing the Formula of the Cell (HSSF) Ptg[] ptgs = FormulaParser.parse(c.getCellFormula(),evaluationWorkbook,c.getCellType(),workbook.getSheetIndex(c.getSheet())); The following exception is raised: ---- STACKTRACE ---- org.apache.poi.ss.formula.FormulaParseException: Parse error near char 7 'D' in specified formula '(C2:D3 D3:E4)'. Expected ')' at org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:217) at org.apache.poi.ss.formula.FormulaParser.Match(FormulaParser.java:249) at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1106) at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1077) at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1064) at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1424) at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1524) at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1508) at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1465) at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1445) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1566) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174) at main.ListProtectedCells.main(ListProtectedCells.java:52)
Created attachment 30878 [details] Proposed fix for intersections I've been looking into intersection parsing and have attached a patch against trunk r1525871 which I believe fixes this. I would appreciate review from someone familiar with the formula parsing code. There are two issues parsing "(C2:D3 D3:E4)": (1) Intersection parsing is unsupported. It requires changing the lexing in GetChar to track whitespace state because although we generally skip whitespace the intersection operator is a space. This approach seems the least invasive as it allows the rest of the parse to continue skipping whitespace. (2) Having fixed that, brackets are not supported around expressions with lower precedence than comparison operations (previously just union; now union and intersection). This seems to be a matter of changing parseSimpleFactor to call unionExpression (the root of the parse) inside the matches for parentheses. Existing tests pass and I've added some covering the intersection and parenthesis behaviour in TestFormulaParser. Queries: (a) The formula "1 2" was a tested parser fail case. It now parses as a intersection. "1,2" already parsed as a union. Neither make sense and Excel understandably complains. Does/should the POI FormulaParser attempt to cope with this type of error? In a more elaborate compiler this might be a type analysis error rather than something caught by the parse. Is there any POI code that attempts this kind of thing? (b) The documentation pulled in via the svn:external to src/documentation/content/xdocs/spreadsheet/formula.xml claims intersections and unions are not yet supported. Is the union documentation just out of date, or is there some other sense in which it isn't supported? If it's just out of date then I guess "Region operators: union, intersection" just wants to move from the unsupported to the supported section. I can supply a separate patch if required.
In r1613737 I have added a unit test for intersect formulas, based on your kindly provided test file This already passes, so it looks like some other formula parser work in the mean time has already added the required support! (If you spot something still missing for this, please re-open the bug, and upload a small junit unit test which shows what is still missing)
Intersection formulae still don't work in XSSF - re-opening to deal with that.
I've now applied Matt's patch from comment 1. I've also added a minimal test to TestXSSFBugs demonstrating what previously didn't work and now does. Fixed in SVN r1696549, docs/release notes followed in r1696550