public void TestResultOutsideRange()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);
cell.CellFormula = "D2:D5"; // IF(TRUE,D2:D5,D2) or OFFSET(D2:D5,0,0) would work too
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (ArgumentException e)
{
if ("Specified row index (0) is outside the allowed range (1..4)".Equals(e.Message))
{
throw new AssertionException("Identified bug in result dereferencing");
}
throw;
}
Assert.AreEqual(CellType.Error, cv.CellType);
Assert.AreEqual(ErrorConstants.ERROR_VALUE, cv.ErrorValue);
// verify circular refs are still detected properly
fe.ClearAllCachedResultValues();
cell.CellFormula = "OFFSET(A1,0,0)";
cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Error, cv.CellType);
Assert.AreEqual(ErrorEval.CIRCULAR_REF_ERROR.ErrorCode, cv.ErrorValue);
}
public void TestEvaluate()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
// Create cells
row1.CreateCell(0, CellType.String);
// Create references
CellReference a1Ref = new CellReference("A1");
// Set values
ICell cellA1 = sh.GetRow(a1Ref.Row).GetCell(a1Ref.Col);
ICell cell1 = row1.CreateCell(1);
cell1.CellFormula = "IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")";
IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
cellA1.SetCellValue("A");
Assert.AreEqual(CellType.String, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric");
Assert.AreEqual("Value for A", evaluator.Evaluate(cell1).StringValue, "IFS should return 'Value for B'");
cellA1.SetCellValue("B");
evaluator.ClearAllCachedResultValues();
Assert.AreEqual(CellType.String, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric");
Assert.AreEqual("Value for B", evaluator.Evaluate(cell1).StringValue, "IFS should return 'Value for B'");
}
protected void ChangeSheetNameWithSharedFormulas(String sampleFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
IFormulaEvaluator Evaluator = wb.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
ISheet sheet = wb.GetSheetAt(0);
for (int rownum = 1; rownum <= 40; rownum++)
{
ICell cellA = sheet.GetRow(1).GetCell(0);
ICell cellB = sheet.GetRow(1).GetCell(1);
Assert.AreEqual(cellB.StringCellValue, Evaluator.Evaluate(cellA).StringValue);
}
wb.SetSheetName(0, "Renamed by POI");
Evaluator.ClearAllCachedResultValues();
for (int rownum = 1; rownum <= 40; rownum++)
{
ICell cellA = sheet.GetRow(1).GetCell(0);
ICell cellB = sheet.GetRow(1).GetCell(1);
Assert.AreEqual(cellB.StringCellValue, Evaluator.Evaluate(cellA).StringValue);
}
wb.Close();
}
public void TestIntermediateCircularReferenceResults_bug46898()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
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.SetCellFormula("IF(FALSE, 1+B1, 42)");
cellB1.CellFormula = (/*setter*/ "1+C1");
cellC1.CellFormula = (/*setter*/ "1+D1");
cellD1.CellFormula = (/*setter*/ "1+E1");
cellE1.CellFormula = (/*setter*/ "1+A1");
IFormulaEvaluator fe = _testDataProvider.CreateFormulaEvaluator(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);
// Identified bug 46898
Assert.AreNotEqual(cv.CellType, ErrorEval.CIRCULAR_REF_ERROR.ErrorCode);
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);
wb.Close();
}
public static void AssertDouble(IFormulaEvaluator fe, ICell cell, string formula, double expectedResult, double delta = 0.0)
{
cell.SetCellFormula(formula);
var result = fe.Evaluate(cell).NumberValue;
fe.ClearAllCachedResultValues();
Assert.AreEqual(expectedResult, result, delta);
}
public static void AssertString(IFormulaEvaluator fe, ICell cell, string formula, string expectedResult)
{
cell.SetCellFormula(formula);
var result = fe.Evaluate(cell).StringValue;
fe.ClearAllCachedResultValues();
Assert.AreEqual(expectedResult, result);
}
public static void AssertError(IFormulaEvaluator fe, ICell cell, string formula, FormulaError expectedError)
{
cell.SetCellFormula(formula);
fe.ClearAllCachedResultValues();
var result = fe.Evaluate(cell).ErrorValue;
Assert.AreEqual(expectedError.Code, result);
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String expectedResult)
{
fe.ClearAllCachedResultValues();
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.String)
{
Assert.Fail("expected String cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.StringValue);
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, double expectedResult)
{
fe.ClearAllCachedResultValues();
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Numeric)
{
Assert.Fail("expected numeric cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
private static string GetCellString(IRow row, int i, IFormulaEvaluator formulaEvaluator)
{
ICell cell = row?.GetCell(i);
if (cell?.CellType == CellType.Formula)
{
formulaEvaluator.ClearAllCachedResultValues();
return $"{formulaEvaluator.EvaluateInCell(cell)}";
}
else
{
return cell?.ToString() ?? "";
}
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
double expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.NUMERIC)
{
throw new AssertionException("expected numeric cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
public void TestSetTypeStringOnFormulaCell()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICell cellA1 = CreateACell(wb);
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
cellA1.CellFormula = ("\"DEF\"");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.CellFormula = ("25.061");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(25.061, cellA1.NumericCellValue, 0.0);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("25.061", cellA1.StringCellValue);
cellA1.CellFormula = ("TRUE");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(true, cellA1.BooleanCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("TRUE", cellA1.StringCellValue);
cellA1.CellFormula = ("#NAME?");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(FormulaError.NAME, FormulaError.ForInt(cellA1.ErrorCellValue));
cellA1.SetCellType(CellType.String);
Assert.AreEqual("#NAME?", cellA1.StringCellValue);
wb.Close();
}
private void Confirm(string formulaText, String expectedResult)
{
cell11.CellFormula = (/*setter*/ formulaText);
Evaluator.ClearAllCachedResultValues();
CellValue cv = Evaluator.Evaluate(cell11);
if (cv.CellType != CellType.String)
{
Assert.Fail("Wrong result type: " + cv.FormatAsString());
}
String actualValue = cv.StringValue;
Assert.AreEqual(expectedResult, actualValue);
}
public void TestSetTypeStringOnFormulaCell()
{
ICell cellA1 = CreateACell();
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
cellA1.CellFormula = ("\"DEF\"");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.CellFormula = ("25.061");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(25.061, cellA1.NumericCellValue, 0.0);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("25.061", cellA1.StringCellValue);
cellA1.CellFormula = ("TRUE");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(true, cellA1.BooleanCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("TRUE", cellA1.StringCellValue);
cellA1.CellFormula = ("#NAME?");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(ErrorConstants.ERROR_NAME, cellA1.ErrorCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("#NAME?", cellA1.StringCellValue);
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
ErrorEval expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Error)
{
throw new AssertionException("expected error cell type but got " + cv.FormatAsString());
}
int expCode = expectedResult.ErrorCode;
if (cv.ErrorValue != expCode)
{
throw new AssertionException("Expected error '" + ErrorEval.GetText(expCode)
+ "' but got '" + cv.FormatAsString() + "'.");
}
}
public void TestRepeatedEvaluation()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow r = sheet.CreateRow(0);
ICell c = r.CreateCell(0, CellType.Formula);
// Create a value and check it
c.CellFormula = (/*setter*/ "Date(2011,10,6)");
CellValue cellValue = fe.Evaluate(c);
Assert.AreEqual(40822.0, cellValue.NumberValue, 0.0);
cellValue = fe.Evaluate(c);
Assert.AreEqual(40822.0, cellValue.NumberValue, 0.0);
// Change it
c.CellFormula = (/*setter*/ "Date(2011,10,4)");
// Evaluate it, no change as the formula Evaluator
// won't know to clear the cache
cellValue = fe.Evaluate(c);
Assert.AreEqual(40822.0, cellValue.NumberValue, 0.0);
// Manually flush for this cell, and check
fe.NotifySetFormula(c);
cellValue = fe.Evaluate(c);
Assert.AreEqual(40820.0, cellValue.NumberValue, 0.0);
// Change again, without Notifying
c.CellFormula = (/*setter*/ "Date(2010,10,4)");
cellValue = fe.Evaluate(c);
Assert.AreEqual(40820.0, cellValue.NumberValue, 0.0);
// Now manually clear all, will see the new value
fe.ClearAllCachedResultValues();
cellValue = fe.Evaluate(c);
Assert.AreEqual(40455.0, cellValue.NumberValue, 0.0);
wb.Close();
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
ErrorEval expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula=(formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Error)
{
throw new AssertionException("expected error cell type but got " + cv.FormatAsString());
}
int expCode = expectedResult.ErrorCode;
if (cv.ErrorValue != expCode)
{
throw new AssertionException("Expected error '" + ErrorEval.GetText(expCode)
+ "' but got '" + cv.FormatAsString() + "'.");
}
}
public void TestJoinSingleLiteralText()
{
evaluator.ClearAllCachedResultValues();
formulaCell.SetCellFormula("TEXTJOIN(\",\", true, \"Text\")");
evaluator.EvaluateFormulaCell(formulaCell);
Assert.AreEqual("Text", formulaCell.StringCellValue);
}
public void TestSetSheetName()
{
IWorkbook wb1 = newSetSheetNameTestingWorkbook();
ISheet sh1 = wb1.GetSheetAt(0);
IName sale_2 = wb1.GetNameAt(1);
IName sale_3 = wb1.GetNameAt(2);
IName sale_4 = wb1.GetNameAt(3);
Assert.AreEqual("sale_2", sale_2.NameName);
Assert.AreEqual("'Testing 47100'!$A$1", sale_2.RefersToFormula);
Assert.AreEqual("sale_3", sale_3.NameName);
Assert.AreEqual("'Testing 47100'!$B$1", sale_3.RefersToFormula);
Assert.AreEqual("sale_4", sale_4.NameName);
Assert.AreEqual("'To be Renamed'!$A$3", sale_4.RefersToFormula);
IFormulaEvaluator Evaluator = wb1.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
ICell cell0 = sh1.GetRow(0).GetCell(0);
ICell cell1 = sh1.GetRow(1).GetCell(0);
ICell cell2 = sh1.GetRow(2).GetCell(0);
Assert.AreEqual("SUM('Testing 47100'!A1:C1)", cell0.CellFormula);
Assert.AreEqual("SUM('Testing 47100'!A1:C1,'To be Renamed'!A1:A5)", cell1.CellFormula);
Assert.AreEqual("sale_2+sale_3+'Testing 47100'!C1", cell2.CellFormula);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
wb1.SetSheetName(1, "47100 - First");
wb1.SetSheetName(2, "47100 - Second");
Assert.AreEqual("sale_2", sale_2.NameName);
Assert.AreEqual("'47100 - First'!$A$1", sale_2.RefersToFormula);
Assert.AreEqual("sale_3", sale_3.NameName);
Assert.AreEqual("'47100 - First'!$B$1", sale_3.RefersToFormula);
Assert.AreEqual("sale_4", sale_4.NameName);
Assert.AreEqual("'47100 - Second'!$A$3", sale_4.RefersToFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1)", cell0.CellFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.CellFormula);
Assert.AreEqual("sale_2+sale_3+'47100 - First'!C1", cell2.CellFormula);
Evaluator.ClearAllCachedResultValues();
Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1);
wb1.Close();
sh1 = wb2.GetSheetAt(0);
sale_2 = wb2.GetNameAt(1);
sale_3 = wb2.GetNameAt(2);
sale_4 = wb2.GetNameAt(3);
cell0 = sh1.GetRow(0).GetCell(0);
cell1 = sh1.GetRow(1).GetCell(0);
cell2 = sh1.GetRow(2).GetCell(0);
Assert.AreEqual("sale_2", sale_2.NameName);
Assert.AreEqual("'47100 - First'!$A$1", sale_2.RefersToFormula);
Assert.AreEqual("sale_3", sale_3.NameName);
Assert.AreEqual("'47100 - First'!$B$1", sale_3.RefersToFormula);
Assert.AreEqual("sale_4", sale_4.NameName);
Assert.AreEqual("'47100 - Second'!$A$3", sale_4.RefersToFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1)", cell0.CellFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.CellFormula);
Assert.AreEqual("sale_2+sale_3+'47100 - First'!C1", cell2.CellFormula);
Evaluator = wb2.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
wb2.Close();
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
double expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Numeric)
{
throw new AssertionException("expected numeric cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
public void TestRandBetweenSameValues()
{
Evaluator.ClearAllCachedResultValues();
formulaCell.CellFormula = ("RANDBETWEEN(1,1)");
Evaluator.EvaluateFormulaCell(formulaCell);
Assert.AreEqual(1, formulaCell.NumericCellValue, 0);
Evaluator.ClearAllCachedResultValues();
formulaCell.CellFormula = ("RANDBETWEEN(-1,-1)");
Evaluator.EvaluateFormulaCell(formulaCell);
Assert.AreEqual(-1, formulaCell.NumericCellValue, 0);
}