/// <summary>
/// Constructs a formula and adds another formula as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="parameter">Another formula to add to this formula's parameter list</param>
public static Formula Formula(string function, Formula parameter)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(parameter).EndGroup();
return formula;
}
/// <summary>
/// Constructs a formula and adds a range as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="range">Range to add as parameter</param>
/// <example><code>Formula formula = new Formula("sum", new Range(cell1, cell2));</code></example>
public static Formula Formula(string function, Range range)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(range).EndGroup();
return formula;
}
/// <summary>
/// Constructs a formula without any parameters
/// </summary>
/// <param name="function">Function name</param>
/// <example><code>Formula formula = new Formula("sum");</code></example>
public static Formula Formula(string function)
{
Formula formula = new Formula();
formula.Add(function).EmptyGroup();
return formula;
}
private static void ParseFormula(Cell cell, Formula formula, string formulaText)
{
Match match;
ParseArgumentType pat = GetArgumentType(formulaText, out match);
switch (pat)
{
case ParseArgumentType.Function:
{
string function = match.Groups["FunctionName"].Value;
Formula subFormula = new Formula();
subFormula.Add(function).StartGroup();
string[] parameters = match.Groups["Parameters"].Value.Split(new [] { ',' });
foreach (string parameter in parameters)
ParseFormula(cell, subFormula, parameter);
subFormula.EndGroup();
formula.Add(subFormula);
break;
}
case ParseArgumentType.Range:
case ParseArgumentType.AbsoluteRange:
{
Range range = new Range(formulaText);
formula.Add(range);
break;
}
case ParseArgumentType.None:
{
formula.Add(formulaText);
break;
}
}
}
internal Parameter(Formula p)
{
ParameterType = ParameterType.Formula;
Value = p;
}
/// <summary>
/// Adds another formula as a parameter in a formula
/// </summary>
/// <param name="formula">Another formula to add to this formula's parameter list</param>
public Formula Add(Formula formula)
{
Parameter p = new Parameter(formula);
Parameters.Add(p);
return this;
}
internal static void Parse(Cell cell, string formulaText)
{
if (formulaText[0] != '=')
{
cell.Value = formulaText;
cell.Content = ContentType.UnresolvedValue;
}
formulaText = formulaText.Substring(1);
Formula formula = new Formula();
ParseFormula(cell, formula, formulaText);
if (formula.parameters[0].ParameterType == ParameterType.Formula)
{
cell.Value = formula.parameters[0].Value as Formula;
cell.Content = ContentType.Formula;
}
else
{
cell.Value = formula;
cell.Content = ContentType.Formula;
}
}
/// <summary>
/// Constructs a formula and adds a filtered range as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="range">Range to add as parameter</param>
/// <param name="cellCompare">A custom defined to compare the values of the range</param>
/// <remarks>
/// Custom delegates can filter all cells and auto add them to the parameter list of a formula
/// by passing a System.Predicate>Cell<, i.e. a
/// delegate which accepts Cell as its value and returns bool
/// to both Formula constructor or Add. All the values accessors (i.e. Value, NumericValue etc.)
/// and cell style can be checked.
/// </remarks>
/// <example>
/// Lets assume column 1,2,3,6 and 7 are bold...
/// <code>
/// XmlStyle style = new XmlStyle();
/// style.Font.Bold = true;
///
/// // VS2008 style
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// cell => cell.Style == style);
///
/// // or VS2005 style
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// delegate (Cell cell) { return cell.Style == style; } );
/// </code>
/// In the first example of style, the value of the cell will be =SUM(A4:C4, F4:G4).
/// <para><b>Continuous ranges matching to true will be joined as one parameter, i.e. A4:C4
/// and not as seperate parameters, i.e. A4,B4,C4</b></para>
///
/// Using value accessors...
/// <code>
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// cell => cell.NumericValue > 10000 & cell.NumericValue <= 50000);
/// </code>
/// </example>
public static Formula Formula(string function, Range range, Predicate<Cell> cellCompare)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(range, cellCompare).EndGroup();
return formula;
}