Beispiel #1
0
        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);
        }
Beispiel #2
0
 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();
 }
Beispiel #3
0
        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 MaxShouldIgnoreHiddenValuesIfIgnoreHiddenValuesIsTrue()
 {
     var func = new Max();
     func.IgnoreHiddenValues = true;
     var args = FunctionsHelper.CreateArgs(4, 2, 5, 2);
     args.ElementAt(2).SetExcelStateFlag(ExcelCellState.HiddenCell);
     var result = func.Execute(args, _parsingContext);
     Assert.AreEqual(4d, result.Result);
 }
 public void MaxShouldCalculateCorrectResult()
 {
     var func = new Max();
     var args = FunctionsHelper.CreateArgs(4, 2, 5, 2);
     var result = func.Execute(args, _parsingContext);
     Assert.AreEqual(5d, result.Result);
 }