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