public void TestEvaluateAllFails()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(0).CellFormula = (/*setter*/ "A21");
for (int i = 2; i < 19; i++)
{
s.CreateRow(i);
}
// Cells outside window will fail, whether referenced or not
s.CreateRow(19).CreateCell(0).CellFormula = (/*setter*/ "A1+A2");
s.CreateRow(20).CreateCell(0).CellFormula = (/*setter*/ "A1+A11+100");
try
{
eval.EvaluateAll();
Assert.Fail("Evaluate All shouldn't work, as some cells outside the window");
}
catch (RowFlushedException)
{
// Expected
}
// Inactive sheets will fail
XSSFWorkbook xwb = new XSSFWorkbook();
xwb.CreateSheet("Open");
xwb.CreateSheet("Closed");
wb.Close();
wb = new SXSSFWorkbook(xwb, 5);
s = wb.GetSheet("Closed") as SXSSFSheet;
s.FlushRows();
s = wb.GetSheet("Open") as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
eval = wb.GetCreationHelper().CreateFormulaEvaluator();
try
{
eval.EvaluateAll();
Assert.Fail("Evaluate All shouldn't work, as sheets flushed");
}
catch (SheetsFlushedException) { }
wb.Close();
}
public void TestAvg()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(1,B2:B3)");
ICell a4 = sh.CreateRow(4).CreateCell(1);
a4.SetCellValue(1);
ICell a5 = sh.CreateRow(5).CreateCell(1);
a5.SetCellValue(7);
ICell a6 = sh.CreateRow(6).CreateCell(1);
a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2");
ICell a7 = sh.CreateRow(7).CreateCell(1);
a7.CellFormula = ("SUBTOTAL(1,B2:B7)");
fe.EvaluateAll();
Assert.AreEqual(2.0, a3.NumericCellValue);
Assert.AreEqual(8.0, a6.NumericCellValue);
Assert.AreEqual(3.0, a7.NumericCellValue);
}
public void TestCount()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(2,B2:B3)");
ICell a4 = sh.CreateRow(4).CreateCell(1);
a4.SetCellValue("POI"); // A4 is string and not counted
ICell a5 = sh.CreateRow(5).CreateCell(1); // A5 is blank and not counted
ICell a6 = sh.CreateRow(6).CreateCell(1);
a6.CellFormula = ("SUBTOTAL(2,B2:B6)*2 + 2");
ICell a7 = sh.CreateRow(7).CreateCell(1);
a7.CellFormula = ("SUBTOTAL(2,B2:B7)");
fe.EvaluateAll();
Assert.AreEqual(2.0, a3.NumericCellValue);
Assert.AreEqual(6.0, a6.NumericCellValue);
Assert.AreEqual(2.0, a7.NumericCellValue);
}
public void TestStdev()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(0).CreateCell(0);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(1).CreateCell(0);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(2).CreateCell(0);
a3.CellFormula = ("SUBTOTAL(7,A1:A2)");
ICell a4 = sh.CreateRow(3).CreateCell(0);
a4.SetCellValue(1);
ICell a5 = sh.CreateRow(4).CreateCell(0);
a5.SetCellValue(7);
ICell a6 = sh.CreateRow(5).CreateCell(0);
a6.CellFormula = ("SUBTOTAL(7,A1:A5)*2 + 2");
ICell a7 = sh.CreateRow(6).CreateCell(0);
a7.CellFormula = ("SUBTOTAL(7,A1:A6)");
fe.EvaluateAll();
Assert.AreEqual(1.41421, a3.NumericCellValue, 0.0001);
Assert.AreEqual(7.65685, a6.NumericCellValue, 0.0001);
Assert.AreEqual(2.82842, a7.NumericCellValue, 0.0001);
}
public void TestEvaluateAllInWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(1).CellFormula = (/*setter*/ "A1+10");
s.CreateRow(2).CreateCell(2).CellFormula = (/*setter*/ "B2+100");
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
eval.EvaluateAll();
Assert.AreEqual(3, (int)s.GetRow(0).GetCell(0).NumericCellValue);
Assert.AreEqual(13, (int)s.GetRow(1).GetCell(1).NumericCellValue);
Assert.AreEqual(113, (int)s.GetRow(2).GetCell(2).NumericCellValue);
wb.Close();
}
private void TestIFEqualsFormulaEvaluation_evaluateAll(
String formula, CellType cellType, String expectedFormula, double expectedResult)
{
IWorkbook wb = TestIFEqualsFormulaEvaluation_setup(formula, cellType);
ICell D1 = wb.GetSheet("IFEquals").GetRow(0).GetCell(3);
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
eval.EvaluateAll();
// Call should modify the contents
Assert.AreEqual(CellType.Formula, D1.CellType);
Assert.AreEqual(expectedFormula, D1.CellFormula);
Assert.AreEqual(CellType.Numeric, D1.CachedFormulaResultType);
Assert.AreEqual(expectedResult, D1.NumericCellValue, EPSILON);
TestIFEqualsFormulaEvaluation_teardown(wb);
}
public void Test50209()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.CellFormula = ("SUBTOTAL(9,B2)");
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(9,B2:B3)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateAll();
Assert.AreEqual(1.0, a2.NumericCellValue);
Assert.AreEqual(1.0, a3.NumericCellValue);
}
public void testUnimplemented()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(8,B2:B3)");
try
{
fe.EvaluateAll();
Assert.Fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
}
catch (NotImplementedException)
{
// expected here
}
a3.CellFormula = ("SUBTOTAL(10,B2:B3)");
try
{
fe.EvaluateAll();
Assert.Fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
}
catch (NotImplementedException)
{
// expected here
}
a3.CellFormula = ("SUBTOTAL(11,B2:B3)");
try
{
fe.EvaluateAll();
Assert.Fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
}
catch (NotImplementedException)
{
// expected here
}
a3.CellFormula = ("SUBTOTAL(107,B2:B3)");
try
{
fe.EvaluateAll();
Assert.Fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
}
catch (NotImplementedException)
{
// expected here
}
a3.CellFormula = ("SUBTOTAL(0,B2:B3)");
fe.EvaluateAll();
Assert.AreEqual(FormulaError.VALUE.Code, a3.ErrorCellValue);
try
{
a3.CellFormula = ("SUBTOTAL(9)");
Assert.Fail("Should catch an exception here");
}
catch (FormulaParseException)
{
// expected here
}
try
{
a3.CellFormula = ("SUBTOTAL()");
Assert.Fail("Should catch an exception here");
}
catch (FormulaParseException)
{
// expected here
}
Subtotal subtotal = new Subtotal();
Assert.AreEqual(ErrorEval.VALUE_INVALID, subtotal.Evaluate(new ValueEval[] { }, 0, 0));
}