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