protected MultipleRangeCriteriasFunction(NumericExpressionEvaluator evaluator, WildCardValueMatcher wildCardValueMatcher)
{
Require.That(evaluator).Named("evaluator").IsNotNull();
Require.That(wildCardValueMatcher).Named("wildCardValueMatcher").IsNotNull();
_numericExpressionEvaluator = evaluator;
_wildCardValueMatcher = wildCardValueMatcher;
}
public CountIf(NumericExpressionEvaluator evaluator, WildCardValueMatcher wildCardValueMatcher)
{
Require.That(evaluator).Named("evaluator").IsNotNull();
Require.That(wildCardValueMatcher).Named("wildCardValueMatcher").IsNotNull();
_numericExpressionEvaluator = evaluator;
_wildCardValueMatcher = wildCardValueMatcher;
}
/// <summary>
/// Gets a value indicating if the given input string matches the regular expression.
/// </summary>
/// <param name="input">The string to find the patter in.</param>
/// <param name="isNumericType">A flag indicating if the input string is a numerical value.</param>
/// <returns>True if a match is found; otherwise false.</returns>
public bool MatchesFilterCriteriaResult(string input, bool isNumericType)
{
bool match = false;
string filterValue = this.HasCustomFilters ? this.CustomFilters.First().TopOrBottomValue : this.Filters.First().TopOrBottomValue;
if (this.FieldFilterType == FieldFilter.Label)
{
bool hasWildcard = filterValue.Contains("*") || filterValue.Contains("?");
filterValue = new WildCardValueMatcher().ExcelWildcardToRegex(filterValue);
var matches = Regex.Match(input, filterValue, RegexOptions.IgnoreCase);
if (this.LabelFilterType == LabelFilterType.CaptionEqual ||
this.LabelFilterType == LabelFilterType.CaptionBeginsWith ||
this.LabelFilterType == LabelFilterType.CaptionEndsWith ||
this.LabelFilterType == LabelFilterType.CaptionContains ||
this.LabelFilterType == LabelFilterType.CaptionLessThanOrEqual)
{
match = matches == Match.Empty ? false : true;
}
else if (this.LabelFilterType == LabelFilterType.CaptionNotEqual ||
this.LabelFilterType == LabelFilterType.CaptionNotBeginsWith ||
this.LabelFilterType == LabelFilterType.CaptionNotEndsWith ||
this.LabelFilterType == LabelFilterType.CaptionNotContains ||
this.LabelFilterType == LabelFilterType.CaptionGreaterThan)
{
match = matches == Match.Empty ? true : false;
}
if (this.LabelFilterType == LabelFilterType.CaptionEqual || this.LabelFilterType == LabelFilterType.CaptionBeginsWith)
{
match = this.CheckFilterAtStartOfString(filterValue, input, match, false, true);
}
else if (this.LabelFilterType == LabelFilterType.CaptionNotEqual || this.LabelFilterType == LabelFilterType.CaptionNotBeginsWith)
{
match = this.CheckFilterAtStartOfString(filterValue, input, match, true, true);
}
else if (this.LabelFilterType == LabelFilterType.CaptionEndsWith)
{
match = this.CheckFilterAtStartOfString(filterValue, input, match, false, false);
}
else if (this.LabelFilterType == LabelFilterType.CaptionNotEndsWith)
{
match = this.CheckFilterAtStartOfString(filterValue, input, match, true, false);
}
else if (this.LabelFilterType == LabelFilterType.CaptionBetween || this.LabelFilterType == LabelFilterType.CaptionNotBetween)
{
match = this.SatisfiesBetweenAndNotBetweenLabelFilter(input, filterValue, hasWildcard, isNumericType);
}
else
{
match = this.SatisfiesInequalityLabelFilter(input, filterValue, match, hasWildcard, isNumericType);
}
}
return(match);
}
/// <summary>
/// Executes the function with the specified <paramref name="arguments"/> in the specified <paramref name="context"/>.
/// </summary>
/// <param name="arguments">The arguments with which to evaluate the function.</param>
/// <param name="context">The context in which to evaluate the function.</param>
/// <returns>An address range <see cref="CompileResult"/> if successful, otherwise an error result.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentsAreValid(arguments, 2, out eErrorType argumentError) == false)
{
return(new CompileResult(argumentError));
}
if (!this.TryGetSearchValue(arguments.ElementAt(0), context, out var searchValue, out var error))
{
return(error);
}
var lookupRange = arguments.ElementAt(1).ValueAsRangeInfo;
var matchType = this.GetMatchType(arguments);
var args = new LookupArguments(searchValue, lookupRange.Address.Address, 0, 0, false, lookupRange);
var lookupDirection = this.GetLookupDirection(lookupRange.Address);
var navigator = LookupNavigatorFactory.Create(lookupDirection, args, context);
int?lastValidIndex = null;
do
{
if (navigator.CurrentValue == null && searchValue == null)
{
return(this.CreateResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError));
}
int?matchResult;
if (matchType == MatchType.ExactMatch)
{
matchResult = new WildCardValueMatcher().IsMatch(searchValue, navigator.CurrentValue);
}
else
{
matchResult = new LookupValueMatcher().IsMatch(searchValue, navigator.CurrentValue);
}
// For all match types, if the match result indicated equality, return the index (1 based)
if (matchResult == 0)
{
return(this.CreateResult(navigator.Index + 1, DataType.Integer));
}
if ((matchType == MatchType.ClosestBelow && matchResult > 0) || (matchType == MatchType.ClosestAbove && matchResult < 0))
{
lastValidIndex = navigator.Index + 1;
}
// If matchType is ClosestBelow or ClosestAbove and the match result test failed, no more searching is required
else if (matchType == MatchType.ClosestBelow || matchType == MatchType.ClosestAbove)
{
break;
}
}while (navigator.MoveNext());
if (lastValidIndex == null)
{
return(this.CreateResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError));
}
return(this.CreateResult(lastValidIndex, DataType.Integer));
}
protected IEnumerable <double> GetMatches(string functionName, IEnumerable <FunctionArgument> arguments, out CompileResult errorResult)
{
ValidateArguments(arguments, 3);
errorResult = null;
var maxRange = arguments.ElementAt(0).ValueAsRangeInfo;
var maxArgs = arguments.Count() < (126 * 2 + 1) ? arguments.Count() : 126 * 2 + 1;
var valueMatcher = new WildCardValueMatcher();
var matches = new List <double>();
var rangeSizeEvaluated = false;
for (var valueIx = 0; valueIx < maxRange.Count(); valueIx++)
{
var isMatch = true;
for (var criteriaIx = 1; criteriaIx < maxArgs; criteriaIx += 2)
{
var criteriaRange = arguments.ElementAt(criteriaIx).ValueAsRangeInfo;
if (!rangeSizeEvaluated)
{
if (criteriaRange.Count() < maxRange.Count())
{
errorResult = CreateResult(eErrorType.Value);
return(Enumerable.Empty <double>());
}
}
var matchCriteria = arguments.ElementAt(criteriaIx + 1).Value;
var candidate = criteriaRange.ElementAt(valueIx).Value;
if (valueMatcher.IsMatch(matchCriteria, candidate) != 0)
{
isMatch = false;
break;
}
}
rangeSizeEvaluated = true;
if (isMatch)
{
matches.Add(maxRange.ElementAt(valueIx).ValueDouble);
}
}
return(matches);
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
var functionArguments = arguments as FunctionArgument[] ?? arguments.ToArray();
if (this.ArgumentsAreValid(functionArguments, 2, out eErrorType argumentError) == false)
{
return(new CompileResult(argumentError));
}
var search = base.ArgToString(functionArguments, 0);
var searchIn = base.ArgToString(functionArguments, 1);
// Subtract 1 because Excel uses 1-based index
var startIndex = functionArguments.Count() > 2 ? base.ArgToInt(functionArguments, 2) - 1 : 0;
int result = -1;
// If the search string contains Excel wildcard values, convert it to regular expression values and find match.
if (search.Contains('~') || search.Contains('?') || search.Contains('*'))
{
var pattern = new WildCardValueMatcher().ExcelWildcardToRegex(search);
var matches = Regex.Match(searchIn.Substring(startIndex), pattern, RegexOptions.IgnoreCase);
if (matches == Match.Empty)
{
return(base.CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError));
}
else
{
result = matches.Index + startIndex;
}
}
// Otherwise, find the index of the search string.
else
{
result = searchIn.IndexOf(search, startIndex, System.StringComparison.OrdinalIgnoreCase);
if (result == -1)
{
return(base.CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError));
}
}
// Adding 1 because Excel uses 1-based index
return(base.CreateResult(result + 1, DataType.Integer));
}
public void Setup()
{
_matcher = new WildCardValueMatcher();
}
public RowMatcher(WildCardValueMatcher wildCardValueMatcher, ExpressionEvaluator expressionEvaluator)
{
_wildCardValueMatcher = wildCardValueMatcher;
_expressionEvaluator = expressionEvaluator;
}
public RowMatcher(WildCardValueMatcher wildCardValueMatcher, NumericExpressionEvaluator numericExpressionEvaluator)
{
_wildCardValueMatcher = wildCardValueMatcher;
_numericExpressionEvaluator = numericExpressionEvaluator;
}
