Search Results for

    Show / Hide Table of Contents

    Class Range

    A class that represents a range of cells

    Inheritance
    System.Object
    Range
    RangeColumn
    RangeRow
    WorkSheet
    Implements
    System.Collections.Generic.IEnumerable<Cell>
    System.Collections.IEnumerable
    Namespace: IronXL
    Assembly: IronXL.dll
    Syntax
    public class Range : Object, IEnumerable<Cell>, IEnumerable

    Properties

    AllColumnsInRange

    Columns is now doing same thing as this

    Declaration
    public RangeColumn[] AllColumnsInRange { get; }
    Property Value
    Type Description
    RangeColumn[]

    AllRowsInRange

    Rows is now doing same thing as this

    Declaration
    public RangeRow[] AllRowsInRange { get; }
    Property Value
    Type Description
    RangeRow[]

    BoolValue

    Gets or sets the System.Boolean value of this Range of cells.

    Getting the BoolValue will return true only if ALL cells in the range are true. Numeric cells greater than zero are treated as TRUE. [Logical AND Operator Applied]

    Setting the BoolValue will set all cells in with the same System.Boolean value.

    Declaration
    public virtual bool BoolValue { get; set; }
    Property Value
    Type Description
    System.Boolean

    The boolean AND aggregate value of the cells.

    Error end NAN cells will return as a false .

    ColumnCount

    The number of columns in this range, including empty ones.

    Declaration
    public int ColumnCount { get; }
    Property Value
    Type Description
    System.Int32

    Columns

    Array of columns (including empty ones) within the current range. The columns contain Cell objects.

    Declaration
    public RangeColumn[] Columns { get; }
    Property Value
    Type Description
    RangeColumn[]

    Count

    The number of all cells (empty and otherwise) in this range.

    Declaration
    public int Count { get; }
    Property Value
    Type Description
    System.Int32

    DateTimeValue

    Gets or sets the nullable System.DateTime value of this Range of cells.

    Getting the DateTimeValue will return highest (MAX) DateTime in the Range, or null if no System.DateTime is found.

    Setting the DateTimeValue will set all cells in with the same System.DateTime value.

    Declaration
    public Nullable<DateTime> DateTimeValue { get; set; }
    Property Value
    Type Description
    System.Nullable<System.DateTime>

    The MAX System.DateTime of all of the cells.

    DecimalValue

    Gets or sets the System.Decimal value of this Range of cells.

    Getting the DecimalValue will return the SUM of all numeric cells in the Range.

    Setting the DecimalValue will set all cells in with the same System.Decimal value.

    Declaration
    public virtual Decimal DecimalValue { get; set; }
    Property Value
    Type Description
    System.Decimal

    The System.Decimal SUM of all of the cells.

    Error end NAN cells will count as 0

    DoubleValue

    Gets or sets the double value of all Cells in this Rance.

    Declaration
    public virtual double DoubleValue { get; set; }
    Property Value
    Type Description
    System.Double

    The double value.

    FloatValue

    Gets or sets the System.Single value of this Range of cells.

    Getting the FloatValue will return the SUM of all numeric cells in the Range.

    Setting the FloatValue will set all cells in with the same System.Single value.

    Declaration
    public virtual float FloatValue { get; set; }
    Property Value
    Type Description
    System.Single

    The System.Single SUM of all of the cells.

    Error end NAN cells will count as 0

    FormatString

    Property to get or set range format string.

    If range contains one cell: {get} returns the cell format string; {set} changes the cell format string

    If range contains more than one cell: {get} returns the format string if all cells within range have the same format string, otherwise returns null; {set} changes the format string of every cell within the range.

    Declaration
    public string FormatString { get; set; }
    Property Value
    Type Description
    System.String
    Remarks

    You can find the list of the built-in format strings in the class BuiltinFormats.

    Formula

    Property to get or set range formula.

    If range contains one cell: {get} returns the cell formula; {set} changes the cell formula

    If range contains more than one cell: {get} returns the formula if all cells within range have the same formula, otherwise returns null; {set} changes the formula of every cell within the range.

    Declaration
    public string Formula { get; set; }
    Property Value
    Type Description
    System.String

    Int32Value

    Synonym of IntValue

    Gets or sets the System.Int32 (Int32) value of this Range of cells.

    Getting the LongValue will return the SUM of all numeric cells in the Range.

    Setting the LongValue will set all cells in with the same System.Int32 value.

    Declaration
    public virtual int Int32Value { get; set; }
    Property Value
    Type Description
    System.Int32

    The System.Int32SUM of all of the cells.

    Error end NAN cells will count as 0

    Int64Value

    Synonym of LongValue

    Gets or sets the System.Int64 (Int64) value of this Range of cells.

    Getting the LongValue will return the SUM of all numeric cells in the Range.

    Setting the LongValue will set all cells in with the same System.Int64 value.

    Declaration
    public virtual long Int64Value { get; set; }
    Property Value
    Type Description
    System.Int64

    The System.Int64 SUM of all of the cells.

    Error end NAN cells will count as 0

    IntValue

    Gets or sets the System.Int32 value of this Range of cells.

    Getting the IntValue will return the SUM of all numeric cells in the Range.

    Setting the IntValue will set all cells in with the same System.Int32 value.

    Declaration
    public virtual int IntValue { get; set; }
    Property Value
    Type Description
    System.Int32

    The System.Int32 SUM of all of the cells.

    Error end NAN cells will count as 0

    IsEmpty

    Property that indicates if the range is empty.

    Declaration
    public bool IsEmpty { get; }
    Property Value
    Type Description
    System.Boolean

    LongValue

    Gets or sets the System.Int64 (Int64) value of this Range of cells.

    Getting the LongValue will return the SUM of all numeric cells in the Range.

    Setting the LongValue will set all cells in with the same System.Int64 value.

    Declaration
    public virtual long LongValue { get; set; }
    Property Value
    Type Description
    System.Int64

    The System.Int64 SUM of all of the cells.

    Error end NAN cells will count as 0

    RangeAddress

    Address of this range.

    Declaration
    public virtual RangeAddress RangeAddress { get; }
    Property Value
    Type Description
    RangeAddress

    RangeAddressAsString

    Gets a string representation of this Range's RangeAddress.

    E.g. "A1:B25"

    Declaration
    public virtual string RangeAddressAsString { get; }
    Property Value
    Type Description
    System.String

    RowCount

    The number of rows in this range, including empty ones.

    Declaration
    public int RowCount { get; }
    Property Value
    Type Description
    System.Int32

    Rows

    Array of rows (including empty ones) within the current range. The rows contain Cell objects.

    Declaration
    public RangeRow[] Rows { get; }
    Property Value
    Type Description
    RangeRow[]

    StringValue

    Gets or sets the System.String value of this Range of cells.

    Getting the StringValue will return formatted System.String values. Columns will be delimited by TABS. Rows delimited by NEWLINE

    Setting the StringValue will set all cells in with the same System.String value.

    Declaration
    public virtual string StringValue { get; set; }
    Property Value
    Type Description
    System.String

    The string value of all of the cells. Numbers and dates will be rendered into readable strings; as displayed in Excel. Formulas will be calculated.

    Error end NAN cells will return as a null string.

    Style

    Style of all cells within this range.

    Declaration
    public IStyle Style { get; }
    Property Value
    Type Description
    IStyle

    Value

    Property to get or set range value.

    If range contains one cell: {get} returns the cell value; {set} changes the cell value

    If range contains more than one cell: {get} returns the sum of all numeric cells within range; {set} changes the value of every cell within the range.

    Declaration
    public object Value { get; set; }
    Property Value
    Type Description
    System.Object

    WorkBook

    The workbook this Range belongs to.

    Declaration
    public WorkBook WorkBook { get; }
    Property Value
    Type Description
    WorkBook

    Methods

    AddRange(Range)

    Add another Range to this current Range. After this operation, the current Range will be extended to span over both ranges. So if range A is A1:B2 and range B is C3:D4, then after calling this method, range A will be A1:D4.

    Declaration
    public virtual Range AddRange(Range range)
    Parameters
    Type Name Description
    Range range

    The range to add to the current range.

    Returns
    Type Description
    Range

    This Range allowing a fluent coding style.

    AddRange(RangeAddress)

    Add another RangeAddress to this current Range. After this operation, the current Range will be extended to span over both ranges. So if range A is A1:B2 and range B is C3:D4, then after calling this method, range A will be A1:D4.

    Declaration
    public virtual Range AddRange(RangeAddress address)
    Parameters
    Type Name Description
    RangeAddress address

    The range address to add to the current range.

    Returns
    Type Description
    Range

    This Range allowing a fluent coding style.

    Avg()

    Returns average value of all numeric cells

    Declaration
    public Decimal Avg()
    Returns
    Type Description
    System.Decimal

    The average value as a decimal.

    ClearContents()

    Empties the contents of the Range, leaving all Cells empty.

    Declaration
    public virtual void ClearContents()

    Column(Int32)

    Get a column by index

    Declaration
    protected RangeColumn Column(int index)
    Parameters
    Type Name Description
    System.Int32 index
    Returns
    Type Description
    RangeColumn

    Copy(WorkSheet, String)

    Copies the specified range to the sheet and inserts it at the specified point.

    E.g. Pastes this Range to a WorkSheet at a specified cell location

    Declaration
    public void Copy(WorkSheet targetWorkSheet, string pointToInsert)
    Parameters
    Type Name Description
    WorkSheet targetWorkSheet

    The worksheet to paste this Range into.

    System.String pointToInsert

    The cell at which to paste the range. E.g. "A11"

    GetEnumerator()

    IEnumerable implementation.

    Declaration
    public IEnumerator<Cell> GetEnumerator()
    Returns
    Type Description
    System.Collections.Generic.IEnumerator<Cell>

    An IEnumerator allowing for-each logical iteration of cells with Ranges.

    GetValue<TType>()

    Method for getting a range's value.

    If range contains one cell then method calls GetValue<TType>() of this cell

    If range contains more than one cell then method tries to cast Sum() of all numeric cells within range to TType

    Use Try/Catch to avoid possible exceptions from this method if cell content types are inconsistent or contain formula errors.

    Declaration
    public TType GetValue<TType>()
    Returns
    Type Description
    TType

    The value of the cell, cast as TType

    Type Parameters
    Name Description
    TType

    The .net type to which the cell's value will be cast.

    Exceptions
    Type Condition
    System.ArgumentException

    Range value cannot be casted to {type.Name}

    InsertColumn(Int32)

    Inserts a new column into the worksheet at the specified position. All columns to the right of the columnIndex will be shifted right one of column.

    Declaration
    public virtual RangeColumn InsertColumn(int columnIndex)
    Parameters
    Type Name Description
    System.Int32 columnIndex

    A 0-based index of the position where to insert a new column

    Returns
    Type Description
    RangeColumn

    RangeColumn that was created in the process

    InsertColumn(String)

    Inserts a new column into the worksheet at the specified position. All columns to the right of the columnLetter will be shifted right one of column.

    Declaration
    public virtual RangeColumn InsertColumn(string columnLetter)
    Parameters
    Type Name Description
    System.String columnLetter

    A letter of the position where to start insert a new column

    Returns
    Type Description
    RangeColumn

    RangeColumn that was created in the process

    InsertColumns(Int32, Int32)

    Inserts specified nunmber of new RangeColumns into the worksheet at the specified position. All columns to the right of the columnIndex will be shifted right columnsCount number of columns.

    Declaration
    public virtual List<RangeColumn> InsertColumns(int columnIndex, int columnsCount = 1)
    Parameters
    Type Name Description
    System.Int32 columnIndex

    A 0-based index of the position where to start inserting new columns

    System.Int32 columnsCount

    Number of columns to insert

    Returns
    Type Description
    System.Collections.Generic.List<RangeColumn>

    RangeColumns that were created in the process

    Exceptions
    Type Condition
    System.ArgumentException

    InsertColumns(String, Int32)

    Inserts specified nunmber of new RangeColumns into the worksheet at the specified position. All columns to the right of the columnLetter will be shifted right columnsCount number of columns.

    Declaration
    public virtual List<RangeColumn> InsertColumns(string columnLetter, int columnsCount = 1)
    Parameters
    Type Name Description
    System.String columnLetter

    A letter of the position where to start inserting new columns

    System.Int32 columnsCount

    Number of columns to insert

    Returns
    Type Description
    System.Collections.Generic.List<RangeColumn>

    RangeColumns that were created in the process

    Exceptions
    Type Condition
    System.ArgumentException

    InsertRow(Int32)

    Inserts a new row into the worksheet at the specified position. All rows below the rowIndex will be shifted down one of row.

    Declaration
    public virtual RangeRow InsertRow(int rowIndex)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    A 0-based index of the position where to insert a new row

    Returns
    Type Description
    RangeRow

    RangeRow that was created in the process

    InsertRows(Int32, Int32)

    Inserts specified nunmber of new RangeRows into the worksheet at the specified position. All rows below the rowIndex will be shifted down rowsCount number of rows.

    Declaration
    public virtual List<RangeRow> InsertRows(int rowIndex, int rowsCount = 1)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    A 0-based index of the position where to start inserting new rows

    System.Int32 rowsCount

    Number of rows to insert

    Returns
    Type Description
    System.Collections.Generic.List<RangeRow>

    RangeRows that were created in the process

    Exceptions
    Type Condition
    System.ArgumentException

    Max()

    Returns maximum value of all numeric cells

    Declaration
    public Decimal Max()
    Returns
    Type Description
    System.Decimal

    The max value as a decimal.

    Min()

    Returns minimum value of all numeric cells within this Range.

    Declaration
    public Decimal Min()
    Returns
    Type Description
    System.Decimal

    The min value as a decimal.

    RemoveColumn(RangeColumn)

    Removes the specified RangeColumn from this Range. If after the deletion the worksheet column is left empty, all columns to the right of the deleted column will be shifted to the left.

    Declaration
    public virtual void RemoveColumn(RangeColumn column)
    Parameters
    Type Name Description
    RangeColumn column

    The column to remove

    RemoveColumn(Int32)

    Removes the RangeColumn with specified number from this Range. If after the deletion the worksheet column is left empty, all columns to the right of the deleted column will be shifted to the left.

    Declaration
    public virtual void RemoveColumn(int columnNumber)
    Parameters
    Type Name Description
    System.Int32 columnNumber

    The number of the column to remove

    RemoveColumn(String)

    Removes the RangeColumn with specified letter name from this Range. If after the deletion the worksheet column is left empty, all columns to the right of the deleted column will be shifted to the left.

    Declaration
    public virtual void RemoveColumn(string columnName)
    Parameters
    Type Name Description
    System.String columnName

    The letter name of the column to remove

    RemoveRow(RangeRow)

    Removes the RangeRow from this Range. If after the deletion the worksheet row is left empty, all rows below the deleted row will be shifted up.

    Declaration
    public virtual void RemoveRow(RangeRow row)
    Parameters
    Type Name Description
    RangeRow row

    The row to remove

    RemoveRow(Int32)

    Removes the RangeRow with specified number from this Range. If after the deletion the worksheet row is left empty, all rows below the deleted row will be shifted up.

    Declaration
    public virtual void RemoveRow(int rowNumber)
    Parameters
    Type Name Description
    System.Int32 rowNumber

    The number of the row to remove

    Replace(String, String)

    Textual and numeric Find and Replace.

    Replaces all found occurrences of oldValue with the newValue. Works with all string and numeric values.

    Declaration
    public void Replace(string oldValue, string newValue)
    Parameters
    Type Name Description
    System.String oldValue

    The string to find.

    System.String newValue

    The string to replace oldValue with.

    Row(Int32)

    Get a row by index

    Declaration
    protected RangeRow Row(int index)
    Parameters
    Type Name Description
    System.Int32 index

    The zero based row index.

    Returns
    Type Description
    RangeRow

    SaveAsNamedRange(String, Boolean)

    Saves the current range as a new named range.

    Declaration
    public void SaveAsNamedRange(string nameOfRange, bool globalName)
    Parameters
    Type Name Description
    System.String nameOfRange

    The user-defined name.

    System.Boolean globalName

    Sets the range as global if set to true. Default value is true.

    SaveAsNamedTable(String, Boolean, TableStyle)

    Saves the current range as named table with the specified name.

    Declaration
    public void SaveAsNamedTable(string tableName, bool showFilter = true, TableStyle tableStyle = null)
    Parameters
    Type Name Description
    System.String tableName

    the name of the table

    System.Boolean showFilter

    show filter on the header. Default value is true.

    TableStyle tableStyle

    table style properties

    Exceptions
    Type Condition
    System.NotSupportedException

    Only XLSX supports named tables.

    SetCellValue(Int32, Int32, Object)

    Set a single cell value using its row and column 0-based indexes.

    Declaration
    public void SetCellValue(int rowIndex, int columnIndex, object value)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    Row index within this Range.

    The first row is numbered 0.

    System.Int32 columnIndex

    Column index within this Range.

    The first column is numbered 0.

    System.Object value

    Value for the cell.

    SortAscending()

    Sort current range in ascending order. This updates the cell positions within your WorkSheets.

    Declaration
    public Range SortAscending()
    Returns
    Type Description
    Range

    This Range, allowing for a fluent code style.

    SortByColumn(Int32, SortOrder)

    Sort current range by specified column. This updates the row positions within your WorkSheet. Range has to be rectangular: first and last rows numbers in all columns should be the same, and first and last columns numbers in all rows should be the same.

    Declaration
    public Range SortByColumn(int columnNumber, SortOrder sortOrder)
    Parameters
    Type Name Description
    System.Int32 columnNumber

    Zero-based column index. Column should intersect current range

    SortOrder sortOrder

    Direction of the sort

    Returns
    Type Description
    Range

    This Range, allowing for a fluent code style.

    Exceptions
    Type Condition
    System.InvalidOperationException

    Thrown if range is not of rectangular shape

    System.IndexOutOfRangeException

    Thrown if columnNumber is out of current range's bounds

    SortByColumn(String, SortOrder)

    Sort current range by specified column. This updates the row positions within your WorkSheet. Range has to be rectangular: first and last rows numbers in all columns should be the same, first and last columns numbers in all rows should be the same.

    Declaration
    public Range SortByColumn(string column, SortOrder sortOrder)
    Parameters
    Type Name Description
    System.String column

    Column name in the string form. For example "A" or "AB". Column should intersect current range

    SortOrder sortOrder

    Direction of the sort

    Returns
    Type Description
    Range

    This Range, allowing for a fluent code style.

    Exceptions
    Type Condition
    System.InvalidOperationException

    Thrown if range is not of rectangular shape

    System.ArgumentException

    Thrown if column name is not a valid column name

    System.IndexOutOfRangeException

    Thrown if column is out of current range's bounds

    SortDescending()

    Sort current range in descending order. This updates the cell positions within your WorkSheets.

    Declaration
    public Range SortDescending()
    Returns
    Type Description
    Range

    This Range, allowing for a fluent code style.

    SubtractRange(Range)

    Subtracts a Range from the current rnage. If the range is only partially intersecting with the current range, the intersection will be cleared of contents, but the range coordiantes will remain. So if range A is A1:B2 and range B is B2:C3, then after calling this method, range A - A1:A1 is subtracted from range B - A1:B2, the B range will remain A1:B2, but the contents of the A1 will be cleared. On the other hand, if the suntrackted range is intersecting entire columns or entire rows of the this range, starting from one of it's edges, this intersection will be removed from this range. So if range A is A1:B2 and range B is A1:C1, then after calling this method, range A will be A2:B2. Another example is if range A is A1:B2 and range B is A1:B2, then after calling this method, the range A will be removed completely.

    Declaration
    public virtual Range SubtractRange(Range range)
    Parameters
    Type Name Description
    Range range

    The range to remove from the current range.

    Returns
    Type Description
    Range

    This Range allowing a fluent coding style.

    SubtractRange(RangeAddress)

    Subtracts a RangeAddress from the current rnage. If the address is only partially intersecting with the current range, the intersection will be cleared of contents, but the range coordiantes will remain. So if range A is A1:B2 and range B is B2:C3, then after calling this method, range A - A1:A1 is subtracted from range B - A1:B2, the B range will remain A1:B2, but the contents of the A1 will be cleared. On the other hand, if the suntrackted range is intersecting entire columns or entire rows of the this range, starting from one of it's edges, this intersection will be removed from this range. So if range A is A1:B2 and range B is A1:C1, then after calling this method, range A will be A2:B2. Another example is if range A is A1:B2 and range B is A1:B2, then after calling this method, the range A will be removed completely.

    Declaration
    public virtual Range SubtractRange(RangeAddress address)
    Parameters
    Type Name Description
    RangeAddress address

    The address to remove from the current range.

    Returns
    Type Description
    Range

    This Range allowing a fluent coding style.

    Sum()

    Returns sum of all numeric cells .

    Declaration
    public Decimal Sum()
    Returns
    Type Description
    System.Decimal

    The sum value as a decimal.

    ToArray()

    Converts the worksheet to flattened array of cells.

    Declaration
    public Cell[] ToArray()
    Returns
    Type Description
    Cell[]

    An array of Cell objects.

    ToDataTable(Boolean)

    Get System.Data.DataTable representation of current range.

    Useful when working with DataGrids, System.Data, SQL or Entity Framework

    Declaration
    public DataTable ToDataTable(bool useFirstRowAsColumnNames = false)
    Parameters
    Type Name Description
    System.Boolean useFirstRowAsColumnNames

    Indicates whether to use first row as column names or not

    Returns
    Type Description
    System.Data.DataTable

    A System.Data.DataTable representation of current range.

    ToString()

    Returns the StringValue of this Range.

    Declaration
    public override string ToString()
    Returns
    Type Description
    System.String

    A System.String that represents this instance.

    Trim()

    Removes all empty rows and columns on the range borders.

    Declaration
    public Range Trim()
    Returns
    Type Description
    Range

    This Range, allowing for a fluent code style.

    TryGetValue<TType>(out TType)

    Method for safely getting a range's total value.

    If range contains one cell then method calls GetValue<TType>() of this cell

    If range contains more than one cell then method tries to cast Sum() of all numeric cells within range to TType

    Use Try/Catch to avoid possible exceptions from this method if cell content types are inconsistent or contain formula errors.

    Declaration
    public bool TryGetValue<TType>(out TType value)
    Parameters
    Type Name Description
    TType value

    The sum value of the range, expressed as TType.

    Returns
    Type Description
    System.Boolean

    true if the range's content could be cast to TType.

    Type Parameters
    Name Description
    TType

    The .net type to which the cell's value will be cast.

    Exceptions
    Type Condition
    System.ArgumentException

    Range value cannot be casted to {type.Name}

    Operators

    Addition(Range, Range)

    Overloaded + operator, see also AddRange(Range)

    Declaration
    public static Range operator +(Range a, Range b)
    Parameters
    Type Name Description
    Range a

    This Range.

    Range b

    Another Range. Both Ranges will be joined in the result.

    Returns
    Type Description
    Range

    The combination of both ranges' areas.

    Subtraction(Range, Range)

    Overloaded - operator, see also SubtractRange(Range)

    Declaration
    public static Range operator -(Range a, Range b)
    Parameters
    Type Name Description
    Range a

    This Range.

    Range b

    Another Range which will be subtracted from the first range.

    Returns
    Type Description
    Range

    All cells in your Range, with any cells in the second Range removed.

    Implements

    System.Collections.Generic.IEnumerable<>
    System.Collections.IEnumerable
    ☀
    ☾
    Downloads
    • Download with Nuget
    • Start for Free
    In This Article
    Back to top
    Install with Nuget
    Want to deploy IronXL to a live project for FREE?
    What’s included?
    30 days of fully-functional product
    Test and share in a live environment
    No watermarks in production
    Get your free 30-day Trial Key instantly.
    No credit card or account creation required
    Your Trial License Key has been emailed to you.
    Download IronXL free to apply
    your Trial Licenses Key
    Install with NuGet View Licenses
    Licenses from $499. Have a question? Get in touch.