/// <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); Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0); Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).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(); } } }