public void TestEvaluateMissingArgs()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula=("if(true,)");
fe.ClearAllCachedResultValues();
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
throw new AssertionException("Missing args Evaluation not implemented (bug 43354");
}
// MissingArg -> BlankEval -> zero (as formula result)
Assert.AreEqual(0.0, cv.NumberValue, 0.0);
// MissingArg -> BlankEval -> empty string (in concatenation)
cell.CellFormula=("\"abc\"&if(true,)");
fe.ClearAllCachedResultValues();
Assert.AreEqual("abc", fe.Evaluate(cell).StringValue);
}
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 TestCountFuncs()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula=("COUNT(C5,,,,)"); // 4 missing args, C5 is blank
Assert.AreEqual(4.0, fe.Evaluate(cell).NumberValue, 0.0);
cell.CellFormula=("COUNTA(C5,,)"); // 2 missing args, C5 is blank
fe.ClearAllCachedResultValues();
Assert.AreEqual(2.0, fe.Evaluate(cell).NumberValue, 0.0);
}
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 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 TestIntermediateCircularReferenceResults_bug46898()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
ICell cellB1 = row.CreateCell(1);
ICell cellC1 = row.CreateCell(2);
ICell cellD1 = row.CreateCell(3);
ICell cellE1 = row.CreateCell(4);
cellA1.CellFormula = ("IF(FALSE, 1+B1, 42)");
cellB1.CellFormula = ("1+C1");
cellC1.CellFormula = ("1+D1");
cellD1.CellFormula = ("1+E1");
cellE1.CellFormula = ("1+A1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv;
// Happy day flow - Evaluate A1 first
cv = fe.Evaluate(cellA1);
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(42.0, cv.NumberValue, 0.0);
cv = fe.Evaluate(cellB1); // no circ-ref-error because A1 result is cached
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(46.0, cv.NumberValue, 0.0);
// Show the bug - Evaluate another cell from the loop first
fe.ClearAllCachedResultValues();
cv = fe.Evaluate(cellB1);
if ((int)cv.CellType == ErrorEval.CIRCULAR_REF_ERROR.ErrorCode)
{
throw new AssertionException("Identified bug 46898");
}
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(46.0, cv.NumberValue, 0.0);
// start Evaluation on another cell
fe.ClearAllCachedResultValues();
cv = fe.Evaluate(cellE1);
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(43.0, cv.NumberValue, 0.0);
}