public void AverageACellReferences()
{
// For cell references, AverageA divides by all cells, but only adds actual numbers, dates, and booleans.
ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Test");
double[] values =
{
0,
2000,
0,
1,
new DateTime(2013, 1, 5).ToOADate(),
0
};
ExcelRange range1 = worksheet.Cells[1, 1];
range1.Formula = "\"1000\"";
range1.Calculate();
var range2 = worksheet.Cells[1, 2];
range2.Value = 2000;
var range3 = worksheet.Cells[1, 3];
range3.Formula = $"\"{new DateTime(2013, 1, 5).ToString("d")}\"";
range3.Calculate();
var range4 = worksheet.Cells[1, 4];
range4.Value = true;
var range5 = worksheet.Cells[1, 5];
range5.Value = new DateTime(2013, 1, 5);
var range6 = worksheet.Cells[1, 6];
range6.Value = "Test";
AverageA average = new AverageA();
var rangeInfo1 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 1, 1, 3);
var rangeInfo2 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 4, 1, 4);
var rangeInfo3 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 5, 1, 6);
var context = ParsingContext.Create();
var address = new OfficeOpenXml.FormulaParsing.ExcelUtilities.RangeAddress();
address.FromRow = address.ToRow = address.FromCol = address.ToCol = 2;
context.Scopes.NewScope(address);
var result = average.Execute(new FunctionArgument[]
{
new FunctionArgument(rangeInfo1),
new FunctionArgument(rangeInfo2),
new FunctionArgument(rangeInfo3)
}, context);
Assert.AreEqual(values.Average(), result.Result);
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (StartRange == null || EndRange == null)
{
return(CreateResult(eErrorType.Value));
}
//Build the address from the minimum row and column to the maximum row and column. StartRange and offsetRange are single cells.
var fromRow = System.Math.Min(StartRange.Address._fromRow, EndRange.Address._fromRow);
var toRow = System.Math.Max(StartRange.Address._toRow, EndRange.Address._toRow);
var fromCol = System.Math.Min(StartRange.Address._fromCol, EndRange.Address._fromCol);
var toCol = System.Math.Max(StartRange.Address._toCol, EndRange.Address._toCol);
var rangeAddress = new EpplusExcelDataProvider.RangeInfo(StartRange.Worksheet, new ExcelAddressBase(fromRow, fromCol, toRow, toCol));
return(CreateResult(rangeAddress, DataType.Enumerable));
}
public void AverageCellReferences()
{
// In the case of cell references, Average DOES NOT parse and include numeric strings, date strings, bools, unparsable strings, etc.
ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Test");
ExcelRange range1 = worksheet.Cells[1, 1];
range1.Formula = "\"1000\"";
range1.Calculate();
var range2 = worksheet.Cells[1, 2];
range2.Value = 2000;
var range3 = worksheet.Cells[1, 3];
range3.Formula = $"\"{new DateTime(2013, 1, 5).ToString("d")}\"";
range3.Calculate();
var range4 = worksheet.Cells[1, 4];
range4.Value = true;
var range5 = worksheet.Cells[1, 5];
range5.Value = new DateTime(2013, 1, 5);
var range6 = worksheet.Cells[1, 6];
range6.Value = "Test";
Average average = new Average();
var rangeInfo1 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 1, 1, 3);
var rangeInfo2 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 4, 1, 4);
var rangeInfo3 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 5, 1, 6);
var context = ParsingContext.Create();
var address = new OfficeOpenXml.FormulaParsing.ExcelUtilities.RangeAddress();
address.FromRow = address.ToRow = address.FromCol = address.ToCol = 2;
context.Scopes.NewScope(address);
var result = average.Execute(new FunctionArgument[]
{
new FunctionArgument(rangeInfo1),
new FunctionArgument(rangeInfo2),
new FunctionArgument(rangeInfo3)
}, context);
Assert.AreEqual((2000 + new DateTime(2013, 1, 5).ToOADate()) / 2, result.Result);
}
