public ExcelValue VLOOKUP(List <ExcelValue> args, ExpressionScope scope)
{
// VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
var argsCount = args.Count();
if (argsCount < 3)
{
return(ExcelValue.NA);
}
if (args.NotArray(1, null, out ExcelValue.ArrayValue tableArray))
{
return(ExcelValue.REF);
}
if (args.NotPosInteger(2, null, out int colNum))
{
return(ExcelValue.VALUE);
}
args.NotBoolean(3, true, out bool approxMatch);
if (!(tableArray.GetColumn(1) is ExcelValue.ArrayValue lookupVector))
{
return(ExcelValue.REF);
}
if (!(tableArray.GetColumn(colNum) is ExcelValue.ArrayValue resultVector))
{
return(ExcelValue.REF);
}
string pattern = null;
var matchMode = approxMatch ? -1 : (ExcelCriteria.IsRegex(args[0].Text, out pattern) ? 2 : 0);
return(XLOOKUP(args[0], lookupVector, resultVector, ExcelValue.NA, matchMode, 2, pattern));
}
public ExcelValue XLOOKUP(List <ExcelValue> args, ExpressionScope scope)
{
// XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
var argsCount = args.Count();
if (argsCount < 3)
{
return(ExcelValue.NA);
}
var lookupValue = args[0];
if (!lookupValue.SingleValue)
{
return(ExcelValue.VALUE);
}
if (args.NotArray(1, null, out ExcelValue.ArrayValue lookupArray))
{
return(ExcelValue.REF);
}
if (args.NotArray(2, null, out ExcelValue.ArrayValue returnArray))
{
return(ExcelValue.REF);
}
var ifNotFound = ExcelValue.NA;
if (argsCount > 3 && args[3] != null)
{
ifNotFound = args[3];
}
if (args.NotInteger(4, 0, out int matchMode))
{
return(ExcelValue.VALUE);
}
if (args.NotInteger(5, 1, out int searchMode))
{
return(ExcelValue.VALUE);
}
if (searchMode < -2 || searchMode > 2 || searchMode == 0)
{
return(ExcelValue.VALUE);
}
string pattern = null;
if (matchMode == 2)
{
if (!ExcelCriteria.IsRegex(lookupValue.Text, out pattern))
{
return(ExcelValue.VALUE);
}
}
return(XLOOKUP(lookupValue, lookupArray, returnArray, ifNotFound, matchMode, searchMode, pattern));
}
public ExcelValue HLOOKUP(List <ExcelValue> args, ExpressionScope scope)
{
// HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
if (args.Count() < 3)
{
return(ExcelValue.NA);
}
var lookupValue = args[0];
if (!lookupValue.SingleValue)
{
return(ExcelValue.VALUE);
}
if (args.NotArray(1, null, out ExcelValue.ArrayValue tableArray))
{
return(ExcelValue.REF);
}
if (args.NotPosInteger(2, null, out int rowNum))
{
return(ExcelValue.VALUE);
}
if (args.NotBoolean(3, true, out bool approximateMatch))
{
return(ExcelValue.VALUE);
}
if (!(tableArray.GetRow(1) is ExcelValue.ArrayValue lookupVector))
{
return(ExcelValue.REF);
}
if (!(tableArray.GetRow(rowNum) is ExcelValue.ArrayValue resultVector))
{
return(ExcelValue.REF);
}
string pattern = null;
var matchMode = approximateMatch ? -1 : (ExcelCriteria.IsRegex(args[0].Text, out pattern) ? 2 : 0);
return(XLOOKUP(args[0], lookupVector, resultVector, ExcelValue.NA, matchMode, 2, pattern));
}
public ExcelValue SUMIF(List <ExcelValue> args, ExpressionScope scope)
{
if (args.NotArray(0, null, out IEnumerable <ExcelValue> range))
{
return(ExcelValue.VALUE);
}
var criteria = args.Count > 1 ? args[1] : null;
if (criteria == null)
{
return(ExcelValue.NA);
}
if (args.NotArray(2, range, out IEnumerable <ExcelValue> sum_range))
{
return(ExcelValue.VALUE);
}
var selected = new List <ExcelValue>();
var filter = ExcelCriteria.Resolve(criteria, scope.OutLanguage);
foreach (var pair in range.Zip(sum_range, (a, b) => new { Predicate = a, Number = b }))
{
if (filter(pair.Predicate))
{
selected.Add(pair.Number);
}
}
var numbers = selected.FlattenNumbers(false);
if (numbers == null)
{
return(ExcelValue.VALUE);
}
return(new ExcelValue.DecimalValue(numbers.Sum(), scope.OutLanguage));
}