/// <summary>
/// Creates a sheet reference from a rectangle and a sheet
/// </summary>
/// <param name="rect">The rectangle to create the sheet reference from</param>
/// <param name="sheet">The sheet that the reference will be on</param>
/// <returns>A sheet reference that matches the given rectangle and is on the given sheet</returns>
/// <remarks>
/// Use this method when you have a rectangle that you would like translated into a sheet reference. Note that the
/// top-left corner of the sheet is (1,1). The method will try to create the appropriate type of reference based on
/// the
/// dimensions of the rectangle. For example: A rectangle 1 unit wide and 1 unit tall will be translated into a cell
/// reference
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// <para>The resulting sheet reference is not within the bounds of its sheet</para>
/// <para>The given sheet argument is not registered with the SheetManager</para>
/// </exception>
/// <example>
/// The following code creates a reference to the range A1:B2 on the currently active sheet
/// <code>
/// Dim engine As New FormulaEngine
/// Dim rect As New Rectangle(1, 1, 2, 2)
/// Dim ref As ISheetReference = factory.FromRectangle(rect)
/// </code>
/// </example>
public ISheetReference FromRectangle(ISheet sheet, Rectangle rect)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
SheetReference @ref;
Rectangle sheetRect = SheetReference.GetSheetRectangle(sheet);
if (rect.Width == 1 & rect.Height == 1)
{
@ref = new CellReference(rect.Top, rect.Left);
}
else if (rect.Height == sheetRect.Height)
{
@ref = new ColumnReference(rect.Left, rect.Right - 1);
}
else if (rect.Width == sheetRect.Width)
{
@ref = new RowReference(rect.Top, rect.Bottom - 1);
}
else
{
@ref = new CellRangeReference(rect);
}
InitializeGridReference(@ref, sheet);
return(@ref);
}
private ReferencePool(SerializationInfo info, StreamingContext context)
{
_owner = (FormulaEngine)info.GetValue("Engine", typeof(FormulaEngine));
_referenceMap =
(IDictionary <Reference, ReferencePoolInfo>)
info.GetValue("Map", typeof(IDictionary <Reference, ReferencePoolInfo>));
}
/// <summary>
/// Creates a reference to a range of columns on a given sheet
/// </summary>
/// <param name="sheet">The sheet the reference will use</param>
/// <param name="start">The left column of the range</param>
/// <param name="finish">The right column of the range</param>
/// <returns>A sheet reference to the range of columns on the given sheet</returns>
/// <remarks>
/// This method will create a sheet reference to an entire range of columns on the given sheet. Use it when you
/// want to reference entire columns and have the two indices handy.
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// <para>The resultant range is not within the bounds of the given sheet</para>
/// <para>The given sheet argument is not registered with the SheetManager</para>
/// </exception>
/// <example>
/// The following example creates a reference to columns A through C on the currently active sheet
/// <code>
/// Dim engine As New FormulaEngine
/// Dim ref As ISheetReference = engine.ReferenceFactory.Columns(1, 3)
/// </code>
/// </example>
public ISheetReference Columns(ISheet sheet, int start, int finish)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
var @ref = new ColumnReference(start, finish);
InitializeGridReference(@ref, sheet);
return(@ref);
}
/// <summary>
/// Creates a sheet reference to a cell on a specific sheet
/// </summary>
/// <param name="sheet">The sheet the reference will be on</param>
/// <param name="row">The row of the cell; first row is 1</param>
/// <param name="column">The column of the cell; first column is 1</param>
/// <returns>A sheet reference to the specified row and column and on the given sheet</returns>
/// <remarks>
/// Use this method when you need a sheet reference to a specific cell and sheet and have
/// the row and column indices handy.
/// You usually need a cell reference when you wish to bind a formula to a cell
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// <para>The cell at row,col is not within the bounds of the given sheet</para>
/// <para>The given sheet argument is not registered with the SheetManager</para>
/// </exception>
/// <example>
/// The following code creates a reference to the cell C3 on the currently active sheet
/// <code>
/// Dim engine As New FormulaEngine
/// Dim ref As ISheetReference = engine.ReferenceFactory.Cell(3, 3)
/// </code>
/// </example>
public ISheetReference Cell(ISheet sheet, int row, int column)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
var @ref = new CellReference(row, column);
InitializeGridReference(@ref, sheet);
return(@ref);
}
/// <summary>
/// Creates a sheet reference to a range of cells on a given sheet
/// </summary>
/// <param name="sheet">The sheet the reference will be on</param>
/// <param name="startRow">The top row of the range</param>
/// <param name="startColumn">The left column of the range</param>
/// <param name="endRow">The bottom row of the range</param>
/// <param name="endColumn">The right column of the range</param>
/// <returns>A sheet reference to the specified range on the specified sheet</returns>
/// <remarks>
/// Use this method when you need a sheet reference to a range of cells on a specific sheet and have the four
/// indices handy.
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// <para>The resultant range is not within the bounds of the given sheet</para>
/// <para>The given sheet argument is not registered with the SheetManager</para>
/// </exception>
/// <example>
/// The following example creates a reference to the range C3:E4 on the currently active sheet
/// <code>
/// Dim engine As New FormulaEngine
/// Dim ref As ISheetReference = engine.ReferenceFactory.Cells(3, 3, 4, 5)
/// </code>
/// </example>
public ISheetReference Cells(ISheet sheet, int startRow, int startColumn, int endRow, int endColumn)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
var @ref = new CellRangeReference(startRow, startColumn, endRow, endColumn);
InitializeGridReference(@ref, sheet);
return(@ref);
}
/// <summary>
/// Creates a named reference
/// </summary>
/// <param name="name">The name of the reference</param>
/// <returns>A reference to the name</returns>
/// <remarks>
/// A named reference lets you refer to a formula by a name and lets you refer to that name in other formulas.
/// A valid name must start with an underscore or letter and can be followed by any combination of underscores,
/// letters, and numbers.
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// <para>The name argument is not in the proper format for a named reference</para>
/// </exception>
public INamedReference Named(string name)
{
FormulaEngine.ValidateNonNull(name, "name");
if (NamedReference.IsValidName(name) == false)
{
OnInvalidReferenceString();
}
var @ref = new NamedReference(name);
@ref.SetEngine(_owner);
@ref.ComputeHashCode();
return(@ref);
}
/// <summary>
/// Removes a sheet from the collection
/// </summary>
/// <param name="sheet">The sheet to remove</param>
/// <remarks>
/// This method unregisters a sheet from the formula engine. All references on the removed sheet will become invalid
/// and all formulas using those references will be recalculated.
/// </remarks>
/// <exception cref="System.ArgumentException">The sheet is not contained in the collection</exception>
public void Remove(ISheet sheet)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
if (Contains(sheet) == false)
{
throw new ArgumentException("Sheet not in list");
}
_sheets.Remove(sheet);
_owner.OnSheetRemoved(sheet);
if (Count == 0)
{
_activeSheet = null;
}
}
/// <summary>
/// Creates a sheet reference from a string
/// </summary>
/// <param name="s">A string that contains a sheet reference expression</param>
/// <returns>A sheet reference parsed from the given string</returns>
/// <remarks>
/// This method creates a sheet reference by parsing a given string.
/// The method accepts strings with the following syntax:
/// <list type="table">
/// <listheader>
/// <term>String format</term><description>Resultant reference</description>
/// </listheader>
/// <item>
/// <term>Column letter followed by a row number: "C3"</term><description>Cell</description>
/// </item>
/// <item>
/// <term>Two column letter and row number pairs separated by a colon: "C3:D4"</term>
/// <description>Cell range</description>
/// </item>
/// <item>
/// <term>Two column letters separated by a colon: "E:G"</term><description>Columns</description>
/// </item>
/// <item>
/// <term>Two row numbers separated by a colon: "4:6"</term><description>Rows</description>
/// </item>
/// </list>
/// All of the above formats can specify a specific sheet by prefixing the reference with a sheet name
/// followed by an exclamation point (ie: "Sheet2!E:G")
/// If no sheet name is specified, the currently active sheet is used.
/// </remarks>
/// <example>
/// This example shows how you would create sheet references from various strings
/// <code>
/// ' Get a reference to cell A1
/// Dim cellRef As ISheetReference = factory.Parse("A1")
/// ' Get a reference to cells B2 through E4
/// Dim rangeRef As ISheetReference = factory.Parse("b2:e4")
/// ' Get a reference to columns D through F
/// Dim colsRef As ISheetReference = factory.Parse("D:F")
/// 'Get a reference to rows 4 through 6
/// Dim rowsRef As ISheetReference = factory.Parse("4:6")
/// ' Get a reference to cell C4 on sheet 'Sheet4'
/// Dim cellRef As ISheetReference = factory.Parse("Sheet4!C4")
/// </code>
/// </example>
/// <exception cref="T:System.ArgumentException">
/// <para>The given string could not be parsed into a sheet reference</para>
/// <para>The string references a sheet name that is not defined</para>
/// <para>The resulting sheet reference is not within the bounds of its sheet</para>
/// </exception>
public ISheetReference Parse(string s)
{
FormulaEngine.ValidateNonNull(s, "s");
SheetReference @ref = TryParseGridReference(s);
if (@ref == null)
{
OnInvalidReferenceString();
}
InitializeParsedGridReference(@ref, s);
return(@ref);
}
internal Formula(FormulaEngine owner, string expression)
{
Engine = owner;
// By default, we evaluate to a primitive
ResultType = OperandType.Primitive;
// Get our analyzer, have it parse the expression, and get all our information from it
var analyzer = (CustomFormulaAnalyzer)Engine.Parser.Analyzer;
ParseTreeElement rootElement = Parse(expression);
ReferenceParseInfo[] infos = analyzer.ReferenceInfos;
ProcessParseInfos(infos);
Template = CreateTemplateString(expression, infos);
analyzer.ResetReferences();
_components = CreateComponents(rootElement);
ValidateComponents();
ComputeRawReferenceHashCodes();
ComputeDependencyReferences();
}
private void InsertSheet(ISheet sheet, int index)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
if (Contains(sheet))
{
throw new ArgumentException("The sheet is already contained in the collection");
}
FormulaEngine.ValidateNonNull(sheet.Name, "sheet.Name");
if (GetSheetByName(sheet.Name) != null)
{
throw new ArgumentException("A sheet with that name already exists");
}
_sheets.Insert(index, sheet);
if (Count == 1)
{
_activeSheet = sheet;
}
}
/// <summary>
/// Gets a sheet by name
/// </summary>
/// <param name="name">The name of the desired sheet</param>
/// <returns>An instance of a sheet with the same name; a null reference if no sheet with the name is found</returns>
/// <remarks>This method lets you get an instance of a sheet by specifying its name</remarks>
public ISheet GetSheetByName(string name)
{
FormulaEngine.ValidateNonNull(name, "name");
return(_sheets.Cast <ISheet>().FirstOrDefault(sheet => name.Equals(sheet.Name, StringComparison.OrdinalIgnoreCase)));
}
private DependencyManager(SerializationInfo info, StreamingContext context)
{
_dependents = (DependencyMap)info.GetValue("Dependents", typeof(DependencyMap));
_precedents = (DependencyMap)info.GetValue("Precedents", typeof(DependencyMap));
_owner = (FormulaEngine)info.GetValue("Engine", typeof(FormulaEngine));
}
internal ReferenceFactory(FormulaEngine owner)
{
_owner = owner;
}
public ReferencePool(FormulaEngine owner)
{
_referenceMap = new Dictionary <Reference, ReferencePoolInfo>(new ReferenceEqualityComparer());
_owner = owner;
}
internal SheetCollection(FormulaEngine owner)
{
_owner = owner;
_sheets = new ArrayList();
}
private SheetCollection(SerializationInfo info, StreamingContext context)
{
_owner = (FormulaEngine)info.GetValue("Engine", typeof(FormulaEngine));
_activeSheet = (ISheet)info.GetValue("ActiveSheet", typeof(ISheet));
_sheets = (IList)info.GetValue("Sheets", typeof(IList));
}
public DependencyManager(FormulaEngine owner)
{
_owner = owner;
_dependents = new DependencyMap();
_precedents = new DependencyMap();
}
