/// <summary>
/// Adds all static methods of the given type that can be formula functions
/// </summary>
/// <param name="target">The type to examine</param>
/// <remarks>
/// This method works similarly to
/// <see cref="M:ciloci.FormulaEngine.FunctionLibrary.AddInstanceFunctions(System.Object)" /> except that it looks at
/// all static methods instead.
/// </remarks>
/// <exception cref="T:System.ArgumentNullException">target is null</exception>
/// <exception cref="T:System.ArgumentException">
/// A method is tagged with a formula function attribute but does not have the
/// correct signature
/// </exception>
/// <exception cref="T:System.InvalidOperationException">
/// The function was called while formulas are defined in the formula engine
/// <para>A function with the same name is already defined</para>
/// </exception>
public void AddStaticFunctions(Type target)
{
FormulaEngine.ValidateNonNull(target, "target");
var creator = new StaticDelegateCreator(target);
AddFormulaMethods(target, creator);
}
/// <summary>
/// Adds all instance methods of the given object that can be formula functions
/// </summary>
/// <param name="instance">The object whose methods you wish to add</param>
/// <remarks>
/// Use this function when you want to add a large number of functions in bulk. The method will search all instance
/// methods
/// of the type. Methods that are tagged with a formula function attribute and have the correct signature will be
/// added to the library.
/// </remarks>
/// <exception cref="T:System.ArgumentNullException">instance is null</exception>
/// <exception cref="T:System.ArgumentException">
/// A method is tagged with a formula function attribute but does not have the
/// correct signature
/// </exception>
/// <exception cref="T:System.InvalidOperationException">
/// The function was called while formulas are defined in the formula engine
/// <para>A function with the same name is already defined</para>
/// </exception>
public void AddInstanceFunctions(object instance)
{
FormulaEngine.ValidateNonNull(instance, "instance");
var creator = new InstanceDelegateCreator(instance);
AddFormulaMethods(instance.GetType(), creator);
}
public void FixtureSetUp()
{
_formulaEngine = new FormulaEngine {
Settings = { ListSeparator = ";", DecimalSeparator = "," }
};
DoFixtureSetup();
}
internal FunctionLibrary(FormulaEngine owner)
{
_owner = owner;
_builtinFunctions = new BuiltinFunctions();
_functions = new Hashtable(StringComparer.OrdinalIgnoreCase);
AddBuiltinFunctions();
}
public override void Evaluate(Stack state, FormulaEngine engine)
{
var op = (IOperand)state.Pop();
IOperand result = ComputeValueInternal(op);
state.Push(result);
}
internal Variable(FormulaEngine engine, string name)
{
MyEngine = engine;
MyReference = (NamedReference)engine.ReferenceFactory.Named(name);
engine.AddFormula("=0", MyReference);
MyReference = (NamedReference)engine.ReferencePool.GetPooledReference(MyReference);
MyReference.OperandValue = null;
}
/// <summary>
/// Gets the regular expression equivalent pattern of an excel wildcard expression
/// </summary>
/// <param name="pattern">The pattern to convert</param>
/// <returns>A regular expression representation of pattern</returns>
/// <remarks>
/// Excel has its own syntax for pattern matching that many functions use. This method converts such an expression
/// into its regular expression equivalent.
/// </remarks>
public static string Wildcard2Regex(string pattern)
{
FormulaEngine.ValidateNonNull(pattern, "pattern");
pattern = EscapeRegex(pattern);
var sb = new StringBuilder(pattern.Length);
bool ignoreChar = false;
for (int i = 0; i <= pattern.Length - 1; i++)
{
char c = pattern[i];
if (ignoreChar)
{
ignoreChar = false;
}
else if (c == '~')
{
// Escape char
if (i == pattern.Length - 1)
{
// If the escape char is the last char then just match it
sb.Append('~');
}
else
{
char nextChar = pattern[i + 1];
if (nextChar == '?')
{
sb.Append("\\?");
}
else if (nextChar == '*')
{
sb.Append("\\*");
}
else
{
sb.Append(nextChar);
}
ignoreChar = true;
}
}
else if (c == '?')
{
sb.Append(".");
}
else if (c == '*')
{
sb.Append(".*");
}
else
{
sb.Append(c);
}
}
return(sb.ToString());
}
public override void EvaluateForDependencyReference(IList references, FormulaEngine engine)
{
base.EvaluateForDependencyReference(references, engine);
if (engine.FunctionLibrary.IsFunctionVolatile(_funcName))
{
var @ref = new VolatileFunctionReference();
references.Add(@ref);
}
}
// Validate for formula
public override void Validate(FormulaEngine engine)
{
base.Validate(engine);
Exception ex = GetValidateException();
if (ex != null)
{
throw new InvalidFormulaException(ex);
}
}
/// <summary>
/// Undefines an individual function
/// </summary>
/// <param name="functionName">The name of the function you wish to undefine</param>
/// <remarks>This method removes a function from the library</remarks>
/// <exception cref="System.ArgumentException">The given function name is not defined</exception>
/// <exception cref="T:System.InvalidOperationException">
/// The function was called while formulas are defined in the formula
/// engine
/// </exception>
public void RemoveFunction(string functionName)
{
FormulaEngine.ValidateNonNull(functionName, "functionName");
ValidateEngineStateForChangingFunctions();
if (_functions.Contains(functionName) == false)
{
throw new ArgumentException("That function is not defined");
}
_functions.Remove(functionName);
}
/// <summary>
/// Gets the number of direct precedents of a reference
/// </summary>
/// <param name="ref">The reference whose direct precedents to get</param>
/// <returns>A count of the number of direct precedents of the reference</returns>
/// <remarks>The count indicates the number of references that have ref as their dependant</remarks>
public int GetDirectPrecedentsCount(IReference @ref)
{
FormulaEngine.ValidateNonNull(@ref, "ref");
Reference realRef = _owner.ReferencePool.GetPooledReference((Reference)@ref);
if (realRef == null)
{
return(0);
}
return(_owner.DependencyManager.GetDirectPrecedentsCount(realRef));
}
public override void ProcessParseProperties(ReferenceParseProperties props, FormulaEngine engine)
{
ISheet sheet = props.SheetName == null ? engine.Sheets.ActiveSheet :
engine.Sheets.GetSheetByName(props.SheetName);
_sheetName = props.SheetName;
if (sheet != null)
{
Sheet = sheet;
}
}
private void FillTable()
{
// Numbers
_table[0, 0] = 123.34;
_table[0, 1] = (double)13;
_table[0, 2] = (double)-5;
_table[0, 3] = 1000.23;
_table[0, 4] = 1300;
// Integer
// More numbers
_table[1, 0] = 0.56;
_table[1, 1] = (double)100;
_table[1, 2] = (double)0;
_table[1, 3] = 3.45;
_table[1, 4] = 155;
//Integer
// Numbers as strings
_table[2, 0] = "123";
_table[2, 1] = "45.23";
_table[2, 2] = "0";
_table[2, 3] = "-11";
_table[2, 4] = string.Empty;
// Random strings
_table[3, 0] = "eugene";
_table[3, 1] = "not a number";
_table[3, 2] = "****";
_table[3, 3] = "()()^^^";
_table[3, 4] = "S";
// Errors
_table[4, 0] = FormulaEngine.CreateError(ErrorValueType.Div0);
_table[4, 1] = FormulaEngine.CreateError(ErrorValueType.NA);
_table[4, 2] = FormulaEngine.CreateError(ErrorValueType.Name);
_table[4, 3] = FormulaEngine.CreateError(ErrorValueType.Null);
_table[4, 4] = FormulaEngine.CreateError(ErrorValueType.Num);
// Empty cells
_table[5, 0] = null;
_table[5, 1] = null;
_table[5, 2] = null;
_table[5, 3] = null;
_table[5, 4] = null;
// Booleans
_table[6, 0] = true;
_table[6, 1] = false;
_table[6, 2] = false;
_table[6, 3] = true;
_table[6, 4] = true;
}
/// <summary>
/// Gets a column from a table of values
/// </summary>
/// <param name="table">The table to get the column from</param>
/// <param name="columnIndex">The index of the column to get</param>
/// <returns>An array containing the values from the requested column</returns>
/// <remarks>
/// This method is used when you have a table of values (like the ones returned by
/// <see cref="M:ciloci.FormulaEngine.ISheetReference.GetValuesTable" />)
/// and you need to get the values from a column.
/// </remarks>
public static object[] GetTableColumn(object[,] table, int columnIndex)
{
FormulaEngine.ValidateNonNull(table, "table");
var arr = new object[table.GetLength(0)];
for (int row = 0; row < arr.Length; row++)
{
arr[row] = table[row, columnIndex];
}
return(arr);
}
/// <summary>
/// Gets a row from a table of values
/// </summary>
/// <param name="table">The table to get the row from</param>
/// <param name="rowIndex">The index of the row to get</param>
/// <returns>An array containing the values from the requested row</returns>
/// <remarks>
/// This method is used when you have a table of values (like the ones returned by
/// <see cref="M:ciloci.FormulaEngine.ISheetReference.GetValuesTable" />)
/// and you need to get the values from a row.
/// </remarks>
public static object[] GetTableRow(object[,] table, int rowIndex)
{
FormulaEngine.ValidateNonNull(table, "table");
var arr = new object[table.GetLength(1)];
for (int col = 0; col < arr.Length; col++)
{
arr[col] = table[rowIndex, col];
}
return(arr);
}
/// <summary>
/// Tries to convert a string into a value similarly to Excel
/// </summary>
/// <param name="text">The string to parse</param>
/// <returns>A value from the parsed string</returns>
/// <remarks>
/// This method will try to parse text into a value. It will try to convert the text into a Boolean,
/// ErrorValueWrapper,
/// DateTime, Integer, Double, or if all of the previous conversions fail, a string.
/// </remarks>
public static object Parse(string text)
{
FormulaEngine.ValidateNonNull(text, "text");
bool b;
if (bool.TryParse(text, out b))
{
return(b);
}
ErrorValueWrapper wrapper = ErrorValueWrapper.TryParse(text);
if (wrapper != null)
{
return(wrapper);
}
DateTime dt;
if (DateTime.TryParseExact(text, new[]
{
"D",
"d",
"G",
"g",
"t",
"T"
}, null, DateTimeStyles.AllowWhiteSpaces, out dt))
{
return(dt);
}
double d;
bool success = double.TryParse(text, NumberStyles.Integer, null, out d);
if (success & d >= int.MinValue & d <= int.MaxValue)
{
return((int)d);
}
success = double.TryParse(text, NumberStyles.Float, null, out d);
if (success)
{
return(d);
}
return(text);
}
/// <summary>式を検証し、結果を返す。</summary>
/// <param name="formula"></param>
/// <param name="formula_LastValid"></param>
/// <param name="result"></param>
/// <param name="isValid"></param>
protected void ValidateFormula(string formula, string formula_LastValid, out double result, out bool isValid)
{
FormulaEngine engine = Application.FormulaEngine;
isValid = true;
try
{
result = engine.Calculate(formula);
}
catch (FormulaResultException)
{
isValid = false;
result = engine.Calculate(formula_LastValid);
}
}
protected override IOperand ComputeValue(IOperand lhs, IOperand rhs, FormulaEngine engine)
{
IOperand errorOp = GetErrorOperand(lhs, rhs);
if (errorOp != null)
{
return(errorOp);
}
CompareResult cr = Compare(lhs, rhs);
bool result = GetBooleanResult(_compareType, cr);
return(new BooleanOperand(result));
}
/// <summary>
/// Gets the column index from a column label
/// </summary>
/// <param name="label">The label whose column index you wish to get</param>
/// <returns>An index representing the label</returns>
/// <remarks>This function is handy when you have a column label and you want to get its associated index.</remarks>
/// <example>
/// <list type="table">
/// <listheader>
/// <term>Column label</term><description>Resultant index</description>
/// </listheader>
/// <item>
/// <term>"A"</term><description>1</description>
/// </item>
/// <item>
/// <term>"N"</term><description>14</description>
/// </item>
/// <item>
/// <term>"DS"</term><description>123</description>
/// </item>
/// <item>
/// <term>"IV"</term><description>256</description>
/// </item>
/// </list>
/// </example>
public static int ColumnLabel2Index(string label)
{
FormulaEngine.ValidateNonNull(label, "label");
if (label.Length < 1 || label.Length > 2)
{
throw new ArgumentException("The given label must be one or two characters long");
}
char c2 = Char.MinValue;
if (label.Length == 2)
{
c2 = label[1];
}
return(SheetReference.ColumnLabel2Index(label[0], c2));
}
public override void Evaluate(Stack state, FormulaEngine engine)
{
var namedRef = (Reference)engine.ReferenceFactory.Named(_name);
Formula target = engine.GetFormulaAt(namedRef);
if (target != null)
{
var selfRef = (NamedReference)target.SelfReference;
IOperand op = selfRef.ValueOperand;
state.Push(op);
}
else
{
state.Push(new ErrorValueOperand(ErrorValueType.Name));
}
}
public override void Validate(FormulaEngine engine)
{
if (engine.FunctionLibrary.IsFunctionDefined(_funcName) == false)
{
var inner =
new InvalidOperationException(string.Format("The function {0} is not defined", _funcName));
throw new InvalidFormulaException(inner);
}
if (engine.FunctionLibrary.IsValidArgumentCount(_funcName, _argCount) == false)
{
var inner =
new ArgumentException(string.Format("Invalid number of arguments provided for function {0}",
_funcName));
throw new InvalidFormulaException(inner);
}
}
private void btnEvaluate_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
#region #evaluate
FormulaEngine engine = spreadsheetControl1.Document.FormulaEngine;
// Get coordinates of the context cell.
int columnIndex = this.contextCell.ColumnIndex;
int rowIndex = this.contextCell.RowIndex;
// Create the expression context.
ExpressionContext context = new ExpressionContext(columnIndex, rowIndex, this.contextSheet,
CultureInfo.GetCultureInfo("en-US"), ReferenceStyle.R1C1, DevExpress.Spreadsheet.Formulas.ExpressionStyle.Normal);
// Evaluate the expression.
ParameterValue result = engine.Evaluate("SUM(R[-2]C:R[-1]C)", context);
// Get the result.
double res = result.NumericValue;
#endregion #evaluate
MessageBox.Show(String.Format("The result is {0}", res), "Evaluate Test");
}
/// <summary>
/// Adds an individual formula function
/// </summary>
/// <param name="functionCall">A delegate pointing to the method you wish to add</param>
/// <remarks>
/// This function lets you add an individual formula function by specifying a delegate pointing to it. The method that
/// the delegate refers to must be tagged with the appropriate
/// <see cref="T:ciloci.FormulaEngine.FormulaFunctionAttribute">attribute</see>.
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// The method that the delegate points to is not tagged with the required
/// attribute
/// </exception>
/// <exception cref="T:System.InvalidOperationException">
/// The function was called while formulas are defined in the formula engine
/// <para>A function with the same name is already defined</para>
/// </exception>
public void AddFunction(FormulaFunctionCall functionCall)
{
FormulaEngine.ValidateNonNull(functionCall, "functionCall");
ValidateEngineStateForChangingFunctions();
var attr =
(FormulaFunctionAttribute)
Attribute.GetCustomAttribute(functionCall.Method, typeof(FormulaFunctionAttribute));
if (attr == null)
{
throw new ArgumentException("The function does not have a FormulaFunctionAttribute defined on it");
}
var info = new FunctionInfo(functionCall, attr);
AddFunctionInternal(info);
}
/// <summary>FormulaEngineに登録されている変数・関数を更新する。</summary>
public void UpdateNumericsSetting()
{
LogMethodStart();
GlobalStructureNumericsData numerics = OpenedProject.GlobalStructureNumerics;
FormulaEngine.VariablesList.Clear();
FormulaEngine.CustomFuncList.Clear();
foreach (ParameterData par in numerics.Parameters)
{
FormulaEngine.VariablesList.Add(par.Name, par.Value);
}
foreach (FunctionData func in numerics.Functions)
{
FormulaEngine.CustomFuncList.Add(func.GetMathFunction());
}
FormulaEngine.InitializeInterpreter();
LogMethodEnd();
}
/// <summary>式を検証し、結果を返す。</summary>
/// <param name="formula"></param>
/// <param name="count"></param>
/// <param name="formula_LastValid"></param>
/// <param name="resultList"></param>
/// <param name="isValid"></param>
protected void ValidateFormula(string formula, int count, string formula_LastValid, double[] resultList, out bool isValid)
{
FormulaEngine engine = Application.FormulaEngine;
double t;
isValid = true;
try
{
for (int i = 0; i < count; i++)
{
t = i / (double)(count - 1);
resultList[i + 1] = engine.CalculateWithT(t, formula);
}
}
catch (FormulaResultException)
{
isValid = false;
for (int i = 0; i < count; i++)
{
t = i / (double)(count - 1);
resultList[i + 1] = engine.CalculateWithT(t, formula_LastValid);
}
}
try
{
t = -1.0 / (double)(count - 1);
resultList[0] = engine.CalculateWithT(t, formula);
}
catch (FormulaResultException)
{
resultList[0] = resultList[1];
}
try
{
t = count / (double)(count - 1);
resultList[count + 1] = engine.CalculateWithT(t, formula);
}
catch (FormulaResultException)
{
resultList[count + 1] = resultList[count];
}
}
private ErrorValueWrapper ExcelError2FormulaError(int value)
{
XlCVError excelErrorValue = (XlCVError)(value & 0xffff);
ErrorValueType errorValue;
switch (excelErrorValue)
{
case XlCVError.xlErrDiv0:
errorValue = ErrorValueType.Div0;
break;
case XlCVError.xlErrNA:
errorValue = ErrorValueType.NA;
break;
case XlCVError.xlErrName:
errorValue = ErrorValueType.Name;
break;
case XlCVError.xlErrNull:
errorValue = ErrorValueType.Null;
break;
case XlCVError.xlErrNum:
errorValue = ErrorValueType.Num;
break;
case XlCVError.xlErrRef:
errorValue = ErrorValueType.Ref;
break;
case XlCVError.xlErrValue:
errorValue = ErrorValueType.Value;
break;
default:
throw new InvalidOperationException("Unknown error code");
}
return(FormulaEngine.CreateError(errorValue));
}
public override void Evaluate(Stack state, FormulaEngine engine)
{
var rhs = (IOperand)state.Pop();
var lhs = (IOperand)state.Pop();
IOperand result;
IOperand lhsConverted = lhs.Convert(ArgumentType);
IOperand rhsConverted = rhs.Convert(ArgumentType);
if (lhsConverted == null | rhsConverted == null)
{
result = GetConvertError(lhs, rhs);
}
else
{
result = ComputeValue(lhsConverted, rhsConverted, engine);
}
state.Push(result);
}
/// <summary>アプリケーションエントリーポイント。</summary>
internal void EntryPoint(string[] args)
{
try
{
LogWriter = new StreamWriter("WaveguideDesigner.log");
}
catch { MessageBox.Show("ログファイルを開けませんでした。ログ出力はされません。", "", MessageBoxButtons.OK, MessageBoxIcon.Error); }
LogMethodStart();
CurrentUndoDepth = 0;
FormulaEngine = new FormulaEngine();
DoesUpdateShapeAuto = true;
EnableUndoRedoStoring = true;
if (args.Length != 0)
{
OpenProject(args[0]);
}
if (MainForm != null)
{
throw new InvalidOperationException();
}
System.Windows.Forms.Application.EnableVisualStyles();
System.Windows.Forms.Application.SetCompatibleTextRenderingDefault(false);
MainForm = new Forms.MainForm();
try
{
System.Windows.Forms.Application.Run(MainForm);
}
catch (Exception e)
{
WriteLog(e.ToString());
}
LogMethodEnd();
LogWriter?.Dispose();
}
private void btnParse_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
#region #parse
FormulaEngine engine = spreadsheetControl1.Document.FormulaEngine;
if (spreadsheetControl1.ActiveCell != null)
{
// Get the formula for parsing.
string formula = spreadsheetControl1.ActiveCell.Formula;
if (formula != string.Empty)
{
// Parse the formula
ParsedExpression p_expr = engine.Parse(formula);
// Traverse the expression tree and modify it.
MyVisitor visitor = new MyVisitor();
p_expr.Expression.Visit(visitor);
// Reconsitute the formula.
string formula1 = p_expr.ToString();
// Place the formula back to the cell.
spreadsheetControl1.ActiveCell.Formula = formula1;
}
#endregion #parse
}
}
private void btnEvaluateExpressionStyle_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
#region #evaluateExpressionStyle
FormulaEngine engine = spreadsheetControl1.Document.FormulaEngine;
// Get coordinates of the active cell.
int columnIndex = spreadsheetControl1.ActiveCell.ColumnIndex;
int rowIndex = spreadsheetControl1.ActiveCell.RowIndex;
// Create the expression context.
ExpressionContext context = new ExpressionContext(columnIndex, rowIndex, this.contextSheet,
CultureInfo.GetCultureInfo("en-US"), ReferenceStyle.UseDocumentSettings, (ExpressionStyle)editExpressionStyle.EditValue);
// Evaluate the expression.
ParameterValue result = engine.Evaluate(spreadsheetControl1.ActiveCell.Formula, context);
// Get the result.
string res = string.Empty;
if (result.IsArray)
{
res += Environment.NewLine;
int rowLength = result.ArrayValue.GetLength(0);
int colLength = result.ArrayValue.GetLength(1);
for (int i = 0; i < rowLength; i++)
{
for (int j = 0; j < colLength; j++)
{
res += String.Format("{0} ", result.ArrayValue[i, j]);
}
res += Environment.NewLine;
}
}
else
{
res = result.ToString();
}
#endregion #evaluateExpressionStyle
MessageBox.Show(String.Format("The result is {0}", res), "Evaluate Using Expression Style");
}
