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