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

Declaration

public RangeColumn[] AllColumnsInRange { get; }

Property Value

Type	Description
RangeColumn[]

AllRowsInRange

List of ALL rows within the current range. Calling this method will create all rows that this range spans over .The rows contain Cell objects.

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 .

Columns

List of "physically" present Excel columns within the current range. The columns contain Cell objects.

Declaration

public RangeColumn[] Columns { get; }

Property Value

Type	Description
RangeColumn[]

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.

See Also

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.Int32 SUM 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 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

RangeAddressAsString

Gets a string representation of this range address.

E.g. "A1:B25"

Declaration

public virtual string RangeAddressAsString { get; }

Property Value

Type	Description
System.String	The range address as string.

Rows

List of "physically" present Excel rows 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 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. Basically, the result is union of two ranges.

Declaration

public virtual Range AddRange(Range range)

Parameters

Type	Name	Description
Range	range	The range to add to the current working 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 Cell 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 sheet, string pointToInsert)

Parameters

Type	Name	Description
WorkSheet	sheet	The worksheet to paste this Range into.
System.String	pointToInsert	The cell at which to paste the range. E.g. "A11"

GetAllColumns()

Get a list of all columns that overlap this Range

Declaration

protected RangeColumn[] GetAllColumns()

Returns

Type	Description
RangeColumn[]

GetAllPhysicalColumns()

Get a list of only the columns that actually exist in this range

Declaration

protected RangeColumn[] GetAllPhysicalColumns()

Returns

Type	Description
RangeColumn[]

GetAllPhysicalRows()

Get a list of only the rows that actually exist in this range

Declaration

protected RangeRow[] GetAllPhysicalRows()

Returns

Type	Description
RangeRow[]

GetAllRows()

Get a list of all rows that overlap this Range

Declaration

protected RangeRow[] GetAllRows()

Returns

Type	Description
RangeRow[]

GetColumns(Boolean)

Gets the list of columns that this Range spans over. Is able to create columns to fill the range with columns if needed, or only return the columns that actually exist on the sheet.

Declaration

protected RangeColumn[] GetColumns(bool createIfNull)

Parameters

Type	Name	Description
System.Boolean	createIfNull	Indicates wether the method should create the columns that are overlapped by this Range and add them to the resulting array, or only get existing columns

Returns

Type	Description
RangeColumn[]

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.

GetRows(Boolean)

Gets the list of rows that this Range spans over. Is able to create rows to fill the range with rows if needed, or only return the rows that actually exist on the sheet.

Declaration

protected RangeRow[] GetRows(bool createIfNull)

Parameters

Type	Name	Description
System.Boolean	createIfNull	Indicates wether the method should create the rows that are overlapped by this Range and add them to the resulting array, or only get existing rows

Returns

Type	Description
RangeRow[]

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}

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.

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 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, and 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. Result is first range without intersection of two ranges.

Declaration

public virtual Range SubtractRange(Range range)

Parameters

Type	Name	Description
Range	range	The range to remove from the current working 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.

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