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

Example 41 with FormulaEvaluator

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

the class TestSXSSFFormulaEvaluation method testEvaluateAllInWindow.

/**
     * If all formula cells + their references are inside the window,
     *  then evaluation works
     * @throws IOException 
     */
@Test
public void testEvaluateAllInWindow() throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook(5);
    SXSSFSheet s = wb.createSheet();
    s.createRow(0).createCell(0).setCellFormula("1+2");
    s.createRow(1).createCell(1).setCellFormula("A1+10");
    s.createRow(2).createCell(2).setCellFormula("B2+100");
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();
    assertEquals(3, (int) s.getRow(0).getCell(0).getNumericCellValue());
    assertEquals(13, (int) s.getRow(1).getCell(1).getNumericCellValue());
    assertEquals(113, (int) s.getRow(2).getCell(2).getNumericCellValue());
    wb.close();
}

Example 42 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 43 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 44 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 45 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);
}