/**
* @return zero based index
*/
private static int FindIndexOfValue(ValueEval lookupValue, ValueVector lookupRange,
bool matchExact, bool FindLargestLessThanOrEqual)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue, matchExact);
int size = lookupRange.Size;
if (matchExact)
{
for (int i = 0; i < size; i++)
{
if (lookupComparer.CompareTo(lookupRange.GetItem(i)).IsEqual)
{
return(i);
}
}
throw new EvaluationException(ErrorEval.NA);
}
if (FindLargestLessThanOrEqual)
{
//in case the lookupRange area is not sorted, still try to get the right index
if (lookupValue is NumericValueEval nve)
{
Dictionary <int, double> dicDeltas = new Dictionary <int, double>();
for (int i = 0; i < size; i++)
{
var item = lookupRange.GetItem(i) as NumericValueEval;
if (lookupComparer.CompareTo(item).IsEqual)
{
return(i);
}
else
{
dicDeltas.Add(i, item.NumberValue - nve.NumberValue);
}
}
return(dicDeltas.Where(kv => kv.Value < 0).OrderByDescending(kv => kv.Value).First().Key);
}
// Note - backward iteration
for (int i = size - 1; i >= 0; i--)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsTypeMismatch)
{
continue;
}
if (!cmp.IsLessThan)
{
return(i);
}
}
throw new EvaluationException(ErrorEval.NA);
}
// else - Find smallest greater than or equal to
// TODO - Is binary search used for (match_type==+1) ?
for (int i = 0; i < size; i++)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsEqual)
{
return(i);
}
if (cmp.IsGreaterThan)
{
if (i < 1)
{
throw new EvaluationException(ErrorEval.NA);
}
return(i - 1);
}
}
return(size - 1);
}
/**
* Finds last (greatest index) matching occurrence of specified value.
* @param lookupComparer the value to be found in column or row vector
* @param vector the values to be searched. For VLOOKUP this is the first column of the
* tableArray. For HLOOKUP this is the first row of the tableArray.
* @param matchMode
* @return zero based index into the vector, -1 if value cannot be found
*/
private static int lookupLastIndexOfValue(LookupValueComparer lookupComparer, ValueVector vector,
MatchMode matchMode)
{
// find last occurrence of lookup value
int size = vector.Size;
int bestMatchIdx = -1;
ValueEval bestMatchEval = null;
for (int i = size - 1; i >= 0; i--)
{
ValueEval valueEval = vector.GetItem(i);
CompareResult result = lookupComparer.CompareTo(valueEval);
if (result.IsEqual)
{
return(i);
}
switch (matchMode)
{
case MatchMode.ExactMatchFallbackToLargerValue:
if (result.IsLessThan)
{
if (bestMatchEval == null)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
else
{
LookupValueComparer matchComparer = CreateTolerantLookupComparer(valueEval, true, true);
if (matchComparer.CompareTo(bestMatchEval).IsLessThan)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
}
}
break;
case MatchMode.ExactMatchFallbackToSmallerValue:
if (result.IsGreaterThan)
{
if (bestMatchEval == null)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
else
{
LookupValueComparer matchComparer = CreateTolerantLookupComparer(valueEval, true, true);
if (matchComparer.CompareTo(bestMatchEval).IsGreaterThan)
{
bestMatchIdx = i;
bestMatchEval = valueEval;
}
}
}
break;
}
}
return(bestMatchIdx);
}
protected override CompareResult CompareSameType(ValueEval other)
{
NumberEval ne = (NumberEval)other;
return(CompareResult.ValueOf(_value.CompareTo(ne.NumberValue)));
}
protected override CompareResult CompareSameType(ValueEval other)
{
StringEval se = (StringEval)other;
return(CompareResult.ValueOf(String.Compare(_value, se.StringValue, StringComparison.OrdinalIgnoreCase)));
}