Examples with WorkbookEvaluator org.apache.poi.ss.formula.WorkbookEvaluator used on opensource projects

Example 1 with WorkbookEvaluator

use of org.apache.poi.ss.formula.WorkbookEvaluator in project poi by apache.

the class TestHSSFFormulaEvaluator method testShortCircuitIfEvaluation.

/**
	 * The HSSFFormula evaluator performance benefits greatly from caching of intermediate cell values
	 */
@Test
public void testShortCircuitIfEvaluation() throws IOException {
    // Set up a simple IF() formula that has measurable evaluation cost for its operands.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    cellA1.setCellFormula("if(B1,C1,D1+E1+F1)");
    // EvaluationListener to check which parts of the first formula get evaluated
    for (int i = 1; i < 6; i++) {
        // formulas are just literal constants "1".."5"
        row.createCell(i).setCellFormula(String.valueOf(i));
    }
    EvalCountListener evalListener = new EvalCountListener();
    WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.createEvaluator(wb, evalListener);
    ValueEval ve = evaluator.evaluate(HSSFEvaluationTestHelper.wrapCell(cellA1));
    int evalCount = evalListener.getEvalCount();
    if (evalCount == 6) {
        // Without short-circuit-if evaluation, evaluating cell 'A1' takes 3 extra evaluations (for D1,E1,F1)
        fail("Identifed bug 48195 - Formula evaluator should short-circuit IF() calculations.");
    }
    assertEquals(3, evalCount);
    assertEquals(2.0, ((NumberEval) ve).getNumberValue(), 0D);
    wb.close();
}

Example 2 with WorkbookEvaluator

use of org.apache.poi.ss.formula.WorkbookEvaluator in project poi by apache.

the class TestDec2Bin method createContext.

private OperationEvaluationContext createContext() {
    HSSFWorkbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("13.43");
    cell = row.createCell(1);
    cell.setCellValue("8");
    cell = row.createCell(2);
    cell.setCellValue("-8");
    cell = row.createCell(3);
    cell.setCellValue("1");
    HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.create(wb);
    WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier() {

        @Override
        public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
            return true;
        }
    }, null);
    OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null);
    return ctx;
}

Example 3 with WorkbookEvaluator

use of org.apache.poi.ss.formula.WorkbookEvaluator in project poi by apache.

the class TestBin2Dec method createContext.

private OperationEvaluationContext createContext() {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet();
    HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.create(wb);
    WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier() {

        @Override
        public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
            return true;
        }
    }, null);
    OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null);
    return ctx;
}

Example 4 with WorkbookEvaluator

use of org.apache.poi.ss.formula.WorkbookEvaluator in project poi by apache.

the class TestHex2Dec method createContext.

private OperationEvaluationContext createContext() {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet();
    HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.create(wb);
    WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier() {

        @Override
        public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
            return true;
        }
    }, null);
    OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null);
    return ctx;
}

Example 5 with WorkbookEvaluator

use of org.apache.poi.ss.formula.WorkbookEvaluator in project poi by apache.

the class TestXSSFBugs method test57196_WorkbookEvaluator.

@Test
public void test57196_WorkbookEvaluator() throws IOException {
    String previousLogger = System.getProperty("org.apache.poi.util.POILogger");
    //System.setProperty("poi.log.level", "3");
    try {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Sheet1");
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("0");
        cell = row.createCell(1);
        cell.setCellValue(0);
        cell = row.createCell(2);
        cell.setCellValue(0);
        // simple formula worked
        cell.setCellFormula("DEC2HEX(O2+D2)");
        WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null);
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        // this already failed! Hex2Dec did not correctly handle RefEval
        cell.setCellFormula("HEX2DEC(O8)");
        workbookEvaluator.clearAllCachedResultValues();
        workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null);
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        // slightly more complex one failed
        cell.setCellFormula("HEX2DEC(O8)-O2+D2");
        workbookEvaluator.clearAllCachedResultValues();
        workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null);
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        // more complicated failed
        cell.setCellFormula("DEC2HEX(HEX2DEC(O8)-O2+D2)");
        workbookEvaluator.clearAllCachedResultValues();
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        // what other similar functions
        cell.setCellFormula("DEC2BIN(O8)-O2+D2");
        workbookEvaluator.clearAllCachedResultValues();
        workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null);
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        // what other similar functions
        cell.setCellFormula("DEC2BIN(A1)");
        workbookEvaluator.clearAllCachedResultValues();
        workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null);
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        // what other similar functions
        cell.setCellFormula("BIN2DEC(B1)");
        workbookEvaluator.clearAllCachedResultValues();
        workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(wb), null, null);
        workbookEvaluator.setDebugEvaluationOutputForNextEval(true);
        workbookEvaluator.evaluate(new XSSFEvaluationCell(cell));
        wb.close();
    } finally {
        if (previousLogger == null) {
            System.clearProperty("org.apache.poi.util.POILogger");
        } else {
            System.setProperty("org.apache.poi.util.POILogger", previousLogger);
        }
        System.clearProperty("poi.log.level");
    }
}