Examples with FormulaEvaluator org.apache.poi.ss.usermodel.FormulaEvaluator used on opensource projects

Example 16 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestWorkbookEvaluator method testResultOutsideRange.

/**
     * Functions like IF, INDIRECT, INDEX, OFFSET etc can return AreaEvals which
     * should be dereferenced by the evaluator
     */
@Test
public void testResultOutsideRange() throws IOException {
    Workbook wb = new HSSFWorkbook();
    try {
        Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
        // IF(TRUE,D2:D5,D2) or  OFFSET(D2:D5,0,0) would work too
        cell.setCellFormula("D2:D5");
        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(CellType.ERROR, cv.getCellTypeEnum());
        assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), cv.getErrorValue());
        // verify circular refs are still detected properly
        fe.clearAllCachedResultValues();
        cell.setCellFormula("OFFSET(A1,0,0)");
        cv = fe.evaluate(cell);
        assertEquals(CellType.ERROR, cv.getCellTypeEnum());
        assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cv.getErrorValue());
    } finally {
        wb.close();
    }
}

Example 17 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestIfError method testEvaluate.

/**
     * =IFERROR(210/35,\"Error in calculation\")"  Divides 210 by 35 and returns 6.0
     * =IFERROR(55/0,\"Error in calculation\")"    Divides 55 by 0 and returns the error text
     * =IFERROR(C1,\"Error in calculation\")"      References the result of dividing 55 by 0 and returns the error text
     */
public static void testEvaluate() {
    Workbook wb = new HSSFWorkbook();
    Sheet sh = wb.createSheet();
    Row row1 = sh.createRow(0);
    Row row2 = sh.createRow(1);
    // Create cells
    row1.createCell(0, CellType.NUMERIC);
    row1.createCell(1, CellType.NUMERIC);
    row1.createCell(2, CellType.NUMERIC);
    row2.createCell(0, CellType.NUMERIC);
    row2.createCell(1, CellType.NUMERIC);
    // Create references
    CellReference a1 = new CellReference("A1");
    CellReference a2 = new CellReference("A2");
    CellReference b1 = new CellReference("B1");
    CellReference b2 = new CellReference("B2");
    CellReference c1 = new CellReference("C1");
    // Set values
    sh.getRow(a1.getRow()).getCell(a1.getCol()).setCellValue(210);
    sh.getRow(a2.getRow()).getCell(a2.getCol()).setCellValue(55);
    sh.getRow(b1.getRow()).getCell(b1.getCol()).setCellValue(35);
    sh.getRow(b2.getRow()).getCell(b2.getCol()).setCellValue(0);
    sh.getRow(c1.getRow()).getCell(c1.getCol()).setCellFormula("A1/B2");
    Cell cell1 = sh.createRow(3).createCell(0);
    cell1.setCellFormula("IFERROR(A1/B1,\"Error in calculation\")");
    Cell cell2 = sh.createRow(3).createCell(0);
    cell2.setCellFormula("IFERROR(A2/B2,\"Error in calculation\")");
    Cell cell3 = sh.createRow(3).createCell(0);
    cell3.setCellFormula("IFERROR(C1,\"error\")");
    double accuracy = 1E-9;
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    assertEquals("Checks that the cell is numeric", CellType.NUMERIC, evaluator.evaluate(cell1).getCellTypeEnum());
    assertEquals("Divides 210 by 35 and returns 6.0", 6.0, evaluator.evaluate(cell1).getNumberValue(), accuracy);
    assertEquals("Checks that the cell is numeric", CellType.STRING, evaluator.evaluate(cell2).getCellTypeEnum());
    assertEquals("Rounds -10 to a nearest multiple of -3 (-9)", "Error in calculation", evaluator.evaluate(cell2).getStringValue());
    assertEquals("Check that C1 returns string", CellType.STRING, evaluator.evaluate(cell3).getCellTypeEnum());
    assertEquals("Check that C1 returns string \"error\"", "error", evaluator.evaluate(cell3).getStringValue());
}

Example 18 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestWorkbookEvaluator method testIFEqualsFormulaEvaluation_evaluateAll.

private void testIFEqualsFormulaEvaluation_evaluateAll(String formula, CellType cellType, String expectedFormula, double expectedResult) {
    Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
    Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();
    // Call should modify the contents
    assertEquals(CellType.FORMULA, D1.getCellTypeEnum());
    assertEquals(expectedFormula, D1.getCellFormula());
    assertEquals(CellType.NUMERIC, D1.getCachedFormulaResultTypeEnum());
    assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
    testIFEqualsFormulaEvaluation_teardown(wb);
}

Example 19 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestWorkbookEvaluator method testIFEqualsFormulaEvaluation_evaluateFormulaCell.

private void testIFEqualsFormulaEvaluation_evaluateFormulaCell(String formula, CellType cellType, String expectedFormula, double expectedResult) {
    Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
    Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    CellType resultCellType = eval.evaluateFormulaCellEnum(D1);
    // Call should modify the contents, but leave the formula intact
    assertEquals(CellType.FORMULA, D1.getCellTypeEnum());
    assertEquals(expectedFormula, D1.getCellFormula());
    assertEquals(CellType.NUMERIC, resultCellType);
    assertEquals(CellType.NUMERIC, D1.getCachedFormulaResultTypeEnum());
    assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
    testIFEqualsFormulaEvaluation_teardown(wb);
}

Example 20 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class BaseTestCircularReferences method testIntermediateCircularReferenceResults_bug46898.

@Test
public void testIntermediateCircularReferenceResults_bug46898() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);
    Cell cellA1 = row.createCell(0);
    Cell cellB1 = row.createCell(1);
    Cell cellC1 = row.createCell(2);
    Cell cellD1 = row.createCell(3);
    Cell cellE1 = row.createCell(4);
    cellA1.setCellFormula("IF(FALSE, 1+B1, 42)");
    cellB1.setCellFormula("1+C1");
    cellC1.setCellFormula("1+D1");
    cellD1.setCellFormula("1+E1");
    cellE1.setCellFormula("1+A1");
    FormulaEvaluator fe = _testDataProvider.createFormulaEvaluator(wb);
    CellValue cv;
    // Happy day flow - evaluate A1 first
    cv = fe.evaluate(cellA1);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(42.0, cv.getNumberValue(), 0.0);
    // no circ-ref-error because A1 result is cached
    cv = fe.evaluate(cellB1);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(46.0, cv.getNumberValue(), 0.0);
    // Show the bug - evaluate another cell from the loop first
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellB1);
    // Identified bug 46898
    assertNotEquals(cv.getCellTypeEnum(), ErrorEval.CIRCULAR_REF_ERROR.getErrorCode());
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(46.0, cv.getNumberValue(), 0.0);
    // start evaluation on another cell
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellE1);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(43.0, cv.getNumberValue(), 0.0);
    wb.close();
}