Make intersection formulae work in XSSF. Patch from Matt Hillsdon plus additional tests.

https://bz.apache.org/bugzilla/show_bug.cgi?id=52111

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1696549 13f79535-47bb-0310-9956-ffa450edef68

2 files changed, 73 insertions, 8 deletions

diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
index ce017f7ed2..09bf5a9b6a 100644
--- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
+++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
@@ -702,7 +702,6 @@ public final class TestFormulaParser extends TestCase {
 		assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString);
 	}
 	public void testParserErrors() {
-		parseExpectedException("1 2");
 		parseExpectedException(" 12 . 345  ");
 		parseExpectedException("1 .23  ");
 
@@ -1060,8 +1059,65 @@ public final class TestFormulaParser extends TestCase {
 		);
 		MemFuncPtg mf = (MemFuncPtg)ptgs[0];
 		assertEquals(45, mf.getLenRefSubexpression());
+
+        // We don't check the type of the operands.
+        confirmTokenClasses("1,2", MemAreaPtg.class, IntPtg.class, IntPtg.class, UnionPtg.class);
 	}
 
+	public void testIntersection() {
+       String formula = "Sheet1!$B$2:$C$3 OFFSET(Sheet1!$E$2:$E$4, 1,Sheet1!$A$1) Sheet1!$D$6";
+        HSSFWorkbook wb = new HSSFWorkbook();
+        wb.createSheet("Sheet1");
+        Ptg[] ptgs = FormulaParser.parse(formula, HSSFEvaluationWorkbook.create(wb), FormulaType.CELL, -1);
+
+        confirmTokenClasses(ptgs,
+                // TODO - AttrPtg.class, // Excel prepends this
+                MemFuncPtg.class,
+                Area3DPtg.class,
+                Area3DPtg.class,
+                IntPtg.class,
+                Ref3DPtg.class,
+                FuncVarPtg.class,
+                IntersectionPtg.class,
+                Ref3DPtg.class,
+                IntersectionPtg.class
+        );
+        MemFuncPtg mf = (MemFuncPtg)ptgs[0];
+        assertEquals(45, mf.getLenRefSubexpression());
+
+        // This used to be an error but now parses.  Union has the same behaviour.
+        confirmTokenClasses("1 2", MemAreaPtg.class, IntPtg.class, IntPtg.class, IntersectionPtg.class);
+	}
+	
+	public void testComparisonInParen() {
+	    confirmTokenClasses("(A1 > B2)", 
+            RefPtg.class, 
+            RefPtg.class, 
+            GreaterThanPtg.class, 
+            ParenthesisPtg.class
+        );
+	}
+	
+	public void testUnionInParen() {
+	    confirmTokenClasses("(A1:B2,B2:C3)", 
+          MemAreaPtg.class, 
+          AreaPtg.class, 
+          AreaPtg.class, 
+          UnionPtg.class, 
+          ParenthesisPtg.class
+        );
+	}
+
+    public void testIntersectionInParen() {
+        confirmTokenClasses("(A1:B2 B2:C3)", 
+            MemAreaPtg.class, 
+            AreaPtg.class, 
+            AreaPtg.class, 
+            IntersectionPtg.class, 
+            ParenthesisPtg.class
+        );
+    }
+    
 	public void testRange_bug46643() {
 		String formula = "Sheet1!A1:Sheet1!B3";
 		HSSFWorkbook wb = new HSSFWorkbook();
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index af6e8bb036..c1e14bc674 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -2664,17 +2664,26 @@ public final class TestBugs extends BaseTestBugzillaIssues {
     public void bug52111() throws Exception {
         Workbook wb = openSample("Intersection-52111.xls");
         Sheet s = wb.getSheetAt(0);
-
-        // Check we can read it correctly
-        Cell intF = s.getRow(2).getCell(0);
+        assertFormula(wb, s.getRow(2).getCell(0), "(C2:D3 D3:E4)", "4.0");
+        assertFormula(wb, s.getRow(6).getCell(0), "Tabelle2!E:E Tabelle2!$A11:$IV11", "5.0");
+        assertFormula(wb, s.getRow(8).getCell(0), "Tabelle2!E:F Tabelle2!$A11:$IV12", null);
+    }
+    
+    private void assertFormula(Workbook wb, Cell intF, String expectedFormula, String expectedResultOrNull) {
         assertEquals(Cell.CELL_TYPE_FORMULA, intF.getCellType());
-        assertEquals(Cell.CELL_TYPE_NUMERIC, intF.getCachedFormulaResultType());
-
-        assertEquals("(C2:D3 D3:E4)", intF.getCellFormula());
+        if (null == expectedResultOrNull) {
+            assertEquals(Cell.CELL_TYPE_ERROR, intF.getCachedFormulaResultType());
+            expectedResultOrNull = "#VALUE!";
+        }
+        else {
+            assertEquals(Cell.CELL_TYPE_NUMERIC, intF.getCachedFormulaResultType());
+        }
+        
+        assertEquals(expectedFormula, intF.getCellFormula());
 
         // Check we can evaluate it correctly
         FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
-        assertEquals("4.0", eval.evaluate(intF).formatAsString());
+        assertEquals(expectedResultOrNull, eval.evaluate(intF).formatAsString());
     }
 
     @Test