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);
}
