static void Main(string[] args)
{
CalcEngine.CalcEngine ce = new CalcEngine.CalcEngine();
bool nextFormula = true;
while (nextFormula)
{
Console.Write("Введите формулу > ");
string s = Console.ReadLine();
if (!string.IsNullOrEmpty(s))
{
var v = ce.Evaluate(s);
Console.WriteLine($"Результат = {v}");
Console.Write("Еще формула? Дд/Нн > ");
string k = Console.ReadLine();
switch (k.ToUpper())
{
case "Д":
case "Да":
Console.Clear();
break;
default:
nextFormula = false;
break;
}
}
}
}
public static void Register(CalcEngine ce)
{
//ce.RegisterFunction("ASC Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
//ce.RegisterFunction("BAHTTEXT Converts a number to text, using the ß (baht) currency format
ce.RegisterFunction("CHAR", 1, _Char); // Returns the character specified by the code number
//ce.RegisterFunction("CLEAN Removes all nonprintable characters from text
ce.RegisterFunction("CODE", 1, Code); // Returns a numeric code for the first character in a text string
ce.RegisterFunction("CONCATENATE", 1, int.MaxValue, Concat); // Joins several text items into one text item
//ce.RegisterFunction("DOLLAR Converts a number to text, using the $ (dollar) currency format
//ce.RegisterFunction("EXACT Checks to see if two text values are identical
ce.RegisterFunction("FIND", 2, 3, Find); //Finds one text value within another (case-sensitive)
//ce.RegisterFunction("FIXED Formats a number as text with a fixed number of decimals
//ce.RegisterFunction("JIS Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
ce.RegisterFunction("LEFT", 1, 2, Left); // LEFTB Returns the leftmost characters from a text value
ce.RegisterFunction("LEN", 1, Len); //, Returns the number of characters in a text string
ce.RegisterFunction("LOWER", 1, Lower); // Converts text to lowercase
ce.RegisterFunction("MID", 3, Mid); // Returns a specific number of characters from a text string starting at the position you specify
//ce.RegisterFunction("PHONETIC Extracts the phonetic (furigana) characters from a text string
ce.RegisterFunction("PROPER", 1, Proper); // Capitalizes the first letter in each word of a text value
ce.RegisterFunction("REPLACE", 4, Replace); // Replaces characters within text
ce.RegisterFunction("REPT", 2, Rept); // Repeats text a given number of times
ce.RegisterFunction("RIGHT", 1, 2, Right); // Returns the rightmost characters from a text value
ce.RegisterFunction("SEARCH", 2, Search); // Finds one text value within another (not case-sensitive)
ce.RegisterFunction("SUBSTITUTE", 3, 4, Substitute); // Substitutes new text for old text in a text string
ce.RegisterFunction("T", 1, T); // Converts its arguments to text
ce.RegisterFunction("TEXT", 2, _Text); // Formats a number and converts it to text
ce.RegisterFunction("TRIM", 1, Trim); // Removes spaces from text
ce.RegisterFunction("UPPER", 1, Upper); // Converts text to uppercase
ce.RegisterFunction("VALUE", 1, Value); // Converts a text argument to a number
}
public static void Register(CalcEngine ce)
{
ce.RegisterFunction("AND", 1, int.MaxValue, And);
ce.RegisterFunction("OR", 1, int.MaxValue, Or);
ce.RegisterFunction("NOT", 1, Not);
ce.RegisterFunction("IF", 3, If);
ce.RegisterFunction("TRUE", 0, True);
ce.RegisterFunction("FALSE", 0, False);
}
/// <summary>
/// Hàm tính toán công thức
/// </summary>
/// <param name="formula">Chuổi công thức - Vd: "basicsalary * workdaycount"</param>
/// <param name="listElementFormula">
/// Danh sách đối tượng phần tử
/// Vd: List<ElementFormula> listElementFormula = new List<ElementFormula>();
/// lstElementFormula.Add(new ElementFormula("workdaycount", 82, 1));
/// lstElementFormula.Add(new ElementFormula("basicsalary", 5200000, 2));
/// <returns></returns>
//public static object ParseFormula(string formula, List<ElementFormula> listElementFormula)
//{
// object result = 0;
// listElementFormula = listElementFormula.OrderBy(v => v.OrderNumber).ToList();
// var ce = new CalcEngine.CalcEngine();
// foreach (var item in listElementFormula)
// {
// if (!ce.Variables.Any(m => m.Key == item.VariableName))
// {
// ce.Variables.Add(item.VariableName, item.Value);
// }
// }
// var x = ce.Parse(formula);
// result = x.Evaluate();
// if (result.ToString() == "NaN")
// { result = 0; }
// return result;
//}
public static FormulaHelperModel ParseFormula(string formula, List <ElementFormula> listElementFormula)
{
FormulaHelperModel result = new FormulaHelperModel();
try
{
listElementFormula = listElementFormula.OrderBy(v => v.OrderNumber).ToList();
var ce = new CalcEngine.CalcEngine();
foreach (var item in listElementFormula)
{
if (formula.Contains(item.VariableName))
{
if (!ce.Variables.Any(m => m.Key.ToUpper() == item.VariableName.ToUpper()))
{
try
{
ce.Variables.Add(item.VariableName, item.Value);
}
catch
{
}
}
}
//////if (!ce.Variables.Any(m => m.Key.ToUpper() == item.VariableName.ToUpper()))
//////{
////// //try
////// //{
////// ce.Variables.Add(item.VariableName, item.Value);
////// //}
////// //catch
////// //{
////// // string ttt = item.VariableName;
////// //}
//////}
}
var x = ce.Parse(formula);
object value = x.Evaluate();
if (value.ToString() == "NaN")
{
value = 0;
}
result.Value = value;
return(result);
}
catch (Exception ex)
{
result.Value = 0;
result.ErrorMessage = ex.Message;
return(result);
}
}
/***************************************/
/* METHODS */
/***************************************/
/// <summary>
/// Initialize the calculation engine and UnityCurve values.
/// </summary>
private void Awake()
{
// Initialize calculation engine
CalculationEngine = new CalcEngine.CalcEngine();
CalculationEngine.Variables[PARAMETER_NAME] = 0;
// Initialize Value
PreviousCurveValue = defaultValue;
CurrentCurveValue = 0;
}
/// <summary>
/// Hàm tính toán công thức
/// </summary>
/// <param name="formula">Chuổi công thức - Vd: "basicsalary * workdaycount"</param>
/// <param name="listElementFormula">
/// Danh sách đối tượng phần tử
/// Vd: List<ElementFormula> listElementFormula = new List<ElementFormula>();
/// lstElementFormula.Add(new ElementFormula("workdaycount", 82, 1));
/// lstElementFormula.Add(new ElementFormula("basicsalary", 5200000, 2));
/// <returns></returns>
//public static object ParseFormula(string formula, List<ElementFormula> listElementFormula)
//{
// object result = 0;
// listElementFormula = listElementFormula.OrderBy(v => v.OrderNumber).ToList();
// var ce = new CalcEngine.CalcEngine();
// foreach (var item in listElementFormula)
// {
// if (!ce.Variables.Any(m => m.Key == item.VariableName))
// {
// ce.Variables.Add(item.VariableName, item.Value);
// }
// }
// var x = ce.Parse(formula);
// result = x.Evaluate();
// if (result.ToString() == "NaN")
// { result = 0; }
// return result;
//}
public static FormulaHelperModel ParseFormula(string formula, List<ElementFormula> listElementFormula)
{
FormulaHelperModel result = new FormulaHelperModel();
try
{
listElementFormula = listElementFormula.OrderBy(v => v.OrderNumber).ToList();
var ce = new CalcEngine.CalcEngine();
foreach (var item in listElementFormula)
{
if(formula.Contains(item.VariableName))
{
if (!ce.Variables.Any(m => m.Key.ToUpper() == item.VariableName.ToUpper()))
{
try
{
ce.Variables.Add(item.VariableName, item.Value);
}
catch
{
}
}
}
//////if (!ce.Variables.Any(m => m.Key.ToUpper() == item.VariableName.ToUpper()))
//////{
////// //try
////// //{
////// ce.Variables.Add(item.VariableName, item.Value);
////// //}
////// //catch
////// //{
////// // string ttt = item.VariableName;
////// //}
//////}
}
var x = ce.Parse(formula);
object value = x.Evaluate();
if (value.ToString() == "NaN")
{ value = 0; }
result.Value = value;
return result;
}
catch (Exception ex)
{
result.Value = 0;
result.ErrorMessage = ex.Message;
return result;
}
}
public static void Test(CalcEngine ce)
{
ce.Test("ABS(-12)", 12.0);
ce.Test("ABS(+12)", 12.0);
ce.Test("ACOS(.23)", Math.Acos(.23));
ce.Test("ASIN(.23)", Math.Asin(.23));
ce.Test("ATAN(.23)", Math.Atan(.23));
ce.Test("ATAN2(1,2)", Math.Atan2(1, 2));
ce.Test("CEILING(1.8)", Math.Ceiling(1.8));
ce.Test("COS(1.23)", Math.Cos(1.23));
ce.Test("COSH(1.23)", Math.Cosh(1.23));
ce.Test("EXP(1)", Math.Exp(1));
ce.Test("FLOOR(1.8)", Math.Floor(1.8));
ce.Test("INT(1.8)", 1);
ce.Test("LOG(1.8)", Math.Log(1.8, 10)); // default base is 10
ce.Test("LOG(1.8, 4)", Math.Log(1.8, 4)); // custom base
ce.Test("LN(1.8)", Math.Log(1.8)); // real log
ce.Test("LOG10(1.8)", Math.Log10(1.8)); // same as Log(1.8)
ce.Test("PI()", Math.PI);
ce.Test("POWER(2,4)", Math.Pow(2, 4));
//ce.Test("RAND") <= 1.0);
//ce.Test("RANDBETWEEN(4,5)") <= 5);
ce.Test("SIGN(-5)", -1);
ce.Test("SIGN(+5)", +1);
ce.Test("SIGN(0)", 0);
ce.Test("SIN(1.23)", Math.Sin(1.23));
ce.Test("SINH(1.23)", Math.Sinh(1.23));
ce.Test("SQRT(144)", Math.Sqrt(144));
ce.Test("SUM(1, 2, 3, 4)", 1 + 2 + 3 + 4.0);
ce.Test("TAN(1.23)", Math.Tan(1.23));
ce.Test("TANH(1.23)", Math.Tanh(1.23));
ce.Test("TRUNC(1.23)", 1.0);
ce.Test("PI()", Math.PI);
ce.Test("PI", Math.PI);
ce.Test("LN(10)", Math.Log(10));
ce.Test("LOG(10)", Math.Log10(10));
ce.Test("EXP(10)", Math.Exp(10));
ce.Test("SIN(PI()/4)", Math.Sin(Math.PI / 4));
ce.Test("ASIN(PI()/4)", Math.Asin(Math.PI / 4));
ce.Test("SINH(PI()/4)", Math.Sinh(Math.PI / 4));
ce.Test("COS(PI()/4)", Math.Cos(Math.PI / 4));
ce.Test("ACOS(PI()/4)", Math.Acos(Math.PI / 4));
ce.Test("COSH(PI()/4)", Math.Cosh(Math.PI / 4));
ce.Test("TAN(PI()/4)", Math.Tan(Math.PI / 4));
ce.Test("ATAN(PI()/4)", Math.Atan(Math.PI / 4));
ce.Test("ATAN2(1,2)", Math.Atan2(1, 2));
ce.Test("TANH(PI()/4)", Math.Tanh(Math.PI / 4));
}
public static void Test(CalcEngine ce)
{
ce.Test("AND(true, true)", true);
ce.Test("AND(true, false)", false);
ce.Test("AND(false, true)", false);
ce.Test("AND(false, false)", false);
ce.Test("OR(true, true)", true);
ce.Test("OR(true, false)", true);
ce.Test("OR(false, true)", true);
ce.Test("OR(false, false)", false);
ce.Test("NOT(false)", true);
ce.Test("NOT(true)", false);
ce.Test("IF(5 > 4, true, false)", true);
ce.Test("IF(5 > 14, true, false)", false);
ce.Test("TRUE()", true);
ce.Test("FALSE()", false);
}
public void CalcInfo()
{
if (Data == null || Data.Length == 0)
{
foreach (var spec in TestSpecList)
{
spec.PassFail = null;
}
return;
}
foreach (var spec in TestSpecList)
{
#region
foreach (var marker in spec.TestMarkerList)
{
if (marker is XYTestMarker)
{
var xyMarker = marker as XYTestMarker;
xyMarker.MarkerResult.Clear();
switch (xyMarker.Type)
{
case XYTestMarkerTypeEnum.ABSMax:
double yAbsMax = GeneFun.GetTwoArraryMax(Data, true);
double xAbsMax = FreqList[0];
xyMarker.MarkerResult.Add(new XYData(xAbsMax, yAbsMax));
break;
case XYTestMarkerTypeEnum.ABSMin:
double yAbsMin = GeneFun.GetTwoArrayMin(Data, true);
double xAbsMin = FreqList[0];
xyMarker.MarkerResult.Add(new XYData(xAbsMin, yAbsMin));
break;
case XYTestMarkerTypeEnum.Max:
double yMax = GeneFun.GetTwoArraryMax(Data, false);
double xMax = FreqList[0];
xyMarker.MarkerResult.Add(new XYData(xMax, yMax));
break;
case XYTestMarkerTypeEnum.Min:
double yMin = GeneFun.GetTwoArrayMin(Data, false);
double xMin = FreqList[0];
xyMarker.MarkerResult.Add(new XYData(xMin, yMin));
break;
case XYTestMarkerTypeEnum.Peak:
double y1Max = GeneFun.GetTwoArraryMax(Data, false);
double y1Min = GeneFun.GetTwoArrayMin(Data, false);
if (Math.Abs(y1Max) >= Math.Abs(y1Min))
{
xyMarker.MarkerResult.Add(new XYData(FreqList[0], y1Max));
}
else
{
xyMarker.MarkerResult.Add(new XYData(FreqList[0], y1Min));
}
break;
default:
break;
}
}
marker.JudgePassFail();
}
#endregion
#region
List <bool> boolList = new List <bool>();
CalcEngine.CalcEngine ce = new CalcEngine.CalcEngine();
ce.Variables.Add("att", null);
ce.Variables.Add("phase", null);
ce.Variables.Add("freq", null);
for (int i = 0; i < Data.GetLength(0); i++)
{
for (int j = 0; j < Data.GetLength(1); j++)
{
ce.Variables["att"] = StateList[i].Att;
ce.Variables["phase"] = StateList[i].Phase;
ce.Variables["freq"] = FreqList[j];
object highLimitStr;
object lowLimitStr;
if (string.IsNullOrWhiteSpace(spec.HighLimit))
{
highLimitStr = null;
}
else
{
highLimitStr = ce.Evaluate(spec.HighLimit);
}
if (string.IsNullOrWhiteSpace(spec.LowLimit))
{
lowLimitStr = null;
}
else
{
lowLimitStr = ce.Evaluate(spec.LowLimit);
}
double?highLimitValue = highLimitStr.ToNullDouble();
double?lowLimitValue = lowLimitStr.ToNullDouble();
bool passFail = GeneFun.IsPassFail(Data[i, j], highLimitValue, lowLimitValue);
boolList.Add(passFail);
}
}
spec.PassFail = GeneFun.NullBoolAndList(boolList);
spec.PassFail = GeneFun.NullBoolAnd(GeneFun.NullBoolAndList(spec.TestMarkerList.Select(x => x.PassFail).ToList()), spec.PassFail);
#endregion
}
PassFail = GeneFun.NullBoolAndList(TestSpecList.Select(x => x.PassFail).ToList());
}
public ExpressionCache(CalcEngine ce)
{
_ce = ce;
_dct = new Dictionary<string, WeakReference>();
}
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?
System.Diagnostics.Debug.Assert(false, "failed to evaluate criteria in SumIf");
return false;
}
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?
System.Diagnostics.Debug.Assert(false, "failed to evaluate criteria in SumIf");
return(false);
}
// ** ctor
internal BindingExpression(CalcEngine engine, List <BindingInfo> bindingPath, CultureInfo ci)
{
_ce = engine;
_bindingPath = bindingPath;
_ci = ci;
}
public static void Test(CalcEngine ce)
{
ce.Test("Average(1, 3, 3, 1, true, false, \"hello\")", 2.0);
ce.Test("AverageA(1, 3, 3, 1, true, false, \"hello\")", (1 + 3 + 3 + 1 + 1 + 0 + 0) / 7.0);
ce.Test("Count(1, 3, 3, 1, true, false, \"hello\")", 4.0);
ce.Test("CountA(1, 3, 3, 1, true, false, \"hello\")", 7.0);
}
public static void Register(CalcEngine ce)
{
//ce.RegisterFunction("AVEDEV", AveDev, 1, int.MaxValue);
ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average);
ce.RegisterFunction("AVERAGEA", 1, int.MaxValue, AverageA);
ce.RegisterFunction("AVG", 1, int.MaxValue, Average);
ce.RegisterFunction("AVGA", 1, int.MaxValue, AverageA);
//BETADIST Returns the beta cumulative distribution function
//BETAINV Returns the inverse of the cumulative distribution function for a specified beta distribution
//BINOMDIST Returns the individual term binomial distribution probability
//CHIDIST Returns the one-tailed probability of the chi-squared distribution
//CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution
//CHITEST Returns the test for independence
//CONFIDENCE Returns the confidence interval for a population mean
//CORREL Returns the correlation coefficient between two data sets
ce.RegisterFunction("COUNT", 1, int.MaxValue, Count);
ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA);
ce.RegisterFunction("COUNTBLANK", 1, int.MaxValue, CountBlank);
ce.RegisterFunction("COUNTIF", 2, CountIf);
//COVAR Returns covariance, the average of the products of paired deviations
//CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
//DEVSQ Returns the sum of squares of deviations
//EXPONDIST Returns the exponential distribution
//FDIST Returns the F probability distribution
//FINV Returns the inverse of the F probability distribution
//FISHER Returns the Fisher transformation
//FISHERINV Returns the inverse of the Fisher transformation
//FORECAST Returns a value along a linear trend
//FREQUENCY Returns a frequency distribution as a vertical array
//FTEST Returns the result of an F-test
//GAMMADIST Returns the gamma distribution
//GAMMAINV Returns the inverse of the gamma cumulative distribution
//GAMMALN Returns the natural logarithm of the gamma function, Γ(x)
//GEOMEAN Returns the geometric mean
//GROWTH Returns values along an exponential trend
//HARMEAN Returns the harmonic mean
//HYPGEOMDIST Returns the hypergeometric distribution
//INTERCEPT Returns the intercept of the linear regression line
//KURT Returns the kurtosis of a data set
//LARGE Returns the k-th largest value in a data set
//LINEST Returns the parameters of a linear trend
//LOGEST Returns the parameters of an exponential trend
//LOGINV Returns the inverse of the lognormal distribution
//LOGNORMDIST Returns the cumulative lognormal distribution
ce.RegisterFunction("MAX", 1, int.MaxValue, Max);
ce.RegisterFunction("MAXA", 1, int.MaxValue, MaxA);
//MEDIAN Returns the median of the given numbers
ce.RegisterFunction("MIN", 1, int.MaxValue, Min);
ce.RegisterFunction("MINA", 1, int.MaxValue, MinA);
//MODE Returns the most common value in a data set
//NEGBINOMDIST Returns the negative binomial distribution
//NORMDIST Returns the normal cumulative distribution
//NORMINV Returns the inverse of the normal cumulative distribution
//NORMSDIST Returns the standard normal cumulative distribution
//NORMSINV Returns the inverse of the standard normal cumulative distribution
//PEARSON Returns the Pearson product moment correlation coefficient
//PERCENTILE Returns the k-th percentile of values in a range
//PERCENTRANK Returns the percentage rank of a value in a data set
//PERMUT Returns the number of permutations for a given number of objects
//POISSON Returns the Poisson distribution
//PROB Returns the probability that values in a range are between two limits
//QUARTILE Returns the quartile of a data set
//RANK Returns the rank of a number in a list of numbers
//RSQ Returns the square of the Pearson product moment correlation coefficient
//SKEW Returns the skewness of a distribution
//SLOPE Returns the slope of the linear regression line
//SMALL Returns the k-th smallest value in a data set
//STANDARDIZE Returns a normalized value
ce.RegisterFunction("STDEV", 1, int.MaxValue, StDev);
ce.RegisterFunction("STDEVA", 1, int.MaxValue, StDevA);
ce.RegisterFunction("STDEVP", 1, int.MaxValue, StDevP);
ce.RegisterFunction("STDEVPA", 1, int.MaxValue, StDevPA);
//STEYX Returns the standard error of the predicted y-value for each x in the regression
//TDIST Returns the Student's t-distribution
//TINV Returns the inverse of the Student's t-distribution
//TREND Returns values along a linear trend
//TRIMMEAN Returns the mean of the interior of a data set
//TTEST Returns the probability associated with a Student's t-test
ce.RegisterFunction("VAR", 1, int.MaxValue, Var);
ce.RegisterFunction("VARA", 1, int.MaxValue, VarA);
ce.RegisterFunction("VARP", 1, int.MaxValue, VarP);
ce.RegisterFunction("VARPA", 1, int.MaxValue, VarPA);
//WEIBULL Returns the Weibull distribution
//ZTEST Returns the one-tailed probability-value of a z-test
}
private string calc_test(string str)
{
CalcEngine.CalcEngine ce = new CalcEngine.CalcEngine();
return ce.Evaluate(str).ToString();
}
public static void Register(CalcEngine ce)
{
//ce.RegisterFunction("AVEDEV", AveDev, 1, int.MaxValue);
ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average);
ce.RegisterFunction("AVERAGEA", 1, int.MaxValue, AverageA);
//BETADIST Returns the beta cumulative distribution function
//BETAINV Returns the inverse of the cumulative distribution function for a specified beta distribution
//BINOMDIST Returns the individual term binomial distribution probability
//CHIDIST Returns the one-tailed probability of the chi-squared distribution
//CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution
//CHITEST Returns the test for independence
//CONFIDENCE Returns the confidence interval for a population mean
//CORREL Returns the correlation coefficient between two data sets
ce.RegisterFunction("COUNT", 1, int.MaxValue, Count);
ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA);
ce.RegisterFunction("COUNTBLANK", 1, int.MaxValue, CountBlank);
ce.RegisterFunction("COUNTIF", 2, CountIf);
//COVAR Returns covariance, the average of the products of paired deviations
//CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
//DEVSQ Returns the sum of squares of deviations
//EXPONDIST Returns the exponential distribution
//FDIST Returns the F probability distribution
//FINV Returns the inverse of the F probability distribution
//FISHER Returns the Fisher transformation
//FISHERINV Returns the inverse of the Fisher transformation
//FORECAST Returns a value along a linear trend
//FREQUENCY Returns a frequency distribution as a vertical array
//FTEST Returns the result of an F-test
//GAMMADIST Returns the gamma distribution
//GAMMAINV Returns the inverse of the gamma cumulative distribution
//GAMMALN Returns the natural logarithm of the gamma function, Γ(x)
//GEOMEAN Returns the geometric mean
//GROWTH Returns values along an exponential trend
//HARMEAN Returns the harmonic mean
//HYPGEOMDIST Returns the hypergeometric distribution
//INTERCEPT Returns the intercept of the linear regression line
//KURT Returns the kurtosis of a data set
//LARGE Returns the k-th largest value in a data set
//LINEST Returns the parameters of a linear trend
//LOGEST Returns the parameters of an exponential trend
//LOGINV Returns the inverse of the lognormal distribution
//LOGNORMDIST Returns the cumulative lognormal distribution
ce.RegisterFunction("MAX", 1, int.MaxValue, Max);
ce.RegisterFunction("MAXA", 1, int.MaxValue, MaxA);
//MEDIAN Returns the median of the given numbers
ce.RegisterFunction("MIN", 1, int.MaxValue, Min);
ce.RegisterFunction("MINA", 1, int.MaxValue, MinA);
//MODE Returns the most common value in a data set
//NEGBINOMDIST Returns the negative binomial distribution
//NORMDIST Returns the normal cumulative distribution
//NORMINV Returns the inverse of the normal cumulative distribution
//NORMSDIST Returns the standard normal cumulative distribution
//NORMSINV Returns the inverse of the standard normal cumulative distribution
//PEARSON Returns the Pearson product moment correlation coefficient
//PERCENTILE Returns the k-th percentile of values in a range
//PERCENTRANK Returns the percentage rank of a value in a data set
//PERMUT Returns the number of permutations for a given number of objects
//POISSON Returns the Poisson distribution
//PROB Returns the probability that values in a range are between two limits
//QUARTILE Returns the quartile of a data set
//RANK Returns the rank of a number in a list of numbers
//RSQ Returns the square of the Pearson product moment correlation coefficient
//SKEW Returns the skewness of a distribution
//SLOPE Returns the slope of the linear regression line
//SMALL Returns the k-th smallest value in a data set
//STANDARDIZE Returns a normalized value
ce.RegisterFunction("STDEV", 1, int.MaxValue, StDev);
ce.RegisterFunction("STDEVA", 1, int.MaxValue, StDevA);
ce.RegisterFunction("STDEVP", 1, int.MaxValue, StDevP);
ce.RegisterFunction("STDEVPA", 1, int.MaxValue, StDevPA);
//STEYX Returns the standard error of the predicted y-value for each x in the regression
//TDIST Returns the Student's t-distribution
//TINV Returns the inverse of the Student's t-distribution
//TREND Returns values along a linear trend
//TRIMMEAN Returns the mean of the interior of a data set
//TTEST Returns the probability associated with a Student's t-test
ce.RegisterFunction("VAR", 1, int.MaxValue, Var);
ce.RegisterFunction("VARA", 1, int.MaxValue, VarA);
ce.RegisterFunction("VARP", 1, int.MaxValue, VarP);
ce.RegisterFunction("VARPA", 1, int.MaxValue, VarPA);
//WEIBULL Returns the Weibull distribution
//ZTEST Returns the one-tailed probability-value of a z-test
}
public static void Register(CalcEngine ce)
{
ce.RegisterFunction("ABS", 1, Abs);
ce.RegisterFunction("ACOS", 1, Acos);
//ce.RegisterFunction("ACOSH", Acosh, 1);
ce.RegisterFunction("ASIN", 1, Asin);
//ce.RegisterFunction("ASINH", Asinh, 1);
ce.RegisterFunction("ATAN", 1, Atan);
ce.RegisterFunction("ATAN2", 2, Atan2);
//ce.RegisterFunction("ATANH", Atanh, 1);
ce.RegisterFunction("CEILING", 1, Ceiling);
//ce.RegisterFunction("COMBIN", Combin, 1);
ce.RegisterFunction("COS", 1, Cos);
ce.RegisterFunction("COSH", 1, Cosh);
//ce.RegisterFunction("DEGREES", Degrees, 1);
//ce.RegisterFunction("EVEN", Even, 1);
ce.RegisterFunction("EXP", 1, Exp);
//ce.RegisterFunction("FACT", Fact, 1);
//ce.RegisterFunction("FACTDOUBLE", FactDouble, 1);
ce.RegisterFunction("FLOOR", 1, Floor);
//ce.RegisterFunction("GCD", Gcd, 1);
ce.RegisterFunction("INT", 1, Int);
//ce.RegisterFunction("LCM", Lcm, 1);
ce.RegisterFunction("LN", 1, Ln);
ce.RegisterFunction("LOG", 1, 2, Log);
ce.RegisterFunction("LOG10", 1, Log10);
//ce.RegisterFunction("MDETERM", MDeterm, 1);
//ce.RegisterFunction("MINVERSE", MInverse, 1);
//ce.RegisterFunction("MMULT", MMult, 1);
//ce.RegisterFunction("MOD", Mod, 2);
//ce.RegisterFunction("MROUND", MRound, 1);
//ce.RegisterFunction("MULTINOMIAL", Multinomial, 1);
//ce.RegisterFunction("ODD", Odd, 1);
ce.RegisterFunction("PI", 0, Pi);
ce.RegisterFunction("POWER", 2, Power);
//ce.RegisterFunction("PRODUCT", Product, 1);
//ce.RegisterFunction("QUOTIENT", Quotient, 1);
//ce.RegisterFunction("RADIANS", Radians, 1);
ce.RegisterFunction("RAND", 0, Rand);
ce.RegisterFunction("RANDBETWEEN", 2, RandBetween);
//ce.RegisterFunction("ROMAN", Roman, 1);
//ce.RegisterFunction("ROUND", Round, 1);
//ce.RegisterFunction("ROUNDDOWN", RoundDown, 1);
//ce.RegisterFunction("ROUNDUP", RoundUp, 1);
//ce.RegisterFunction("SERIESSUM", SeriesSum, 1);
ce.RegisterFunction("SIGN", 1, Sign);
ce.RegisterFunction("SIN", 1, Sin);
ce.RegisterFunction("SINH", 1, Sinh);
ce.RegisterFunction("SQRT", 1, Sqrt);
//ce.RegisterFunction("SQRTPI", SqrtPi, 1);
//ce.RegisterFunction("SUBTOTAL", Subtotal, 1);
ce.RegisterFunction("SUM", 1, int.MaxValue, Sum);
ce.RegisterFunction("SUMIF", 2, 3, SumIf);
//ce.RegisterFunction("SUMPRODUCT", SumProduct, 1);
//ce.RegisterFunction("SUMSQ", SumSq, 1);
//ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1);
//ce.RegisterFunction("SUMX2PY2", SumX2PY2, 1);
//ce.RegisterFunction("SUMXMY2", SumXMY2, 1);
ce.RegisterFunction("TAN", 1, Tan);
ce.RegisterFunction("TANH", 1, Tanh);
ce.RegisterFunction("TRUNC", 1, Trunc);
ce.RegisterFunction("MOD", 2, Mod);
ce.RegisterFunction("HEX", 1, Hex);
ce.RegisterFunction("GCD", 2, Gcd); //Great Common Divisor
ce.RegisterFunction("LCM", 2, Lcm); //Least Common Multiple
ce.RegisterFunction("CHKSUM8", 1, int.MaxValue, Checksum_u8);
ce.RegisterFunction("CHKSUM16", 1, int.MaxValue, Checksum_u16);
ce.RegisterFunction("BYTE", 1, UInt_Byte);
ce.RegisterFunction("UINT8", 1, UInt_Byte);
ce.RegisterFunction("UINT16", 1, UInt_U16);
ce.RegisterFunction("UINT32", 1, UInt_U32);
ce.RegisterFunction("UINT64", 1, UInt_U64);
ce.RegisterFunction("SBYTE", 1, Int_SByte);
ce.RegisterFunction("INT8", 1, Int_SByte);
ce.RegisterFunction("INT16", 1, Int_U16);
ce.RegisterFunction("INT32", 1, Int_U32);
ce.RegisterFunction("INT64", 1, Int_U64);
ce.RegisterFunction("TOHEX", 1, ToHex);
ce.RegisterFunction("TOBIN", 1, ToBin);
}
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;
}
public static void Register(CalcEngine ce)
{
ce.RegisterFunction("ABS", 1, Abs);
ce.RegisterFunction("ACOS", 1, Acos);
//ce.RegisterFunction("ACOSH", Acosh, 1);
ce.RegisterFunction("ASIN", 1, Asin);
//ce.RegisterFunction("ASINH", Asinh, 1);
ce.RegisterFunction("ATAN", 1, Atan);
ce.RegisterFunction("ATAN2", 2, Atan2);
//ce.RegisterFunction("ATANH", Atanh, 1);
ce.RegisterFunction("CEILING", 1, Ceiling);
//ce.RegisterFunction("COMBIN", Combin, 1);
ce.RegisterFunction("COS", 1, Cos);
ce.RegisterFunction("COSH", 1, Cosh);
//ce.RegisterFunction("DEGREES", Degrees, 1);
//ce.RegisterFunction("EVEN", Even, 1);
ce.RegisterFunction("EXP", 1, Exp);
//ce.RegisterFunction("FACT", Fact, 1);
//ce.RegisterFunction("FACTDOUBLE", FactDouble, 1);
ce.RegisterFunction("FLOOR", 1, Floor);
//ce.RegisterFunction("GCD", Gcd, 1);
ce.RegisterFunction("INT", 1, Int);
//ce.RegisterFunction("LCM", Lcm, 1);
ce.RegisterFunction("LN", 1, Ln);
ce.RegisterFunction("LOG", 1, 2, Log);
ce.RegisterFunction("LOG10", 1, Log10);
//ce.RegisterFunction("MDETERM", MDeterm, 1);
//ce.RegisterFunction("MINVERSE", MInverse, 1);
//ce.RegisterFunction("MMULT", MMult, 1);
//ce.RegisterFunction("MOD", Mod, 2);
//ce.RegisterFunction("MROUND", MRound, 1);
//ce.RegisterFunction("MULTINOMIAL", Multinomial, 1);
//ce.RegisterFunction("ODD", Odd, 1);
ce.RegisterFunction("PI", 0, Pi);
ce.RegisterFunction("POWER", 2, Power);
//ce.RegisterFunction("PRODUCT", Product, 1);
//ce.RegisterFunction("QUOTIENT", Quotient, 1);
//ce.RegisterFunction("RADIANS", Radians, 1);
ce.RegisterFunction("RAND", 0, Rand);
ce.RegisterFunction("RANDBETWEEN", 2, RandBetween);
//ce.RegisterFunction("ROMAN", Roman, 1);
ce.RegisterFunction("ROUND", 2, Round);
//ce.RegisterFunction("ROUNDDOWN", RoundDown, 1);
//ce.RegisterFunction("ROUNDUP", RoundUp, 1);
//ce.RegisterFunction("SERIESSUM", SeriesSum, 1);
ce.RegisterFunction("SIGN", 1, Sign);
ce.RegisterFunction("SIN", 1, Sin);
ce.RegisterFunction("SINH", 1, Sinh);
ce.RegisterFunction("SQRT", 1, Sqrt);
//ce.RegisterFunction("SQRTPI", SqrtPi, 1);
//ce.RegisterFunction("SUBTOTAL", Subtotal, 1);
ce.RegisterFunction("SUM", 1, int.MaxValue, Sum);
ce.RegisterFunction("SUMIF", 2, 3, SumIf);
//ce.RegisterFunction("SUMPRODUCT", SumProduct, 1);
//ce.RegisterFunction("SUMSQ", SumSq, 1);
//ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1);
//ce.RegisterFunction("SUMX2PY2", SumX2PY2, 1);
//ce.RegisterFunction("SUMXMY2", SumXMY2, 1);
ce.RegisterFunction("TAN", 1, Tan);
ce.RegisterFunction("TANH", 1, Tanh);
ce.RegisterFunction("TRUNC", 1, Trunc);
ce.RegisterFunction("MONTH", 1, MONTH);
ce.RegisterFunction("YEAR", 1, YEAR);
ce.RegisterFunction("DAY", 1, DAY);
ce.RegisterFunction("EOMONTH", 2, EOMONTH);
ce.RegisterFunction("DATE", 3, DATE);
ce.RegisterFunction("ADDMONTH", 2, ADDMONTH);
ce.RegisterFunction("ADDDAY", 2, ADDDAY);
ce.RegisterFunction("ADDYEAR", 2, ADDYEAR);
//ce.RegisterFunction("CONCATENATE", 2, CONCATENATE);
}
private ExpTool()
{
m_numFmt = IntegerFormat.DEC;
m_calcEngine = new CalcEngine.CalcEngine();
m_varList = new Dictionary<string, object>();
}
static object SumIf(List<Expression> p)
{
// get parameters
IEnumerable range = p[0] as IEnumerable;
IEnumerable sumRange = p.Count < 3 ? range : p[2] as IEnumerable;
var criteria = p[1].Evaluate();
// build list of values in range and sumRange
var rangeValues = new List<object>();
foreach (var value in range)
{
rangeValues.Add(value);
}
var sumRangeValues = new List<object>();
foreach (var value in sumRange)
{
sumRangeValues.Add(value);
}
// compute total
var ce = new CalcEngine();
var tally = new Tally();
for (int i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++)
{
if (ValueSatisfiesCriteria(rangeValues[i], criteria, ce))
{
tally.AddValue(sumRangeValues[i]);
}
}
// done
return tally.Sum();
}
public static void Test(CalcEngine ce)
{
ce.Test("CHAR(65)", "A");
ce.Test("CODE(\"A\")", 65);
ce.Test("CONCATENATE(\"a\", \"b\")", "ab");
ce.Test("FIND(\"bra\", \"abracadabra\")", 2);
ce.Test("FIND(\"BRA\", \"abracadabra\")", -1);
ce.Test("LEFT(\"abracadabra\", 3)", "abr");
ce.Test("LEFT(\"abracadabra\")", "a");
ce.Test("LEN(\"abracadabra\")", 11);
ce.Test("LOWER(\"ABRACADABRA\")", "abracadabra");
ce.Test("MID(\"abracadabra\", 1, 3)", "abr");
ce.Test("PROPER(\"abracadabra\")", "Abracadabra");
ce.Test("REPLACE(\"abracadabra\", 1, 3, \"XYZ\")", "XYZacadabra");
ce.Test("REPT(\"abr\", 3)", "abrabrabr");
ce.Test("RIGHT(\"abracadabra\", 3)", "bra");
ce.Test("SEARCH(\"bra\", \"abracadabra\")", 2);
ce.Test("SEARCH(\"BRA\", \"abracadabra\")", 2);
ce.Test("SUBSTITUTE(\"abracadabra\", \"a\", \"b\")", "bbrbcbdbbrb");
ce.Test("T(123)", "");
ce.Test("T(\"123\")", "123");
ce.Test("TEXT(1234, \"n2\")", "1,234.00");
ce.Test("TRIM(\" hello \")", "hello");
ce.Test("UPPER(\"abracadabra\")", "ABRACADABRA");
ce.Test("VALUE(\"1234\")", 1234.0);
ce.Test("SUBSTITUTE(\"abcabcabc\", \"a\", \"b\")", "bbcbbcbbc");
ce.Test("SUBSTITUTE(\"abcabcabc\", \"a\", \"b\", 1)", "bbcabcabc");
ce.Test("SUBSTITUTE(\"abcabcabc\", \"a\", \"b\", 2)", "abcbbcabc");
ce.Test("SUBSTITUTE(\"abcabcabc\", \"A\", \"b\", 2)", "abcabcabc"); // case-sensitive!
// test taken from http://www.codeproject.com/KB/vb/FormulaEngine.aspx
var a1 = "\"http://j-walk.com/ss/books\"";
var exp = "RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,\"/\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,\"/\",\"\")))))";
ce.Test(exp.Replace("A1", a1), "books");
}
public static void Register(CalcEngine ce)
{
ce.RegisterFunction("ABS", 1, Abs);
ce.RegisterFunction("ACOS", 1, Acos);
//ce.RegisterFunction("ACOSH", Acosh, 1);
ce.RegisterFunction("ASIN", 1, Asin);
//ce.RegisterFunction("ASINH", Asinh, 1);
ce.RegisterFunction("ATAN", 1, Atan);
ce.RegisterFunction("ATAN2", 2, Atan2);
//ce.RegisterFunction("ATANH", Atanh, 1);
ce.RegisterFunction("CEILING", 1, Ceiling);
//ce.RegisterFunction("COMBIN", Combin, 1);
ce.RegisterFunction("COS", 1, Cos);
ce.RegisterFunction("COSH", 1, Cosh);
//ce.RegisterFunction("DEGREES", Degrees, 1);
//ce.RegisterFunction("EVEN", Even, 1);
ce.RegisterFunction("EXP", 1, Exp);
//ce.RegisterFunction("FACT", Fact, 1);
//ce.RegisterFunction("FACTDOUBLE", FactDouble, 1);
ce.RegisterFunction("FLOOR", 1, Floor);
//ce.RegisterFunction("GCD", Gcd, 1);
ce.RegisterFunction("INT", 1, Int);
//ce.RegisterFunction("LCM", Lcm, 1);
ce.RegisterFunction("LN", 1, Ln);
ce.RegisterFunction("LOG", 1, 2, Log);
ce.RegisterFunction("LOG10", 1, Log10);
//ce.RegisterFunction("MDETERM", MDeterm, 1);
//ce.RegisterFunction("MINVERSE", MInverse, 1);
//ce.RegisterFunction("MMULT", MMult, 1);
//ce.RegisterFunction("MOD", Mod, 2);
//ce.RegisterFunction("MROUND", MRound, 1);
//ce.RegisterFunction("MULTINOMIAL", Multinomial, 1);
//ce.RegisterFunction("ODD", Odd, 1);
ce.RegisterFunction("PI", 0, Pi);
ce.RegisterFunction("POWER", 2, Power);
//ce.RegisterFunction("PRODUCT", Product, 1);
//ce.RegisterFunction("QUOTIENT", Quotient, 1);
//ce.RegisterFunction("RADIANS", Radians, 1);
ce.RegisterFunction("RAND", 0, Rand);
ce.RegisterFunction("RANDBETWEEN", 2, RandBetween);
//ce.RegisterFunction("ROMAN", Roman, 1);
//ce.RegisterFunction("ROUND", Round, 1);
//ce.RegisterFunction("ROUNDDOWN", RoundDown, 1);
//ce.RegisterFunction("ROUNDUP", RoundUp, 1);
//ce.RegisterFunction("SERIESSUM", SeriesSum, 1);
ce.RegisterFunction("SIGN", 1, Sign);
ce.RegisterFunction("SIN", 1, Sin);
ce.RegisterFunction("SINH", 1, Sinh);
ce.RegisterFunction("SQRT", 1, Sqrt);
//ce.RegisterFunction("SQRTPI", SqrtPi, 1);
//ce.RegisterFunction("SUBTOTAL", Subtotal, 1);
ce.RegisterFunction("SUM", 1, int.MaxValue, Sum);
ce.RegisterFunction("SUMIF", 2, 3, SumIf);
//ce.RegisterFunction("SUMPRODUCT", SumProduct, 1);
//ce.RegisterFunction("SUMSQ", SumSq, 1);
//ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1);
//ce.RegisterFunction("SUMX2PY2", SumX2PY2, 1);
//ce.RegisterFunction("SUMXMY2", SumXMY2, 1);
ce.RegisterFunction("TAN", 1, Tan);
ce.RegisterFunction("TANH", 1, Tanh);
ce.RegisterFunction("TRUNC", 1, Trunc);
}
