Package org.apache.poi.ss.usermodel

Examples of org.apache.poi.ss.usermodel.FormulaEvaluator

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
    assertNotNull(mainWorkbook);
    assertNotNull(sourceWorkbook);
  }

  public void testMissingWorkbookMissing() throws IOException {
    FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator();
   
    HSSFSheet lSheet = mainWorkbook.getSheetAt(0);
    HSSFRow lARow = lSheet.getRow(0);
    HSSFCell lA1Cell = lARow.getCell(0);
   
    assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType());
    try {
      evaluator.evaluateFormulaCell(lA1Cell);
      fail("Missing external workbook reference exception expected!");
    }catch(RuntimeException re) {
      assertTrue("Unexpected exception: " + re, re.getMessage().indexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1);
    }
  }
View Full Code Here
106
107
108
109
110
111
112
113
114
115
116
        Cell cell3 = sh.createRow(2).createCell(0);
        cell3.setCellFormula("MYFUNC2(\"C1\")&\"-\"&A2");  //where A2 is defined above
        assertEquals("MYFUNC2(\"C1\")&\"-\"&A2", cell3.getCellFormula());

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        assertEquals(2.0, evaluator.evaluate(cell1).getNumberValue());
        assertEquals("B1abc", evaluator.evaluate(cell2).getStringValue());
        assertEquals("C1abc2-B1abc", evaluator.evaluate(cell3).getStringValue());

    }
View Full Code Here
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
     *
     * @param testFile  either atp.xls or atp.xlsx
     */
    public void baseTestInvokeATP(String testFile){
        Workbook wb = _testDataProvider.openSampleWorkbook(testFile);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh  = wb.getSheetAt(0);
        // these two are not imlemented in r
        assertEquals("DELTA(1.3,1.5)", sh.getRow(0).getCell(1).getCellFormula());
        assertEquals("COMPLEX(2,4)", sh.getRow(1).getCell(1).getCellFormula());

        Cell cell2 = sh.getRow(2).getCell(1);
        assertEquals("ISODD(2)", cell2.getCellFormula());
        assertEquals(false, evaluator.evaluate(cell2).getBooleanValue());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, evaluator.evaluateFormulaCell(cell2));

        Cell cell3 = sh.getRow(3).getCell(1);
        assertEquals("ISEVEN(2)", cell3.getCellFormula());
        assertEquals(true, evaluator.evaluate(cell3).getBooleanValue());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, evaluator.evaluateFormulaCell(cell3));

    }
View Full Code Here
84
85
86
87
88
89
90
91
92
93
94
 
  public void testGetEvaluator() {
    fixture = new ExcelAntWorkbookUtilTestHelper(
                mortgageCalculatorFileName ) ;
   
    FormulaEvaluator evaluator = fixture.getEvaluator(
                                          mortgageCalculatorFileName ) ;
   
    assertNotNull( evaluator ) ;
   
   
View Full Code Here
84
85
86
87
88
89
90
91
92
93
94
 
  public void testGetEvaluator() {
    fixture = new ExcelAntWorkbookUtilTestHelper(
                mortgageCalculatorFileName ) ;
   
    FormulaEvaluator evaluator = fixture.getEvaluator(
                                          mortgageCalculatorFileName ) ;
   
    assertNotNull( evaluator ) ;
   
   
View Full Code Here
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
          Cell cfn = r.createCell(2, Cell.CELL_TYPE_FORMULA);
          cfn.setCellFormula("A1"); 
          Cell cfs = r.createCell(3, Cell.CELL_TYPE_FORMULA);
          cfs.setCellFormula("B1");
         
          FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
          assertEquals(Cell.CELL_TYPE_NUMERIC, fe.evaluate(cfn).getCellType());
          assertEquals(Cell.CELL_TYPE_STRING, fe.evaluate(cfs).getCellType());
          fe.evaluateFormulaCell(cfn);
          fe.evaluateFormulaCell(cfs);
         
          // Now test
          assertEquals(Cell.CELL_TYPE_NUMERIC, cn.getCellType());
          assertEquals(Cell.CELL_TYPE_STRING, cs.getCellType());
          assertEquals(Cell.CELL_TYPE_FORMULA, cfn.getCellType());
View Full Code Here
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
    }

    public void test49783() throws Exception {
        Workbook wb =  XSSFTestDataSamples.openSampleWorkbook("49783.xlsx");
        Sheet sheet = wb.getSheetAt(0);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Cell cell;

        cell = sheet.getRow(0).getCell(0);
        assertEquals("#REF!*#REF!", cell.getCellFormula());
        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());

        Name nm1 = wb.getName("sale_1");
        assertNotNull("name sale_1 should be present", nm1);
        assertEquals("Sheet1!#REF!", nm1.getRefersToFormula());
        Name nm2 = wb.getName("sale_2");
        assertNotNull("name sale_2 should be present", nm2);
        assertEquals("Sheet1!#REF!", nm2.getRefersToFormula());

        cell = sheet.getRow(1).getCell(0);
        assertEquals("sale_1*sale_2", cell.getCellFormula());
        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());
    }
View Full Code Here
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
   */
  public void testResultOutsideRange() {
    Workbook wb = new HSSFWorkbook();
    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())) {
        throw new AssertionFailedError("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());
  }
View Full Code Here
272
273
274
275
276
277
278
279
280
281
282
    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());
    assertEquals(10.0, fe.evaluate(row1.getCell(2)).getNumberValue());
    assertEquals(15.0, fe.evaluate(row2.getCell(2)).getNumberValue());
    assertEquals(28.14, fe.evaluate(row3.getCell(2)).getNumberValue());
  }
View Full Code Here
197
198
199
200
201
202
203
204
205
206
207
   * @param testFocusFunctionName name of a single function/operator to test alone.
   * Typically pass <code>null</code> to test all functions
   */
  private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
    FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);

    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);
View Full Code Here
TOP

Related Classes of org.apache.poi.ss.usermodel.FormulaEvaluator

  • com.asakusafw.testdriver.excel.ExcelSheetDataModelSource
  • loxia.support.excel.impl.DefaultExcelReader
  • loxia.support.excel.impl.DefaultExcelWriter
  • org.apache.metamodel.excel.ExcelUtils
  • org.apache.poi.hssf.record.formula.Area3DPtg
  • org.apache.poi.hssf.record.formula.AreaPtg
  • org.apache.poi.hssf.record.formula.eval.Area2DEval
  • org.apache.poi.hssf.record.formula.eval.Area3DEval
  • org.apache.poi.hssf.record.formula.eval.AreaEval
  • org.apache.poi.hssf.record.formula.eval.BoolEval
    • Copyright © 2018 www.massapicom. All rights reserved.
    All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.