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

Example 1 with FormulaEvaluator

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

the class UserDefinedFunctionExample method main.

public static void main(String[] args) throws Exception {
    if (args.length != 2) {
        // e.g. src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls Sheet1!B4
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;
    }
    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);
    File workbookFile = new File(args[0]);
    Workbook workbook = WorkbookFactory.create(workbookFile, null, true);
    try {
        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };
        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);
        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);
        CellValue value = evaluator.evaluate(cell);
        System.out.println("returns value: " + value);
    } finally {
        workbook.close();
    }
}

Example 2 with FormulaEvaluator

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

the class ExcelAntWorkbookUtil method evaluateCell.

/**
     * Uses a String in standard Excel format (SheetName!CellId) to locate a
     * cell and evaluate it.
     *
     * @param cellName
     * @param expectedValue
     * @param precision
     */
public ExcelAntEvaluationResult evaluateCell(String cellName, double expectedValue, double precision) {
    ExcelAntEvaluationResult evalResults = null;
    Cell cell = getCell(cellName);
    FormulaEvaluator evaluator = getEvaluator(excelFileName);
    CellValue resultOfEval = evaluator.evaluate(cell);
    if (resultOfEval.getErrorValue() == 0) {
        // the evaluation did not encounter errors
        double result = resultOfEval.getNumberValue();
        double delta = Math.abs(result - expectedValue);
        if (delta > precision) {
            evalResults = new ExcelAntEvaluationResult(false, false, resultOfEval.getNumberValue(), "Results was out of range based on precision " + " of " + precision + ".  Delta was actually " + delta, delta, cellName);
        } else {
            evalResults = new ExcelAntEvaluationResult(false, true, resultOfEval.getNumberValue(), "Evaluation passed without error within in range.", delta, cellName);
        }
    } else {
        String errorMeaning = null;
        try {
            errorMeaning = FormulaError.forInt(resultOfEval.getErrorValue()).getString();
        } catch (IllegalArgumentException iae) {
            errorMeaning = "unknown error code: " + Byte.toString(resultOfEval.getErrorValue());
        }
        evalResults = new ExcelAntEvaluationResult(true, false, resultOfEval.getNumberValue(), "Evaluation failed due to an evaluation error of " + resultOfEval.getErrorValue() + " which is " + errorMeaning, 0, cellName);
    }
    return evalResults;
}

Example 3 with FormulaEvaluator

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

the class TestSumifsXSSF method testBug60858.

/**
     * handle null cell predicate
     */
@Test
public void testBug60858() {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("bug60858.xlsx");
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sheet = wb.getSheetAt(0);
    Cell cell = sheet.getRow(1).getCell(5);
    fe.evaluate(cell);
    assertEquals(0.0, cell.getNumericCellValue(), 0.0000000000000001);
}

Example 4 with FormulaEvaluator

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

the class CountifsTests method testCallFunction.

/**
     * Basic call
     */
@Test
public void testCallFunction() {
    Sheet sheet = workbook.createSheet("test");
    Row row1 = sheet.createRow(0);
    Cell cellA1 = row1.createCell(0, CellType.FORMULA);
    Cell cellB1 = row1.createCell(1, CellType.NUMERIC);
    Cell cellC1 = row1.createCell(2, CellType.NUMERIC);
    Cell cellD1 = row1.createCell(3, CellType.NUMERIC);
    Cell cellE1 = row1.createCell(4, CellType.NUMERIC);
    cellB1.setCellValue(1);
    cellC1.setCellValue(1);
    cellD1.setCellValue(2);
    cellE1.setCellValue(4);
    cellA1.setCellFormula("COUNTIFS(B1:C1,1, D1:E1,2)");
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue evaluate = evaluator.evaluate(cellA1);
    assertEquals(1.0d, evaluate.getNumberValue(), 0.000000000000001);
}

Example 5 with FormulaEvaluator

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

the class TestSXSSFFormulaEvaluation method testEvaluateSimple.

@Test
public void testEvaluateSimple() throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook(5);
    SXSSFSheet s = wb.createSheet();
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    SXSSFCell c = s.createRow(0).createCell(0);
    c.setCellFormula("1+2");
    assertEquals(0, (int) c.getNumericCellValue());
    eval.evaluateFormulaCellEnum(c);
    assertEquals(3, (int) c.getNumericCellValue());
    c = s.createRow(1).createCell(0);
    c.setCellFormula("CONCATENATE(\"hello\",\" \",\"world\")");
    eval.evaluateFormulaCellEnum(c);
    assertEquals("hello world", c.getStringCellValue());
    wb.close();
}