AbstractRange Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

Base class for classes representing one or more excel cells.

public abstract class AbstractRange
Public MustInherit Class AbstractRange
Inheritance:
System.Object
AbstractRange
Derived
CellRange
ExcelCell

Properties

CharacterRanges

Returns all FormattedCharacterRange objects that represents a range of characters within the cell text.

public IEnumerable<FormattedCharacterRange> CharacterRanges { get; }
Public ReadOnly Property CharacterRanges As IEnumerable(Of FormattedCharacterRange)
Property Value
System.Collections.Generic.IEnumerable<FormattedCharacterRange>

All FormattedCharacterRange objects that represents a range of characters within the cell text.

Comment

Gets or sets comment.

public abstract ExcelComment Comment { get; set; }
Public MustOverride Property Comment As ExcelComment
Property Value
ExcelComment

Formula

Gets or sets formula string.

public abstract string Formula { get; set; }
Public MustOverride Property Formula As String
Property Value
System.String

FormulaR1C1

Gets or sets a formula string in R1C1 notation.

public abstract string FormulaR1C1 { get; set; }
Public MustOverride Property FormulaR1C1 As String
Property Value
System.String

Gets or sets hyperlink.

public abstract SpreadsheetHyperlink Hyperlink { get; set; }
Public MustOverride Property Hyperlink As SpreadsheetHyperlink
Property Value
SpreadsheetHyperlink

Name

Gets the name of AbstractRange instance.

public string Name { get; }
Public ReadOnly Property Name As String
Property Value
System.String
Remarks

A cell name includes its column letter and row number. For example, the name of a cell in row 0 and column 0 is "A1". A cell range name includes the column letter and row number of first (top-left) and last (bottom-right) cell. For example, the name of range [0, 0]...[4, 4] is "A1:E5", of range [0, 0]...[4, MaxColumns-1] is "1:5", of range [0, 0]...[MaxRows-1, 4] is "A:E", of single cell range [4, 4]...[4, 4] is "E5".

Style

Gets or sets the cell formatting of one or more cells.

public CellStyle Style { get; set; }
Public Property Style As CellStyle
Property Value
CellStyle

The cell formatting of one or more cells.

Remarks

Conceptually, cell formatting is divided into following groups:

  • Number - indicates how to format and render the numeric value of a cell. Associated property is NumberFormat.
  • Alignment - formatting information pertaining to text alignment in cells. Associated properties are HorizontalAlignment, VerticalAlignment, Indent, Rotation, IsTextVertical, WrapText and ShrinkToFit.
  • Font - defines the properties for the used font. Associated property is Font.
  • Border - expresses a single set of cell border formats (left, right, top, bottom and diagonal). Associated property is Borders.
  • Fill - specifies fill formatting (pattern or gradient). Associated property is FillPattern.
  • Protection - contains protection properties associated with the cell. Associated properties are Locked and FormulaHidden.

Additional CellStyle properties not associated with any formatting group are:

  • Name - name of the referenced Style.
  • QuotePrefix - true to store numeric value of a cell as text; otherwise, false.
  • IsDefault - true if Name Style is default (Normal) and there are no additional modifications of cell formatting; otherwise, false.

ExcelFile contains a set of Styles which can be referenced by multiple cells.

ExcelFile must always contain at least one Style which cannot be removed and is, by default, referenced by all cells. This default style is Normal.

Style can either be built-in or user-defined. Built-in style is accessible from Styles via BuiltInCellStyleName enumeration.

Cell formatting group (Number, Alignment, Font, Border, Fill or Protection) (and its associated properties) is resolved from Name Style, unless cell formatting group or its associated property is modified.

Cell formatting is available for one or more cells through Style property which is available on ExcelCell and CellRange types. Cell formatting specified on ExcelColumn and ExcelRow types through Style property is simply propagated to cell formatting of its Cells.

note

For performance reasons, cell formatting on CellRange is resolved based just on its top-left cell formatting, except borders which are resolved based on corner cells depending on border side.

Setting cell formatting property on CellRange is propagated to each cell in a range.

To set Style to one or more cells, simply assign it to Style property.

tip

Preferable way to modify formatting property of multiple cells is to get CellRange to which all those cells belong, and use Style property of that range to make the modification.

If modifying multiple formatting properties of a CellRange, without preserving unmodified formatting properties, preferable way is to create new instance of CellStyle, make modifications on it, and assign it to Style property of that range.

GemBox.Spreadsheet internally takes care not to allocate unnecessary cells when formatting a range of cells (for example, when formatting Cells) and to cache formatting information of equally formatted cells, at the appropriate time, to reduce memory footprint.

Value

Gets or sets cell value on one or more excel cells.

public abstract object Value { get; set; }
Public MustOverride Property Value As Object
Property Value
System.Object

Methods

Calculate()

Calculates formula for one or more excel cells.

public abstract void Calculate()
Public MustOverride Sub Calculate

Clear(ClearOptions)

Clears the range (content, formatting, comment, hyperlink or all).

public void Clear(ClearOptions options)
Public Sub Clear(options As ClearOptions)
Parameters
options
ClearOptions

The clear options.

GetCharacters(Int32)

Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position.

public FormattedCharacterRange GetCharacters(int startIndex)
Public Function GetCharacters(startIndex As Integer) As FormattedCharacterRange
Parameters
startIndex
System.Int32

The zero-based starting character position.

Returns
FormattedCharacterRange
Remarks

This method only works on cell with string value.

Exceptions
System.ArgumentOutOfRangeException

Argument startIndex is less than zero or is equal to or greater than 32767.

GetCharacters(Int32, Int32)

Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position and has a specified length.

public FormattedCharacterRange GetCharacters(int startIndex, int length)
Public Function GetCharacters(startIndex As Integer, length As Integer) As FormattedCharacterRange
Parameters
startIndex
System.Int32

The zero-based starting character position.

length
System.Int32

The number of characters.

Returns
FormattedCharacterRange
Remarks

This method only works on cell with string value.

Exceptions
System.ArgumentOutOfRangeException

Argument startIndex is less than zero or is equal to or greater than 32767.

System.ArgumentOutOfRangeException

Argument length is equal to or less than zero or is greater than 32767.

Insert(InsertShiftDirection)

Inserts a range by shifting cells in the specified direction.

public abstract void Insert(InsertShiftDirection direction)
Public MustOverride Sub Insert(direction As InsertShiftDirection)
Parameters
direction
InsertShiftDirection

The shift direction.

Remove(RemoveShiftDirection)

Removes a range by shifting cells in the specified direction.

public abstract void Remove(RemoveShiftDirection direction)
Public MustOverride Sub Remove(direction As RemoveShiftDirection)
Parameters
direction
RemoveShiftDirection

The shift direction.

ReplaceText(String, String)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

public bool ReplaceText(string oldValue, string newValue)
Public Function ReplaceText(oldValue As String, newValue As String) As Boolean
Parameters
oldValue
System.String

The text to be replaced.

newValue
System.String

The text to replace all occurrences of oldValue.

Returns
System.Boolean

true if text is found, false otherwise.

ReplaceText(String, String, Boolean)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

public bool ReplaceText(string oldValue, string newValue, bool matchCase)
Public Function ReplaceText(oldValue As String, newValue As String, matchCase As Boolean) As Boolean
Parameters
oldValue
System.String

The text to be replaced.

newValue
System.String

The text to replace all occurrences of oldValue.

matchCase
System.Boolean

true to match exact case, false otherwise.

Returns
System.Boolean

true if text is found, false otherwise.

ReplaceText(String, String, Boolean, Boolean)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

public bool ReplaceText(string oldValue, string newValue, bool matchCase, bool matchEntireCellContents)
Public Function ReplaceText(oldValue As String, newValue As String, matchCase As Boolean, matchEntireCellContents As Boolean) As Boolean
Parameters
oldValue
System.String

The text to be replaced.

newValue
System.String

The text to replace all occurrences of oldValue.

matchCase
System.Boolean

true to match exact case, false otherwise.

matchEntireCellContents
System.Boolean

true to match entire cell contents, false otherwise.

Returns
System.Boolean

true if text is found, false otherwise.

ReplaceText(Regex, String)

Replaces all matches of the specified System.Text.RegularExpressions.Regex with specified text in the current AbstractRange.

public bool ReplaceText(Regex regex, string replacement)
Public Function ReplaceText(regex As Regex, replacement As String) As Boolean
Parameters
regex
System.Text.RegularExpressions.Regex

The Regex which should be matched.

replacement
System.String

The text which should replace all matches of searched System.Text.RegularExpressions.Regex.

Returns
System.Boolean

true if text is found, false otherwise.

ToString()

Returns a System.String that represents this AbstractRange instance.

public override string ToString()
Public Overrides Function ToString As String
Returns
System.String

A System.String that represents this AbstractRange instance.

Overrides
System.Object.ToString()
Remarks

This method should be used primarily for debugging purposes and should be considered volatile (format of its return value might change in future versions).

Examples