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