private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
String expectedResult)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.STRING);
Assert.AreEqual(expectedResult, result.StringValue);
}
private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
int expectedResult)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.NUMERIC);
Assert.AreEqual(expectedResult, result.NumberValue, 0.0);
}
private static void ConfirmError(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
int expectedErrorCode)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.ERROR);
Assert.AreEqual(expectedErrorCode, result.ErrorValue);
}
public void TestRangeUsingOffsetFunc_bug46948()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row = wb.CreateSheet("Sheet1").CreateRow(0);
ICell cellA1 = row.CreateCell(0);
ICell cellB1 = row.CreateCell(1);
row.CreateCell(2).SetCellValue(5.0); // C1
row.CreateCell(3).SetCellValue(7.0); // D1
row.CreateCell(4).SetCellValue(9.0); // E1
cellA1.CellFormula = ("SUM(C1:OFFSET(C1,0,B1))");
cellB1.SetCellValue(1.0); // range will be C1:D1
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv;
try
{
cv = fe.Evaluate(cellA1);
}
catch (ArgumentException e)
{
if (e.Message.Equals("Unexpected ref arg class (NPOI.SS.Formula.LazyAreaEval)"))
{
throw new AssertionException("Identified bug 46948");
}
throw e;
}
Assert.AreEqual(12.0, cv.NumberValue, 0.0);
cellB1.SetCellValue(2.0); // range will be C1:E1
fe.NotifyUpdateCell(cellB1);
cv = fe.Evaluate(cellA1);
Assert.AreEqual(21.0, cv.NumberValue, 0.0);
cellB1.SetCellValue(0.0); // range will be C1:C1
fe.NotifyUpdateCell(cellB1);
cv = fe.Evaluate(cellA1);
Assert.AreEqual(5.0, cv.NumberValue, 0.0);
}
public void TestEvaluateMultipleWorkbooks()
{
HSSFWorkbook wbA = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaA.xls");
HSSFWorkbook wbB = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaB.xls");
HSSFFormulaEvaluator EvaluatorA = new HSSFFormulaEvaluator(wbA);
HSSFFormulaEvaluator EvaluatorB = new HSSFFormulaEvaluator(wbB);
// Hook up the workbook Evaluators to enable Evaluation of formulas across books
String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
HSSFFormulaEvaluator[] Evaluators = { EvaluatorA, EvaluatorB, };
HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators);
ISheet aSheet1 = wbA.GetSheetAt(0);
ISheet bSheet1 = wbB.GetSheetAt(0);
// Simple case - single link from wbA to wbB
ConfirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
ICell cell = aSheet1.GetRow(0).GetCell(0);
ConfirmEvaluation(35, EvaluatorA, cell);
// more complex case - back link into wbA
// [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
ConfirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
// [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
ConfirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
cell = aSheet1.GetRow(1).GetCell(0);
ConfirmEvaluation(264, EvaluatorA, cell);
// change [wbB]BSheet1!B3 (from 50 to 60)
ICell cellB3 = bSheet1.GetRow(2).GetCell(1);
cellB3.SetCellValue(60);
EvaluatorB.NotifyUpdateCell(cellB3);
ConfirmEvaluation(274, EvaluatorA, cell);
// change [wbA]ASheet1!A3 (from 100 to 80)
ICell cellA3 = aSheet1.GetRow(2).GetCell(0);
cellA3.SetCellValue(80);
EvaluatorA.NotifyUpdateCell(cellA3);
ConfirmEvaluation(234, EvaluatorA, cell);
// change [wbA]AnotherSheet!A1 (from 2 to 3)
ICell cellA1 = wbA.GetSheetAt(1).GetRow(0).GetCell(0);
cellA1.SetCellValue(3);
EvaluatorA.NotifyUpdateCell(cellA1);
ConfirmEvaluation(235, EvaluatorA, cell);
}
public void TestDateWithNegativeParts_bug48528()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFRow row = (HSSFRow)sheet.CreateRow(1);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
// 5th Feb 2012 = 40944
// 1st Feb 2012 = 40940
// 5th Jan 2012 = 40913
// 5th Dec 2011 = 40882
// 5th Feb 2011 = 40579
cell.CellFormula=("DATE(2012,2,1)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40940.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula=("DATE(2012,2,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40944.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula=("DATE(2012,2-1,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40913.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula=("DATE(2012,2,1-27)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40913.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula=("DATE(2012,2-2,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40882.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula=("DATE(2012,2,1-58)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40882.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula=("DATE(2012,2-12,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40579.0, fe.Evaluate(cell).NumberValue);
}
