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