Table of Contents

Interface IXLSRange

Namespace
Spire.Xls.Core
Assembly
Spire.XLS.dll

Represents a cell, row, column, selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

public interface IXLSRange : IExcelApplication, IEnumerable
Inherited Members

Properties

BooleanValue

Gets / sets boolean value that is contained by this range. The following code illustrates how to access Boolean property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set and get BooleanValue
worksheet.Range[2, 4].BooleanValue = true;
bool boolean = worksheet.Range[2, 4].BooleanValue;
bool BooleanValue { get; set; }

Property Value

bool

Borders

Returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format). The following code illustrates how to access Borders property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";

//Set borders
IBorders borders = worksheet["C2"].Borders;

//Set line style
borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

//Set border color
borders[BordersLineType.EdgeTop].Color = Color.Red;
borders[BordersLineType.EdgeBottom].Color = Color.Red;

//Save to file
workbook.SaveToFile("CellFormats.xlsx");
IBorders Borders { get; }

Property Value

IBorders

BuiltInStyle

Gets/sets built in style. The following code illustrates how to access BuiltInStyle property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";

//Set built in style
worksheet["C2"].BuiltInStyle = BuiltInStyles.Accent3;

//Save to file
workbook.SaveToFile("BuiltInStyle.xlsx");
BuiltInStyles? BuiltInStyle { get; set; }

Property Value

BuiltInStyles?

CellList

Returns a Range object that represents the cells in the specified range. Read-only. The following code illustrates how to access CellList property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text. The content contained by ![CDATA[]] will be expressed as plain text
ListCellRange cells = worksheet["A1:E8"].CellList;

//Do some manipulations
foreach (CellRange Range in cells)
    Range.Text = Range.RangeAddressLocal;

//Save to file
workbook.SaveToFile("CellList.xlsx");
List<CellRange> CellList { get; }

Property Value

List<CellRange>

CellStyleName

Returns name of the Style object that represents the style of the specified range. Read/write String. The following code illustrates how to access CellStyleName of the specified range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Add and set style
CellStyle style = workbook.Styles.Add("CustomStyle");
worksheet["C2"].Style = style;

//Check Style name
Console.Write(worksheet["C2"].CellStyleName);
string CellStyleName { get; set; }

Property Value

string

Cells

Returns a Range object that represents the cells in the specified range. Read-only.

[Obsolete("Please use CellList")]
CellRange[] Cells { get; }

Property Value

CellRange[]

Column

Returns the number of the first column in the first area in the specified range. Read-only. The following code illustrates how to access Column property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get specific column
int firstColumn = worksheet["E1:R3"].Column;
int Column { get; }

Property Value

int

ColumnGroupLevel

Column group level. Read-only. -1 - Not all columns in the range have same group level. 0 - No grouping, 1 - 7 - Group level.

int ColumnGroupLevel { get; }

Property Value

int

ColumnWidth

Returns or sets the width of all columns in the specified range. Read/write Double. The following code illustrates how to set the width of all columns in the specified range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set the ColumnWidth
worksheet["A1"].Text = "This cell contains sample text";
worksheet["A1"].ColumnWidth = 25;

//Save to file
workbook.SaveToFile("ColumnWidth.xlsx");
double ColumnWidth { get; set; }

Property Value

double

Columns

For a Range object, returns an array of Range objects that represent the columns in the specified range. The following code illustrates how to access columns:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set columns
IXLSRange[] columns = worksheet["A1:E8"].Columns;

//Do some manipulations
foreach (IXLSRange column in columns)
  column.Text = column.RangeAddressLocal;

//Save to file
workbook.SaveToFile("Columns.xlsx");
IXLSRange[] Columns { get; }

Property Value

IXLSRange[]

Comment

Comment assigned to the range. Read-only. The following code illustrates how to access Comments property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Adding comments to a cell
worksheet.Range["A1"].AddComment().Text = "Comments";

//Add Rich Text Comments
CellRange range = worksheet.Range["A6"];
range.AddComment().RichText.Text = "RichText";
IRichTextString rtf = range.Comment.RichText;

//Formatting first 4 characters
IFont redFont = workbook.CreateFont();
redFont.IsBold = true;
redFont.Color = Color.Red;
rtf.SetFont(0, 3, redFont);

//Save to file
workbook.SaveToFile("DataValidation.xlsx");
ICommentShape Comment { get; }

Property Value

ICommentShape

ConditionalFormats

Collection of conditional formats.

ConditionalFormats ConditionalFormats { get; }

Property Value

ConditionalFormats

Count

Returns the number of objects in the collection. Read-only.

int Count { get; }

Property Value

int

DataValidation

Data validation for the range. The following code illustrates how to access DataValidation property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Data validation for number
IDataValidation validation = worksheet.Range["A3"].DataValidation;
validation.AllowType = CellDataType.Integer;

//Value between 0 to 10
validation.CompareOperator = ValidationComparisonOperator.Between;
validation.Formula1 = "0";
validation.Formula2 = "10";

//Save to file
workbook.SaveToFile("DataValidation.xlsx");
Validation DataValidation { get; }

Property Value

Validation

DateTimeValue

Gets / sets DateTime contained by this cell. Read-write DateTime. The following code illustrates how to set and access DateTimeValue property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set and get the DateTimeValue of specified range
worksheet.Range[2, 4].DateTimeValue = DateTime.Now;
DateTime dateTime = worksheet.Range[2, 4].DateTimeValue;

//Save to file
workbook.SaveToFile("DateTimeValue.xlsx");
DateTime DateTimeValue { get; set; }

Property Value

DateTime

EndCell

Returns a Range object that represents the cell at the end of the region that contains the source range.

IXLSRange EndCell { get; }

Property Value

IXLSRange

EntireColumn

Returns a Range object that represents the entire column (or columns) that contains the specified range. Read-only.

IXLSRange EntireColumn { get; }

Property Value

IXLSRange

EntireRow

Returns a Range object that represents the entire row (or rows) that contains the specified range. Read-only.

IXLSRange EntireRow { get; }

Property Value

IXLSRange

EnvalutedValue

Returns the calculated value of a formula using the most current inputs. The following code illustrates how to access a calculated value:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Returns the calculated value of a formula using the most current inputs
string calculatedValue = worksheet["C1"].EnvalutedValue;
Console.WriteLine(calculatedValue);
string EnvalutedValue { get; }

Property Value

string

ErrorValue

Gets / sets error value that is contained by this range.

string ErrorValue { get; set; }

Property Value

string

Formula

Returns or sets the object's formula in A1-style notation and in the language of the macro. Read/write Variant.

string Formula { get; set; }

Property Value

string

FormulaArray

Represents array-entered formula. Visit http://www.cpearson.com/excel/array.htm for more information. The following code illustrates how to set and access FormulaArray property of the range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Assign array formula
worksheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";

//Adding a named range for the range A1 to D1
worksheet.Names.Add("ArrayRange", worksheet.Range["A1:D1"]);

//Assign formula array with named range
worksheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";

//Save to file
workbook.SaveToFile("FormulaArray.xlsx");
string FormulaArray { get; set; }

Property Value

string

FormulaArrayR1C1

Returns or sets the formula array for the range, using R1C1-style notation.

string FormulaArrayR1C1 { get; set; }

Property Value

string

FormulaBoolValue

Returns the calculated value of the formula as a boolean.

bool FormulaBoolValue { get; set; }

Property Value

bool

FormulaDateTime

Get / set formula DateTime value contained by this cell. DateTime.MinValue if not all cells of the range have same DateTime value.

DateTime FormulaDateTime { get; set; }

Property Value

DateTime

FormulaErrorValue

Returns the calculated value of the formula as a string.

string FormulaErrorValue { get; set; }

Property Value

string

FormulaNumberValue

Gets / sets number value evaluated by formula.

double FormulaNumberValue { get; set; }

Property Value

double

FormulaR1C1

Returns or sets the formula for the range, using R1C1-style notation.

string FormulaR1C1 { get; set; }

Property Value

string

FormulaStringValue

Gets / sets string value evaluated by formula.

string FormulaStringValue { get; set; }

Property Value

string

HasBoolean

Indicates whether range contains bool value. Read-only. The following code illustrates how to set and access HasBoolean property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
worksheet["A3"].Value2 = false;

//Checking Range types
bool isboolean = worksheet["A3"].HasBoolean;

//Save to file
workbook.SaveToFile("HasBoolean.xlsx");
bool HasBoolean { get; }

Property Value

bool

HasDataValidation

Indicates whether specified range object has data validation. If Range is not single cell, then returns true only if all cells have data validation. Read-only.

bool HasDataValidation { get; }

Property Value

bool

HasDateTime

Indicates whether range contains DateTime value. Read-only. The following code illustrates how to set and access HasDateTime property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
worksheet["A1"].Value2 = DateTime.Now;

//Checking Range types
bool isDateTime =  worksheet["A1"].HasDateTime;

//Save to file
workbook.SaveToFile("HasDateTime.xlsx");
bool HasDateTime { get; }

Property Value

bool

HasError

Indicates whether range contains error value.

bool HasError { get; }

Property Value

bool

HasExternalFormula

Indicates is current range has external formula. Read-only.

bool HasExternalFormula { get; }

Property Value

bool

HasFormula

True if all cells in the range contain formulas; False if at least one of the cells in the range doesn't contain a formula. Read-only Boolean.

bool HasFormula { get; }

Property Value

bool

HasFormulaArray

Indicates whether range contains array-entered formula. Read-only.

bool HasFormulaArray { get; }

Property Value

bool

HasFormulaBoolValue

Indicates if current range has formula bool value. Read only.

bool HasFormulaBoolValue { get; }

Property Value

bool

HasFormulaDateTime

Indicates if current range has formula value formatted as DateTime. Read-only.

bool HasFormulaDateTime { get; }

Property Value

bool

HasFormulaErrorValue

Indicates if current range has formula error value. Read only.

bool HasFormulaErrorValue { get; }

Property Value

bool

HasFormulaNumberValue

Indicates if the current range has formula number value. Read-only.

bool HasFormulaNumberValue { get; }

Property Value

bool

HasFormulaStringValue

Indicates if the current range has formula string value. Read-only.

bool HasFormulaStringValue { get; }

Property Value

bool

HasMerged

Indicates whether this range is part of merged range. Read-only. The following code illustrates how to access HasMerged property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Sample text in cell";

//Set merge
worksheet["A1:B1"].Merge();

//Check merge
Console.Write(worksheet["A1:B1"].HasMerged);
bool HasMerged { get; }

Property Value

bool

HasNumber

Indicates whether the range contains number. Read-only. The following code illustrates how to set and access Value2 property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
worksheet["A2"].Value2 = 45;

//Checking Range types
bool isNumber =  worksheet["A2"].HasNumber;

//Save to file
workbook.SaveToFile("HasNumber.xlsx");
bool HasNumber { get; }

Property Value

bool

HasRichText

Indicates whether cell contains formatted rich text string. The following code illustrates how to access HasRichText property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
IStyle style = workbook.Styles.Add("CustomStyle");

//Set rich text
IRichTextString richText = worksheet["C2"].RichText;
richText.Text = "Sample";
IFont font = style.Font;
font.Color = Color.Red;
richText.SetFont(0, 5, font);

//Check HasRichText
Console.Write(worksheet["C2"].HasRichText);

//Save to file
workbook.SaveToFile("HasRichText.xlsx");
bool HasRichText { get; }

Property Value

bool

HasString

Indicates whether the range contains String. Read-only.

bool HasString { get; }

Property Value

bool

HasStyle

Indicates whether range has default style. False means default style. Read-only. The following code illustrates how to access HasStyle property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Add style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set color and style
style.Color = Color.Red;
worksheet["C2"].Style = style;

//Check HasStyle
Console.Write(worksheet["C2"].HasStyle);

//Save to file
workbook.SaveToFile("HasStyle.xlsx");
bool HasStyle { get; }

Property Value

bool

HorizontalAlignment

Returns or sets the horizontal alignment for the specified object. Read/write HorizontalAlignType. The following code illustrates how to set alignment type:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Test";

//Set alignment
worksheet["A1"].HorizontalAlignment = HorizontalAlignType.Right;

//Save to file
workbook.SaveToFile("HorizontalAlignment.xlsx");
HorizontalAlignType HorizontalAlignment { get; set; }

Property Value

HorizontalAlignType

HtmlString

Gets and sets the html string which contains data and some formattings in this cell.

string HtmlString { get; set; }

Property Value

string

Returns hyperlinks for this range.

IHyperLinks Hyperlinks { get; }

Property Value

IHyperLinks

IgnoreErrorOptions

Represents ignore error options.

IgnoreErrorType IgnoreErrorOptions { get; set; }

Property Value

IgnoreErrorType

IndentLevel

Returns or sets the indent level for the cell or range. Can be an integer from 0 to 15. Read/write Integer. The following code illustrates how to set indent level for a cell:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";

//Set indent level
worksheet["C2"].IndentLevel = 2;

//Save to file
workbook.SaveToFile("IndentLevel.xlsx");
int IndentLevel { get; set; }

Property Value

int

IsBlank

Indicates whether the range is blank. Read-only.

bool IsBlank { get; }

Property Value

bool

IsFormulaHidden

True if the formula will be hidden when the worksheet is protected. False if at least part of formula in the range is not hidden.

bool IsFormulaHidden { get; set; }

Property Value

bool

IsGroupedByColumn

Indicates whether this range is grouped by column. Read-only.

bool IsGroupedByColumn { get; }

Property Value

bool

IsGroupedByRow

Indicates whether this range is grouped by row. Read-only.

bool IsGroupedByRow { get; }

Property Value

bool

IsInitialized

Indicates whether cell is initialized. Read-only.

bool IsInitialized { get; }

Property Value

bool

IsStringsPreserved

Indicates whether all values in the range are preserved as strings.

bool? IsStringsPreserved { get; set; }

Property Value

bool?

IsWrapText

True if Microsoft Excel wraps the text in the object. Read/write Boolean. The following code illustrates how to access WrapText property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "This cell contains sample text";

//Set wrap text
worksheet["A1"].IsWrapText = true;

//Save to file
workbook.SaveToFile("IsWrapText.xlsx");
bool IsWrapText { get; set; }

Property Value

bool

this[int, int]

Gets / sets cell by row and column index. Row and column indexes are one-based.

IXLSRange this[int row, int column] { get; set; }

Parameters

row int
column int

Property Value

IXLSRange

this[int, int, int, int]

Get cell range. Row and column indexes are one-based. Read-only.

IXLSRange this[int row, int column, int lastRow, int lastColumn] { get; }

Parameters

row int
column int
lastRow int
lastColumn int

Property Value

IXLSRange

this[string]

Get cell range. Read-only.

IXLSRange this[string name] { get; }

Parameters

name string

Property Value

IXLSRange

this[string, bool]

Gets cell range. Read-only.

IXLSRange this[string name, bool IsR1C1Notation] { get; }

Parameters

name string
IsR1C1Notation bool

Property Value

IXLSRange

LastColumn

Returns last column of the range. Read-only.

int LastColumn { get; set; }

Property Value

int

LastRow

Returns last row of the range. Read-only.

int LastRow { get; set; }

Property Value

int

MergeArea

Returns a Range object that represents the merged range containing the specified cell. If the specified cell is not in a merged range, this property returns NULL. Read-only. The following code illustrates how to access MergeArea property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample text in cell";

//Set merge
worksheet["C2:D3"].Merge();

//Check merge area
Console.Write(worksheet["C2"].MergeArea.AddressLocal);
IXLSRange MergeArea { get; }

Property Value

IXLSRange

NumberFormat

Format of current cell. Analog of Style.NumberFormat property. The following code illustrates how to set NumberFormat property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set data
worksheet["C2"].Value = "3100.23";

//Set number format
worksheet["C2"].NumberFormat = "#,##1.##";

//Save to file
workbook.SaveToFile("NumberFormat.xlsx");
string NumberFormat { get; set; }

Property Value

string

NumberText

Returns cell value after number format application. Read-only. The following code illustrates how to access NumberText property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Gets cell value with its number format
CellRange range= worksheet.Range[3, 1];
range.Value = "1/1/2015";
range.NumberFormat = "dd-MMM-yyyy";
string numberText = range.NumberText;

//Save to file
workbook.SaveToFile("NumberText.xlsx");
string NumberText { get; }

Property Value

string

NumberValue

Gets / sets double value of the range.

double NumberValue { get; set; }

Property Value

double

RangeAddress

Returns the range reference in the language of the macro. Read-only String. The following code illustrates how to access Address property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get RangeAddress
string address = worksheet.Range[3, 4].RangeAddress;
string RangeAddress { get; }

Property Value

string

RangeAddressLocal

Returns the range reference for the specified range in the language of the user. Read-only String. The following code illustrates how to access AddressLocal property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get RangeAddressLocal
string address = worksheet.Range[3, 4].RangeAddressLocal;
string RangeAddressLocal { get; }

Property Value

string

RangeGlobalAddress

Returns range Address in format "'Sheet1'!$A$1". The following code illustrates how to access AddressGlobal property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get RangeAddress
string address = worksheet.Range[3, 4].RangeGlobalAddress;
string RangeGlobalAddress { get; }

Property Value

string

RangeR1C1Address

Returns the range reference using R1C1 notation. Read-only String. The following code illustrates how to access AddressR1C1 property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get RangeR1C1Address
string address = worksheet.Range[3, 4].RangeR1C1Address;
string RangeR1C1Address { get; }

Property Value

string

RangeR1C1AddressLocal

Returns the range reference using R1C1 notation. Read-only String. The following code illustrates how to access AddressR1C1Local property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get RangeR1C1AddressLocal
string address = worksheet.Range[3, 4].RangeR1C1Address;
string RangeR1C1AddressLocal { get; }

Property Value

string

RichText

String with rich text formatting. Read-only. The following code illustrates how to set rich text formatting in the range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
IStyle style = workbook.Styles.Add("CustomStyle");

//Set rich text
IRichTextString richText = worksheet["C2"].RichText;
richText.Text = "Sample text";

//Set rich text font
IFont font = style.Font;
font.IsBold = true;
richText.SetFont(0, 5, font);

//Save to file
workbook.SaveToFile("RichText.xlsx");
IRichTextString RichText { get; }

Property Value

IRichTextString

Row

Returns the number of the first row of the first area in the range. Read-only Long.

int Row { get; }

Property Value

int

RowGroupLevel

Row group level. Read-only. -1 - Not all rows in the range have same group level. 0 - No grouping, 1 - 7 - Group level.

int RowGroupLevel { get; }

Property Value

int

RowHeight

Returns the height of all the rows in the range specified, measured in points. Returns Double.MinValue if the rows in the specified range aren't all the same height. Read / write Double. The following code illustrates how to set row height:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Test";

//Set row height
worksheet["A1"].RowHeight = 30;

//Save to file
workbook.SaveToFile("RowHeight.xlsx");
double RowHeight { get; set; }

Property Value

double

Rows

For a Range object, returns an array of Range objects that represent the rows in the specified range. The following code illustrates how to access rows:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set rows
IXLSRange[] rows = worksheet["A1:E8"].Rows;

//Do some manipulations
foreach (IXLSRange row in rows)
  row.Text = row.RangeAddressLocal;

//Save to file
workbook.SaveToFile("Rows.xlsx");
IXLSRange[] Rows { get; }

Property Value

IXLSRange[]

Style

Returns a Style object that represents the style of the specified range. Read/write IStyle. The following code illustrates how to the style of the specified range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";

//Add and set style
CellStyle style = workbook.Styles.Add("BorderStyle");
style.Color = Color.Red;
worksheet["C2"].Style = style;

//Save to file
workbook.SaveToFile("Style.xlsx");
IStyle Style { get; set; }

Property Value

IStyle

Text

Gets / sets string value of the range.

string Text { get; set; }

Property Value

string

TimeSpanValue

Gets / sets time value of the range.

TimeSpan TimeSpanValue { get; set; }

Property Value

TimeSpan

Value

Returns or sets the value of the specified range. Read/write Variant. The following code illustrates how to set Value of the specified range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set value of the range
CellRange range= worksheet.Range[3, 1];
range.Value = "1/1/2015";

//Save to file
workbook.SaveToFile("Value.xlsx");
string Value { get; set; }

Property Value

string

Value2

Returns or sets the cell value. Read/write Variant. The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types. The following code illustrates how to access Value2 property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
worksheet["A1"].Value2 = DateTime.Now;
worksheet["A3"].Value2 = false;

//Checking Range types
Console.WriteLine(worksheet["A1"].HasDateTime);
Console.WriteLine(worksheet["A3"].HasBoolean);
object Value2 { get; set; }

Property Value

object

VerticalAlignment

Returns or sets the vertical alignment of the specified object. Read/write VerticalAlignType. The following code illustrates how to set vertical alignment type:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Test";

//Set alignment
worksheet["A1"].VerticalAlignment = VerticalAlignType.Top;

//Save to file
workbook.SaveToFile("VerticalAlignment.xlsx");
VerticalAlignType VerticalAlignment { get; set; }

Property Value

VerticalAlignType

Worksheet

Returns a Worksheet object that represents the worksheet containing the specified range. Read-only.

IWorksheet Worksheet { get; }

Property Value

IWorksheet

Methods

Activate(bool)

Activates a single cell, scroll to it and activates the corresponding sheet. To select a range of cells, use the Select method. The following code illustrates how to activate a Range with scroll flag:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Activates 'F1' cell.
worksheet.Range["F1"].Activate(true);

//Save to file
workbook.SaveToFile("Activate.xlsx");
IXLSRange Activate(bool scroll)

Parameters

scroll bool

True to scroll to the cell

Returns

IXLSRange

Returns the active cell.

AddComment()

Adds comment to the range. The following code illustrates how to insert Comments in the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Adding comments to a cell
ICommentShape comment = worksheet.Range["A1"].AddComment();
comment.Text= "Comments";

//Save to file
workbook.SaveToFile("AddComment.xlsx");
ICommentShape AddComment()

Returns

ICommentShape

Range's comment.

AutoFitColumns()

Autofits all columns in the range. The following code illustrates how to auto-size column width to its cell content:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Auto-fit columns
worksheet.Range["B4"].Text = "Fit the content to column";
worksheet.Range["B4"].AutoFitColumns();

//Save to file
workbook.SaveToFile("AutoFitRows.xlsx");
void AutoFitColumns()

AutoFitRows()

Autofits all rows in the range. The following code illustrates how to auto-size row height to its cell content:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Auto-fit rows
worksheet.Range["A2"].Text = "Fit the content to row";
worksheet.Range["A2"].IsWrapText = true;
worksheet.Range["A2"].AutoFitRows();

//Save to file
workbook.SaveToFile("AutoFitRows.xlsx");
void AutoFitRows()

BorderAround()

Sets around border for current range. The following code illustrates how to apply border around the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderAround();

//Save to file
workbook.SaveToFile("BorderAround.xlsx");
void BorderAround()

BorderAround(LineStyleType)

Sets around border for current range. The following code illustrates how to apply border around the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderAround(LineStyleType.Thick);

//Save to file
workbook.SaveToFile("BorderAround.xlsx");
void BorderAround(LineStyleType borderLine)

Parameters

borderLine LineStyleType

Represents border line.

BorderAround(LineStyleType, ExcelColors)

Sets around border for current range. The following code illustrates how to apply border around the Range with color from Spire.Xls.ExcelColors structure:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderAround(LineStyleType.Thick , ExcelColors.Red);

//Save to file
workbook.SaveToFile("BorderAround.xlsx");
void BorderAround(LineStyleType borderLine, ExcelColors borderColor)

Parameters

borderLine LineStyleType

Represents border line.

borderColor ExcelColors

Represents border color as ExcelColors.

BorderAround(LineStyleType, Color)

Sets around border for current range. The following code illustrates how to apply border around the Range with color from System.Drawing.Color structure:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderAround(LineStyleType.Thick , Color.Red);

//Save to file
workbook.SaveToFile("BorderAround.xlsx");
void BorderAround(LineStyleType borderLine, Color borderColor)

Parameters

borderLine LineStyleType

Represents border line.

borderColor Color

Represents border color.

BorderInside()

Sets inside border for current range. The following code illustrates how to apply border inside the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderInside();

//Save to file
workbook.SaveToFile("BorderInside.xlsx");
void BorderInside()

BorderInside(LineStyleType)

Sets inside border for current range. The following code illustrates how to apply border inside the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderInside(LineStyleType.Thick);

//Save to file
workbook.SaveToFile("BorderInside.xlsx");
void BorderInside(LineStyleType borderLine)

Parameters

borderLine LineStyleType

Represents border line.

BorderInside(LineStyleType, ExcelColors)

Sets inside border for current range. The following code illustrates how to apply border inside the Range with color from Spire.Xls.ExcelColors structure:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderInside(LineStyleType.Thick , ExcelColors.Red);

//Save to file
workbook.SaveToFile("BorderInside.xlsx");
void BorderInside(LineStyleType borderLine, ExcelColors borderColor)

Parameters

borderLine LineStyleType

Represents border line.

borderColor ExcelColors

Represents border color as ExcelColors.

BorderInside(LineStyleType, Color)

Sets inside border for current range. The following code illustrates how to apply border inside the Range with color from System.Drawing.Color structure:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";

//Set border
worksheet["C2:D3"].BorderInside(LineStyleType.Thick , Color.Red);

//Save to file
workbook.SaveToFile("BorderInside.xlsx");
void BorderInside(LineStyleType borderLine, Color borderColor)

Parameters

borderLine LineStyleType

Represents border line.

borderColor Color

Represents border color.

BorderNone()

Sets none border for current range. The following code illustrates how to remove borders in the Range:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Remove borders
worksheet["C2"].BorderNone();

//Save to file
workbook.SaveToFile("BorderNone.xlsx");
void BorderNone()

Clear(ExcelClearOptions)

Clears the cell content, formats, comments based on clear option. The following code illustrates how to clear the Range with clear options:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Clears the Range C2 with its clear options
worksheet.Range["C2"].Clear(ExcelClearOptions.ClearAll);

//Save to file
workbook.SaveToFile("ClearContents.xlsx");
void Clear(ExcelClearOptions option)

Parameters

option ExcelClearOptions

Represents the clear options.

ClearContents()

Clear the contents of the Range. The following code illustrates how to clear the Range:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Clears the Range C2
worksheet.Range["C2"].ClearContents();

//Save to file
workbook.SaveToFile("ClearContents.xlsx");
void ClearContents()

CollapseGroup(GroupByType)

Collapses current group. The following code illustrates how to remove borders in the Range:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Collapse group
worksheet.Range["A5:A15"].CollapseGroup(GroupByType.ByRows);

//Save to file
workbook.SaveToFile("CollapseGroup.xlsx");
void CollapseGroup(GroupByType groupBy)

Parameters

groupBy GroupByType

This parameter specifies whether the grouping should be performed by rows or by columns.

ExpandGroup(GroupByType)

Expands current group. The following code illustrates how to expand the group in the Range:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Expand group with flag set to expand parent
worksheet.Range["A5:A15"].ExpandGroup(GroupByType.ByRows);

//Save to file
workbook.SaveToFile("ExpandGroup.xlsx");
void ExpandGroup(GroupByType groupBy)

Parameters

groupBy GroupByType

This parameter specifies whether the grouping should be performed by rows or by columns.

ExpandGroup(GroupByType, ExpandCollapseFlags)

Expands current group. The following code illustrates how to perform ExpandGroup in the Range with collapse option:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Expand group with flag set to expand parent
worksheet.Range["A5:A15"].ExpandGroup(GroupByType.ByRows, ExpandCollapseFlags.ExpandParent);

//Save to file
workbook.SaveToFile("ExpandGroup.xlsx");
void ExpandGroup(GroupByType groupBy, ExpandCollapseFlags flags)

Parameters

groupBy GroupByType

This parameter specifies whether the grouping should be performed by rows or by columns.

flags ExpandCollapseFlags

Additional option flags.

ExportDataTable(ExportTableOptions) 

DataTable ExportDataTable(ExportTableOptions options)

Parameters

options ExportTableOptions

Returns

DataTable

FreezePanes()

Freezes pane at the current range. The following code illustrates how to freeze a pane in the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Applying Freeze Pane to the sheet by specifying a cell
worksheet.Range["B2"].FreezePanes();

//Save to file
workbook.SaveToFile("FreezePanes.xlsx");
void FreezePanes()

Intersect(IXLSRange)

Returns intersection of this range with the specified one. The following code illustrates how to perform intersectwith in the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get intersect range
IXLSRange range = worksheet.Range["A16:C16"];
IXLSRange commonRange = worksheet.Range["B16:D16"].Intersect(range);

//Save to file
workbook.SaveToFile("Intersect.xlsx");
IXLSRange Intersect(IXLSRange range)

Parameters

range IXLSRange

The Range with which to intersect.

Returns

IXLSRange

Range intersection; if there is no intersection, NULL is returned.

Merge()

Creates a merged cell from the specified Range object. The following code illustrates how to merge the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Merged cell";

//Merge cells
worksheet["A1:B1"].Merge();

//Save to file
workbook.SaveToFile("Merge.xlsx");
void Merge()

Merge(IXLSRange)

Returns merge of this range with the specified one. The following code illustrates how to check whether two ranges are mergable or not:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Merge range
worksheet["A2:B2"].Merge();

//Get mergable range
IXLSRange mergableRange = worksheet["A2"].MergeArea.Merge(worksheet["C2"]);

//Check mergable Area
Console.Write(mergableRange.RangeAddressLocal);

//Save to file
workbook.SaveToFile("Intersect.xlsx");
IXLSRange Merge(IXLSRange range)

Parameters

range IXLSRange

The Range to merge with.

Returns

IXLSRange

Merged ranges or NULL if wasn't able to merge ranges.

Merge(bool)

Creates a merged cell from the specified Range object. The following code illustrates how to merge the Range with clear option:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Merged cell";
worksheet["B1"].Text = "sample";

//Merge cells
worksheet["A1:B1"].Merge(true);

//Save to file
workbook.SaveToFile("Merge.xlsx");
void Merge(bool clearCells)

Parameters

clearCells bool

Indicates whether to clear unnecessary cells.

UnMerge()

Separates a merged area into individual cells. The following code illustrates how to UnMerge the merged cells:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Merged cell";

//Merge cells
worksheet["A1:B1"].Merge(true);

//Unmerge cells
worksheet["A1:B1"].UnMerge();

//Save to file
workbook.SaveToFile("UnMerge.xlsx");
void UnMerge()