static object CountIf(List <Expression> p) { CalcEngine ce = new CalcEngine(); var cnt = 0.0; var ienum = p[0] as IEnumerable; if (ienum != null) { var crit = (string)p[1].Evaluate(); foreach (var value in ienum) { if (!IsBlank(value)) { var exp = string.Format("{0}{1}", value, crit); if ((bool)ce.Evaluate(exp)) { cnt++; } } } } return(cnt); }
internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) { // safety... if (value == null) { return(false); } // if criteria is a number, straight comparison if (criteria is double) { if (value is Double) { return((double)value == (double)criteria); } Double dValue; return(Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria); } // convert criteria to string var cs = criteria as string; if (cs != null) { if (value is string && (value as string).Trim() == "") { return(cs == ""); } if (cs == "") { return(cs.Equals(value)); } // if criteria is an expression (e.g. ">20"), use calc engine if ((cs[0] == '=' && cs.IndexOfAny(new[] { '*', '?' }) < 0) || cs[0] == '<' || cs[0] == '>') { // build expression var expression = string.Format("{0}{1}", value, cs); // add quotes if necessary var pattern = @"([\w\s]+)(\W+)(\w+)"; var m = Regex.Match(expression, pattern); if (m.Groups.Count == 4) { double d; if (!double.TryParse(m.Groups[1].Value, out d) || !double.TryParse(m.Groups[3].Value, out d)) { expression = string.Format("\"{0}\"{1}\"{2}\"", m.Groups[1].Value, m.Groups[2].Value, m.Groups[3].Value); } } // evaluate return((bool)ce.Evaluate(expression)); } // if criteria is a regular expression, use regex if (cs.IndexOfAny(new[] { '*', '?' }) > -1) { if (cs[0] == '=') { cs = cs.Substring(1); } var pattern = Regex.Replace( cs, "(" + String.Join( "|", patternReplacements.Value.Values.Select(t => t.Item1)) + ")", m => patternReplacements.Value[m.Value].Item2); pattern = $"^{pattern}$"; return(Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase)); } // straight string comparison return(string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase)); } // should never get here? Debug.Assert(false, "failed to evaluate criteria in SumIf"); return(false); }
private static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) { // safety... if (value == null) { return(false); } // if criteria is a number, straight comparison if (criteria is double) { if (value is Double) { return((double)value == (double)criteria); } Double dValue; return(Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria); } // convert criteria to string var cs = criteria as string; if (!string.IsNullOrEmpty(cs)) { // if criteria is an expression (e.g. ">20"), use calc engine if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') { // build expression var expression = string.Format("{0}{1}", value, cs); // add quotes if necessary var pattern = @"(\w+)(\W+)(\w+)"; var m = Regex.Match(expression, pattern); if (m.Groups.Count == 4) { double d; if (!double.TryParse(m.Groups[1].Value, out d) || !double.TryParse(m.Groups[3].Value, out d)) { expression = string.Format("\"{0}\"{1}\"{2}\"", m.Groups[1].Value, m.Groups[2].Value, m.Groups[3].Value); } } // evaluate return((bool)ce.Evaluate(expression)); } // if criteria is a regular expression, use regex if (cs.IndexOf('*') > -1) { var pattern = cs.Replace(@"\", @"\\"); pattern = pattern.Replace(".", @"\"); pattern = pattern.Replace("*", ".*"); return(Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase)); } // straight string comparison return(string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase)); } // should never get here? Debug.Assert(false, "failed to evaluate criteria in SumIf"); return(false); }
internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) { // safety... if (value == null) { return(false); } // Excel treats TRUE and 1 as unequal, but LibreOffice treats them as equal. We follow Excel's convention if (criteria is Boolean b1) { return((value is Boolean b2) && b1.Equals(b2)); } if (value is Boolean) { return(false); } // if criteria is a number, straight comparison Double cdbl; if (criteria is Double dbl2) { cdbl = dbl2; } else if (criteria is Int32 i) { cdbl = i; // results of DATE function can be an integer } else if (criteria is DateTime dt) { cdbl = dt.ToOADate(); } else if (criteria is TimeSpan ts) { cdbl = ts.TotalDays; } else if (criteria is String cs) { if (value is string && (value as string).Trim() == "") { return(cs == ""); } if (cs == "") { return(cs.Equals(value)); } // if criteria is an expression (e.g. ">20"), use calc engine if ((cs[0] == '=' && cs.IndexOfAny(new[] { '*', '?' }) < 0) || cs[0] == '<' || cs[0] == '>') { // build expression var expression = string.Format("{0}{1}", value, cs); // add quotes if necessary var pattern = @"([\w\s]+)(\W+)(\w+)"; var m = Regex.Match(expression, pattern); if (m.Groups.Count == 4 && (!double.TryParse(m.Groups[1].Value, out double d) || !double.TryParse(m.Groups[3].Value, out d))) { expression = string.Format("\"{0}\"{1}\"{2}\"", m.Groups[1].Value, m.Groups[2].Value, m.Groups[3].Value); } // evaluate return((bool)ce.Evaluate(expression)); } // if criteria is a regular expression, use regex if (cs.IndexOfAny(new[] { '*', '?' }) > -1) { if (cs[0] == '=') { cs = cs.Substring(1); } var pattern = Regex.Replace( cs, "(" + String.Join( "|", patternReplacements.Value.Values.Select(t => t.Item1)) + ")", m => patternReplacements.Value[m.Value].Item2); pattern = $"^{pattern}$"; return(Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase)); } // straight string comparison if (value is string vs) { return(vs.Equals(cs, StringComparison.OrdinalIgnoreCase)); } else { return(string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase)); } } else { throw new NotImplementedException(); } Double vdbl; if (value is Double dbl) { vdbl = dbl; } else if (value is Int32 i) { vdbl = i; } else if (value is DateTime dt) { vdbl = dt.ToOADate(); } else if (value is TimeSpan ts) { vdbl = ts.TotalDays; } else if (value is String s) { if (!Double.TryParse(s, out vdbl)) { return(false); } } else { throw new NotImplementedException(); } return(Math.Abs(vdbl - cdbl) < Double.Epsilon); }
private static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) { // safety... if (value == null) { return false; } // if criteria is a number, straight comparison if (criteria is double) { return (double) value == (double) criteria; } // convert criteria to string var cs = criteria as string; if (!string.IsNullOrEmpty(cs)) { // if criteria is an expression (e.g. ">20"), use calc engine if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') { // build expression var expression = string.Format("{0}{1}", value, cs); // add quotes if necessary var pattern = @"(\w+)(\W+)(\w+)"; var m = Regex.Match(expression, pattern); if (m.Groups.Count == 4) { double d; if (!double.TryParse(m.Groups[1].Value, out d) || !double.TryParse(m.Groups[3].Value, out d)) { expression = string.Format("\"{0}\"{1}\"{2}\"", m.Groups[1].Value, m.Groups[2].Value, m.Groups[3].Value); } } // evaluate return (bool) ce.Evaluate(expression); } // if criteria is a regular expression, use regex if (cs.IndexOf('*') > -1) { var pattern = cs.Replace(@"\", @"\\"); pattern = pattern.Replace(".", @"\"); pattern = pattern.Replace("*", ".*"); return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); } // straight string comparison return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); } // should never get here? Debug.Assert(false, "failed to evaluate criteria in SumIf"); return false; }
static object CountIf(List<Expression> p) { CalcEngine ce = new CalcEngine(); var cnt = 0.0; var ienum = p[0] as IEnumerable; if (ienum != null) { var crit = (string)p[1].Evaluate(); foreach (var value in ienum) { if (!IsBlank(value)) { var exp = string.Format("{0}{1}", value, crit); if ((bool)ce.Evaluate(exp)) cnt++; } } } return cnt; }