public static Func <ExcelValue, bool> Resolve(ExcelValue criteria, Language language)
{
Func <ExcelValue, bool> predicate = null;
if (criteria is ExcelValue.TextValue)
{
var text = criteria.Text;
if (!string.IsNullOrEmpty(text))
{
if (TryResolveLogical(text, language, out predicate))
{
return(predicate);
}
if (IsRegex(text, out string pattern))
{
return(v => Regex.IsMatch(v.Text, pattern, RegexOptions.IgnoreCase));
}
predicate = v => v.Text == text;
}
}
if (predicate == null && criteria.InnerValue != null)
{
predicate = v => criteria.InnerValue.Equals(v.InnerValue);
}
return(predicate);
}
public ExcelValue GetFromParent(ExcelValue path)
{
var parentName = ParentName;
if (parentName != null)
{
var parent = (IEnumerable <ExcelValue>)Get(parentName).InnerValue;
var values = parent.Select(item => Get(item, path));
return(new ExcelValue.ArrayValue(values, OutLanguage));
}
throw new InvalidOperationException();
}
public ExcelValue Get(ExcelValue key, ExcelValue path)
{
JObject target;
if (key is ExcelValue.JsonObjectValue)
{
target = (JObject)key.InnerValue;
}
else
{
target = (JObject)Get(key.Text).InnerValue;
}
return(ExcelValue.Create(target.SelectToken(path.Text), OutLanguage));
}
public ExcelValue TIME(List <ExcelValue> args, ExpressionScope scope)
{
if (args.NotInteger(0, null, out int hours))
{
return(ExcelValue.NA);
}
if (args.NotInteger(1, null, out int minutes))
{
return(ExcelValue.NA);
}
if (args.NotInteger(2, null, out int seconds))
{
return(ExcelValue.NA);
}
return(ExcelValue.CreateDateValue(0, 0, 0, hours, minutes, seconds, scope.OutLanguage, ExpressionFormat.ShortTimePattern));
}
public ExcelValue DATE(List <ExcelValue> args, ExpressionScope scope)
{
if (args.NotInteger(0, null, out int year))
{
return(ExcelValue.NA);
}
if (args.NotInteger(1, null, out int month))
{
return(ExcelValue.NA);
}
if (args.NotInteger(2, null, out int day))
{
return(ExcelValue.NA);
}
return(ExcelValue.CreateDateValue(year, month, day, scope.OutLanguage, ExpressionFormat.ShortDatePattern));
}
public ExcelValue YEAR(List <ExcelValue> args, ExpressionScope scope)
{
if (args.NotDecimal(0, null, out double serial))
{
return(ExcelValue.NA);
}
if (serial == 0)
{
return(new ExcelValue.DecimalValue(1900, scope.OutLanguage, ExpressionFormat.General));
}
var date = ExcelValue.FromDateSerial(serial);
if (date.HasValue)
{
return(new ExcelValue.DecimalValue(date.Value.Year, scope.OutLanguage, ExpressionFormat.General));
}
return(ExcelValue.VALUE);
}
public ExcelValue Get(string key)
{
if (sourceValues.ContainsKey(key))
{
return(sourceValues[key]);
}
if (sources.Any(o => o.Name == key || o.Cell == key))
{
return(ExcelValue.Create(sources.First(o => o.Name == key || o.Cell == key).Payload, OutLanguage));
}
if (values.ContainsKey(key))
{
return(values[key]);
}
if (key.Contains(':'))
{
return(GetRangeValues(key));
}
return(ExcelValue.NULL);
//throw new InvalidOperationException($"Name or cell {key} not found in scope.");
}
public ExcelValue EXACT(List <ExcelValue> args, ExpressionScope scope)
{
return(ExcelValue.CreateBoolean("=", args[0], args[1], false));
}
private void PerformComparisons()
{
ReplaceTriplet(part => part.IsComparisonOperator, (oper, a, b) => ExcelValue.CreateBoolean(oper, a, b));
}
public override int CompareTo(ExcelValue other) => other is DecimalValue ? ((double)InnerValue).CompareTo((double)other.InnerValue) : -1;
public override int CompareTo(ExcelValue other) => - 1;
public override int CompareTo(ExcelValue other) => other is ErrorValue ? -1 : (other is BooleanValue ? ((bool)InnerValue).CompareTo((bool)other.InnerValue) : 1);
public override int CompareTo(ExcelValue other) => other is DecimalValue ? 1 : (other is TextValue ? Text.CompareTo(other.Text): -1);
public override int CompareTo(ExcelValue other) => this == other ? 0 : 1;
public override int CompareTo(ExcelValue other) => this == other || other == NULL ? 0 : 1;
public ExcelValue DATEDIF(List <ExcelValue> args, ExpressionScope scope)
{
if (args.NotDecimal(0, null, out double serial1))
{
return(ExcelValue.VALUE);
}
if (args.NotDecimal(1, null, out double serial2))
{
return(ExcelValue.VALUE);
}
if (serial1 < 0 || serial2 < 0 || serial1 > serial2)
{
return(ExcelValue.VALUE);
}
var dv1 = ExcelValue.FromDateSerial(serial1);
if (!dv1.HasValue)
{
return(ExcelValue.NA);
}
var dv2 = ExcelValue.FromDateSerial(serial2);
if (!dv2.HasValue)
{
return(ExcelValue.NA);
}
if (args.NotText(2, null, scope.OutLanguage, out string unit))
{
return(ExcelValue.NA);
}
var d1 = dv1.Value;
var d2 = dv2.Value;
double result;
switch (unit.ToUpperInvariant())
{
case "Y":
result = d2.Year - d1.Year - (d2.Month >= d1.Month && d2.Day >= d1.Day ? 0 : 1); break;
case "M": result = 12 * (d2.Year - d1.Year) + (d2.Month - d1.Month); break;
case "D": result = Math.Round((d2 - d1).TotalDays); break;
case "MD": result = d2.Day - d1.Day; break;
case "YM":
result = d2.Month - d1.Month;
if (result < 0)
{
result += 12;
}
break;
case "YD":
var tmpD1 = new DateTime(d1.Year + (d2.Month >= d1.Month && d2.Day >= d1.Day ? 1 : 0), d1.Month, d1.Day);
result = Math.Round((d2 - tmpD1).TotalDays);
break;
default: return(ExcelValue.VALUE);
}
return(new ExcelValue.DecimalValue(result, scope.OutLanguage));
}
public void Set(string key, ExcelValue value)
{
values[key] = value;
}
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);
}
public ExcelValue NOW(List <ExcelValue> args, ExpressionScope scope)
{
var now = DateTime.Now;
return(ExcelValue.CreateDateValue(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second, scope.OutLanguage, ExpressionFormat.ShortDatePattern));
}
public ExcelExpressionPart(ExcelValue value)
{
this.value = value;
this.TokenType = ExcelFormulaTokenType.Operand;
this.Operator = null;
}
public ExcelValue TODAY(List <ExcelValue> args, ExpressionScope scope)
{
var today = DateTime.Today;
return(ExcelValue.CreateDateValue(today.Year, today.Month, today.Day, 0, 0, 0, scope.OutLanguage, ExpressionFormat.ShortDatePattern));
}