public static bool NotArray(this IEnumerable <ExcelValue> args, int index, ExcelValue.ArrayValue defaultValue, out ExcelValue.ArrayValue value)
{
value = null;
if (args.Count() > index && args.ElementAt(index) is ExcelValue.ArrayValue arr)
{
value = arr;
}
if (value == null)
{
value = defaultValue;
}
return(value == null);
}
private ExcelValue XLOOKUP(ExcelValue lookupValue, ExcelValue.ArrayValue lookupArray, ExcelValue.ArrayValue returnArray,
ExcelValue ifNotFound, int matchMode, int searchMode, string pattern)
{
Func <ExcelValue, int> comparer;
switch (matchMode)
{
case 0: // Exact match. If none found, return #N/A. This is the default.
case -1: // Exact match. If none found, return the next smaller item.
case 1: // Exact match. If none found, return the next larger item.
comparer = v => v.CompareTo(lookupValue);
break;
case 2: // A wildcard match where *, ?, and ~ have special meaning.
comparer = v => Regex.IsMatch(v.Text, pattern, RegexOptions.IgnoreCase) ? 0 : -1;
break;
default:
return(ExcelValue.VALUE);
}
IEnumerable <ExcelValue> lookupValues, returnValues;
if (searchMode > 0)
{
lookupValues = lookupArray.Values;
returnValues = returnArray.Values;
}
else
{
lookupValues = lookupArray.Values.Reverse();
returnValues = returnArray.Values.Reverse();
}
var expectSorted = Math.Abs(searchMode) == 2;
ExcelValue resultA = null;
ExcelValue resultB = null;
foreach (var pair in lookupValues.Zip(returnValues, (a, b) => new { A = a, B = b }))
{
var comparison = comparer(pair.A);
if (comparison == 0)
{
resultB = pair.B;
}
else if (comparison < 0 && matchMode == -1)
{
if (resultB == null || pair.A.CompareTo(resultA) > 0)
{
resultA = pair.A;
resultB = pair.B;
}
}
else if (comparison > 0 && matchMode == 1)
{
if (resultB == null || pair.A.CompareTo(resultA) < 0)
{
resultA = pair.A;
resultB = pair.B;
}
}
if (comparison == 0 || (expectSorted && comparison > 0))
{
break;
}
}
return(resultB ?? ifNotFound);
}
