C# HSSFFormulaEvaluator.EvaluateInCell方法代码示例

 /// <summary>
 /// 根据Excel列类型获取列的值
 /// </summary>
 /// <param name="cell">Excel列</param>
 /// <returns></returns>
 private static string GetCellValue(ICell cell)
 {
     if (cell == null)
         return string.Empty;
     switch (cell.CellType)
     {
         case CellType.BLANK:
             return string.Empty;
         case CellType.BOOLEAN:
             return cell.BooleanCellValue.ToString();
         case CellType.ERROR:
             return cell.ErrorCellValue.ToString();
         case CellType.NUMERIC:
         case CellType.Unknown:
         default:
             return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
         case CellType.STRING:
             return cell.StringCellValue;
         case CellType.FORMULA:
             try
             {
                 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                 e.EvaluateInCell(cell);
                 return cell.ToString();
             }
             catch
             {
                 return cell.NumericCellValue.ToString();
             } 
     }
 }

        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 TestEvaluateBooleanInCell_bug44508()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);

            cell.CellFormula = ("1=1");

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            try
            {
                fe.EvaluateInCell(cell);
            }
            catch (FormatException)
            {
                Assert.Fail("Identified bug 44508");
            }
            Assert.AreEqual(true, cell.BooleanCellValue);
        }

 public void TestEvaluateInCellWithErrorCode_bug44950()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
     IRow row = sheet.CreateRow(1);
     ICell cell = row.CreateCell(0);
     cell.CellFormula = ("na()"); // this formula Evaluates to an Excel error code '#N/A'
     HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
     try
     {
         fe.EvaluateInCell(cell);
     }
     catch (InvalidOperationException e)
     {
         if (e.Message.StartsWith("Cannot get a error value from"))
         {
             throw new AssertionException("Identified bug 44950 b");
         }
         throw;
     }
 }

        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();

                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }