You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by dn...@apache.org on 2015/08/19 12:10:08 UTC
svn commit: r1696549 - in /poi/trunk: src/java/org/apache/poi/ss/formula/
src/ooxml/testcases/org/apache/poi/xssf/usermodel/
src/testcases/org/apache/poi/hssf/model/
src/testcases/org/apache/poi/hssf/usermodel/ test-data/spreadsheet/
Author: dnorth
Date: Wed Aug 19 10:10:08 2015
New Revision: 1696549
URL: http://svn.apache.org/r1696549
Log:
Make intersection formulae work in XSSF. Patch from Matt Hillsdon plus additional tests.
https://bz.apache.org/bugzilla/show_bug.cgi?id=52111
Added:
poi/trunk/test-data/spreadsheet/Intersection-52111-xssf.xlsx (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
poi/trunk/test-data/spreadsheet/Intersection-52111.xls
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?rev=1696549&r1=1696548&r2=1696549&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java Wed Aug 19 10:10:08 2015
@@ -39,6 +39,7 @@ import org.apache.poi.ss.formula.ptg.Fun
import org.apache.poi.ss.formula.ptg.FuncVarPtg;
import org.apache.poi.ss.formula.ptg.GreaterEqualPtg;
import org.apache.poi.ss.formula.ptg.GreaterThanPtg;
+import org.apache.poi.ss.formula.ptg.IntersectionPtg;
import org.apache.poi.ss.formula.ptg.IntPtg;
import org.apache.poi.ss.formula.ptg.LessEqualPtg;
import org.apache.poi.ss.formula.ptg.LessThanPtg;
@@ -100,6 +101,12 @@ public final class FormulaParser {
*/
private char look;
+ /**
+ * Tracks whether the run of whitespace preceeding "look" could be an
+ * intersection operator. See GetChar.
+ */
+ private boolean _inIntersection = false;
+
private FormulaParsingWorkbook _book;
private SpreadsheetVersion _ssVersion;
@@ -145,9 +152,20 @@ public final class FormulaParser {
fp.parse();
return fp.getRPNPtg(formulaType);
}
-
+
/** Read New Character From Input Stream */
private void GetChar() {
+ // The intersection operator is a space. We track whether the run of
+ // whitespace preceeding "look" counts as an intersection operator.
+ if (IsWhite(look)) {
+ if (look == ' ') {
+ _inIntersection = true;
+ }
+ }
+ else {
+ _inIntersection = false;
+ }
+
// Check to see if we've walked off the end of the string.
if (_pointer > _formulaLength) {
throw new RuntimeException("too far");
@@ -158,6 +176,7 @@ public final class FormulaParser {
// Just return if so and reset 'look' to something to keep
// SkipWhitespace from spinning
look = (char)0;
+ _inIntersection = false;
}
_pointer++;
//System.out.println("Got char: "+ look);
@@ -1108,7 +1127,7 @@ public final class FormulaParser {
return parseUnary(true);
case '(':
Match('(');
- ParseNode inside = comparisonExpression();
+ ParseNode inside = unionExpression();
Match(')');
return new ParseNode(ParenthesisPtg.instance, inside);
case '"':
@@ -1447,8 +1466,9 @@ public final class FormulaParser {
result = new ParseNode(operator, result, other);
}
}
+
private ParseNode unionExpression() {
- ParseNode result = comparisonExpression();
+ ParseNode result = intersectionExpression();
boolean hasUnions = false;
while (true) {
SkipWhite();
@@ -1456,7 +1476,7 @@ public final class FormulaParser {
case ',':
GetChar();
hasUnions = true;
- ParseNode other = comparisonExpression();
+ ParseNode other = intersectionExpression();
result = new ParseNode(UnionPtg.instance, result, other);
continue;
}
@@ -1467,6 +1487,25 @@ public final class FormulaParser {
}
}
+ private ParseNode intersectionExpression() {
+ ParseNode result = comparisonExpression();
+ boolean hasIntersections = false;
+ while (true) {
+ SkipWhite();
+ if (_inIntersection) {
+ // Don't getChar() as the space has already been eaten and recorded by SkipWhite().
+ hasIntersections = true;
+ ParseNode other = comparisonExpression();
+ result = new ParseNode(IntersectionPtg.instance, result, other);
+ continue;
+ }
+ if (hasIntersections) {
+ return augmentWithMemPtg(result);
+ }
+ return result;
+ }
+ }
+
private ParseNode comparisonExpression() {
ParseNode result = concatExpression();
while (true) {
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java?rev=1696549&r1=1696548&r2=1696549&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java Wed Aug 19 10:10:08 2015
@@ -21,6 +21,7 @@ import org.apache.poi.ss.formula.ptg.Abs
import org.apache.poi.ss.formula.ptg.AttrPtg;
import org.apache.poi.ss.formula.ptg.ControlPtg;
import org.apache.poi.ss.formula.ptg.FuncVarPtg;
+import org.apache.poi.ss.formula.ptg.IntersectionPtg;
import org.apache.poi.ss.formula.ptg.MemAreaPtg;
import org.apache.poi.ss.formula.ptg.MemFuncPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
@@ -117,7 +118,8 @@ final class OperandClassTransformer {
if (token instanceof ValueOperatorPtg || token instanceof ControlPtg
|| token instanceof MemFuncPtg
|| token instanceof MemAreaPtg
- || token instanceof UnionPtg) {
+ || token instanceof UnionPtg
+ || token instanceof IntersectionPtg) {
// Value Operator Ptgs and Control are base tokens, so token will be unchanged
// but any child nodes are processed according to desiredOperandClass and callerForceArrayFlag
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java?rev=1696549&r1=1696548&r2=1696549&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java Wed Aug 19 10:10:08 2015
@@ -2501,4 +2501,30 @@ public final class TestXSSFBugs extends
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57181.xlsm");
assertEquals(9, wb.getNumberOfSheets());
}
+
+ @Test
+ public void bug52111() throws Exception {
+ Workbook wb = XSSFTestDataSamples.openSampleWorkbook("Intersection-52111-xssf.xlsx");
+ Sheet s = wb.getSheetAt(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!11:11", "5.0");
+ assertFormula(wb, s.getRow(8).getCell(0), "Tabelle2!E:F Tabelle2!11:12", null);
+ }
+
+ private void assertFormula(Workbook wb, Cell intF, String expectedFormula, String expectedResultOrNull) {
+ assertEquals(Cell.CELL_TYPE_FORMULA, intF.getCellType());
+ 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(expectedResultOrNull, eval.evaluate(intF).formatAsString());
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java?rev=1696549&r1=1696548&r2=1696549&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java Wed Aug 19 10:10:08 2015
@@ -702,7 +702,6 @@ public final class TestFormulaParser ext
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 ext
);
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();
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java?rev=1696549&r1=1696548&r2=1696549&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java Wed Aug 19 10:10:08 2015
@@ -2664,17 +2664,26 @@ public final class TestBugs extends Base
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
Added: poi/trunk/test-data/spreadsheet/Intersection-52111-xssf.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/Intersection-52111-xssf.xlsx?rev=1696549&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/Intersection-52111-xssf.xlsx
------------------------------------------------------------------------------
--- svn:mime-type (added)
+++ svn:mime-type Wed Aug 19 10:10:08 2015
@@ -0,0 +1 @@
+application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Modified: poi/trunk/test-data/spreadsheet/Intersection-52111.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/Intersection-52111.xls?rev=1696549&r1=1696548&r2=1696549&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org