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

Example 31 with FormulaEvaluator

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

the class TestBugs method assertFormula.

private void assertFormula(Workbook wb, Cell intF, String expectedFormula, String expectedResultOrNull) {
    assertEquals(CellType.FORMULA, intF.getCellTypeEnum());
    if (null == expectedResultOrNull) {
        assertEquals(CellType.ERROR, intF.getCachedFormulaResultTypeEnum());
        expectedResultOrNull = "#VALUE!";
    } else {
        assertEquals(CellType.NUMERIC, intF.getCachedFormulaResultTypeEnum());
    }
    assertEquals(expectedFormula, intF.getCellFormula());
    // Check we can evaluate it correctly
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    assertEquals(expectedResultOrNull, eval.evaluate(intF).formatAsString());
}

Example 32 with FormulaEvaluator

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

the class TestBugs method test48043.

@Test
public void test48043() throws IOException {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("56325a.xls");
    wb.removeSheetAt(2);
    wb.removeSheetAt(1);
    //Sheet s = wb.createSheet("sheetname");
    Sheet s = wb.getSheetAt(0);
    Row row = s.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellFormula("IF(AND(ISBLANK(A10)," + "ISBLANK(B10)),\"\"," + "CONCATENATE(A10,\"-\",B10))");
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();
    /*OutputStream out = new FileOutputStream("C:\\temp\\48043.xls");
        try {
          wb.write(out);
        } finally {
          out.close();
        }*/
    Workbook wbBack = HSSFTestDataSamples.writeOutAndReadBack(wb);
    assertNotNull(wbBack);
    wbBack.close();
    wb.close();
}

Example 33 with FormulaEvaluator

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

the class BaseFormulaEvaluator method evaluateAllFormulaCells.

/**
     * Loops over all cells in all sheets of the supplied
     *  workbook.
     * For cells that contain formulas, their formulas are
     *  evaluated, and the results are saved. These cells
     *  remain as formula cells.
     * For cells that do not contain formulas, no changes
     *  are made.
     * This is a helpful wrapper around looping over all
     *  cells, and calling evaluateFormulaCell on each one.
     */
public static void evaluateAllFormulaCells(Workbook wb) {
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    evaluateAllFormulaCells(wb, evaluator);
}

Example 34 with FormulaEvaluator

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

the class CollaboratingWorkbooksEnvironment method setupFormulaEvaluator.

public static void setupFormulaEvaluator(Map<String, FormulaEvaluator> evaluators) {
    Map<String, WorkbookEvaluator> evaluatorsByName = new HashMap<String, WorkbookEvaluator>(evaluators.size());
    for (Map.Entry<String, FormulaEvaluator> swb : evaluators.entrySet()) {
        String wbName = swb.getKey();
        FormulaEvaluator eval = swb.getValue();
        if (eval instanceof WorkbookEvaluatorProvider) {
            evaluatorsByName.put(wbName, ((WorkbookEvaluatorProvider) eval)._getWorkbookEvaluator());
        } else {
            throw new IllegalArgumentException("Formula Evaluator " + eval + " provides no WorkbookEvaluator access");
        }
    }
    setup(evaluatorsByName);
}

Example 35 with FormulaEvaluator

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

the class TestXSSFFormulaParser method test58648FormulaParsing.

@Test
public void test58648FormulaParsing() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58648.xlsx");
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet xsheet = wb.getSheetAt(i);
        for (Row row : xsheet) {
            for (Cell cell : row) {
                if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    try {
                        evaluator.evaluateFormulaCellEnum(cell);
                    } catch (Exception e) {
                        CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
                        throw new RuntimeException("error at: " + cellRef, e);
                    }
                }
            }
        }
    }
    Sheet sheet = wb.getSheet("my-sheet");
    Cell cell = sheet.getRow(1).getCell(4);
    assertEquals(5d, cell.getNumericCellValue(), 0d);
    wb.close();
}