Click or drag to resize

XlsRange Methods

The XlsRange type exposes the following members.

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
See Also