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