From 70e85686963adf321894979ad32b8718d279468f Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Fri, 19 Sep 2008 02:19:58 +0000 Subject: Partitioning common formula logic. Introduced FormulaParsingWorkbook and EvaluationWorkbook interfaces to make merge with ooxml branch easier git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@696898 13f79535-47bb-0310-9956-ffa450edef68 --- .../eventusermodel/TestEventWorkbookBuilder.java | 10 ++-- .../apache/poi/hssf/model/TestFormulaParser.java | 57 +++++++++++----------- .../poi/hssf/model/TestFormulaParserEval.java | 18 ++++--- .../hssf/model/TestOperandClassTransformer.java | 17 +++++-- .../org/apache/poi/hssf/model/TestRVA.java | 2 +- .../poi/hssf/record/formula/TestAreaPtg.java | 6 +-- .../formula/TestExternalFunctionFormulas.java | 4 +- .../poi/hssf/record/formula/TestFuncVarPtg.java | 4 +- .../function/TestParseMissingBuiltInFuncs.java | 6 +-- .../hssf/record/formula/functions/TestDate.java | 2 +- .../poi/ss/formula/FormulaParserTestHelper.java | 48 ++++++++++++++++++ 11 files changed, 116 insertions(+), 58 deletions(-) create mode 100644 src/testcases/org/apache/poi/ss/formula/FormulaParserTestHelper.java (limited to 'src/testcases/org/apache/poi') diff --git a/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java b/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java index a17414af74..2c56ea57a1 100644 --- a/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java +++ b/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java @@ -26,7 +26,7 @@ import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.Record; @@ -101,7 +101,7 @@ public final class TestEventWorkbookBuilder extends TestCase { // Check we can get the formula without breaking for(int i=0; inull */ /* package */ static Ptg[] parseFormula(String formula) { - Ptg[] result = FormulaParser.parse(formula, null); + Ptg[] result = HSSFFormulaParser.parse(formula, (HSSFWorkbook)null); assertNotNull("Ptg array should not be null", result); return result; } + private static String toFormulaString(Ptg[] ptgs) { + return HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, ptgs); + } public void testSimpleFormula() { Ptg[] ptgs = parseFormula("2+2"); @@ -133,7 +136,7 @@ public final class TestFormulaParser extends TestCase { HSSFWorkbook w = HSSFTestDataSamples.openSampleWorkbook("testNames.xls"); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(w); - Ptg[] ptg = FormulaParser.parse("myFunc()", w); + Ptg[] ptg = HSSFFormulaParser.parse("myFunc()", w); // myFunc() actually takes 1 parameter. Don't know if POI will ever be able to detect this problem // the name gets encoded as the first arg @@ -405,7 +408,7 @@ public final class TestFormulaParser extends TestCase { Ptg[] ptgs = { new FuncPtg(10), }; - assertEquals("NA()", FormulaParser.toFormulaString(book, ptgs)); + assertEquals("NA()", HSSFFormulaParser.toFormulaString(book, ptgs)); } public void testPercent() { @@ -639,11 +642,11 @@ public final class TestFormulaParser extends TestCase { String formulaString; Ptg[] ptgs; ptgs = parseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))"); - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); assertEquals("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString); ptgs = parseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)"); - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString); } public void testParserErrors() { @@ -665,12 +668,9 @@ public final class TestFormulaParser extends TestCase { try { parseFormula(formula); throw new AssertionFailedError("expected parse exception"); - } catch (FormulaParseException e) { - // expected during successful test - assertNotNull(e.getMessage()); } catch (RuntimeException e) { - e.printStackTrace(); - fail("Wrong exception:" + e.getMessage()); + // expected during successful test + FormulaParserTestHelper.confirmParseException(e); } } @@ -697,7 +697,7 @@ public final class TestFormulaParser extends TestCase { Ptg[] ptgs = { spacePtg, new IntPtg(4), }; String formulaString; try { - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); } catch (IllegalStateException e) { if(e.getMessage().equalsIgnoreCase("too much stuff left on the stack")) { throw new AssertionFailedError("Identified bug 44609"); @@ -709,7 +709,7 @@ public final class TestFormulaParser extends TestCase { assertEquals("4", formulaString); ptgs = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, }; - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); assertEquals("3+4", formulaString); } @@ -725,7 +725,7 @@ public final class TestFormulaParser extends TestCase { DividePtg.instance, }; try { - FormulaParser.toFormulaString(null, ptgs); + toFormulaString(ptgs); fail("Expected exception was not thrown"); } catch (IllegalStateException e) { // expected during successful test @@ -764,10 +764,10 @@ public final class TestFormulaParser extends TestCase { private static void confirmArgCountMsg(String formula, String expectedMessage) { HSSFWorkbook book = new HSSFWorkbook(); try { - FormulaParser.parse(formula, book); + HSSFFormulaParser.parse(formula, book); throw new AssertionFailedError("Didn't get parse exception as expected"); - } catch (FormulaParseException e) { - assertEquals(expectedMessage, e.getMessage()); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, expectedMessage); } } @@ -776,15 +776,17 @@ public final class TestFormulaParser extends TestCase { try { parseFormula("round(3.14;2)"); throw new AssertionFailedError("Didn't get parse exception as expected"); - } catch (FormulaParseException e) { - assertEquals("Parse error near char 10 ';' in specified formula 'round(3.14;2)'. Expected ',' or ')'", e.getMessage()); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, + "Parse error near char 10 ';' in specified formula 'round(3.14;2)'. Expected ',' or ')'"); } try { parseFormula(" =2+2"); throw new AssertionFailedError("Didn't get parse exception as expected"); - } catch (FormulaParseException e) { - assertEquals("The specified formula ' =2+2' starts with an equals sign which is not allowed.", e.getMessage()); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, + "The specified formula ' =2+2' starts with an equals sign which is not allowed."); } } @@ -819,7 +821,7 @@ public final class TestFormulaParser extends TestCase { Ptg[] ptgs; try { - ptgs = FormulaParser.parse("count(pfy1)", wb); + ptgs = HSSFFormulaParser.parse("count(pfy1)", wb); } catch (IllegalArgumentException e) { if (e.getMessage().equals("Specified colIx (1012) is out of range")) { throw new AssertionFailedError("Identified bug 45354"); @@ -835,10 +837,9 @@ public final class TestFormulaParser extends TestCase { try { cell.setCellFormula("count(pf1)"); throw new AssertionFailedError("Expected formula parse execption"); - } catch (FormulaParseException e) { - if (!e.getMessage().equals("Specified named range 'pf1' does not exist in the current workbook.")) { - throw e; - } + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, + "Specified named range 'pf1' does not exist in the current workbook."); } cell.setCellFormula("count(fp1)"); // plain cell ref, col is in range } @@ -850,14 +851,14 @@ public final class TestFormulaParser extends TestCase { HSSFWorkbook book = new HSSFWorkbook(); book.createSheet("Sheet1"); - ptgs = FormulaParser.parse("Sheet1!A10:A40000", book); + ptgs = HSSFFormulaParser.parse("Sheet1!A10:A40000", book); aptg = (AreaI) ptgs[0]; if (aptg.getLastRow() == -25537) { throw new AssertionFailedError("Identified bug 45358"); } assertEquals(39999, aptg.getLastRow()); - ptgs = FormulaParser.parse("Sheet1!A10:A65536", book); + ptgs = HSSFFormulaParser.parse("Sheet1!A10:A65536", book); aptg = (AreaI) ptgs[0]; assertEquals(65535, aptg.getLastRow()); diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java index 2bede8e5f5..fbbdbd54dc 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java @@ -17,9 +17,9 @@ package org.apache.poi.hssf.model; +import junit.framework.AssertionFailedError; import junit.framework.TestCase; -import org.apache.poi.hssf.model.FormulaParser.FormulaParseException; import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.NamePtg; import org.apache.poi.hssf.record.formula.Ptg; @@ -30,6 +30,7 @@ import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; +import org.apache.poi.ss.formula.FormulaParserTestHelper; /** * Test the low level formula parser functionality, @@ -51,21 +52,21 @@ public final class TestFormulaParserEval extends TestCase { name.setNameName("testName"); name.setReference("A1:A2"); - ptgs = FormulaParser.parse("SUM(testName)", workbook); + ptgs = HSSFFormulaParser.parse("SUM(testName)", workbook); assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); assertEquals(NamePtg.class, ptgs[0].getClass()); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); // Now make it a single cell name.setReference("C3"); - ptgs = FormulaParser.parse("SUM(testName)", workbook); + ptgs = HSSFFormulaParser.parse("SUM(testName)", workbook); assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); assertEquals(NamePtg.class, ptgs[0].getClass()); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); // And make it non-contiguous name.setReference("A1:A2,C3"); - ptgs = FormulaParser.parse("SUM(testName)", workbook); + ptgs = HSSFFormulaParser.parse("SUM(testName)", workbook); assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); assertEquals(NamePtg.class, ptgs[0].getClass()); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); @@ -89,11 +90,12 @@ public final class TestFormulaParserEval extends TestCase { CellValue result; try { result = fe.evaluate(cell); - } catch (FormulaParseException e) { - if(e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) { - fail("Identifed bug 44539"); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e); + if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) { + throw new AssertionFailedError("Identifed bug 44539"); } - throw new RuntimeException(e); + throw e; } assertEquals(HSSFCell.CELL_TYPE_NUMERIC, result.getCellType()); assertEquals(42.0, result.getNumberValue(), 0.0); diff --git a/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java b/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java index 47cfb574c1..530c2b09c2 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java +++ b/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java @@ -23,6 +23,7 @@ import junit.framework.TestCase; import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * Tests specific formula examples in OperandClassTransformer. @@ -31,9 +32,15 @@ import org.apache.poi.hssf.record.formula.Ptg; */ public final class TestOperandClassTransformer extends TestCase { + private static Ptg[] parseFormula(String formula) { + Ptg[] result = HSSFFormulaParser.parse(formula, (HSSFWorkbook)null); + assertNotNull("Ptg array should not be null", result); + return result; + } + public void testMdeterm() { String formula = "MDETERM(ABS(A1))"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); confirmTokenClass(ptgs, 0, Ptg.CLASS_ARRAY); confirmFuncClass(ptgs, 1, "ABS", Ptg.CLASS_ARRAY); @@ -51,7 +58,7 @@ public final class TestOperandClassTransformer extends TestCase { */ public void DISABLED_testIndexPi1() { String formula = "INDEX(PI(),1)"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); confirmFuncClass(ptgs, 1, "PI", Ptg.CLASS_ARRAY); // fails as of POI 3.1 confirmFuncClass(ptgs, 2, "INDEX", Ptg.CLASS_VALUE); @@ -63,7 +70,7 @@ public final class TestOperandClassTransformer extends TestCase { */ public void testDirectOperandOfValueOperator() { String formula = "COUNT(A1*1)"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); if (ptgs[0].getPtgClass() == Ptg.CLASS_REF) { throw new AssertionFailedError("Identified bug 45348"); } @@ -78,13 +85,13 @@ public final class TestOperandClassTransformer extends TestCase { public void testRtoV() { String formula = "lookup(A1, A3:A52, B3:B52)"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); confirmTokenClass(ptgs, 0, Ptg.CLASS_VALUE); } public void testComplexIRR_bug45041() { String formula = "(1+IRR(SUMIF(A:A,ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A))),B:B),0))^365-1"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); FuncVarPtg rowFunc = (FuncVarPtg) ptgs[10]; FuncVarPtg sumifFunc = (FuncVarPtg) ptgs[12]; diff --git a/src/testcases/org/apache/poi/hssf/model/TestRVA.java b/src/testcases/org/apache/poi/hssf/model/TestRVA.java index 1ec7c92ae9..eb69a5cd0b 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestRVA.java +++ b/src/testcases/org/apache/poi/hssf/model/TestRVA.java @@ -82,7 +82,7 @@ public final class TestRVA extends TestCase { private void confirmCell(HSSFCell formulaCell, String formula, HSSFWorkbook wb) { Ptg[] excelPtgs = FormulaExtractor.getPtgs(formulaCell); - Ptg[] poiPtgs = FormulaParser.parse(formula, wb); + Ptg[] poiPtgs = HSSFFormulaParser.parse(formula, wb); int nExcelTokens = excelPtgs.length; int nPoiTokens = poiPtgs.length; if (nExcelTokens != nPoiTokens) { diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java b/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java index f5b80f6dac..84f6897748 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java @@ -20,7 +20,7 @@ package org.apache.poi.hssf.record.formula; import junit.framework.TestCase; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** @@ -87,7 +87,7 @@ public final class TestAreaPtg extends TestCase { private static String shiftAllColumnsBy1(String formula) { int letUsShiftColumn1By1Column=1; HSSFWorkbook wb = null; - Ptg[] ptgs = FormulaParser.parse(formula, wb); + Ptg[] ptgs = HSSFFormulaParser.parse(formula, wb); for(int i=0; i