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.Core.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); } } } }
private static void Process(HSSFWorkbook wb) { HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); for (int i = 0; i < wb.NumberOfSheets; i++) { Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(i); IEnumerator it = s.GetRowEnumerator(); while (it.MoveNext()) { IRow r = (IRow)it.Current; Process(r, eval); } } }
public void TestEvaluateAll() { HSSFWorkbook wb = new HSSFWorkbook(); Npoi.Core.SS.UserModel.ISheet s1 = wb.CreateSheet(); Npoi.Core.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.Core.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.Core.SS.UserModel.CellType.Formula) { evaluator.EvaluateFormulaCell(c); // For Testing - all should be numeric Assert.AreEqual(Npoi.Core.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.Core.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.Core.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.Core.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.Core.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.Core.SS.UserModel.CellType.Formula) { evaluator.EvaluateInCell(c); } } } } Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0); Assert.AreEqual(Npoi.Core.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.Core.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.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType); }