Пример #1
0
        public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
        {
            double result;

            if (args.Length != 2)
            {
                return(ErrorEval.VALUE_INVALID);
            }

            try
            {
                double startDateAsNumber     = GetValue(args[0]);
                int    offsetInMonthAsNumber = (int)GetValue(args[1]);

                // resolve the arguments
                DateTime startDate = DateUtil.GetJavaDate(startDateAsNumber);

                DateTime resultDate = startDate.AddMonths(offsetInMonthAsNumber);
                result = DateUtil.GetExcelDate(resultDate);

                NumericFunction.CheckValue(result);
                return(new NumberEval(result));
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
        }
Пример #2
0
        public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
        {
            int nArgs = args.Length;

            if (nArgs < 2)
            {
                return(ErrorEval.VALUE_INVALID);
            }

            try
            {
                double rate = NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
                // convert tail arguments into an array of doubles
                ValueEval[] vargs = new ValueEval[args.Length - 1];
                Array.Copy(args, 1, vargs, 0, vargs.Length);
                double[] values = AggregateFunction.ValueCollector.CollectValues(vargs);

                double result = FinanceLib.npv(rate, values);
                NumericFunction.CheckValue(result);
                return(new NumberEval(result));
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
        }
Пример #3
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
        {
            double result;

            try
            {
                double d0    = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                double d1    = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
                double multi = Math.Pow(10d, d1);
                if (d0 < 0)
                {
                    result = -Math.Floor(-d0 * multi) / multi;
                }
                else
                {
                    result = Math.Floor(d0 * multi) / multi;
                }
                //result = Math.Floor(d0 * multi) / multi;
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
            return(new NumberEval(result));
        }
Пример #4
0
        public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
        {
            if (args.Length != 2)
            {
                return(ErrorEval.VALUE_INVALID);
            }

            try
            {
                double startDateAsNumber = NumericFunction.SingleOperandEvaluate(args[0], ec.RowIndex, ec.ColumnIndex);
                int    months            = (int)NumericFunction.SingleOperandEvaluate(args[1], ec.RowIndex, ec.ColumnIndex);

                // Excel treats date 0 as 1900-01-00; EOMONTH results in 1900-01-31
                if (startDateAsNumber >= 0.0 && startDateAsNumber < 1.0)
                {
                    startDateAsNumber = 1.0;
                }

                DateTime startDate = DateUtil.GetJavaDate(startDateAsNumber, false);
                //the month
                DateTime dtEnd = startDate.AddMonths(months);
                //next month
                dtEnd = dtEnd.AddMonths(1);
                //first day of next month
                dtEnd = new DateTime(dtEnd.Year, dtEnd.Month, 1);
                //last day of the month
                dtEnd = dtEnd.AddDays(-1);

                return(new NumberEval(DateUtil.GetExcelDate(dtEnd)));
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
        }
Пример #5
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
                                           ValueEval arg1)
        {
            double result;

            try
            {
                double d0    = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                double d1    = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
                double logE  = Math.Log(d0);
                double base1 = d1;
                if (base1 == Math.E)
                {
                    result = logE;
                }
                else
                {
                    result = logE / Math.Log(base1);
                }
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
            return(new NumberEval(result));
        }
Пример #6
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval serialNumVE, ValueEval returnTypeVE)
        {
            double serialNum = 0.0;

            try
            {
                serialNum = NumericFunction.SingleOperandEvaluate(serialNumVE, srcRowIndex, srcColumnIndex);
            }
            catch (EvaluationException)
            {
                return(ErrorEval.VALUE_INVALID);
            }
            //Calendar serialNumCalendar = new GregorianCalendar();
            //serialNumCalendar.setTime(DateUtil.GetJavaDate(serialNum, false));
            DateTime serialNumCalendar = DateUtil.GetJavaDate(serialNum, false);

            int returnType = 0;

            try
            {
                ValueEval ve = OperandResolver.GetSingleValue(returnTypeVE, srcRowIndex, srcColumnIndex);
                returnType = OperandResolver.CoerceValueToInt(ve);
            }
            catch (EvaluationException)
            {
                return(ErrorEval.NUM_ERROR);
            }

            if (returnType != 1 && returnType != 2)
            {
                return(ErrorEval.NUM_ERROR);
            }

            return(new NumberEval(getWeekNo(serialNumCalendar, returnType)));
        }
Пример #7
0
        public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
        {
            if (args.Length == 0 || args.Length > 2)
            {
                // Wrong number of arguments
                return(ErrorEval.VALUE_INVALID);
            }

            try
            {
                double[] values = AggregateFunction.ValueCollector.CollectValues(args[0]);
                double   guess;
                if (args.Length == 2)
                {
                    guess = NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
                }
                else
                {
                    guess = 0.1d;
                }
                double result = irr(values, guess);
                NumericFunction.CheckValue(result);
                return(new NumberEval(result));
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
        }
Пример #8
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
                                           ValueEval arg1)
        {
            double dn;

            try
            {
                ValueEval ve1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
                dn = OperandResolver.CoerceValueToDouble(ve1);
            }
            catch (EvaluationException)
            {
                // all errors in the second arg translate to #VALUE!
                return(ErrorEval.VALUE_INVALID);
            }
            if (dn < 0 || dn > 1)
            { // has to be percentage
                return(ErrorEval.NUM_ERROR);
            }

            double result;

            try
            {
                double[] ds = Npoi.Core.SS.Formula.Functions.AggregateFunction.ValueCollector.CollectValues(arg0);
                int      N  = ds.Length;

                if (N == 0 || N > 8191)
                {
                    return(ErrorEval.NUM_ERROR);
                }

                double n = (N - 1) * dn + 1;
                if (n == 1d)
                {
                    result = StatsLib.kthSmallest(ds, 1);
                }
                else if (n == N)
                {
                    result = StatsLib.kthLargest(ds, 1);
                }
                else
                {
                    int    k = (int)n;
                    double d = n - k;
                    result = StatsLib.kthSmallest(ds, k) + d
                             * (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k));
                }

                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }

            return(new NumberEval(result));
        }
Пример #9
0
        protected double Eval(ValueEval[] args, int srcCellRow, short srcCellCol)
        {
            if (args.Length != 1)
            {
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
            }
            double d = NumericFunction.SingleOperandEvaluate(args[0], srcCellRow, srcCellCol);

            return(Evaluate(d));
        }
Пример #10
0
        private bool CheckArgument(double aDouble)
        {
            NumericFunction.CheckValue(aDouble);

            // make sure that the number is positive
            if (aDouble < 0)
            {
                throw new EvaluationException(ErrorEval.NUM_ERROR);
            }

            return(true);
        }
Пример #11
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
        {
            double result;

            try
            {
                double d = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                result = Evaluate(d);
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
            return(new NumberEval(result));
        }
Пример #12
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
        {
            double result;

            try
            {
                double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
                double d2 = NumericFunction.SingleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
                result = Evaluate(GetYear(d0), (int)d1, (int)d2);
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
            return(new NumberEval(result));
        }
Пример #13
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
        {
            double result;

            try
            {
                double    d0     = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                double    d1     = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
                ValueEval ve     = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
                bool?     method = OperandResolver.CoerceValueToBoolean(ve, false);
                result = Evaluate(d0, d1);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
            return(new NumberEval(result));
        }
Пример #14
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
                                           ValueEval arg1)
        {
            double dn;

            try
            {
                ValueEval ve1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
                dn = OperandResolver.CoerceValueToDouble(ve1);
            }
            catch (EvaluationException)
            {
                // all errors in the second arg translate to #VALUE!
                return(ErrorEval.VALUE_INVALID);
            }
            // weird Excel behaviour on second arg
            if (dn < 1.0)
            {
                // values between 0.0 and 1.0 result in #NUM!
                return(ErrorEval.NUM_ERROR);
            }
            // all other values are rounded up to the next integer
            int k = (int)Math.Ceiling(dn);

            double result;

            try
            {
                double[] ds = Npoi.Core.SS.Formula.Functions.AggregateFunction.ValueCollector.CollectValues(arg0);
                if (k > ds.Length)
                {
                    return(ErrorEval.NUM_ERROR);
                }
                result = _isLarge ? StatsLib.kthLargest(ds, k) : StatsLib.kthSmallest(ds, k);
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }

            return(new NumberEval(result));
        }
Пример #15
0
        public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
        {
            // arguments/result for this function
            double mean       = 0;
            double x          = 0;
            bool   cumulative = ((BoolEval)arg2).BooleanValue;
            double result     = 0;

            try
            {
                x    = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                mean = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);

                // check for default result : excel implementation for 0,0
                // is different to Math Common.
                if (IsDefaultResult(x, mean))
                {
                    return(new NumberEval(DEFAULT_RETURN_RESULT));
                }
                // check the arguments : as per excel function def
                CheckArgument(x);
                CheckArgument(mean);

                // truncate x : as per excel function def
                if (cumulative)
                {
                    result = cumulativeProbability((int)x, mean);
                }
                else
                {
                    result = probability((int)x, mean);
                }

                // check the result
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }

            return(new NumberEval(result));
        }
Пример #16
0
        public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
                                  ValueEval arg2, ValueEval arg3)
        {
            double result;

            try
            {
                double rate = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
                double d1   = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
                double d2   = NumericFunction.SingleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
                double d3   = NumericFunction.SingleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex);
                result = Evaluate(rate, d1, d2, d3);
                NumericFunction.CheckValue(result);
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
            return(new NumberEval(result));
        }
Пример #17
0
        public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
                                  int srcColumnIndex)
        {
            if (args.Length < 2 || args.Length > 5)
            {
                return(ErrorEval.VALUE_INVALID);
            }
            try
            {
                bool pAbsRow, pAbsCol;

                int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
                int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);

                int refType;
                if (args.Length > 2 && args[2] != MissingArgEval.instance)
                {
                    refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
                }
                else
                {
                    refType = REF_ABSOLUTE; // this is also the default if parameter is not given
                }
                switch (refType)
                {
                case REF_ABSOLUTE:
                    pAbsRow = true;
                    pAbsCol = true;
                    break;

                case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
                    pAbsRow = true;
                    pAbsCol = false;
                    break;

                case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
                    pAbsRow = false;
                    pAbsCol = true;
                    break;

                case REF_RELATIVE:
                    pAbsRow = false;
                    pAbsCol = false;
                    break;

                default:
                    throw new EvaluationException(ErrorEval.VALUE_INVALID);
                }

                bool a1;
                if (args.Length > 3)
                {
                    ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
                    // TODO R1C1 style is not yet supported
                    a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
                }
                else
                {
                    a1 = true;
                }

                string sheetName;
                if (args.Length == 5)
                {
                    ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
                    sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
                }
                else
                {
                    sheetName = null;
                }

                CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
                StringBuilder sb   = new StringBuilder(32);
                if (sheetName != null)
                {
                    SheetNameFormatter.AppendFormat(sb, sheetName);
                    sb.Append('!');
                }
                sb.Append(ref1.FormatAsString());

                return(new StringEval(sb.ToString()));
            }
            catch (EvaluationException e)
            {
                return(e.GetErrorEval());
            }
        }