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

Example 51 with FormulaEvaluator

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

the class TestWorkbookEvaluator method testIFEqualsFormulaEvaluation_evaluate.

private void testIFEqualsFormulaEvaluation_evaluate(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();
    CellValue result = eval.evaluate(D1);
    // Call should not modify the contents
    assertEquals(CellType.FORMULA, D1.getCellTypeEnum());
    assertEquals(expectedFormula, D1.getCellFormula());
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(expectedResult, result.getNumberValue(), EPSILON);
    testIFEqualsFormulaEvaluation_teardown(wb);
}

Example 52 with FormulaEvaluator

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

the class TestWorkbookEvaluator method testNamesInFormulas.

/**
     * formulas with defined names.
     */
@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();
}

Example 53 with FormulaEvaluator

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

the class BaseTestExternalFunctions method baseTestInvokeATP.

/**
     * test invoking saved ATP functions
     *
     * @param testFile  either atp.xls or atp.xlsx
     */
@Test
public void baseTestInvokeATP() throws IOException {
    Workbook wb = _testDataProvider.openSampleWorkbook(atpFile);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sh = wb.getSheetAt(0);
    // these two are not implemented 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(CellType.BOOLEAN, evaluator.evaluateFormulaCellEnum(cell2));
    Cell cell3 = sh.getRow(3).getCell(1);
    assertEquals("ISEVEN(2)", cell3.getCellFormula());
    assertEquals(true, evaluator.evaluate(cell3).getBooleanValue());
    assertEquals(CellType.BOOLEAN, evaluator.evaluateFormulaCellEnum(cell3));
    wb.close();
}

Example 54 with FormulaEvaluator

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

the class TestMissingWorkbook method testMissingWorkbookMissingOverride.

public void testMissingWorkbookMissingOverride() throws IOException {
    Sheet lSheet = mainWorkbook.getSheetAt(0);
    Cell lA1Cell = lSheet.getRow(0).getCell(0);
    Cell lB1Cell = lSheet.getRow(1).getCell(0);
    Cell lC1Cell = lSheet.getRow(2).getCell(0);
    assertEquals(CellType.FORMULA, lA1Cell.getCellTypeEnum());
    assertEquals(CellType.FORMULA, lB1Cell.getCellTypeEnum());
    assertEquals(CellType.FORMULA, lC1Cell.getCellTypeEnum());
    // Check cached values
    assertEquals(10.0d, lA1Cell.getNumericCellValue(), 0.00001d);
    assertEquals("POI rocks!", lB1Cell.getStringCellValue());
    assertEquals(true, lC1Cell.getBooleanCellValue());
    // Evaluate
    FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator();
    evaluator.setIgnoreMissingWorkbooks(true);
    assertEquals(CellType.NUMERIC, evaluator.evaluateFormulaCellEnum(lA1Cell));
    assertEquals(CellType.STRING, evaluator.evaluateFormulaCellEnum(lB1Cell));
    assertEquals(CellType.BOOLEAN, evaluator.evaluateFormulaCellEnum(lC1Cell));
    assertEquals(10.0d, lA1Cell.getNumericCellValue(), 0.00001d);
    assertEquals("POI rocks!", lB1Cell.getStringCellValue());
    assertEquals(true, lC1Cell.getBooleanCellValue());
}

Example 55 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project chilo-producer by cccties.

the class ExcelReader method getStringFormulaValue.

public String getStringFormulaValue(Cell cell) {
    assert cell.getCellType() == Cell.CELL_TYPE_FORMULA;
    CreationHelper helper = workBook.getCreationHelper();
    FormulaEvaluator evaluator = helper.createFormulaEvaluator();
    CellValue value = evaluator.evaluate(cell);
    switch(value.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return value.getStringValue();
        case Cell.CELL_TYPE_NUMERIC:
            return Double.toString(value.getNumberValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(value.getBooleanValue());
        default:
            System.out.println(value.getCellType());
            return null;
    }
}