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); }