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

Example 6 with WorkbookEvaluator

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

the class TestFormulaEvaluatorBugs method testSlowEvaluate45376.

/**
     * The HSSFFormula evaluator performance benefits greatly from caching of intermediate cell values
     */
@Test
public void testSlowEvaluate45376() throws Exception {
    /*
         * Note - to observe behaviour without caching, disable the call to
         * updateValue() from FormulaCellCacheEntry.updateFormulaResult().
         */
    // Firstly set up a sequence of formula cells where each depends on the  previous multiple
    // times.  Without caching, each subsequent cell take about 4 times longer to evaluate.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    for (int i = 1; i < 10; i++) {
        HSSFCell cell = row.createCell(i);
        char prevCol = (char) ('A' + i - 1);
        String prevCell = prevCol + "1";
        // this formula is inspired by the offending formula of the attachment for bug 45376
        // IF(DATE(YEAR(A1),MONTH(A1)+1,1)<=$D$3,DATE(YEAR(A1),MONTH(A1)+1,1),NA()) etc
        String formula = "IF(DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1)<=$D$3," + "DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1),NA())";
        cell.setCellFormula(formula);
    }
    Calendar cal = LocaleUtil.getLocaleCalendar(2000, 0, 1, 0, 0, 0);
    row.createCell(0).setCellValue(cal);
    // Choose cell A9 instead of A10, so that the failing test case doesn't take too long to execute.
    HSSFCell cell = row.getCell(8);
    EvalListener evalListener = new EvalListener();
    WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.createEvaluator(wb, evalListener);
    ValueEval ve = evaluator.evaluate(HSSFEvaluationTestHelper.wrapCell(cell));
    int evalCount = evalListener.getCountCacheMisses();
    if (evalCount > 10) {
        // Without caching, evaluating cell 'A9' takes 21845 evaluations which consumes
        // much time (~3 sec on Core 2 Duo 2.2GHz)
        // short-circuit-if optimisation cuts this down to 255 evaluations which is still too high
        // System.err.println("Cell A9 took " + evalCount + " intermediate evaluations");
        fail("Identifed bug 45376 - Formula evaluator should cache values");
    }
    // With caching, the evaluationCount is 8 which is exactly the
    // number of formula cells that needed to be evaluated.
    assertEquals(8, evalCount);
    // The cache hits would be 24 if fully evaluating all arguments of the
    // "IF()" functions (Each of the 8 formulas has 4 refs to formula cells
    // which result in 1 cache miss and 3 cache hits). However with the
    // short-circuit-if optimisation, 2 of the cell refs get skipped
    // reducing this metric 8.
    assertEquals(8, evalListener.getCountCacheHits());
    // confirm the evaluation result too
    assertEquals(ErrorEval.NA, ve);
    wb.close();
}

Example 7 with WorkbookEvaluator

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

the class TestDec2Hex 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("123.43");
    cell = row.createCell(1);
    cell.setCellValue("8");
    cell = row.createCell(2);
    cell.setCellValue("-8");
    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;
}