Table of Contents

Interface IXLRangeBase

Namespace
SlapKit.Excel.Excel
Assembly
SlapKit.Excel.dll
public interface IXLRangeBase : IXLAddressable
Inherited Members
Extension Methods

Properties

FormulaA1

Sets the cells' formula with A1 references.

string FormulaA1 { set; }

Property Value

string

The formula with A1 references.

FormulaArrayA1

Create an array formula for all cells in the range.

string FormulaArrayA1 { set; }

Property Value

string

Exceptions

InvalidOperationException

When the range overlaps with a table, pivot table, merged cells or partially overlaps another array formula.

FormulaR1C1

Sets the cells' formula with R1C1 references.

string FormulaR1C1 { set; }

Property Value

string

The formula with R1C1 references.

IXLHyperlinks Hyperlinks { get; }

Property Value

IXLHyperlinks

ShareString

Gets or sets a value indicating whether this cell's text should be shared or not.

bool ShareString { set; }

Property Value

bool

If false the cell's text will not be shared and stored as an inline value.

Style 

IXLStyle Style { get; set; }

Property Value

IXLStyle

Value

Sets a value to every cell in this range.

Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text. 

XLCellValue Value { set; }

Property Value

XLCellValue

Worksheet 

IXLWorksheet Worksheet { get; }

Property Value

IXLWorksheet

Methods

AddConditionalFormat() 

IXLConditionalFormat AddConditionalFormat()

Returns

IXLConditionalFormat

AddToNamed(string)

Creates a named range out of this range.

If the named range exists, it will add this range to that named range.

The default scope for the named range is Workbook.

IXLRange AddToNamed(string rangeName)

Parameters

rangeName string

Name of the range.

Returns

IXLRange

AddToNamed(string, XLScope)

Creates a named range out of this range.

If the named range exists, it will add this range to that named range.

Name of the range. The scope for the named range. 
IXLRange AddToNamed(string rangeName, XLScope scope)

Parameters

rangeName string
scope XLScope

Returns

IXLRange

AddToNamed(string, XLScope, string)

Creates a named range out of this range.

If the named range exists, it will add this range to that named range.

Name of the range. The scope for the named range. The comments for the named range. 
IXLRange AddToNamed(string rangeName, XLScope scope, string comment)

Parameters

rangeName string
scope XLScope
comment string

Returns

IXLRange

AsRange()

Converts this object to a range.

IXLRange AsRange()

Returns

IXLRange

Cells()

Returns the collection of cells.

IXLCells Cells()

Returns

IXLCells

Cells(bool) 

IXLCells Cells(bool usedCellsOnly)

Parameters

usedCellsOnly bool

Returns

IXLCells

Cells(bool, XLCellsUsedOptions) 

IXLCells Cells(bool usedCellsOnly, XLCellsUsedOptions options)

Parameters

usedCellsOnly bool
options XLCellsUsedOptions

Returns

IXLCells

Cells(Func<IXLCell, bool>) 

IXLCells Cells(Func<IXLCell, bool> predicate)

Parameters

predicate Func<IXLCell, bool>

Returns

IXLCells

Cells(string) 

IXLCells Cells(string cells)

Parameters

cells string

Returns

IXLCells

CellsUsed()

Returns the collection of cells that have a value. Formats are ignored.

IXLCells CellsUsed()

Returns

IXLCells

CellsUsed(XLCellsUsedOptions)

Returns the collection of cells that have a value.

IXLCells CellsUsed(XLCellsUsedOptions options)

Parameters

options XLCellsUsedOptions

The options to determine whether a cell is used.

Returns

IXLCells

CellsUsed(XLCellsUsedOptions, Func<IXLCell, bool>) 

IXLCells CellsUsed(XLCellsUsedOptions options, Func<IXLCell, bool> predicate)

Parameters

options XLCellsUsedOptions
predicate Func<IXLCell, bool>

Returns

IXLCells

CellsUsed(Func<IXLCell, bool>) 

IXLCells CellsUsed(Func<IXLCell, bool> predicate)

Parameters

predicate Func<IXLCell, bool>

Returns

IXLCells

Clear(XLClearOptions)

Clears the contents of this range.

IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.All)

Parameters

clearOptions XLClearOptions

Specify what you want to clear.

Returns

IXLRangeBase

Contains(IXLCell) 

bool Contains(IXLCell cell)

Parameters

cell IXLCell

Returns

bool

Contains(IXLRangeBase)

Determines whether this range contains the specified range (completely).

For partial matches use the range.Intersects method.

bool Contains(IXLRangeBase range)

Parameters

range IXLRangeBase

The range to match.

Returns

bool

true if this range contains the specified range; otherwise, false.

Contains(string)

Determines whether this range contains the specified range (completely).

For partial matches use the range.Intersects method.

bool Contains(string rangeAddress)

Parameters

rangeAddress string

The range address.

Returns

bool

true if this range contains the specified range; otherwise, false.

CreateDataValidation()

Creates a new data validation rule for the range, replacing the existing one.

IXLDataValidation CreateDataValidation()

Returns

IXLDataValidation

CreatePivotTable(IXLCell, string) 

IXLPivotTable CreatePivotTable(IXLCell targetCell, string name)

Parameters

targetCell IXLCell
name string

Returns

IXLPivotTable

DeleteComments()

Deletes the cell comments from this range.

void DeleteComments()

Difference(IXLRangeBase, Func<IXLCell, bool>, Func<IXLCell, bool>)

Returns all cells in the current range that are not in the other range.

IXLCells Difference(IXLRangeBase otherRange, Func<IXLCell, bool> thisRangePredicate = null, Func<IXLCell, bool> otherRangePredicate = null)

Parameters

otherRange IXLRangeBase

The other range.

thisRangePredicate Func<IXLCell, bool>

Predicate applied to this range's cells.

otherRangePredicate Func<IXLCell, bool>

Predicate applied to the other range's cells.

Returns

IXLCells

FirstCell()

Returns the first cell of this range.

IXLCell FirstCell()

Returns

IXLCell

FirstCellUsed()

Returns the first non-empty cell with a value of this range. Formats are ignored.

The cell's address is going to be ([First Row with a value], [First Column with a value])

IXLCell FirstCellUsed()

Returns

IXLCell

FirstCellUsed(XLCellsUsedOptions)

Returns the first non-empty cell with a value of this range.

IXLCell FirstCellUsed(XLCellsUsedOptions options)

Parameters

options XLCellsUsedOptions

The options to determine whether a cell is used.

Returns

IXLCell

FirstCellUsed(XLCellsUsedOptions, Func<IXLCell, bool>)

Returns the first non-empty cell with a value of this range.

IXLCell FirstCellUsed(XLCellsUsedOptions options, Func<IXLCell, bool> predicate)

Parameters

options XLCellsUsedOptions

The options to determine whether a cell is used.

predicate Func<IXLCell, bool>

The predicate used to choose cells

Returns

IXLCell

FirstCellUsed(Func<IXLCell, bool>) 

IXLCell FirstCellUsed(Func<IXLCell, bool> predicate)

Parameters

predicate Func<IXLCell, bool>

Returns

IXLCell

GetDataValidation()

Returns a data validation rule assigned to the range, if any, or creates a new instance of data validation rule if no rule exists.

IXLDataValidation GetDataValidation()

Returns

IXLDataValidation

Grow()

Grows this the current range by one cell to each side

IXLRangeBase Grow()

Returns

IXLRangeBase

Grow(int)

Grows this the current range by the specified number of cells to each side.

IXLRangeBase Grow(int growCount)

Parameters

growCount int

The grow count.

Returns

IXLRangeBase

Intersection(IXLRangeBase, Func<IXLCell, bool>, Func<IXLCell, bool>)

Returns the intersection of this range with another range on the same worksheet.

IXLRangeAddress Intersection(IXLRangeBase otherRange, Func<IXLCell, bool> thisRangePredicate = null, Func<IXLCell, bool> otherRangePredicate = null)

Parameters

otherRange IXLRangeBase

The other range.

thisRangePredicate Func<IXLCell, bool>

Predicate applied to this range's cells.

otherRangePredicate Func<IXLCell, bool>

Predicate applied to the other range's cells.

Returns

IXLRangeAddress

The range address of the intersection

Intersects(IXLRangeBase)

Determines whether this range contains the specified range.

For whole matches use the range.Contains method.

bool Intersects(IXLRangeBase range)

Parameters

range IXLRangeBase

The range to match.

Returns

bool

true if this range intersects the specified range; otherwise, false.

Intersects(string)

Determines whether this range intersects the specified range.

For whole matches use the range.Contains method.

bool Intersects(string rangeAddress)

Parameters

rangeAddress string

The range address.

Returns

bool

true if this range intersects the specified range; otherwise, false.

IsEmpty() 

bool IsEmpty()

Returns

bool

IsEmpty(XLCellsUsedOptions) 

bool IsEmpty(XLCellsUsedOptions options)

Parameters

options XLCellsUsedOptions

Returns

bool

IsEntireColumn()

Determines whether range address spans the entire column.

bool IsEntireColumn()

Returns

bool

true if is entire column; otherwise, false.

IsEntireRow()

Determines whether range address spans the entire row.

bool IsEntireRow()

Returns

bool

true if is entire row; otherwise, false.

IsEntireSheet()

Determines whether the range address spans the entire worksheet.

bool IsEntireSheet()

Returns

bool

true if is entire sheet; otherwise, false.

IsMerged() 

bool IsMerged()

Returns

bool

LastCell()

Returns the last cell of this range.

IXLCell LastCell()

Returns

IXLCell

LastCellUsed()

Returns the last non-empty cell with a value of this range. Formats are ignored.

The cell's address is going to be ([Last Row with a value], [Last Column with a value])

IXLCell LastCellUsed()

Returns

IXLCell

LastCellUsed(XLCellsUsedOptions)

Returns the last non-empty cell with a value of this range.

IXLCell LastCellUsed(XLCellsUsedOptions options)

Parameters

options XLCellsUsedOptions

The options to determine whether a cell is used.

Returns

IXLCell

LastCellUsed(XLCellsUsedOptions, Func<IXLCell, bool>) 

IXLCell LastCellUsed(XLCellsUsedOptions options, Func<IXLCell, bool> predicate)

Parameters

options XLCellsUsedOptions
predicate Func<IXLCell, bool>

Returns

IXLCell

LastCellUsed(Func<IXLCell, bool>) 

IXLCell LastCellUsed(Func<IXLCell, bool> predicate)

Parameters

predicate Func<IXLCell, bool>

Returns

IXLCell

Merge()

Merges this range. Only the top-left cell will have a value, other values will be blank.

IXLRange Merge()

Returns

IXLRange

Merge(bool) 

IXLRange Merge(bool checkIntersect)

Parameters

checkIntersect bool

Returns

IXLRange

Relative(IXLRangeBase, IXLRangeBase)

Returns a range so that its offset from the target base range is equal to the offset of the current range to the source base range. For example, if the current range is D4:E4, the source base range is A1:C3, then the relative range to the target base range B10:D13 is E14:F14

IXLRangeBase Relative(IXLRangeBase sourceBaseRange, IXLRangeBase targetBaseRange)

Parameters

sourceBaseRange IXLRangeBase

The source base range.

targetBaseRange IXLRangeBase

The target base range.

Returns

IXLRangeBase

The relative range

Search(string, CompareOptions, bool)

Searches the cells' contents for a given piece of text

IXLCells Search(string searchText, CompareOptions compareOptions = CompareOptions.Ordinal, bool searchFormulae = false)

Parameters

searchText string

The search text.

compareOptions CompareOptions

The compare options.

searchFormulae bool

if set to true search formulae instead of cell values.

Returns

IXLCells

Select() 

void Select()

SetAutoFilter() 

IXLAutoFilter SetAutoFilter()

Returns

IXLAutoFilter

SetAutoFilter(bool) 

IXLAutoFilter SetAutoFilter(bool value)

Parameters

value bool

Returns

IXLAutoFilter

SetDataValidation() 

[Obsolete("Use GetDataValidation() to access the existing rule, or CreateDataValidation() to create a new one.")]
IXLDataValidation SetDataValidation()

Returns

IXLDataValidation

SetValue(XLCellValue)

Set value to all cells in the range.

IXLRangeBase SetValue(XLCellValue value)

Parameters

value XLCellValue

Returns

IXLRangeBase

Shrink()

Shrinks this current range by one cell.

IXLRangeBase Shrink()

Returns

IXLRangeBase

Shrink(int)

Shrinks the current range by the specified number of cells from each side.

IXLRangeBase Shrink(int shrinkCount)

Parameters

shrinkCount int

The shrink count.

Returns

IXLRangeBase

SurroundingCells(Func<IXLCell, bool>)

Returns the set of cells surrounding the current range.

IXLCells SurroundingCells(Func<IXLCell, bool> predicate = null)

Parameters

predicate Func<IXLCell, bool>

The predicate to apply on the resulting set of cells.

Returns

IXLCells

Union(IXLRangeBase, Func<IXLCell, bool>, Func<IXLCell, bool>)

Calculates the union of two ranges on the same worksheet.

IXLCells Union(IXLRangeBase otherRange, Func<IXLCell, bool> thisRangePredicate = null, Func<IXLCell, bool> otherRangePredicate = null)

Parameters

otherRange IXLRangeBase

The other range.

thisRangePredicate Func<IXLCell, bool>

Predicate applied to this range's cells.

otherRangePredicate Func<IXLCell, bool>

Predicate applied to the other range's cells.

Returns

IXLCells

The union

Unmerge()

Unmerges this range.

IXLRange Unmerge()

Returns

IXLRange