poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From one...@apache.org
Subject svn commit: r1712909 - /poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
Date Fri, 06 Nov 2015 04:45:26 GMT
Author: onealj
Date: Fri Nov  6 04:45:25 2015
New Revision: 1712909

URL: http://svn.apache.org/viewvc?rev=1712909&view=rev
Log:
whitespace

Modified:
    poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java

Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java?rev=1712909&r1=1712908&r2=1712909&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java Fri Nov 
6 04:45:25 2015
@@ -60,246 +60,245 @@ public class TestWorkbookEvaluator {
 
     private static final double EPSILON = 0.0000001;
 
-	private static ValueEval evaluateFormula(Ptg[] ptgs) {
-		OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null);
-		return new WorkbookEvaluator(null, null, null).evaluateFormula(ec, ptgs);
-	}
-
-	/**
-	 * Make sure that the evaluator can directly handle tAttrSum (instead of relying on re-parsing
-	 * the whole formula which converts tAttrSum to tFuncVar("SUM") )
-	 */
-	@Test
-	public void testAttrSum() {
-
-		Ptg[] ptgs = {
-			new IntPtg(42),
-			AttrPtg.SUM,
-		};
-
-		ValueEval result = evaluateFormula(ptgs);
-		assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
-	}
-
-	/**
-	 * Make sure that the evaluator can directly handle (deleted) ref error tokens
-	 * (instead of relying on re-parsing the whole formula which converts these
-	 * to the error constant #REF! )
-	 */
-	@Test
-	public void testRefErr() {
-
-		confirmRefErr(new RefErrorPtg());
-		confirmRefErr(new AreaErrPtg());
-		confirmRefErr(new DeletedRef3DPtg(0));
-		confirmRefErr(new DeletedArea3DPtg(0));
-	}
-	private static void confirmRefErr(Ptg ptg) {
-		Ptg[] ptgs = {
-			ptg,
-		};
-
-		ValueEval result = evaluateFormula(ptgs);
-		assertEquals(ErrorEval.REF_INVALID, result);
-	}
-
-	/**
-	 * Make sure that the evaluator can directly handle tAttrSum (instead of relying on re-parsing
-	 * the whole formula which converts tAttrSum to tFuncVar("SUM") )
-	 */
-	@Test
-	public void testMemFunc() {
-
-		Ptg[] ptgs = {
-			new IntPtg(42),
-			AttrPtg.SUM,
-		};
-
-		ValueEval result = evaluateFormula(ptgs);
-		assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
-	}
-
-
-	@Test
-	public void testEvaluateMultipleWorkbooks() {
-		HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
-		HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");
-
-		HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
-		HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);
-
-		// Hook up the workbook evaluators to enable evaluation of formulas across books
-		String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
-		HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
-		HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
-
-		HSSFCell cell;
-
-		HSSFSheet aSheet1 = wbA.getSheetAt(0);
-		HSSFSheet bSheet1 = wbB.getSheetAt(0);
-
-		// Simple case - single link from wbA to wbB
-		confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
-		cell = aSheet1.getRow(0).getCell(0);
-		confirmEvaluation(35, evaluatorA, cell);
-
-
-		// more complex case - back link into wbA
-		// [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
-		confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
-		// [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
-		confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
-
-		cell = aSheet1.getRow(1).getCell(0);
-		confirmEvaluation(264, evaluatorA, cell);
-
-		// change [wbB]BSheet1!B3 (from 50 to 60)
-		HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
-		cellB3.setCellValue(60);
-		evaluatorB.notifyUpdateCell(cellB3);
-		confirmEvaluation(274, evaluatorA, cell);
-
-		// change [wbA]ASheet1!A3 (from 100 to 80)
-		HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
-		cellA3.setCellValue(80);
-		evaluatorA.notifyUpdateCell(cellA3);
-		confirmEvaluation(234, evaluatorA, cell);
-
-		// change [wbA]AnotherSheet!A1 (from 2 to 3)
-		HSSFCell cellA1 = wbA.getSheetAt(1).getRow(0).getCell(0);
-		cellA1.setCellValue(3);
-		evaluatorA.notifyUpdateCell(cellA1);
-		confirmEvaluation(235, evaluatorA, cell);
-	}
-
-	private static void confirmEvaluation(double expectedValue, HSSFFormulaEvaluator fe, HSSFCell
cell) {
-		assertEquals(expectedValue, fe.evaluate(cell).getNumberValue(), 0.0);
-	}
-
-	private static void confirmFormula(HSSFWorkbook wb, int sheetIndex, int rowIndex, int columnIndex,
-			String expectedFormula) {
-		HSSFCell cell = wb.getSheetAt(sheetIndex).getRow(rowIndex).getCell(columnIndex);
-		assertEquals(expectedFormula, cell.getCellFormula());
-	}
-
-	/**
-	 * This test makes sure that any {@link MissingArgEval} that propagates to
-	 * the result of a function gets translated to {@link BlankEval}.
-	 */
-	@Test
-	public void testMissingArg() {
-		HSSFWorkbook wb = new HSSFWorkbook();
-		HSSFSheet sheet = wb.createSheet("Sheet1");
-		HSSFRow row = sheet.createRow(0);
-		HSSFCell cell = row.createCell(0);
-		cell.setCellFormula("1+IF(1,,)");
-		HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
-		CellValue cv = null;
-		try {
-			cv = fe.evaluate(cell);
-		} catch (RuntimeException e) {
-			fail("Missing arg result not being handled correctly.");
-		}
-		assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
-		// adding blank to 1.0 gives 1.0
-		assertEquals(1.0, cv.getNumberValue(), 0.0);
-
-		// check with string operand
-		cell.setCellFormula("\"abc\"&IF(1,,)");
-		fe.notifySetFormula(cell);
-		cv = fe.evaluate(cell);
-		assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
-		// adding blank to "abc" gives "abc"
-		assertEquals("abc", cv.getStringValue());
-
-		// check CHOOSE()
-		cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");
-		fe.notifySetFormula(cell);
-		cv = fe.evaluate(cell);
-		assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
-		// adding blank to "abc" gives "abc"
-		assertEquals("abc", cv.getStringValue());
-	}
-
-	/**
-	 * Functions like IF, INDIRECT, INDEX, OFFSET etc can return AreaEvals which
-	 * should be dereferenced by the evaluator
-	 * @throws IOException 
-	 */
-	@Test
-	public void testResultOutsideRange() throws IOException {
-		Workbook wb = new HSSFWorkbook();
-		try {
-    		Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
-    		cell.setCellFormula("D2:D5"); // IF(TRUE,D2:D5,D2) or  OFFSET(D2:D5,0,0) would work
too
-    		FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-    		CellValue cv;
-    		try {
-    			cv = fe.evaluate(cell);
-    		} catch (IllegalArgumentException e) {
-    			if ("Specified row index (0) is outside the allowed range (1..4)".equals(e.getMessage()))
{
-    				fail("Identified bug in result dereferencing");
-    			}
-    			throw new RuntimeException(e);
-    		}
-    		assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
-    		assertEquals(ErrorConstants.ERROR_VALUE, cv.getErrorValue());
-    
-    		// verify circular refs are still detected properly
-    		fe.clearAllCachedResultValues();
-    		cell.setCellFormula("OFFSET(A1,0,0)");
-    		cv = fe.evaluate(cell);
-    		assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
-    		assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cv.getErrorValue());
-		} finally {
-		    wb.close();
-		}
-	}
-	
-
-  /**
-   * formulas with defined names.
- * @throws IOException 
-   */
-  @Test
-  public void testNamesInFormulas() throws IOException {
-    Workbook wb = new HSSFWorkbook();
-    Sheet sheet = wb.createSheet("Sheet1");
-    
-    Name name1 = wb.createName();
-    name1.setNameName("aConstant");
-    name1.setRefersToFormula("3.14");
-
-    Name name2 = wb.createName();
-    name2.setNameName("aFormula");
-    name2.setRefersToFormula("SUM(Sheet1!$A$1:$A$3)");
-
-    Name name3 = wb.createName();
-    name3.setNameName("aSet");
-    name3.setRefersToFormula("Sheet1!$A$2:$A$4");
-
-    
-    Row row0 = sheet.createRow(0);
-    Row row1 = sheet.createRow(1);
-    Row row2 = sheet.createRow(2);
-    Row row3 = sheet.createRow(3);
-    row0.createCell(0).setCellValue(2);
-    row1.createCell(0).setCellValue(5);
-    row2.createCell(0).setCellValue(3);
-    row3.createCell(0).setCellValue(7);
-    
-    row0.createCell(2).setCellFormula("aConstant");
-    row1.createCell(2).setCellFormula("aFormula");
-    row2.createCell(2).setCellFormula("SUM(aSet)");
-    row3.createCell(2).setCellFormula("aConstant+aFormula+SUM(aSet)");
-
-    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-    assertEquals(3.14, fe.evaluate(row0.getCell(2)).getNumberValue(), EPSILON);
-    assertEquals(10.0, fe.evaluate(row1.getCell(2)).getNumberValue(), EPSILON);
-    assertEquals(15.0, fe.evaluate(row2.getCell(2)).getNumberValue(), EPSILON);
-    assertEquals(28.14, fe.evaluate(row3.getCell(2)).getNumberValue(), EPSILON);
-    
-    wb.close();
-  }
-	
+    private static ValueEval evaluateFormula(Ptg[] ptgs) {
+        OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0,
0, null);
+        return new WorkbookEvaluator(null, null, null).evaluateFormula(ec, ptgs);
+    }
+
+    /**
+     * Make sure that the evaluator can directly handle tAttrSum (instead of relying on re-parsing
+     * the whole formula which converts tAttrSum to tFuncVar("SUM") )
+     */
+    @Test
+    public void testAttrSum() {
+
+        Ptg[] ptgs = {
+            new IntPtg(42),
+            AttrPtg.SUM,
+        };
+
+        ValueEval result = evaluateFormula(ptgs);
+        assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
+    }
+
+    /**
+     * Make sure that the evaluator can directly handle (deleted) ref error tokens
+     * (instead of relying on re-parsing the whole formula which converts these
+     * to the error constant #REF! )
+     */
+    @Test
+    public void testRefErr() {
+
+        confirmRefErr(new RefErrorPtg());
+        confirmRefErr(new AreaErrPtg());
+        confirmRefErr(new DeletedRef3DPtg(0));
+        confirmRefErr(new DeletedArea3DPtg(0));
+    }
+    private static void confirmRefErr(Ptg ptg) {
+        Ptg[] ptgs = {
+            ptg,
+        };
+
+        ValueEval result = evaluateFormula(ptgs);
+        assertEquals(ErrorEval.REF_INVALID, result);
+    }
+
+    /**
+     * Make sure that the evaluator can directly handle tAttrSum (instead of relying on re-parsing
+     * the whole formula which converts tAttrSum to tFuncVar("SUM") )
+     */
+    @Test
+    public void testMemFunc() {
+
+        Ptg[] ptgs = {
+            new IntPtg(42),
+            AttrPtg.SUM,
+        };
+
+        ValueEval result = evaluateFormula(ptgs);
+        assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
+    }
+
+
+    @Test
+    public void testEvaluateMultipleWorkbooks() {
+        HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
+        HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");
+
+        HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
+        HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);
+
+        // Hook up the workbook evaluators to enable evaluation of formulas across books
+        String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
+        HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
+        HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
+
+        HSSFCell cell;
+
+        HSSFSheet aSheet1 = wbA.getSheetAt(0);
+        HSSFSheet bSheet1 = wbB.getSheetAt(0);
+
+        // Simple case - single link from wbA to wbB
+        confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
+        cell = aSheet1.getRow(0).getCell(0);
+        confirmEvaluation(35, evaluatorA, cell);
+
+
+        // more complex case - back link into wbA
+        // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
+        confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
+        // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
+        confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
+
+        cell = aSheet1.getRow(1).getCell(0);
+        confirmEvaluation(264, evaluatorA, cell);
+
+        // change [wbB]BSheet1!B3 (from 50 to 60)
+        HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
+        cellB3.setCellValue(60);
+        evaluatorB.notifyUpdateCell(cellB3);
+        confirmEvaluation(274, evaluatorA, cell);
+
+        // change [wbA]ASheet1!A3 (from 100 to 80)
+        HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
+        cellA3.setCellValue(80);
+        evaluatorA.notifyUpdateCell(cellA3);
+        confirmEvaluation(234, evaluatorA, cell);
+
+        // change [wbA]AnotherSheet!A1 (from 2 to 3)
+        HSSFCell cellA1 = wbA.getSheetAt(1).getRow(0).getCell(0);
+        cellA1.setCellValue(3);
+        evaluatorA.notifyUpdateCell(cellA1);
+        confirmEvaluation(235, evaluatorA, cell);
+    }
+
+    private static void confirmEvaluation(double expectedValue, HSSFFormulaEvaluator fe,
HSSFCell cell) {
+        assertEquals(expectedValue, fe.evaluate(cell).getNumberValue(), 0.0);
+    }
+
+    private static void confirmFormula(HSSFWorkbook wb, int sheetIndex, int rowIndex, int
columnIndex,
+            String expectedFormula) {
+        HSSFCell cell = wb.getSheetAt(sheetIndex).getRow(rowIndex).getCell(columnIndex);
+        assertEquals(expectedFormula, cell.getCellFormula());
+    }
+
+    /**
+     * This test makes sure that any {@link MissingArgEval} that propagates to
+     * the result of a function gets translated to {@link BlankEval}.
+     */
+    @Test
+    public void testMissingArg() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet("Sheet1");
+        HSSFRow row = sheet.createRow(0);
+        HSSFCell cell = row.createCell(0);
+        cell.setCellFormula("1+IF(1,,)");
+        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+        CellValue cv = null;
+        try {
+            cv = fe.evaluate(cell);
+        } catch (RuntimeException e) {
+            fail("Missing arg result not being handled correctly.");
+        }
+        assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
+        // adding blank to 1.0 gives 1.0
+        assertEquals(1.0, cv.getNumberValue(), 0.0);
+
+        // check with string operand
+        cell.setCellFormula("\"abc\"&IF(1,,)");
+        fe.notifySetFormula(cell);
+        cv = fe.evaluate(cell);
+        assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
+        // adding blank to "abc" gives "abc"
+        assertEquals("abc", cv.getStringValue());
+
+        // check CHOOSE()
+        cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");
+        fe.notifySetFormula(cell);
+        cv = fe.evaluate(cell);
+        assertEquals(HSSFCell.CELL_TYPE_STRING, cv.getCellType());
+        // adding blank to "abc" gives "abc"
+        assertEquals("abc", cv.getStringValue());
+    }
+
+    /**
+     * Functions like IF, INDIRECT, INDEX, OFFSET etc can return AreaEvals which
+     * should be dereferenced by the evaluator
+     * @throws IOException 
+     */
+    @Test
+    public void testResultOutsideRange() throws IOException {
+        Workbook wb = new HSSFWorkbook();
+        try {
+            Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
+            cell.setCellFormula("D2:D5"); // IF(TRUE,D2:D5,D2) or  OFFSET(D2:D5,0,0) would
work too
+            FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+            CellValue cv;
+            try {
+                cv = fe.evaluate(cell);
+            } catch (IllegalArgumentException e) {
+                if ("Specified row index (0) is outside the allowed range (1..4)".equals(e.getMessage()))
{
+                    fail("Identified bug in result dereferencing");
+                }
+                throw new RuntimeException(e);
+            }
+            assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
+            assertEquals(ErrorConstants.ERROR_VALUE, cv.getErrorValue());
+
+            // verify circular refs are still detected properly
+            fe.clearAllCachedResultValues();
+            cell.setCellFormula("OFFSET(A1,0,0)");
+            cv = fe.evaluate(cell);
+            assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
+            assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cv.getErrorValue());
+        } finally {
+            wb.close();
+        }
+    }
+
+
+    /**
+     * formulas with defined names.
+     * @throws IOException 
+     */
+    @Test
+    public void testNamesInFormulas() throws IOException {
+        Workbook wb = new HSSFWorkbook();
+        Sheet sheet = wb.createSheet("Sheet1");
+        
+        Name name1 = wb.createName();
+        name1.setNameName("aConstant");
+        name1.setRefersToFormula("3.14");
+
+        Name name2 = wb.createName();
+        name2.setNameName("aFormula");
+        name2.setRefersToFormula("SUM(Sheet1!$A$1:$A$3)");
+
+        Name name3 = wb.createName();
+        name3.setNameName("aSet");
+        name3.setRefersToFormula("Sheet1!$A$2:$A$4");
+
+        
+        Row row0 = sheet.createRow(0);
+        Row row1 = sheet.createRow(1);
+        Row row2 = sheet.createRow(2);
+        Row row3 = sheet.createRow(3);
+        row0.createCell(0).setCellValue(2);
+        row1.createCell(0).setCellValue(5);
+        row2.createCell(0).setCellValue(3);
+        row3.createCell(0).setCellValue(7);
+        
+        row0.createCell(2).setCellFormula("aConstant");
+        row1.createCell(2).setCellFormula("aFormula");
+        row2.createCell(2).setCellFormula("SUM(aSet)");
+        row3.createCell(2).setCellFormula("aConstant+aFormula+SUM(aSet)");
+
+        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+        assertEquals(3.14, fe.evaluate(row0.getCell(2)).getNumberValue(), EPSILON);
+        assertEquals(10.0, fe.evaluate(row1.getCell(2)).getNumberValue(), EPSILON);
+        assertEquals(15.0, fe.evaluate(row2.getCell(2)).getNumberValue(), EPSILON);
+        assertEquals(28.14, fe.evaluate(row3.getCell(2)).getNumberValue(), EPSILON);
+        
+        wb.close();
+    }
 }



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message