Click or drag to resize

XlsRange Class

Represents a range of cells in an Excel worksheet.
Inheritance Hierarchy
System Object
Spire.Xls.Core.Spreadsheet XlsObject
Spire.Xls.Core.Spreadsheet XlsRange
Spire.Xls CellRange

Namespace: Spire.Xls.Core.Spreadsheet
Assembly: Spire.XLS (in Spire.XLS.dll) Version: 15.4.0.0 (15.4.0.5046)
Syntax
Copy 
public class XlsRange : XlsObject, IXLSRange, 
	IExcelApplication, IEnumerable, ICombinedRange, IEnumerable<IXLSRange>, 
	IDisposable

The XlsRange type exposes the following members.

Properties
Name Description
Public property Code example BooleanValue
Returns or sets the bool value of the specified range.
Examples
The following code illustrates how to access Boolean property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set and get BooleanValue</para><para>worksheet.Range[2, 4].BooleanValue = true;</para><para>bool boolean = worksheet.Range[2, 4].BooleanValue;</para>
Public property Borders
Returns aBorders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format).
Public property Code example BuiltInStyle
Gets/sets built in style.
Examples
The following code illustrates how to access BuiltInStyle property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>//Set built in style</para><para>worksheet["C2"].BuiltInStyle = BuiltInStyles.Accent3;</para><para>//Save to file</para><para>workbook.SaveToFile("BuiltInStyle.xlsx");</para>
Public property CellList
Returns a Range object that represents the cells in the specified range.
Public property Cells Obsolete.
Returns a Range object that represents the cells in the specified range.
Public property CellsCount
Gets number of cells.
Public property Code example CellStyleName
Gets/sets name of the style for the current range.
Examples
The following code illustrates how to access CellStyleName of the specified range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Add and set style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>worksheet["C2"].Style = style;</para><para>//Check Style name</para><para>Console.Write(worksheet["C2"].CellStyleName);</para>
Public property Code example Column
Returns the number of the first column in the first area in the specified range.
Examples
The following code illustrates how to access Column property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get specified column</para><para>int firstColumn = worksheet["E1:R3"].Column;</para>
Public property ColumnCount
Gets number of columns.
Public property ColumnGroupLevel
Column group level.
Public property Columns
For a Range object, it returns an array of Range objects that represent the columns in the specified range.
Public property Code example ColumnWidth
Returns or sets the width of all columns in the specified range.
Examples
The following code illustrates how to set the width of all columns in the specified range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set the ColumnWidth</para><para>worksheet["A1"].Text = "This cell contains sample text";</para><para>worksheet["A1"].ColumnWidth = 25;</para><para>//Save to file</para><para>workbook.SaveToFile("ColumnWidth.xlsx");</para>
Public property CombinedAddress
Returns the combined range reference in the language. Read-only String.
Public property CombinedCells
Gets the list of combined cells.
Public property Comment
Returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range.
Public property ConditionalFormats Obsolete.
Collection of conditional formats for the range.
Public property Count
Returns the number of objects in the collection.
Public property CurrentRegion
Get the range associated with a range.
Public property Code example DataValidation
Get dataValidation of the sheet. Read Only.
Examples
The following code illustrates how to access DataValidation property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Data validation for number</para><para>IDataValidation validation = worksheet.Range["A3"].DataValidation;</para><para>validation.AllowType = CellDataType.Integer;</para><para>//Value between 0 to 10</para><para>validation.CompareOperator = ValidationComparisonOperator.Between;</para><para>validation.Formula1 = "0";</para><para>validation.Formula2 = "10";</para><para>//Save to file</para><para>workbook.SaveToFile("DataValidation.xlsx");</para>
Public property Code example DateTimeValue
Gets/sets DateTime value of the range.
Examples
The following code illustrates how to set and access DateTimeValue property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set and get the DateTimeValue of specified range</para><para>worksheet.Range[2, 4].DateTimeValue = DateTime.Now;</para><para>DateTime dateTime = worksheet.Range[2, 4].DateTimeValue;</para><para>//Save to file</para><para>workbook.SaveToFile("DateTimeValue.xlsx");</para>
Public property DisplayedText
Gets cell displayed text.
Public property EndCell
Returns a Range object that represents the cell at the end of the region that contains the source range.
Public property EntireColumn
Returns a Range object that represents the entire column (or columns) that contains the specified range.
Public property EntireRow
Returns a Range object that represents the entire row (or rows) that contains the specified range. Read-only.
Public property Code example EnvalutedValue
Returns the calculated value of a formula.
Examples
The following code illustrates how to access a calculated value:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Returns the calculated value of a formula using the most current inputs</para><para>string calculatedValue = worksheet["C1"].EnvalutedValue;</para><para>Console.WriteLine(calculatedValue);</para>
Public property ErrorValue
Gets or sets error value of this range.
Public property ExtendedFormatIndex
Sets / gets index of extended format.
Protected property FirstColumn
Gets or sets the index of the first column in the range.
Protected property FirstRow
Gets or sets the first row of the range.
Public property Formula
Returns or sets the object's formula in A1-style notation and in the language of the macro.
Public property Code example FormulaArray
Returns or sets the array formula of a range.
Examples
The following code illustrates how to set and access FormulaArray property of the range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Assign array formula</para><para>worksheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";</para><para>//Adding a named range for the range A1 to D1</para><para>worksheet.Names.Add("ArrayRange", worksheet.Range["A1:D1"]);</para><para>//Assign formula array with named range</para><para>worksheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";</para><para>//Save to file</para><para>workbook.SaveToFile("FormulaArray.xlsx");</para>
Public property FormulaArrayR1C1
Returns or sets the formula for the object, using R1C1-style notation in the language of the macro
Public property FormulaBoolValue
Gets or sets bool value of the formula.
Public property FormulaDateTime
Gets or sets bool value of the formula.
Public property FormulaErrorValue
Gets or sets error value of the formula.
Public property FormulaNumberValue
Gets or sets double value of the formula.
Public property FormulaR1C1
Returns or sets the formula for the object, using R1C1-style notation in the language of the macro
Public property FormulaStringValue
Gets or sets string value of the range.
Public property FormulaValue
Gets formula value.
Public property Code example HasBoolean
Indicates whether range contains bool value.
Examples
The following code illustrates how to set and access HasBoolean property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Assigning Value2 property of the Range</para><para>worksheet["A3"].Value2 = false;</para><para>//Checking Range types</para><para>bool isboolean = worksheet["A3"].HasBoolean;</para><para>//Save to file</para><para>workbook.SaveToFile("HasBoolean.xlsx");</para>
Public property HasComment
Gets a value indicating whether the cell has a comment.
Public property HasConditionFormats
Indicates whether each cell of the range has some conditional formatting.
Public property 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.
Public property Code example HasDateTime
Determines if all cells in the range contain datetime.
Examples
The following code illustrates how to set and access HasDateTime property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Assigning Value2 property of the Range</para><para>worksheet["A1"].Value2 = DateTime.Now;</para><para>//Checking Range types</para><para>bool isDateTime =worksheet["A1"].HasDateTime;</para><para>//Save to file</para><para>workbook.SaveToFile("HasDateTime.xlsx");</para>
Public property HasError
Indicates whether range contains error value.
Public property HasExternalFormula
Check if the formula in the range has external links. Read-only.
Public property HasFormula
True if all cells in the range contain formulas;
Public property HasFormulaArray
Determines if all cells in the range contain array-entered formula.
Public property HasFormulaBoolValue
Determines if all cells in the range contain formula bool value..
Public property HasFormulaDateTime
Indicates if current range has formula value formatted as DateTime. Read-only.
Public property HasFormulaErrorValue
Determines if all cells in the range contain error value.
Public property HasFormulaNumberValue
Indicates whether current range has formula number value.
Public property HasFormulaStringValue
Indicates whether current range contains formula value which evaluated as string.
Public property Code example HasMerged
Indicates whether this range is part of merged range.
Examples
The following code illustrates how to access HasMerged property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Sample text in cell";</para><para>//Set merge</para><para>worksheet["A1:B1"].Merge();</para><para>//Check merge</para><para>Console.Write(worksheet["A1:B1"].HasMerged);</para>
Public property Code example HasNumber
Determines if any one cell in the range contain number.
Examples
The following code illustrates how to set and access Value2 property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Assigning Value2 property of the Range</para><para>worksheet["A2"].Value2 = 45;</para><para>//Checking Range types</para><para>bool isNumber =worksheet["A2"].HasNumber;</para><para>//Save to file</para><para>workbook.SaveToFile("HasNumber.xlsx");</para>
Public property HasPictures
Indicates whether the range is blank.
Public property Code example HasRichText
Determines if all cells in the range contain rich text string.
Examples
The following code illustrates how to access HasRichText property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>IStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set rich text</para><para>IRichTextString richText = worksheet["C2"].RichText;</para><para>richText.Text = "Sample";</para><para>IFont font = style.Font;</para><para>font.Color = Color.Red;</para><para>richText.SetFont(0, 5, font);</para><para>//Check HasRichText</para><para>Console.Write(worksheet["C2"].HasRichText);</para><para>//Save to file</para><para>workbook.SaveToFile("HasRichText.xlsx");</para>
Public property HasString
Determines if all cells in the range contain string.
Public property Code example HasStyle
Determines if all cells in the range containdiffers from default style.
Examples
The following code illustrates how to access HasStyle property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Add style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set color and style</para><para>style.Color = Color.Red;</para><para>worksheet["C2"].Style = style;</para><para>//Check HasStyle</para><para>Console.Write(worksheet["C2"].HasStyle);</para><para>//Save to file</para><para>workbook.SaveToFile("HasStyle.xlsx");</para>
Public property Code example HorizontalAlignment
Returns or sets the horizontal alignment for the specified object.
Examples
The following code illustrates how to set and access HasStyle property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Test";</para><para>//Set alignment</para><para>worksheet["A1"].HorizontalAlignment = HorizontalAlignType.Right;</para><para>//Save to file</para><para>workbook.SaveToFile("HorizontalAlignment.xlsx");</para>
Public property HtmlString
Gets and sets the html string which contains data and some formattings in this cell.
Public property Hyperlinks
Returns hyperlinks for this range.
Public property IgnoreErrorOptions
Represents ignore error options. If not single cell returs concatenateed flags.
Public property Code example IndentLevel
Returns or sets the indent level for the cell or range. value should be 0 between 15.
Examples
The following code illustrates how to set indent level for a cell:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>//Set indent level</para><para>worksheet["C2"].IndentLevel = 2;</para><para>//Save to file</para><para>workbook.SaveToFile("IndentLevel.xlsx");</para>
Public property IsAllNumber
Determines if all cells in the range contain number.
Public property IsBlank
Indicates whether the range is blank.
Public property IsFormulaHidden
Determines if the formula will be hidden when the worksheet is protected.
Public property IsGroupedByColumn
Indicates whether this range is grouped by column.
Public property IsGroupedByRow
Indicates whether this range is grouped by row.
Public property IsInitialized
Indicates whether range has been initialized.
Protected property IsSingleCell
Checks if the current selection is a single cell.
Public property IsStringsPreserved
Indicates whether all values in the range are preserved as strings.
Public property Code example IsWrapText
Determines if Microsoft Excel wraps the text in the object.
Examples
The following code illustrates how to access WrapText property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "This cell contains sample text";</para><para>//Set wrap text</para><para>worksheet["A1"].IsWrapText = true;</para><para>//Save to file</para><para>workbook.SaveToFile("IsWrapText.xlsx");</para>
Public property Item String
Gets cell range. Read-only.
Public property Item Int32, Int32
Gets / sets cell by row and column index. Row and column indexes are one-based.
Public property Item String, Boolean
Gets cell range. Read-only.
Public property Item Int32, Int32, Int32, Int32
Get cell range. Row and column indexes are one-based. Read-only.
Public property LastColumn
Gets or sets last column of the range.
Public property LastRow
Gets or sets last row of the range.
Public property MergeArea
Returns a Range object that represents the merged range containing the specified cell.
Public property Code example NumberFormat
Returns or sets the format code for the object.
Examples
The following code illustrates how to set NumberFormat property:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set data</para><para>worksheet["C2"].Value = "3100.23";</para><para>//Set number format</para><para>worksheet["C2"].NumberFormat = "#,##1.##";</para><para>//Save to file</para><para>workbook.SaveToFile("NumberFormat.xlsx");</para>
Public property Code example NumberText
Returns cell text for number format.
Examples
The following code illustrates how to access NumberText property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Gets cell value with its number format</para><para>CellRange range= worksheet.Range[3, 1];</para><para>range.Value = "1/1/2015";</para><para>range.NumberFormat = "dd-MMM-yyyy";</para><para>string numberText = range.NumberText;</para><para>//Save to file</para><para>workbook.SaveToFile("NumberText.xlsx");</para>
Public property NumberValue
Gets or sets number value of the range.
Public property Parent
Parent object for this object.
Public property Code example RangeAddress
Returns the range reference in the language of the macro. Read-only String.
Examples
The following code illustrates how to access Address property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get RangeAddress</para><para>string address = worksheet.Range[3, 4].RangeAddress;</para>
Public property Code example RangeAddressLocal
Returns the range reference for the specified range in the language of the user.
Examples
The following code illustrates how to access AddressLocal property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get RangeAddressLocal</para><para>string address = worksheet.Range[3, 4].RangeAddressLocal;</para>
Public property Code example RangeGlobalAddress
Returns the range reference in the language of the macro.
Examples
The following code illustrates how to access AddressGlobal property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get RangeAddress</para><para>string address = worksheet.Range[3, 4].RangeGlobalAddress;</para>
Public property RangeGlobalAddress2007
Gets address global in the format required by Excel 2007.
Public property RangeGlobalAddressWithoutSheetName
Return global address without worksheet name.
Public property Code example RangeR1C1Address
Returns the range reference using R1C1 notation.
Examples
The following code illustrates how to access AddressR1C1 property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get RangeR1C1Address</para><para>string address = worksheet.Range[3, 4].RangeR1C1Address;</para>
Public property Code example RangeR1C1AddressLocal
Returns the range reference using R1C1 notation.
Examples
The following code illustrates how to access AddressR1C1Local property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get RangeR1C1AddressLocal</para><para>string address = worksheet.Range[3, 4].RangeR1C1Address;</para>
Protected property ReferenceCount
Get quantity of instance references.
(Inherited from XlsObject .)
Public property RichText
Returns a RichTextString object that represents the rich text style.
Public property Row
Returns the number of the first row of the first area in the range.
Public property RowCount
Gets number of rows.
Public property RowGroupLevel
Row group level.
Public property Code example RowHeight
Returns the height of all the rows in the range specified, measured in points.
Examples
The following code illustrates how to set row height:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Test";</para><para>//Set row height</para><para>worksheet["A1"].RowHeight = 30;</para><para>//Save to file</para><para>workbook.SaveToFile("RowHeight.xlsx");</para>
Public property Rows
For a Range object, it returns an array of Range objects that represent the rows in the specified range.
Public property Style
Returns a Style object that represents the style of the specified range.
Public property Text
Gets / sets text of range.
Public property TimeSpanValue
Gets or sets timespan value of cell.
Public property Code example Value
Returns or sets the value of the specified range.
Examples
The following code illustrates how to set Value of the specified range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set value of the range</para><para>CellRange range= worksheet.Range[3, 1];</para><para>range.Value = "1/1/2015";</para><para>//Save to file</para><para>workbook.SaveToFile("Value.xlsx");</para>
Public property Code example Value2
Returns or sets the cell value. It's not use for current and datetime types.
Examples
The following code illustrates how to access Value2 property of the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Assigning Value2 property of the Range</para><para>worksheet["A1"].Value2 = DateTime.Now;</para><para>worksheet["A3"].Value2 = false;</para><para>//Checking Range types</para><para>Console.WriteLine(worksheet["A1"].HasDateTime);</para><para>Console.WriteLine(worksheet["A3"].HasBoolean);</para>
Public property Code example VerticalAlignment
Returns or sets the vertical alignment of the specified object.
Examples
The following code illustrates how to set vertical alignment type:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Test";</para><para>//Set alignment</para><para>worksheet["A1"].VerticalAlignment = VerticalAlignType.Top;</para><para>//Save to file</para><para>workbook.SaveToFile("VerticalAlignment.xlsx");</para>
Protected property Workbook
Gets the workbook .
Public property Worksheet
Returns a worksheet object that represents the worksheet containing the specified range.
Public property WorksheetName
Returns name of the parent worksheet.
Top
Methods
Name Description
Public method Activate
Active single cell in the worksheet
Public method Code example Activate(Boolean)
Activates a single cell, scroll to it and activates the corresponding sheet. To select a range of cells, use the Select method.
Examples
The following code illustrates how to activate a Range with scroll flag:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Activates 'F1' cell.</para><para>worksheet.Range["F1"].Activate(true);</para><para>//Save to file</para><para>workbook.SaveToFile("Activate.xlsx");</para>
Public method AddCombinedRange
Adds a combined range to the collection if it is not already present.
Public method AddComment
Adds a comment to the range.
Public method AddComment(Boolean)
Adds a comment to the range.
Protected method AddComment(ICommentShape)
Adds a comment to the shape.
Protected method AddReference
Increase the quantity of reference. User must use this method when new wrapper on object is created or reference on object stored.
(Inherited from XlsObject .)
Public method ApplyStyle
Applies the specified style to the cells within the range, based on the given flag.
Public method Code example AutoFitColumns
Changes the width of the columns in the range in the range to achieve the best fit.
Examples
The following code illustrates how to auto-size column width to its cell content:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Auto-fit columns</para><para>worksheet.Range["B4"].Text = "Fit the content to column";</para><para>worksheet.Range["B4"].AutoFitColumns();</para><para>//Save to file</para><para>workbook.SaveToFile("AutoFitRows.xlsx");</para>
Public method Code example AutoFitRows
Changes the width of the height of the rows in the range to achieve the best fit.
Examples
The following code illustrates how to auto-size row height to its cell content:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Auto-fit rows</para><para>worksheet.Range["A2"].Text = "Fit the content to row";</para><para>worksheet.Range["A2"].IsWrapText = true;</para><para>worksheet.Range["A2"].AutoFitRows();</para><para>//Save to file</para><para>workbook.SaveToFile("AutoFitRows.xlsx");</para>
Public method Code example BorderAround
Sets around border for current range.
Examples
The following code illustrates how to apply border around the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderAround();</para><para>//Save to file</para><para>workbook.SaveToFile("BorderAround.xlsx");</para>
Public method Code example BorderAround(LineStyleType)
Sets around border for current range.
Examples
The following code illustrates how to apply border around the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderAround(LineStyleType.Thick);</para><para>//Save to file</para><para>workbook.SaveToFile("BorderAround.xlsx");</para>
Public method Code example BorderAround(LineStyleType, ExcelColors)
Sets around border for current range.
Examples
The following code illustrates how to apply border around the Rangewith color from Spire.Xls.ExcelColors structure:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderAround(LineStyleType.Thick , ExcelColors.Red);</para><para>//Save to file</para><para>workbook.SaveToFile("BorderAround.xlsx");</para>
Public method Code example BorderAround(LineStyleType, Color)
Sets around border for current range.
Examples
The following code illustrates how to apply border around the Rangewith color from System.Drawing.Color structure:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderAround(LineStyleType.Thick , Color.Red);</para><para>//Save to file</para><para>workbook.SaveToFile("BorderAround.xlsx");</para>
Public method Code example BorderInside
Sets inside border for current range.
Examples
The following code illustrates how to apply border inside the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderInside();</para><para>//Save to file</para><para>workbook.SaveToFile("BorderInside.xlsx");</para>
Public method Code example BorderInside(LineStyleType)
Sets inside border for current range.
Examples
The following code illustrates how to apply border inside the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderInside(LineStyleType.Thick);</para><para>//Save to file</para><para>workbook.SaveToFile("BorderInside.xlsx");</para>
Public method Code example BorderInside(LineStyleType, ExcelColors)
Sets inside border for current range.
Examples
The following code illustrates how to apply border inside the Range with color from Spire.Xls.ExcelColors structure:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderInside(LineStyleType.Thick , ExcelColors.Red);</para><para>//Save to file</para><para>workbook.SaveToFile("BorderInside.xlsx");</para>
Public method Code example BorderInside(LineStyleType, Color)
Sets inside border for current range.
Examples
The following code illustrates how to apply border inside the Range with color from System.Drawing.Color structure:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample";</para><para>worksheet["D2"].Text = "text";</para><para>worksheet["C3"].Text = "in";</para><para>worksheet["D3"].Text = "cell";</para><para>//Set border</para><para>worksheet["C2:D3"].BorderInside(LineStyleType.Thick , Color.Red);</para><para>//Save to file</para><para>workbook.SaveToFile("BorderInside.xlsx");</para>
Public method Code example BorderNone
Sets none border for current range.
Examples
The following code illustrates how to remove borders in the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Remove borders</para><para>worksheet["C2"].BorderNone();</para><para>//Save to file</para><para>workbook.SaveToFile("BorderNone.xlsx");</para>
Public method CalculateAllValue
Caculate all formula for the specified range
Protected method CheckDisposed
Checks whether object was disposed and throws exception if it was.
(Inherited from XlsObject .)
Protected method CheckRange
Checks if the given row and column indices are within the valid range of the book. Throws an ArgumentOutOfRangeException if the indices are out of range.
Protected method Clear(Boolean)
Clears the cells in the specified range and optionally resets the cell style to "Normal".
Public method Code example Clear(ExcelClearOptions)
Clears the cell based on clear options.
Examples
The following code illustrates how to clear the Range with clear options:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Clears the Range C2 with its clear options</para><para>worksheet.Range["C2"].Clear(ExcelClearOptions.ClearAll);</para><para>//Save to file</para><para>workbook.SaveToFile("ClearContents.xlsx");</para>
Public method ClearAll
Clears the entire object.
Public method ClearConditionalFormats
Clears conditional formats.
Public method Code example ClearContents
Clear the contents of the Range.
Examples
The following code illustrates how to clear the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Clears the Range C2</para><para>worksheet.Range["C2"].ClearContents();</para><para>//Save to file</para><para>workbook.SaveToFile("ClearContents.xlsx");</para>
Public method Clone
Clones current IXLSRange.
Public method Code example CollapseGroup
Collapses current group.
Examples
The following code illustrates how to remove borders in the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Collapse group</para><para>worksheet.Range["A5:A15"].CollapseGroup(GroupByType.ByRows);</para><para>//Save to file</para><para>workbook.SaveToFile("CollapseGroup.xlsx");</para>
Public method ConvertToNumber
Convert number that stored as text to number
Public method CopyToClipboard Obsolete.
Copies range to the clipboard.
Public method Dispose
This method is called when disposing the object.
Public method Equals
Determines whether the specified object is equal to the current object.
(Inherited from Object .)
Public method Code example ExpandGroup(GroupByType)
Expands current group.
Examples
The following code illustrates how to expand the group in the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Expand group with flag set to expand parent</para><para>worksheet.Range["A5:A15"].ExpandGroup(GroupByType.ByRows);</para><para>//Save to file</para><para>workbook.SaveToFile("ExpandGroup.xlsx");</para>
Public method Code example ExpandGroup(GroupByType, ExpandCollapseFlags)
Expands current group.
Examples
The following code illustrates how to perform ExpandGroup in the Range with collapse option:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Expand group with flag set to expand parent</para><para>worksheet.Range["A5:A15"].ExpandGroup(GroupByType.ByRows, ExpandCollapseFlags.ExpandParent);</para><para>//Save to file</para><para>workbook.SaveToFile("ExpandGroup.xlsx");</para>
Public method ExportDataTable
Exports the data from the worksheet to a DataTable based on the provided options.
Protected method Finalize
Destructor. Call dispose method of current object.
(Inherited from XlsObject .)
Protected method FindAll(Boolean)
Finds all cell ranges that match the specified value within the CellRange.
Protected method FindAll(DateTime)
Finds all occurrences of the specified DateTime value within the CellRange.
Protected method FindAll(TimeSpan)
Finds all occurrences of the specified TimeSpan value within the CellRange.
Protected method FindAll(Double, FindType)
Finds all occurrences of the specified value within the CellRange.
Protected method FindAll(String, FindType)
Finds all occurrences of the specified value within the CellRange.
Protected method FindAll(String, FindType, ExcelFindOptions)
Finds all occurrences of a specified value within the CellRange.
Protected method FindFirst(Boolean)
Finds the first cell range that matches the specified value within the CellRange.
Protected method FindFirst(DateTime)
Searches for the first occurrence of a DateTime value within the specified area of the CellRange.
Protected method FindFirst(TimeSpan)
Finds the first occurrence of a specified TimeSpan value in the worksheet within the current area.
Protected method FindFirst(Double, FindType)
Finds the first occurrence of a specified value in the worksheet within the current area.
Protected method FindFirst(String, FindType)
Finds the first occurrence of a specified string value in the worksheet within the current area.
Protected method FindParent(Type)
This method is used to find parent with specific type.
(Inherited from XlsObject .)
Protected method FindParent( Type )
Find parent of object.
(Inherited from XlsObject .)
Protected method FindParent(Type, Boolean)
This method is used to find parent with specific type.
(Inherited from XlsObject .)
Protected method FindParents
Finds parent objects.
(Inherited from XlsObject .)
Protected method FindWorksheet
Finds the worksheet by the given sheet name.
Public method Code example FreezePanes
Freezes panes at the current range in the worksheet. current range should be single cell range.
Examples
The following code illustrates how to freeze a pane in the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Applying Freeze Pane to the sheet by specifying a cell</para><para>worksheet.Range["B2"].FreezePanes();</para><para>//Save to file</para><para>workbook.SaveToFile("FreezePanes.xlsx");</para>
Public method GetConditionFormatsStyle
Get the calculated condition format style of current Range. If style of every cell is not same, return null. If current range without condition format, return null.
Public method GetEnumerator
Returns an enumerator that iterates through the collection of IXLSRange objects.
Public method GetHashCode
Serves as the default hash function.
(Inherited from Object .)
Public method GetNamedRange
Get the named range object of current Range.
Public method GetNewRangeLocation
Gets new range location.
Public method GetRectangles
Gets rectangle information of current range.
Public method GetRectanglesCount
Returns number of rectangles..
Public method GetType
Gets the Type of the current instance.
(Inherited from Object .)
Public method GroupByColumns
Groups columns.
Public method GroupByRows
Groups row.
Protected method InfillCells
Infills cells based on the current range and updates the internal state.
Public method InsertOrUpdateCellImage(Stream, Boolean)
Adds CellImage from the specified file. this method only support WPS
Public method InsertOrUpdateCellImage(String, Boolean)
Adds CellImage from the specified file. this method only support WPS
Public method Code example Intersect
Returns intersection of this range with the specified one.
Examples
The following code illustrates how to perform intersectwith in the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get intersect range</para><para>IXLSRange range = worksheet.Range["A16:C16"];</para><para>IXLSRange commonRange = worksheet.Range["B16:D16"].Intersect(range);</para><para>//Save to file</para><para>workbook.SaveToFile("Intersect.xlsx");</para>
Public method IsIntersect
Determines if the current range intersects with the specified range.
Public method MeasureString
Measures size of the string.
Protected method MemberwiseClone
Creates a shallow copy of the current Object .
(Inherited from Object .)
Public method Code example Merge
Creates a merged cell from the specified Range object.
Examples
The following code illustrates how to merge the Range:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Merged cell";</para><para>//Merge cells</para><para>worksheet["A1:B1"].Merge();</para><para>//Save to file</para><para>workbook.SaveToFile("Merge.xlsx");</para>
Public method Code example Merge(Boolean)
Creates a merged cell from the specified Range object.
Examples
The following code illustrates how to merge the Range with clear option:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Merged cell";</para><para>worksheet["B1"].Text = "sample";</para><para>//Merge cells</para><para>worksheet["A1:B1"].Merge(true);</para><para>//Save to file</para><para>workbook.SaveToFile("Merge.xlsx");</para>
Public method Merge(IXLSRange)
Creates a merged cell from the specified Range object.
Protected method MoveTo(IXLSRange)
Moves the range to the specified destination with the default copy options.
Protected method MoveTo(IXLSRange, Boolean)
Moves the range to the specified destination.
Protected method OnDispose
Method which can be overriden by users to take any specific actions when object is disposed.
(Inherited from XlsObject .)
Public method PartialClear
Partially clear range.
Protected method ReleaseReference
Decrease quantity of Reference. User must call this method when freeing resources.
(Inherited from XlsObject .)
Public method RemoveCellImage
Remove CellImage.
Public method RemoveCombinedRange
Removes a combined range from the list of combined ranges and refreshes the combined cells.
Public method RemoveMergeComment
Removes merge comments from a cell range.
Public method ReparseFormulaString
Reparses formula.
Public method Replace(String, DateTime)
Replaces cells' values with new data.
Public method Replace(String, Double)
Replaces cells' values with new data.
Public method Replace(String, String)
Replaces cells' values with new data.
Public method Replace(String, DataColumn, Boolean)
Replaces cells' values with new data.
Public method Replace(String, DataTable, Boolean)
Replaces cells' values with new data.
Public method Replace(String, Double , Boolean)
Replaces cells' values with new data.
Public method Replace(String, Int32 , Boolean)
Replaces cells' values with new data.
Public method Replace(String, String , Boolean)
Replaces cells' values with new data.
Public method SetAutoFormat(AutoFormatType) Obsolete.
Sets auto format for current range.
Public method SetAutoFormat(AutoFormatType, AutoFormatOptions) Obsolete.
Sets auto format for current range.
Protected method SetBorderToSingleCell
Sets the border for a single cell.
Public method SetDataValidation
Sets data validation for the range.
Public method SetExtendedFormatIndex
Sets index of extended format that defines style for this range..
Protected method SetParent
Sets parent of the object.
(Inherited from XlsObject .)
Public method SetRowHeight
Sets row height.
Public method SetSharedFormula
Sets the shared formula for a cell or a range of cells.
Public method TextPartReplace
Replaces cell's part text and reserve text's format.
Public method ToString
Returns a string that represents the current object.
(Inherited from Object .)
Public method UngroupByColumns
Ungroups column.
Public method UngroupByRows
Ungroups row.
Public method Union
Combines the current range with another cell range.
Public method Code example UnMerge
Separates a merged area into individual cells.
Examples
The following code illustrates how to UnMerge the merged cells:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Merged cell";</para><para>//Merge cells</para><para>worksheet["A1:B1"].Merge(true);</para><para>//Unmerge cells</para><para>worksheet["A1:B1"].UnMerge();</para><para>//Save to file</para><para>workbook.SaveToFile("UnMerge.xlsx");</para>
Public method UpdateRange
Update region of range
Top
Fields
Name Description
Public field Static member DEF_MAX_HEIGHT
Default maximum height value.
Protected field m_bIsDisposed
Flag which indicates if the object was disposed or not.
(Inherited from XlsObject .)
Protected field m_book
Represents the workbook object.
Protected field m_iBottomRow
Index of the bottom row.
Protected field m_iLeftColumn
Index of the left column.
Protected field m_iRightColumn
Index of the right column.
Protected field m_iTopRow
Index of the top row.
Protected field m_style
The base style for the cell.
Top
See Also