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

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