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

Example 11 with FormulaEvaluator

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

the class TestXSSFFormulaEvaluation method test59736.

@Test
public void test59736() {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("59736.xlsx");
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    Cell cell = wb.getSheetAt(0).getRow(0).getCell(0);
    assertEquals(1, cell.getNumericCellValue(), 0.001);
    cell = wb.getSheetAt(0).getRow(1).getCell(0);
    CellValue value = evaluator.evaluate(cell);
    assertEquals(1, value.getNumberValue(), 0.001);
    cell = wb.getSheetAt(0).getRow(2).getCell(0);
    value = evaluator.evaluate(cell);
    assertEquals(1, value.getNumberValue(), 0.001);
}

Example 12 with FormulaEvaluator

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

the class TestBugs method bug56737.

/**
     * Formulas which reference named ranges, either in other
     *  sheets, or workbook scoped but in other workbooks.
     * Used to fail with
     * java.lang.RuntimeException: Unexpected eval class (org.apache.poi.ss.formula.eval.NameXEval)
     */
@Test
public void bug56737() throws IOException {
    Workbook wb = openSample("56737.xls");
    // Check the named range definitions
    Name nSheetScope = wb.getName("NR_To_A1");
    Name nWBScope = wb.getName("NR_Global_B2");
    assertNotNull(nSheetScope);
    assertNotNull(nWBScope);
    assertEquals("Defines!$A$1", nSheetScope.getRefersToFormula());
    assertEquals("Defines!$B$2", nWBScope.getRefersToFormula());
    // Check the different kinds of formulas
    Sheet s = wb.getSheetAt(0);
    Cell cRefSName = s.getRow(1).getCell(3);
    Cell cRefWName = s.getRow(2).getCell(3);
    assertEquals("Defines!NR_To_A1", cRefSName.getCellFormula());
    // TODO Correct this, so that the filename is shown too, see bug #56742
    // This is what Excel itself shows
    //assertEquals("'56737.xls'!NR_Global_B2", cRefWName.getCellFormula());
    // TODO This isn't right, but it's what we currently generate....
    assertEquals("NR_Global_B2", cRefWName.getCellFormula());
    // Try to evaluate them
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    assertEquals("Test A1", eval.evaluate(cRefSName).getStringValue());
    assertEquals(142, (int) eval.evaluate(cRefWName).getNumberValue());
    // Try to evaluate everything
    eval.evaluateAll();
    wb.close();
}

Example 13 with FormulaEvaluator

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

the class BaseTestExternalFunctions method testExternalFunctions.

@Test
public void testExternalFunctions() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sh = wb.createSheet();
    Cell cell1 = sh.createRow(0).createCell(0);
    // functions from the Excel Analysis Toolpack
    cell1.setCellFormula("ISODD(1)+ISEVEN(2)");
    assertEquals("ISODD(1)+ISEVEN(2)", cell1.getCellFormula());
    Cell cell2 = sh.createRow(1).createCell(0);
    // unregistered functions are parseable and renderable, but may not be evaluateable
    cell2.setCellFormula("MYFUNC(\"B1\")");
    try {
        evaluator.evaluate(cell2);
        fail("Expected NotImplementedFunctionException/NotImplementedException");
    } catch (final NotImplementedException e) {
        assertTrue(e.getCause() instanceof NotImplementedFunctionException);
    // Alternatively, a future implementation of evaluate could return #NAME? error to align behavior with Excel
    // assertEquals(ErrorEval.NAME_INVALID, ErrorEval.valueOf(evaluator.evaluate(cell2).getErrorValue()));
    }
    wb.addToolPack(customToolpack);
    cell2.setCellFormula("MYFUNC(\"B1\")");
    assertEquals("MYFUNC(\"B1\")", cell2.getCellFormula());
    Cell cell3 = sh.createRow(2).createCell(0);
    //where A2 is defined above
    cell3.setCellFormula("MYFUNC2(\"C1\")&\"-\"&A2");
    assertEquals("MYFUNC2(\"C1\")&\"-\"&A2", cell3.getCellFormula());
    assertEquals(2.0, evaluator.evaluate(cell1).getNumberValue(), 0);
    assertEquals("B1abc", evaluator.evaluate(cell2).getStringValue());
    assertEquals("C1abc2-B1abc", evaluator.evaluate(cell3).getStringValue());
    wb.close();
}

Example 14 with FormulaEvaluator

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

the class TestMissingWorkbook method testExistingWorkbook.

public void testExistingWorkbook() 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());
    FormulaEvaluator lMainWorkbookEvaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator();
    FormulaEvaluator lSourceEvaluator = sourceWorkbook.getCreationHelper().createFormulaEvaluator();
    Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
    workbooks.put(MAIN_WORKBOOK_FILENAME, lMainWorkbookEvaluator);
    workbooks.put(SOURCE_DUMMY_WORKBOOK_FILENAME, lSourceEvaluator);
    lMainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
    assertEquals(CellType.NUMERIC, lMainWorkbookEvaluator.evaluateFormulaCellEnum(lA1Cell));
    assertEquals(CellType.STRING, lMainWorkbookEvaluator.evaluateFormulaCellEnum(lB1Cell));
    assertEquals(CellType.BOOLEAN, lMainWorkbookEvaluator.evaluateFormulaCellEnum(lC1Cell));
    assertEquals(20.0d, lA1Cell.getNumericCellValue(), 0.00001d);
    assertEquals("Apache rocks!", lB1Cell.getStringCellValue());
    assertEquals(false, lC1Cell.getBooleanCellValue());
}

Example 15 with FormulaEvaluator

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

the class TestMissingWorkbook method testMissingWorkbookMissing.

public void testMissingWorkbookMissing() throws IOException {
    FormulaEvaluator evaluator = mainWorkbook.getCreationHelper().createFormulaEvaluator();
    Sheet lSheet = mainWorkbook.getSheetAt(0);
    Row lARow = lSheet.getRow(0);
    Cell lA1Cell = lARow.getCell(0);
    assertEquals(CellType.FORMULA, lA1Cell.getCellTypeEnum());
    try {
        evaluator.evaluateFormulaCellEnum(lA1Cell);
        fail("Missing external workbook reference exception expected!");
    } catch (RuntimeException re) {
        assertTrue("Unexpected exception: " + re, re.getMessage().indexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1);
    }
}