Beispiel #1
0
        public void TestShortCircuitIfEvaluation()
        {
            // Set up a simple IF() formula that has measurable evaluation cost for its operands.
            HSSFWorkbook wb     = new HSSFWorkbook();
            ISheet       sheet  = wb.CreateSheet("Sheet1");
            IRow         row    = sheet.CreateRow(0);
            ICell        cellA1 = row.CreateCell(0);

            cellA1.CellFormula = "if(B1,C1,D1+E1+F1)";
            // populate cells B1..F1 with simple formulas instead of plain values so we can use
            // 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).CellFormula = i.ToString();
            }

            EvalCountListener evalListener = new EvalCountListener();
            WorkbookEvaluator evaluator    = WorkbookEvaluatorTestHelper.CreateEvaluator(wb, evalListener);
            ValueEval         ve           = evaluator.Evaluate(HSSFEvaluationTestHelper.WrapCell(cellA1));
            int evalCount = evalListener.EvalCount;

            if (evalCount == 6)
            {
                // Without short-circuit-if evaluation, evaluating cell 'A1' takes 3 extra evaluations (for D1,E1,F1)
                Assert.Fail("Identifed bug 48195 - Formula evaluator should short-circuit IF() calculations.");
            }
            Assert.AreEqual(3, evalCount);
            Assert.AreEqual(2.0, ((NumberEval)ve).NumberValue, 0D);

            wb.Close();
        }
        public void TestSlowEvaluate45376()
        {
            /*
             * 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();

            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            for (int i = 1; i < 10; i++)
            {
                ICell  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
                String formula = "IF(DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1)<=$D$3," +
                                 "DATE(YEAR(" + prevCell + "),MONTH(" + prevCell + ")+1,1),NA())";
                cell.CellFormula = (formula);
            }
            row.CreateCell(0).SetCellValue(new DateTime(2000, 1, 1, 0, 0, 0));

            // Choose cell A9, so that the Assert.Failing Test case doesn't take too long to execute.
            ICell             cell1        = row.GetCell(8);
            EvalListener      evalListener = new EvalListener();
            WorkbookEvaluator evaluator    = WorkbookEvaluatorTestHelper.CreateEvaluator(wb, evalListener);
            ValueEval         ve           = evaluator.Evaluate(HSSFEvaluationTestHelper.WrapCell(cell1));
            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)
                Console.Error.WriteLine("Cell A9 took " + evalCount + " intermediate evaluations");
                throw new AssertionException("Identifed bug 45376 - Formula evaluator should cache values");
            }
            // With caching, the evaluationCount is 8 which is a big improvement
            // Note - these expected values may change if the WorkbookEvaluator is
            // ever optimised to short circuit 'if' functions.
            Assert.AreEqual(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.
            Assert.AreEqual(8, evalListener.GetCountCacheHits());

            // confirm the evaluation result too
            Assert.AreEqual(ErrorEval.NA, ve);

            wb.Close();
        }