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