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