/// <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); }
/// <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()); }
/// <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)); }
/// <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> /// 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)); }
/// <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> /// Sets the formula function result to a string /// </summary> /// <param name="value">The value you wish to be the result of the function</param> /// <remarks>Use this method when the result of your function is a string</remarks> public void SetValue(string value) { FormulaEngine.ValidateNonNull(value, "value"); MyOperand = new StringOperand(value); }
/// <summary> /// Sets the formula function result to a given argument /// </summary> /// <param name="arg">The value you wish to be the result of the function</param> /// <remarks> /// Use this method when you wish to use one of the arguments supplied to your function as its result without altering /// the value. /// The if function, for example, uses this method. /// </remarks> public void SetValue(Argument arg) { FormulaEngine.ValidateNonNull(arg, "arg"); MyOperand = arg.ValueAsOperand; }
/// <summary> /// Sets the formula function result to a reference /// </summary> /// <param name="value">The value you wish to be the result of the function</param> /// <remarks> /// Use this method when the result of your function is a reference. Usually used when wishing to implement /// dynamic references /// </remarks> public void SetValue(IReference value) { FormulaEngine.ValidateNonNull(value, "value"); MyOperand = (IOperand)value; }
/// <summary> /// Determines whether a rectangle is inside the bounds of a given sheet /// </summary> /// <param name="rect">The rectangle to test</param> /// <param name="sheet">The sheet to use</param> /// <returns>True if the sheet contains the rectangle; False otherwise</returns> /// <remarks> /// Use this function when you have a rectangle and a sheet and need to know if the rectangle is inside the /// sheet's bounds. /// </remarks> public static bool IsRectangleInSheet(Rectangle rect, ISheet sheet) { FormulaEngine.ValidateNonNull(sheet, "sheet"); return(SheetReference.IsRectangleInSheet(rect, sheet)); }
/// <summary> /// Gets the calculation list for a reference /// </summary> /// <param name="root">The reference from where to calculate the list</param> /// <returns>An array of formulas that would need to be recalculated</returns> /// <remarks> /// Given a reference, this method returns a list of formulas that would need to be recalculated when that reference /// changes. /// The formulas in the list will be in natural order. /// </remarks> public Formula[] GetCalculationList(IReference root) { FormulaEngine.ValidateNonNull(root, "root"); Reference[] refs = _owner.DependencyManager.GetReferenceCalculationList((Reference)root); return(_owner.GetFormulasFromReferences(refs)); }