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);
}
private void Initialize()
{
_functions[1] = new Average();
_functions[2] = new Count();
_functions[3] = new CountA();
_functions[4] = new Max();
_functions[5] = new Min();
_functions[6] = new Product();
_functions[7] = new Stdev();
_functions[8] = new StdevP();
_functions[9] = new Sum();
_functions[10] = new Var();
_functions[11] = new VarP();
AddHiddenValueHandlingFunction(new Average(), 101);
AddHiddenValueHandlingFunction(new Count(), 102);
AddHiddenValueHandlingFunction(new CountA(), 103);
AddHiddenValueHandlingFunction(new Max(), 104);
AddHiddenValueHandlingFunction(new Min(), 105);
AddHiddenValueHandlingFunction(new Product(), 106);
AddHiddenValueHandlingFunction(new Stdev(), 107);
AddHiddenValueHandlingFunction(new StdevP(), 108);
AddHiddenValueHandlingFunction(new Sum(), 109);
AddHiddenValueHandlingFunction(new Var(), 110);
AddHiddenValueHandlingFunction(new VarP(), 111);
}
private void Initialize()
{
_functions[1] = new Average();
_functions[2] = new Count();
_functions[3] = new CountA();
_functions[4] = new Max();
_functions[5] = new Min();
_functions[6] = new Product();
_functions[7] = new Stdev();
_functions[8] = new StdevP();
_functions[9] = new Sum();
_functions[10] = new Var();
_functions[11] = new VarP();
AddHiddenValueHandlingFunction(new Average(), 101);
AddHiddenValueHandlingFunction(new Count(), 102);
AddHiddenValueHandlingFunction(new CountA(), 103);
AddHiddenValueHandlingFunction(new Max(), 104);
AddHiddenValueHandlingFunction(new Min(), 105);
AddHiddenValueHandlingFunction(new Product(), 106);
AddHiddenValueHandlingFunction(new Stdev(), 107);
AddHiddenValueHandlingFunction(new StdevP(), 108);
AddHiddenValueHandlingFunction(new Sum(), 109);
AddHiddenValueHandlingFunction(new Var(), 110);
AddHiddenValueHandlingFunction(new VarP(), 111);
}
public void AverageArray()
{
// In the case of arrays, Average DOES NOT parse and include numeric strings, date strings, bools, unparsable strings, etc.
Average average = new Average();
var date1 = new DateTime(2013, 1, 5);
var date2 = new DateTime(2013, 1, 15);
double value = 2000;
var result = average.Execute(new FunctionArgument[]
{
new FunctionArgument(new FunctionArgument[]
{
new FunctionArgument(1000.ToString("n")),
new FunctionArgument(value),
new FunctionArgument(6000.ToString("n")),
new FunctionArgument(true),
new FunctionArgument(date1),
new FunctionArgument(date2.ToString("d")),
new FunctionArgument("test")
})
}, ParsingContext.Create());
Assert.AreEqual((2000 + date1.ToOADate()) / 2, result.Result);
}
public void AverageLiterals()
{
// In the case of literals, Average DOES parse and include numeric strings, date strings, bools, etc.
Average average = new Average();
var date1 = new DateTime(2013, 1, 5);
var date2 = new DateTime(2013, 1, 15);
double value1 = 1000;
double value2 = 2000;
double value3 = 6000;
double value4 = 1;
double value5 = date1.ToOADate();
double value6 = date2.ToOADate();
var result = average.Execute(new FunctionArgument[]
{
new FunctionArgument(value1.ToString("n")),
new FunctionArgument(value2),
new FunctionArgument(value3.ToString("n")),
new FunctionArgument(true),
new FunctionArgument(date1),
new FunctionArgument(date2.ToString("d"))
}, ParsingContext.Create());
Assert.AreEqual((value1 + value2 + value3 + value4 + value5 + value6) / 6, result.Result);
}
public void AverageUnparsableLiteral()
{
// In the case of literals, any unparsable string literal results in a #VALUE.
Average average = new Average();
var result = average.Execute(new FunctionArgument[]
{
new FunctionArgument(1000),
new FunctionArgument("Test")
}, ParsingContext.Create());
}
public BuiltInFunctions()
{
// Text
Functions["len"] = new Len();
Functions["lower"] = new Lower();
Functions["upper"] = new Upper();
Functions["left"] = new Left();
Functions["right"] = new Right();
Functions["mid"] = new Mid();
Functions["replace"] = new Replace();
Functions["rept"] = new Rept();
Functions["substitute"] = new Substitute();
Functions["concatenate"] = new Concatenate();
Functions["char"] = new CharFunction();
Functions["exact"] = new Exact();
Functions["find"] = new Find();
Functions["fixed"] = new Fixed();
Functions["proper"] = new Proper();
Functions["text"] = new Text.Text();
Functions["t"] = new T();
Functions["hyperlink"] = new Hyperlink();
// Numbers
Functions["int"] = new CInt();
// Math
Functions["abs"] = new Abs();
Functions["asin"] = new Asin();
Functions["asinh"] = new Asinh();
Functions["cos"] = new Cos();
Functions["cosh"] = new Cosh();
Functions["power"] = new Power();
Functions["sign"] = new Sign();
Functions["sqrt"] = new Sqrt();
Functions["sqrtpi"] = new SqrtPi();
Functions["pi"] = new Pi();
Functions["product"] = new Product();
Functions["ceiling"] = new Ceiling();
Functions["count"] = new Count();
Functions["counta"] = new CountA();
Functions["countblank"] = new CountBlank();
Functions["countif"] = new CountIf();
Functions["countifs"] = new CountIfs();
Functions["fact"] = new Fact();
Functions["floor"] = new Floor();
Functions["sin"] = new Sin();
Functions["sinh"] = new Sinh();
Functions["sum"] = new Sum();
Functions["sumif"] = new SumIf();
Functions["sumifs"] = new SumIfs();
Functions["sumproduct"] = new SumProduct();
Functions["sumsq"] = new Sumsq();
Functions["stdev"] = new Stdev();
Functions["stdevp"] = new StdevP();
Functions["stdev.s"] = new Stdev();
Functions["stdev.p"] = new StdevP();
Functions["subtotal"] = new Subtotal();
Functions["exp"] = new Exp();
Functions["log"] = new Log();
Functions["log10"] = new Log10();
Functions["ln"] = new Ln();
Functions["max"] = new Max();
Functions["maxa"] = new Maxa();
Functions["median"] = new Median();
Functions["min"] = new Min();
Functions["mina"] = new Mina();
Functions["mod"] = new Mod();
Functions["average"] = new Average();
Functions["averagea"] = new AverageA();
Functions["averageif"] = new AverageIf();
Functions["averageifs"] = new AverageIfs();
Functions["round"] = new Round();
Functions["rounddown"] = new Rounddown();
Functions["roundup"] = new Roundup();
Functions["rand"] = new Rand();
Functions["randbetween"] = new RandBetween();
Functions["quotient"] = new Quotient();
Functions["trunc"] = new Trunc();
Functions["tan"] = new Tan();
Functions["tanh"] = new Tanh();
Functions["atan"] = new Atan();
Functions["atan2"] = new Atan2();
Functions["atanh"] = new Atanh();
Functions["acos"] = new Acos();
Functions["acosh"] = new Acosh();
Functions["var"] = new Var();
Functions["varp"] = new VarP();
Functions["large"] = new Large();
Functions["small"] = new Small();
Functions["degrees"] = new Degrees();
// Information
Functions["isblank"] = new IsBlank();
Functions["isnumber"] = new IsNumber();
Functions["istext"] = new IsText();
Functions["isnontext"] = new IsNonText();
Functions["iserror"] = new IsError();
Functions["iserr"] = new IsErr();
Functions["error.type"] = new ErrorType();
Functions["iseven"] = new IsEven();
Functions["isodd"] = new IsOdd();
Functions["islogical"] = new IsLogical();
Functions["isna"] = new IsNa();
Functions["na"] = new Na();
Functions["n"] = new N();
// Logical
Functions["if"] = new If();
Functions["iferror"] = new IfError();
Functions["ifna"] = new IfNa();
Functions["not"] = new Not();
Functions["and"] = new And();
Functions["or"] = new Or();
Functions["true"] = new True();
Functions["false"] = new False();
// Reference and lookup
Functions["address"] = new Address();
Functions["hlookup"] = new HLookup();
Functions["vlookup"] = new VLookup();
Functions["lookup"] = new Lookup();
Functions["match"] = new Match();
Functions["row"] = new Row(){SkipArgumentEvaluation = true};
Functions["rows"] = new Rows(){SkipArgumentEvaluation = true};
Functions["column"] = new Column(){SkipArgumentEvaluation = true};
Functions["columns"] = new Columns(){SkipArgumentEvaluation = true};
Functions["choose"] = new Choose();
Functions["index"] = new Index();
Functions["indirect"] = new Indirect();
Functions["offset"] = new Offset(){SkipArgumentEvaluation = true};
// Date
Functions["date"] = new Date();
Functions["today"] = new Today();
Functions["now"] = new Now();
Functions["day"] = new Day();
Functions["month"] = new Month();
Functions["year"] = new Year();
Functions["time"] = new Time();
Functions["hour"] = new Hour();
Functions["minute"] = new Minute();
Functions["second"] = new Second();
Functions["weeknum"] = new Weeknum();
Functions["weekday"] = new Weekday();
Functions["days360"] = new Days360();
Functions["yearfrac"] = new Yearfrac();
Functions["edate"] = new Edate();
Functions["eomonth"] = new Eomonth();
Functions["isoweeknum"] = new IsoWeekNum();
Functions["workday"] = new Workday();
// Database
Functions["dget"] = new Dget();
Functions["dcount"] = new Dcount();
Functions["dcounta"] = new DcountA();
Functions["dmax"] = new Dmax();
Functions["dmin"] = new Dmin();
Functions["dsum"] = new Dsum();
Functions["daverage"] = new Daverage();
Functions["dvar"] = new Dvar();
Functions["dvarp"] = new Dvarp();
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 3);
var funcNum = ArgToInt(arguments, 0);
var nToSkip = IsNumeric(arguments.ElementAt(1).Value) ? 2 : 1;
var options = nToSkip == 1 ? 0 : ArgToInt(arguments, 1);
if (options < 0 || options > 7)
{
return(CreateResult(eErrorType.Value));
}
if (IgnoreNestedSubtotalAndAggregate(options))
{
context.Scopes.Current.IsSubtotal = true;
}
CompileResult result = null;
switch (funcNum)
{
case 1:
var f1 = new Average()
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f1.Execute(arguments.Skip(nToSkip), context);
break;
case 2:
var f2 = new Count()
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f2.Execute(arguments.Skip(nToSkip), context);
break;
case 3:
var f3 = new CountA
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f3.Execute(arguments.Skip(nToSkip), context);
break;
case 4:
var f4 = new Max
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f4.Execute(arguments.Skip(nToSkip), context);
break;
case 5:
var f5 = new Min
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f5.Execute(arguments.Skip(nToSkip), context);
break;
case 6:
var f6 = new Product
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f6.Execute(arguments.Skip(nToSkip), context);
break;
case 7:
var f7 = new StdevDotS
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f7.Execute(arguments.Skip(nToSkip), context);
break;
case 8:
var f8 = new StdevDotP
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f8.Execute(arguments.Skip(nToSkip), context);
break;
case 9:
var f9 = new Sum
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f9.Execute(arguments.Skip(nToSkip), context);
break;
case 10:
VarDotS f10 = new VarDotS
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f10.Execute(arguments.Skip(nToSkip), context);
break;
case 11:
var f11 = new VarDotP
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f11.Execute(arguments.Skip(nToSkip), context);
break;
case 12:
var f12 = new Median
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f12.Execute(arguments.Skip(nToSkip), context);
break;
case 13:
var f13 = new ModeSngl
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f13.Execute(arguments.Skip(nToSkip), context);
break;
case 14:
var f14 = new Large
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
var a141 = arguments.ElementAt(nToSkip);
var a142 = arguments.ElementAt(nToSkip + 1);
result = f14.Execute(new List <FunctionArgument> {
a141, a142
}, context);
break;
case 15:
var f15 = new Small
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f15.Execute(new List <FunctionArgument> {
arguments.ElementAt(nToSkip), arguments.ElementAt(nToSkip + 1)
}, context);
break;
case 16:
var f16 = new PercentileInc
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f16.Execute(new List <FunctionArgument> {
arguments.ElementAt(nToSkip), arguments.ElementAt(nToSkip + 1)
}, context);
break;
case 17:
var f17 = new QuartileInc
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f17.Execute(new List <FunctionArgument> {
arguments.ElementAt(nToSkip), arguments.ElementAt(nToSkip + 1)
}, context);
break;
case 18:
var f18 = new PercentileExc
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f18.Execute(new List <FunctionArgument> {
arguments.ElementAt(nToSkip), arguments.ElementAt(nToSkip + 1)
}, context);
break;
case 19:
var f19 = new QuartileExc
{
IgnoreHiddenValues = IgnoreHidden(options),
IgnoreErrors = IgnoreErrors(options)
};
result = f19.Execute(new List <FunctionArgument> {
arguments.ElementAt(nToSkip), arguments.ElementAt(nToSkip + 1)
}, context);
break;
default:
result = CreateResult(eErrorType.Value);
break;
}
result.IsResultOfSubtotal = IgnoreNestedSubtotalAndAggregate(options);
return(result);
}
public void AverageShouldThrowDivByZeroExcelErrorValueIfEmptyArgs()
{
eErrorType errorType = eErrorType.Value;
var func = new Average();
var args = new FunctionArgument[0];
try
{
func.Execute(args, _parsingContext);
}
catch (ExcelErrorValueException e)
{
errorType = e.ErrorValue.Type;
}
Assert.AreEqual(eErrorType.Div0, errorType);
}
public void AverageShouldIgnoreHiddenFieldsIfIgnoreHiddenValuesIsTrue()
{
var expectedResult = (4d + 2d + 2d + 1d) / 4d;
var func = new Average();
func.IgnoreHiddenValues = true;
var args = FunctionsHelper.CreateArgs(FunctionsHelper.CreateArgs(4d, 2d), 5d, 2d, true);
args.ElementAt(1).SetExcelStateFlag(ExcelCellState.HiddenCell);
var result = func.Execute(args, _parsingContext);
Assert.AreEqual(expectedResult, result.Result);
}
public void AverageShouldCalculateCorrectResultWithEnumerableAndBoolMembers()
{
var expectedResult = (4d + 2d + 5d + 2d + 1d) / 5d;
var func = new Average();
var args = FunctionsHelper.CreateArgs(FunctionsHelper.CreateArgs(4d, 2d), 5d, 2d, true);
var result = func.Execute(args, _parsingContext);
Assert.AreEqual(expectedResult, result.Result);
}
public void AverageShouldCalculateCorrectResult()
{
var expectedResult = (4d + 2d + 5d + 2d) / 4d;
var func = new Average();
var args = FunctionsHelper.CreateArgs(4d, 2d, 5d, 2d);
var result = func.Execute(args, _parsingContext);
Assert.AreEqual(expectedResult, result.Result);
}