private double CalculateWithLookupRange(ExcelDataProvider.IRangeInfo range, string criteria, ExcelDataProvider.IRangeInfo sumRange, ParsingContext context) { var retVal = 0d; var nMatches = 0; foreach (var cell in range) { if (criteria != null && Evaluate(cell.Value, criteria)) { var or = cell.Row - range.Address._fromRow; var oc = cell.Column - range.Address._fromCol; if (sumRange.Address._fromRow + or <= sumRange.Address._toRow && sumRange.Address._fromCol + oc <= sumRange.Address._toCol) { var v = sumRange.GetOffset(or, oc); if (v is ExcelErrorValue) { throw (new ExcelErrorValueException((ExcelErrorValue)v)); } nMatches++; retVal += ConvertUtil.GetValueDouble(v, true); } } } return(Divide(retVal, nMatches)); }
/// <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)); } }
private double CalculateWithLookupRange(ExcelDataProvider.IRangeInfo argRange, string criteria, ExcelDataProvider.IRangeInfo sumRange, ParsingContext context) { var returnValue = 0d; var nMatches = 0; foreach (var cell in argRange) { if (criteria != null && Evaluate(cell.Value, criteria)) { var rowOffset = cell.Row - argRange.Address._fromRow; var columnOffset = cell.Column - argRange.Address._fromCol; if (sumRange.Address._fromRow + rowOffset <= sumRange.Address._toRow && sumRange.Address._fromCol + columnOffset <= sumRange.Address._toCol) { var val = sumRange.GetOffset(rowOffset, columnOffset); if (val is ExcelErrorValue) { ThrowExcelErrorValueException(((ExcelErrorValue)val)); } nMatches++; returnValue += ConvertUtil.GetValueDouble(val, true); } } } return(Divide(returnValue, nMatches)); }
private CompileResult CompileSingleCell(ExcelDataProvider.IRangeInfo result) { if (result.Address.Address == ExcelErrorValue.Values.Ref) { return(new CompileResult(eErrorType.Ref)); } var cell = result.FirstOrDefault(); if (cell == null) { return(CompileResult.Empty); } var factory = new CompileResultFactory(); var compileResult = factory.Create(cell.Value); if (_negate) { if (compileResult.IsNumeric) { compileResult = new CompileResult(compileResult.ResultNumeric * -1, compileResult.DataType); } else if (compileResult.DataType == DataType.String) { if (compileResult.ResultValue is string resultString && double.TryParse(resultString, out double resultDouble)) { compileResult = new CompileResult((resultDouble * -1).ToString(), compileResult.DataType); } else { compileResult = new CompileResult(eErrorType.Value); } } }
/// <summary> /// Builds a <see cref="CompileResult"/> based off the result and the expression configuration. /// </summary> /// <param name="result">The <see cref="ExcelDataProvider.IRangeInfo"/> result.</param> /// <returns>The processed result.</returns> protected CompileResult BuildResult(ExcelDataProvider.IRangeInfo result) { if (this.ResolveAsRange || result.Address.Rows > 1 || result.Address.Columns > 1) { return(new CompileResult(result, DataType.Enumerable)); } return(CompileSingleCell(result)); }
public LookupArguments(object searchedValue, string rangeAddress, int lookupIndex, int lookupOffset, bool rangeLookup, ExcelDataProvider.IRangeInfo rangeInfo) { SearchedValue = searchedValue; RangeAddress = rangeAddress; RangeInfo = rangeInfo; LookupIndex = lookupIndex; LookupOffset = lookupOffset; RangeLookup = rangeLookup; }
private CompileResult CompileSingleCell(ExcelDataProvider.IRangeInfo result) { var cell = result.First(); var factory = new CompileResultFactory(); var compileResult = factory.Create(cell.Value); if (_negate && compileResult.IsNumeric) { compileResult = new CompileResult(compileResult.ResultNumeric * -1, compileResult.DataType); } compileResult.IsHiddenCell = cell.IsHiddenRow; return(compileResult); }
private double CalculateSingleRange(ExcelDataProvider.IRangeInfo range, string expression, ParsingContext context) { var retVal = 0d; foreach (var candidate in range) { if (expression != null && IsNumeric(candidate.Value) && _evaluator.Evaluate(candidate.Value, expression) && IsNumeric(candidate.Value)) { if (candidate.IsExcelError) { throw (new ExcelErrorValueException((ExcelErrorValue)candidate.Value)); } retVal += candidate.ValueDouble; } } return(retVal); }
/// <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)); } }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { var functionArguments = arguments as FunctionArgument[] ?? arguments.ToArray(); ValidateArguments(functionArguments, 2); var range = functionArguments.ElementAt(0); var criteria = functionArguments.ElementAt(1).ValueFirst != null?ArgToString(functionArguments, 1) : null; double result = 0d; if (range.IsExcelRange) { ExcelDataProvider.IRangeInfo rangeInfo = range.ValueAsRangeInfo; for (int row = rangeInfo.Address.Start.Row; row < rangeInfo.Address.End.Row + 1; row++) { for (int col = rangeInfo.Address.Start.Column; col < rangeInfo.Address.End.Column + 1; col++) { if (criteria != null && Evaluate(rangeInfo.Worksheet.GetValue(row, col), criteria)) { result++; } } } } else if (range.Value is IEnumerable <FunctionArgument> ) { foreach (var arg in (IEnumerable <FunctionArgument>)range.Value) { if (Evaluate(arg.Value, criteria)) { result++; } } } else { if (Evaluate(range.Value, criteria)) { result++; } } return(CreateResult(result, DataType.Integer)); }
private double CalculateSingleRange(ExcelDataProvider.IRangeInfo range, string expression, ParsingContext context) { var returnValue = 0d; var nMatches = 0; foreach (var candidate in range) { if (expression != null && IsNumeric(candidate.Value) && Evaluate(candidate.Value, expression)) { if (candidate.IsExcelError) { ThrowExcelErrorValueException(((ExcelErrorValue)candidate.Value)); } returnValue += candidate.ValueDouble; nMatches++; } } return(Divide(returnValue, nMatches)); }
protected List <int> GetMatchIndexes(ExcelDataProvider.IRangeInfo rangeInfo, string searched) { var result = new List <int>(); var internalIndex = 0; for (var row = rangeInfo.Address._fromRow; row <= rangeInfo.Address._toRow; row++) { for (var col = rangeInfo.Address._fromCol; col <= rangeInfo.Address._toCol; col++) { var candidate = rangeInfo.GetValue(row, col); if (searched != null && Evaluate(candidate, searched)) { result.Add(internalIndex); } internalIndex++; } } return(result); }
private double CalculateWithSumRange(ExcelDataProvider.IRangeInfo range, string criteria, ExcelDataProvider.IRangeInfo sumRange, ParsingContext context) { var retVal = 0d; foreach (var cell in range) { if (criteria != default(string) && _evaluator.Evaluate(cell.Value, criteria)) { var rowOffset = cell.Row - range.Address._fromRow; var columnOffset = cell.Column - range.Address._fromCol; if (sumRange.Address._fromRow + rowOffset <= sumRange.Address._toRow && sumRange.Address._fromCol + columnOffset <= sumRange.Address._toCol) { var val = sumRange.GetOffset(rowOffset, columnOffset); if (val is ExcelErrorValue) { ThrowExcelErrorValueException((ExcelErrorValue)val); } retVal += ConvertUtil.GetValueDouble(val, true); } } } return(retVal); }
protected virtual IEnumerable <double> ArgsToDoubleEnumerableZeroPadded(bool ignoreHiddenCells, ExcelDataProvider.IRangeInfo rangeInfo, ParsingContext context) { var startRow = rangeInfo.Address.Start.Row; var endRow = rangeInfo.Address.End.Row; var funcArg = new FunctionArgument(rangeInfo); var result = ArgsToDoubleEnumerable(ignoreHiddenCells, new List <FunctionArgument> { funcArg }, context); var dict = new Dictionary <int, double>(); result.ToList().ForEach(x => dict.Add(x.CellRow.Value, x.Value)); var resultList = new List <double>(); for (var row = startRow; row <= endRow; row++) { if (dict.ContainsKey(row)) { resultList.Add(dict[row]); } else { resultList.Add(0d); } } return(resultList); }
private CompileResult InternalCompile(ExcelDataProvider.IRangeInfo offsetRange1, ExcelDataProvider.IRangeInfo offsetRange2) { throw new NotImplementedException(); }
private CompileResult InternalCompile(string address, ExcelDataProvider.IRangeInfo offsetRange) { throw new NotImplementedException(); }