Пример #1
0
        /// <summary>
        /// Returns the average (arithmetic mean) of all the cells in a range that meet a given criterion.
        /// </summary>
        /// <param name="arguments">The arguments used to calculate the average.</param>
        /// <param name="context">The context for the function.</param>
        /// <returns>Returns the average of all cells in the given range that passed the given criterion.</returns>
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            if (this.ArgumentCountIsValid(arguments, 2) == false)
            {
                return(new CompileResult(eErrorType.Value));
            }
            var cellRangeToCheck = arguments.ElementAt(0).Value as ExcelDataProvider.IRangeInfo;

            if (cellRangeToCheck == null)
            {
                return(new CompileResult(eErrorType.Value));
            }
            var criterionObject = IfHelper.ExtractCriterionObject(arguments.ElementAt(1), context);

            if (arguments.Count() > 2)
            {
                var cellRangeToAverage = arguments.ElementAt(2).Value as ExcelDataProvider.IRangeInfo;
                if (cellRangeToAverage == null)
                {
                    return(new CompileResult(eErrorType.Value));
                }
                else
                {
                    return(this.CalculateAverageUsingAverageRange(cellRangeToCheck, criterionObject, cellRangeToAverage));
                }
            }
            else
            {
                return(this.CalculateAverageUsingRange(cellRangeToCheck, criterionObject));
            }
        }
Пример #2
0
        /// <summary>
        /// Returns the sum of all cells that meet multiple criteria.
        /// </summary>
        /// <param name="arguments">The arguments used to calculate the sum.</param>
        /// <param name="context">The context for the function.</param>
        /// <returns>Returns the sum of all cells in the given range that pass the given criteria.</returns>
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            if (!this.ArgumentsAreValid(arguments, 3, out eErrorType errorType))
            {
                return(new CompileResult(errorType));
            }
            var sumRange = arguments.ElementAt(0).Value as ExcelDataProvider.IRangeInfo;

            if (sumRange == null)
            {
                return(new CompileResult(0d, DataType.Decimal));
            }
            var indicesOfValidCells = new List <int>();

            for (var argumentIndex = 1; argumentIndex < arguments.Count(); argumentIndex += 2)
            {
                var currentRangeToCompare = arguments.ElementAt(argumentIndex).ValueAsRangeInfo;
                if (currentRangeToCompare == null || !IfHelper.RangesAreTheSameShape(sumRange, currentRangeToCompare))
                {
                    return(new CompileResult(eErrorType.Value));
                }
                var currentCriterion = IfHelper.ExtractCriterionObject(arguments.ElementAt(argumentIndex + 1), context);

                // This will always look at every cell in the given range of cells to compare. This is done instead of
                // using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
                // does not include empty cells that have not been set since the workbook's creation. This function
                // wants to consider empty cells for comparing with the criterion, but it can be better optimized.
                // A similar problem and optimization opportunity exists in the AverageIf, AverageIfs, SumIf, CountIf, and CountIfs functions.
                var passingIndices = IfHelper.GetIndicesOfCellsPassingCriterion(currentRangeToCompare, currentCriterion);
                if (argumentIndex == 1)
                {
                    indicesOfValidCells = passingIndices;
                }
                else
                {
                    indicesOfValidCells = indicesOfValidCells.Intersect(passingIndices).ToList();
                }
            }
            double sumOfValidValues = 0d;

            if (sumRange.Count() > 0)
            {
                // Again, all cells, including empty cells, need to be available here.
                // The IRangeInfo will only provide non-empty cells.
                var allSumValues = sumRange.AllValues();
                foreach (var cellIndex in indicesOfValidCells)
                {
                    var currentCellValue = allSumValues.ElementAt(cellIndex);
                    if (currentCellValue is ExcelErrorValue cellError)
                    {
                        return(new CompileResult(cellError.Type));
                    }
                    else if (ConvertUtil.IsNumeric(currentCellValue, true))
                    {
                        sumOfValidValues += ConvertUtil.GetValueDouble(currentCellValue);
                    }
                }
            }
            return(this.CreateResult(sumOfValidValues, DataType.Decimal));
        }
Пример #3
0
        /// <summary>
        /// Compares the given <paramref name="testObject"/> against the given <paramref name="rawCriterionObject"/>.
        /// This method is expected to be used with any of the *IF or *IFS Excel functions (ex: the AVERAGEIF function)
        /// for comparing an object against a criterion. See the documentation for the any of the *IF or *IFS functions
        /// for information on acceptable forms of the criterion.
        /// </summary>
        /// <param name="testObject">The object to compare against the given <paramref name="rawCriterionObject"/>.</param>
        /// <param name="rawCriterionObject">The criterion value or expression that dictates whether the given <paramref name="testObject"/> passes or fails.</param>
        /// <returns>Returns true if <paramref name="testObject"/> matches the <paramref name="rawCriterionObject"/>.</returns>
        public static bool ObjectMatchesCriterion(object testObject, object rawCriterionObject)
        {
            object       criterion             = rawCriterionObject;
            OperatorType criterionOperator     = OperatorType.Equals;
            bool         criterionIsExpression = false;

            if (rawCriterionObject is string rawCriterionString)
            {
                string criterionString;
                if (IfHelper.TryParseCriterionAsExpression(rawCriterionString, out IOperator expressionOperator, out string expressionCriterion))
                {
                    criterionOperator     = expressionOperator.Operator;
                    criterionString       = expressionCriterion;
                    criterionIsExpression = true;
                }
                else
                {
                    criterionString = rawCriterionString.ToUpper(CultureInfo.CurrentCulture);
                }

                if (IfHelper.TryParseCriterionStringToObject(criterionString, out object criterionObject))
                {
                    criterion = criterionObject;
                }
                else
                {
                    criterion = criterionString;
                }
            }
Пример #4
0
        /// <summary>
        /// Calculates the average value of all cells that match the given criterion. The sizes/shapes of
        /// <paramref name="cellsToCompare"/> and <paramref name="potentialCellsToAverage"/> do not have to be the same;
        /// The size and shape of <paramref name="cellsToCompare"/> is applied to <paramref name="potentialCellsToAverage"/>,
        /// using the first cell in <paramref name="potentialCellsToAverage"/> as a reference point.
        /// </summary>
        /// <param name="cellsToCompare">The range of cells to compare against the <paramref name="comparisonCriterion"/>.</param>
        /// <param name="comparisonCriterion">The criterion dictating which cells should be included in the average calculation.</param>
        /// <param name="potentialCellsToAverage">
        ///		If a cell in <paramref name="cellsToCompare"/> passes the criterion, then its
        ///		corresponding cell in this cell range will be included in the average calculation.</param>
        /// <returns>Returns the average for all cells that pass the <paramref name="comparisonCriterion"/>.</returns>
        private CompileResult CalculateAverageUsingAverageRange(ExcelDataProvider.IRangeInfo cellsToCompare, object comparisonCriterion, ExcelDataProvider.IRangeInfo potentialCellsToAverage)
        {
            var sumOfValidValues    = 0d;
            var numberOfValidValues = 0;

            var startingRowForComparison    = cellsToCompare.Address._fromRow;
            var startingColumnForComparison = cellsToCompare.Address._fromCol;
            var endingRowForComparison      = cellsToCompare.Address._toRow;
            var endingColumnForComparison   = cellsToCompare.Address._toCol;

            // This will always look at every cell in the given range of cells to compare. This is done instead of
            // using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
            // does not include empty cells that have not been set since the workbook's creation. This function
            // wants to consider empty cells for comparing with the criterion, but it can be better optimized.
            // A similar problem and optimization opportunity exists in the AverageIfs, SumIf, SumIfs, CountIf, and CountIfs functions.
            for (var currentRow = startingRowForComparison; currentRow <= endingRowForComparison; currentRow++)
            {
                for (var currentColumn = startingColumnForComparison; currentColumn <= endingColumnForComparison; currentColumn++)
                {
                    var currentCellValue = this.GetFirstArgument(cellsToCompare.GetValue(currentRow, currentColumn));
                    if (IfHelper.ObjectMatchesCriterion(currentCellValue, comparisonCriterion))
                    {
                        var relativeRow          = currentRow - startingRowForComparison;
                        var relativeColumn       = currentColumn - startingColumnForComparison;
                        var valueOfCellToAverage = potentialCellsToAverage.GetOffset(relativeRow, relativeColumn);
                        if (valueOfCellToAverage is ExcelErrorValue cellError)
                        {
                            return(new CompileResult(cellError.Type));
                        }
                        else if (ConvertUtil.IsNumeric(valueOfCellToAverage, true))
                        {
                            sumOfValidValues += ConvertUtil.GetValueDouble(valueOfCellToAverage);
                            numberOfValidValues++;
                        }
                    }
                }
            }
            if (numberOfValidValues == 0)
            {
                return(new CompileResult(eErrorType.Div0));
            }
            else
            {
                return(this.CreateResult(sumOfValidValues / numberOfValidValues, DataType.Decimal));
            }
        }
Пример #5
0
        /// <summary>
        /// This function applies criteria to cells across multiple ranges and counts the number of times
        /// all criteria are met. If multiple cell ranges are being compared against criteria, all ranges must
        /// have the same number of rows and columns as the first given range, but the ranges do not have to be
        /// adjacent to each other.
        /// </summary>
        /// <param name="arguments">The arguments being evaluated.</param>
        /// <param name="context">The context for this function.</param>
        /// <returns>Returns the number of times all criteria are met across a row of cells.</returns>
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            if (this.ArgumentCountIsValid(arguments, 2) == false)
            {
                return(new CompileResult(eErrorType.Value));
            }
            var firstRangeToCompare = arguments.ElementAt(0).ValueAsRangeInfo;

            if (firstRangeToCompare == null)
            {
                return(new CompileResult(eErrorType.Value));
            }
            var indicesOfValidCells = new List <int>();

            for (var argumentIndex = 0; argumentIndex < arguments.Count(); argumentIndex += 2)
            {
                var currentRangeToCompare = arguments.ElementAt(argumentIndex).ValueAsRangeInfo;
                if (currentRangeToCompare == null || !IfHelper.RangesAreTheSameShape(firstRangeToCompare, currentRangeToCompare))
                {
                    return(new CompileResult(eErrorType.Value));
                }
                var currentCriterion = IfHelper.ExtractCriterionObject(arguments.ElementAt(argumentIndex + 1), context);

                // This will always look at every cell in the given range of cells to compare. This is done instead of
                // using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
                // does not include empty cells that have not been set since the workbook's creation. This function
                // wants to consider empty cells for comparing with the criterion, but it can be better optimized.
                // A similar problem and optimization opportunity exists in the AverageIf, AverageIfs, SumIf, SumIfs, and CountIf functions.
                var passingIndices = IfHelper.GetIndicesOfCellsPassingCriterion(currentRangeToCompare, currentCriterion);
                if (argumentIndex == 0)
                {
                    indicesOfValidCells = passingIndices;
                }
                else
                {
                    indicesOfValidCells = indicesOfValidCells.Intersect(passingIndices).ToList();
                }
            }
            double count = indicesOfValidCells.Count();

            return(this.CreateResult(count, DataType.Integer));
        }
Пример #6
0
        /// <summary>
        /// Count the number of cells in a cell range that meet a criterion.
        /// </summary>
        /// <param name="arguments">The arguments used to calculate the number of valid cells.</param>
        /// <param name="context">The context for this function.</param>
        /// <returns>Returns the number of cells in the cell range that meet the criterion.</returns>
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            if (this.ArgumentCountIsValid(arguments, 2) == false)
            {
                return(new CompileResult(eErrorType.Value));
            }
            var cellRangeToCheck = arguments.ElementAt(0).Value as ExcelDataProvider.IRangeInfo;

            if (cellRangeToCheck == null)
            {
                return(new CompileResult(eErrorType.Value));
            }

            // This will always look at every cell in the given range of cells to compare. This is done instead of
            // using the iterator provided by the range of cells to compare because the collection of cells that it iterates over
            // does not include empty cells that have not been set since the workbook's creation. This function
            // wants to consider empty cells for comparing with the criterion, but it can be better optimized.
            // A similar problem and optimization opportunity exists in the AverageIf, AverageIfs, SumIf, SumIfs, and CountIfs functions.
            var    criteriaObject = IfHelper.ExtractCriterionObject(arguments.ElementAt(1), context);
            double count          = cellRangeToCheck.AllValues().Where(cellValue => IfHelper.ObjectMatchesCriterion(cellValue, criteriaObject)).Count();

            return(this.CreateResult(count, DataType.Integer));
        }
Пример #7
0
        /// <summary>
        /// Calculates the average value of all cells that match the given criterion.
        /// </summary>
        /// <param name="potentialCellsToAverage">
        ///		The cell range to compare against the given <paramref name="comparisonCriterion"/>
        ///		If a cell passes the criterion, then its value is included in the average calculation.</param>
        /// <param name="comparisonCriterion">The criterion dictating which cells should be included in the average calculation.</param>
        /// <returns>Returns the average value for all cells that pass the <paramref name="comparisonCriterion"/>.</returns>
        private CompileResult CalculateAverageUsingRange(ExcelDataProvider.IRangeInfo potentialCellsToAverage, object comparisonCriterion)
        {
            var sumOfValidValues    = 0d;
            var numberOfValidValues = 0;
            var valuesToAverage     = potentialCellsToAverage.Select(cell => this.GetFirstArgument(cell.Value)).Where(cellValue => IfHelper.ObjectMatchesCriterion(cellValue, comparisonCriterion));

            foreach (var value in valuesToAverage)
            {
                if (value is ExcelErrorValue cellErrorValue)
                {
                    return(new CompileResult(cellErrorValue.Type));
                }
                else if (ConvertUtil.IsNumeric(value, true))
                {
                    sumOfValidValues += ConvertUtil.GetValueDouble(value);
                    numberOfValidValues++;
                }
            }
            if (numberOfValidValues == 0)
            {
                return(new CompileResult(eErrorType.Div0));
            }
            else
            {
                return(this.CreateResult(sumOfValidValues / numberOfValidValues, DataType.Decimal));
            }
        }