C# HSSFFormulaEvaluator.EvaluateFormulaCell方法代码示例

        public void TestEvaluateInSheetExample2()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
            sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
            sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
            sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
            sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
            sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
            sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);

            ICell cell = row.CreateCell(8);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            // Enumeration
            cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            double res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);

            // Range
            cell.CellFormula = ("NPV(A2, A4:A8)+A3");

            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
        }

        public void TestExistingWorkbook()
        {
            HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
            HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
            HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
            HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);

            Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
            Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
            Assert.AreEqual(CellType.Formula, lC1Cell.CellType);

            HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
            HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
            HSSFFormulaEvaluator.SetupEnvironment(
                    new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME },
                    new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator });

            Assert.AreEqual(CellType.Numeric, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));
            Assert.AreEqual(CellType.String, lMainWorkbookEvaluator.EvaluateFormulaCell(lB1Cell));
            Assert.AreEqual(CellType.Boolean, lMainWorkbookEvaluator.EvaluateFormulaCell(lC1Cell));

            Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);
            Assert.AreEqual("Apache rocks!", lB1Cell.StringCellValue);
            Assert.AreEqual(false, lC1Cell.BooleanCellValue);
        }

 public void TestEvaluate()
 {
     HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
     HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
     CellReference cellRef = new CellReference(wb.GetName("QUANT").RefersToFormula);
     ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     cell.SetCellValue(NEW_QUANT);
     cell = wb2.GetSheet("CostSheet").GetRow(1).GetCell(1);
     cell.SetCellValue(NEW_PART_COST);
     HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb);
     HSSFFormulaEvaluator EvaluatorCost = new HSSFFormulaEvaluator(wb2);
     String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
     HSSFFormulaEvaluator[] Evaluators = { Evaluator, EvaluatorCost, };
     HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators);
     cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula);
     ICell uccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     cellRef = new CellReference(wb.GetName("COST").RefersToFormula);
     ICell ccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula);
     ICell tccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     Evaluator.EvaluateFormulaCell(uccell);
     Evaluator.EvaluateFormulaCell(ccell);
     Evaluator.EvaluateFormulaCell(tccell);
     Assert.AreEqual(NEW_PART_COST, uccell.NumericCellValue);
     Assert.AreEqual(NEW_PART_COST * NEW_QUANT, ccell.NumericCellValue);
     Assert.AreEqual(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.NumericCellValue);
 }

        public void TestEvaluate_bug_45732()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            ICell cell = sheet.CreateRow(0).CreateCell(0);

            cell.CellFormula = ("NPER(12,4500,100000,100000)");
            cell.SetCellValue(15.0);
            Assert.AreEqual("NPER(12,4500,100000,100000)", cell.CellFormula);
            Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
            Assert.AreEqual(15.0, cell.NumericCellValue, 0.0);

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            fe.EvaluateFormulaCell(cell);
            Assert.AreEqual(CellType.ERROR, cell.CachedFormulaResultType);
            Assert.AreEqual(HSSFErrorConstants.ERROR_NUM, cell.ErrorCellValue);
        }

        public void TestEvaluateInSheet()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            row.CreateCell(0).SetCellValue(-4000d);
            row.CreateCell(1).SetCellValue(1200d);
            row.CreateCell(2).SetCellValue(1410d);
            row.CreateCell(3).SetCellValue(1875d);
            row.CreateCell(4).SetCellValue(1050d);

            ICell cell = row.CreateCell(5);
            cell.CellFormula = ("IRR(A1:E1)");

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            double res = cell.NumericCellValue;
            Assert.AreEqual(0.143d, Math.Round(res * 1000d) / 1000d);
        }

        public void TestDummyToSource()
        {
            bool changed = mainWorkbook.ChangeExternalReference("DOESNOTEXIST", SOURCE_WORKBOOK_FILENAME);
            Assert.IsFalse(changed);

            changed = mainWorkbook.ChangeExternalReference(SOURCE_DUMMY_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME);
            Assert.IsTrue(changed);

            HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
            HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);

            Assert.AreEqual(CellType.FORMULA, lA1Cell.CellType);

            HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
            HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
            HSSFFormulaEvaluator.SetupEnvironment(
                    new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME },
                    new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator });

            Assert.AreEqual(CellType.NUMERIC, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));

            Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);

        }

        public void TestZipCodeFormulas()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet s = wb.CreateSheet();
            s.CreateRow(0);
            ICell c1 = s.GetRow(0).CreateCell(0);
            ICell c2 = s.GetRow(0).CreateCell(1);
            ICell c3 = s.GetRow(0).CreateCell(2);

            // As number and string
            c1.CellFormula = ("70164");
            c2.CellFormula = ("\"70164\"");
            c3.CellFormula = ("\"90210\"");

            // Check the formulas
            Assert.AreEqual("70164", c1.CellFormula);
            Assert.AreEqual("\"70164\"", c2.CellFormula);

            // And Check the values - blank
            ConfirmCachedValue(0.0, c1);
            ConfirmCachedValue(0.0, c2);
            ConfirmCachedValue(0.0, c3);

            // Try changing the cached value on one of the string
            //  formula cells, so we can see it updates properly
            c3.SetCellValue(new HSSFRichTextString("Test"));
            ConfirmCachedValue("Test", c3);
            try
            {
                double a = c3.NumericCellValue;
                throw new AssertionException("exception should have been thrown");
            }
            catch (InvalidOperationException e)
            {
                Assert.AreEqual("Cannot get a numeric value from a text formula cell", e.Message);
            }


            // Now Evaluate, they should all be changed
            HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
            eval.EvaluateFormulaCell(c1);
            eval.EvaluateFormulaCell(c2);
            eval.EvaluateFormulaCell(c3);

            // Check that the cells now contain
            //  the correct values
            ConfirmCachedValue(70164.0, c1);
            ConfirmCachedValue("70164", c2);
            ConfirmCachedValue("90210", c3);


            // Write and read
            HSSFWorkbook nwb = WriteOutAndReadBack(wb);
            HSSFSheet ns = (HSSFSheet)nwb.GetSheetAt(0);
            ICell nc1 = ns.GetRow(0).GetCell(0);
            ICell nc2 = ns.GetRow(0).GetCell(1);
            ICell nc3 = ns.GetRow(0).GetCell(2);

            // Re-Check
            ConfirmCachedValue(70164.0, nc1);
            ConfirmCachedValue("70164", nc2);
            ConfirmCachedValue("90210", nc3);

            CellValueRecordInterface[] cvrs = ns.Sheet.GetValueRecords();
            for (int i = 0; i < cvrs.Length; i++)
            {
                CellValueRecordInterface cvr = cvrs[i];
                if (cvr is FormulaRecordAggregate)
                {
                    FormulaRecordAggregate fr = (FormulaRecordAggregate)cvr;

                    if (i == 0)
                    {
                        Assert.AreEqual(70164.0, fr.FormulaRecord.Value, 0.0001);
                        Assert.IsNull(fr.StringRecord);
                    }
                    else if (i == 1)
                    {
                        Assert.AreEqual(0.0, fr.FormulaRecord.Value, 0.0001);
                        Assert.IsNotNull(fr.StringRecord);
                        Assert.AreEqual("70164", fr.StringRecord.String);
                    }
                    else
                    {
                        Assert.AreEqual(0.0, fr.FormulaRecord.Value, 0.0001);
                        Assert.IsNotNull(fr.StringRecord);
                        Assert.AreEqual("90210", fr.StringRecord.String);
                    }
                }
            }
            Assert.AreEqual(3, cvrs.Length);
        }

        public void TestXRefs()
        {
            IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
            IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
            ICell cell;

            // VLookup on a name in another file
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
            Assert.AreEqual(CellType.Formula, cell.CellType);
            Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
            Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
            // WARNING - this is wrong!
            // The file name should be Showing, but bug #45970 is fixed
            //  we seem to loose it
            Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);


            // Simple reference to a name in another file
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
            Assert.AreEqual(CellType.Formula, cell.CellType);
            Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
            Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
            // WARNING - this is wrong!
            // The file name should be Showing, but bug #45970 is fixed
            //  we seem to loose it
            Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);


            // Evaluate the cells
            HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
            HSSFFormulaEvaluator.SetupEnvironment(
                  new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
                  new HSSFFormulaEvaluator[] {
                  eval,
                  new HSSFFormulaEvaluator(wbData)
            }
            );
            eval.EvaluateFormulaCell(
                  wb.GetSheetAt(0).GetRow(1).GetCell(2)
            );
            eval.EvaluateFormulaCell(
                  wb.GetSheetAt(0).GetRow(1).GetCell(4)
            );


            // Re-check VLOOKUP one
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
            Assert.AreEqual(CellType.Formula, cell.CellType);
            Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
            Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);

            // Re-check ref one
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
            Assert.AreEqual(CellType.Formula, cell.CellType);
            Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
            Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);

            // Add a formula that refers to one of the existing external workbooks
            cell = wb.GetSheetAt(0).GetRow(1).CreateCell(40);
            cell.CellFormula = (/*setter*/"Cost*[XRefCalcData.xls]MarkupSheet!$B$1");

            // Check is was stored correctly
            Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);

            // Check it Evaluates correctly
            eval.EvaluateFormulaCell(cell);
            Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);


            // Try to add a formula for a new external workbook, won't be allowed to start
            try
            {
                cell = wb.GetSheetAt(0).GetRow(1).CreateCell(42);
                cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1");
                Assert.Fail("New workbook not linked, shouldn't be able to Add");
            }
            catch (Exception e) { }

            // Link our new workbook
            HSSFWorkbook alt = new HSSFWorkbook();
            alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
            wb.LinkExternalWorkbook("alt.xls", alt);

            // Now add a formula that refers to our new workbook
            cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1");
            Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);

            // Evaluate it, without a link to that workbook
            try
            {
                eval.Evaluate(cell);
                Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
            }
            catch (Exception e) { }

            // Add a link, check it does
            HSSFFormulaEvaluator.SetupEnvironment(
                    new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
                    new HSSFFormulaEvaluator[] {
                    eval,
//.........这里部分代码省略.........

        /**
         * 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(HSSFWorkbook wb)
        {
            for (int i = 0; i < wb.NumberOfSheets; i++)
            {
                NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i);
                HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
                {
                    HSSFRow r = (HSSFRow)rit.Current;
                    //evaluator.SetCurrentRow(r);

                    for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext(); )
                    {
                        NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                            evaluator.EvaluateFormulaCell(c);
                    }
                }
            }
        }

        /**
     * Sum across multiple workbooks
     *  eg =SUM($Sheet2.A1:$Sheet3.A1)
     * DISABLED - We currently get the formula wrong, and mis-evaluate
     */
        public void DISABLEDtest48703()
        {
            HSSFWorkbook wb = OpenSample("48703.xls");
            Assert.AreEqual(3, wb.NumberOfSheets);

            // Check reading the formula
            ISheet sheet = wb.GetSheetAt(0);
            IRow r = sheet.GetRow(0);
            ICell c = r.GetCell(0);

            Assert.AreEqual("SUM(Sheet2!A1:Sheet3!A1)", c.CellFormula);
            Assert.AreEqual(4.0, c.NumericCellValue);

            // Check the evaluated result
            HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
            eval.EvaluateFormulaCell(c);
            Assert.AreEqual(4.0, c.NumericCellValue);
        }

        /**
         * 
         * Returns the Formatted value of a cell as a <tt>String</tt> regardless
         * of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
         * cell value will be Formatted using a default FormatBase.
         * 
         * When passed a null or blank cell, this method will return an empty
         * String (""). Formula cells will be evaluated using the given
         * {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
         * evaluator is null, then the formula String will be returned. The caller
         * is responsible for setting the currentRow on the evaluator
         *
         *
         * @param cell The cell (can be null)
         * @param evaluator The HSSFFormulaEvaluator (can be null)
         * @return a string value of the cell
         */
        public String FormatCellValue(HSSFCell cell, HSSFFormulaEvaluator evaluator)
        {

            if (cell == null)
            {
                return "";
            }

            int cellType = cell.CellType;
            if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA)
            {
                try
                {
                    cellType = evaluator.EvaluateFormulaCell(cell);
                }
                catch (Exception e)
                {
                    throw new Exception("Did you forGet to set the current" +
                            " row on the HSSFFormulaEvaluator?", e);
                }
            }
            switch (cellType)
            {
                case HSSFCell.CELL_TYPE_BLANK:
                    return "";

                case HSSFCell.CELL_TYPE_FORMULA:
                    // should only occur if evaluator is null
                    return cell.CellFormula;

                case HSSFCell.CELL_TYPE_NUMERIC:

                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {
                        return GetFormattedDateString(cell);
                    }
                    return GetFormattedNumberString(cell);

                case HSSFCell.CELL_TYPE_STRING:
                    return cell.RichStringCellValue.String;

                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return cell.BooleanCellValue?"TRUE":"FALSE";

                case HSSFCell.CELL_TYPE_ERROR:
                    return NPOI.HSSF.Record.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue);

            }
            throw new Exception("Unexpected celltype (" + cellType + ")");
        }

        public void TestEvaluateInSheet()
        {
            IWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            row.CreateCell(0).SetCellValue(-7500d);
            row.CreateCell(1).SetCellValue(3000d);
            row.CreateCell(2).SetCellValue(5000d);
            row.CreateCell(3).SetCellValue(1200d);
            row.CreateCell(4).SetCellValue(4000d);

            row.CreateCell(5).SetCellValue(0.05d);
            row.CreateCell(6).SetCellValue(0.08d);

            ICell cell = row.CreateCell(7);
            cell.CellFormula = (/*setter*/"MIRR(A1:E1, F1, G1)");

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            double res = cell.NumericCellValue;
            Assert.AreEqual(0.18736225093, res, 0.00000001);
        }

        public void TestXRefs()
        {
            IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
            IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
            ICell cell;

            // VLookup on a name in another file
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
            Assert.AreEqual(CellType.FORMULA, cell.CellType);
            Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
            Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
            // WARNING - this is wrong!
            // The file name should be Showing, but bug #45970 is fixed
            //  we seem to loose it
            Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);


            // Simple reference to a name in another file
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
            Assert.AreEqual(CellType.FORMULA, cell.CellType);
            Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
            Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
            // WARNING - this is wrong!
            // The file name should be Showing, but bug #45970 is fixed
            //  we seem to loose it
            Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);


            // Evaluate the cells
            HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
            HSSFFormulaEvaluator.SetupEnvironment(
                  new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
                  new HSSFFormulaEvaluator[] {
                  eval,
                  new HSSFFormulaEvaluator(wbData)
            }
            );
            eval.EvaluateFormulaCell(
                  wb.GetSheetAt(0).GetRow(1).GetCell(2)
            );
            eval.EvaluateFormulaCell(
                  wb.GetSheetAt(0).GetRow(1).GetCell(4)
            );


            // Re-check VLOOKUP one
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
            Assert.AreEqual(CellType.FORMULA, cell.CellType);
            Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
            Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);

            // Re-check ref one
            cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
            Assert.AreEqual(CellType.FORMULA, cell.CellType);
            Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
            Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
        }

        public void TestEvaluateAll()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet();
            NPOI.SS.UserModel.ISheet s2 = wb.CreateSheet();
            wb.SetSheetName(0, "S1");
            wb.SetSheetName(1, "S2");

            IRow s1r1 = s1.CreateRow(0);
            IRow s1r2 = s1.CreateRow(1);
            IRow s2r1 = s2.CreateRow(0);

            ICell s1r1c1 = s1r1.CreateCell(0);
            ICell s1r1c2 = s1r1.CreateCell(1);
            ICell s1r1c3 = s1r1.CreateCell(2);
            s1r1c1.SetCellValue(22.3);
            s1r1c2.SetCellValue(33.4);
            s1r1c3.CellFormula = ("SUM(A1:B1)");

            ICell s1r2c1 = s1r2.CreateCell(0);
            ICell s1r2c2 = s1r2.CreateCell(1);
            ICell s1r2c3 = s1r2.CreateCell(2);
            s1r2c1.SetCellValue(-1.2);
            s1r2c2.SetCellValue(-3.4);
            s1r2c3.CellFormula = ("SUM(A2:B2)");

            ICell s2r1c1 = s2r1.CreateCell(0);
            s2r1c1.CellFormula = ("S1!A1");

            // Not Evaluated yet
            Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);

            // Do a full Evaluate, as per our docs
            // uses EvaluateFormulaCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateFormulaCell(c);

                            // For Testing - all should be numeric
                            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
                        }
                    }
                }
            }

            // Check now as expected
            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);

            Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
            Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);


            // Now do the alternate call, which zaps the formulas
            // uses EvaluateInCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateInCell(c);
                        }
                    }
                }
            }

            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
//.........这里部分代码省略.........

        public void TestClassCast_bug44861()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls");

            // Check direct
            HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);

            // And via calls
            int numSheets = wb.NumberOfSheets;
            for (int i = 0; i < numSheets; i++)
            {
                NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i);
                HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext(); )
                {
                    IRow r = (IRow)rows.Current;

                    for (IEnumerator cells = r.GetEnumerator(); cells.MoveNext(); )
                    {
                        ICell c = (ICell)cells.Current;
                        eval.EvaluateFormulaCell(c);
                    }
                }
            }
        }