Bug 52111 - [PATCH] Parsing of formula with reference intersection
Summary: [PATCH] Parsing of formula with reference intersection
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Blocks: 58648
  Show dependency tree
Reported: 2011-10-29 10:19 UTC by Sebastian
Modified: 2016-04-05 10:10 UTC (History)
0 users

The spreadsheetfile used. (13.50 KB, application/vnd.ms-excel)
2011-10-29 10:19 UTC, Sebastian
Proposed fix for intersections (7.10 KB, patch)
2013-09-24 13:58 UTC, Matt Hillsdon
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Sebastian 2011-10-29 10:19:09 UTC
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)
Comment 1 Matt Hillsdon 2013-09-24 13:58:02 UTC
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.


(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.
Comment 2 Nick Burch 2014-07-26 22:41:10 UTC
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)
Comment 3 David North 2015-08-19 09:58:20 UTC
Intersection formulae still don't work in XSSF - re-opening to deal with that.
Comment 4 David North 2015-08-19 10:13:27 UTC
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