Class Range
A class that represents a range of cells
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
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()
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 |
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 |
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 |
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 |
System.IndexOutOfRangeException | Thrown if |
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 |
|
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. |