public void CountIfWithNumbers()
{
_worksheet.Cells["A1"].Value = 1d;
_worksheet.Cells["A2"].Value = 2d;
_worksheet.Cells["A3"].Value = 3d;
var func = new CountIf();
IRangeInfo range = _provider.GetRange(_worksheet.Name, 1, 1, 3, 1);
var args = FunctionsHelper.CreateArgs(range, ">1");
var result = func.Execute(args, _parsingContext);
Assert.AreEqual(2d, result.Result);
}
public void ExtractCriterionFromCellRangeWithNonMatchingColReturnsZero()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
var provider = new EpplusExcelDataProvider(package);
this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
worksheet.Cells["E7"].Value = 5;
worksheet.Cells["F7"].Value = 10;
worksheet.Cells["G7"].Value = 15;
IRangeInfo testRange = provider.GetRange(worksheet.Name, 7, 5, 7, 7);
IRangeInfo firstRange = provider.GetRange(worksheet.Name, 8, 8, 8, 8);
var address = firstRange.Address;
var arguments = FunctionsHelper.CreateArgs(firstRange, testRange);
var result = IfHelper.ExtractCriterionFromCellRange(arguments.ElementAt(1), worksheet, address._fromRow, address._fromCol);
Assert.AreEqual(0, result);
}
}
public void ExtractCriterionFromCellRangeWithSameRowCellReferenceReturnsCorrectValue()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet2");
var provider = new EpplusExcelDataProvider(package);
this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
worksheet.Cells["B1"].Value = 5;
worksheet.Cells["B2"].Value = 10;
worksheet.Cells["B3"].Value = 15;
IRangeInfo testRange = provider.GetRange(worksheet.Name, 1, 2, 3, 2);
IRangeInfo firstRange = provider.GetRange(worksheet.Name, 2, 2, 2, 2);
var address = firstRange.Address;
var arguments = FunctionsHelper.CreateArgs(firstRange, testRange);
var result = IfHelper.ExtractCriterionFromCellRange(arguments.ElementAt(1), worksheet, address._fromRow, address._fromCol);
Assert.AreEqual(10, result);
}
}
public void FromCompileResultEnumerableIntegerUnknown()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["C1"].Value = 1;
sheet.Cells["C2"].Value = 2;
sheet.Cells["C3"].Value = 3;
var dataProvider = new EpplusExcelDataProvider(package);
var range = dataProvider.GetRange("Sheet1", 1, 3, 3, 3);
var compileResult = new CompileResult(range, DataType.Unknown);
var result = new ExpressionConverter().FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(IntegerExpression));
Assert.AreEqual(1d, result.Compile().Result);
}
}
public void AverageIfNumeric()
{
_worksheet.Cells["A1"].Value = 1d;
_worksheet.Cells["A2"].Value = 2d;
_worksheet.Cells["A3"].Value = 3d;
_worksheet.Cells["B1"].Value = 1d;
_worksheet.Cells["B2"].Value = 3d;
_worksheet.Cells["B3"].Value = 5d;
var func = new AverageIf();
IRangeInfo range1 = _provider.GetRange(_worksheet.Name, 1, 1, 3, 1);
IRangeInfo range2 = _provider.GetRange(_worksheet.Name, 1, 2, 3, 2);
var args = FunctionsHelper.CreateArgs(range1, ">1", range2);
var result = func.Execute(args, _parsingContext);
Assert.AreEqual(4d, result.Result);
}
public void AverageIfWithEmptyStringCriteria()
{
_worksheet.Cells["A1"].Value = null;
_worksheet.Cells["A2"].Value = string.Empty;
_worksheet.Cells["A3"].Value = "Not Empty";
_worksheet.Cells["B1"].Value = 1d;
_worksheet.Cells["B2"].Value = 3d;
_worksheet.Cells["B3"].Value = 5d;
var func = new AverageIf();
IRangeInfo range1 = _provider.GetRange(_worksheet.Name, 1, 1, 3, 1);
IRangeInfo range2 = _provider.GetRange(_worksheet.Name, 1, 2, 3, 2);
var args = FunctionsHelper.CreateArgs(range1, string.Empty, range2);
var result = func.Execute(args, _parsingContext);
Assert.AreEqual(2d, result.Result);
}
