XlsRange module

class XlsRange.EnumeratorXlsRange

Bases: IEnumerator[XlsRange]

class XlsRange.ListXlsRanges(ptr)

Bases: IList[IXLSRange]

GetEnumerator() → IEnumerator
class XlsRange.XlsRange

Bases: XlsObject, ICombinedRange, IEnumerable[IXLSRange]

Activate

Activates a single cell, scroll to it and activates the corresponding sheet. To select a range of cells, use the Select method.

Parameters:

scroll – True to scroll to the cell

Returns:

Returns the active cell.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Activates 'F1' cell.
worksheet.Range["F1"].Activate(true)
#Save to file
workbook.SaveToFile("Activate.xlsx")
AddCombinedRange(cr: XlsRange) → XlsRange
AddComment
ApplyStyle(style: IStyle, flag: CellStyleFlag)
AutoFitColumns()

Changes the width of the columns in the range in the range to achieve the best fit. Example:

#Create worksheet
workbook = Workbook()
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")
AutoFitRows()

Changes the width of the height of the rows in the range to achieve the best fit. Example:

#Create worksheet
workbook = Workbook()
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")
property BooleanValue: bool

Returns or sets the bool value of the specified range. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set and get BooleanValue
worksheet.Range[2, 4].BooleanValue = true
boolean = worksheet.Range[2, 4].BooleanValue
BorderAround

Sets around border for current range. Example:

#Create worksheet
workbook = Workbook()
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")
BorderInside

Sets inside border for current range. Example:

#Create worksheet
workbook = Workbook()
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")
BorderNone()

Sets none border for current range. Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
worksheet = workbook.Worksheets[0]
#Remove borders
worksheet["C2"].BorderNone()
#Save to file
workbook.SaveToFile("BorderNone.xlsx")
property Borders: IBorders
property BuiltInStyle: BuiltInStyles

Gets/sets built in style. Example:

#Create worksheet
workbook = Workbook()
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")
CalculateAllValue()

Caculate all formula for the specified range

property CellStyleName: str

Gets/sets name of the style for the current range. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Add and set style
style = workbook.Styles.Add("CustomStyle")
worksheet["C2"].Style = style
#Check Style name
Console.Write(worksheet["C2"].CellStyleName)
property Cells: ListXlsRanges
property CellsCount: int

Gets number of cells.

Clear(option: ExcelClearOptions)

Clears the cell based on clear options.

Parameters:

option – Represents the clear options.

Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
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")
ClearAll()

Clears the entire object.

ClearConditionalFormats()

Clears conditional formats.

ClearContents()

Clear the contents of the Range. Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
worksheet = workbook.Worksheets[0]
#Clears the Range C2
worksheet.Range["C2"].ClearContents()
#Save to file
workbook.SaveToFile("ClearContents.xlsx")
CollapseGroup(groupBy: GroupByType)

Collapses current group.

Parameters:

groupBy –

Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
worksheet = workbook.Worksheets[0]
#Collapse group
worksheet.Range["A5:A15"].CollapseGroup(GroupByType.ByRows)
#Save to file
workbook.SaveToFile("CollapseGroup.xlsx")
property Column: int

Returns the number of the first column in the first area in the specified range. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get specified column
firstColumn = worksheet["E1:R3"].Column
property ColumnCount: int

Gets number of columns.

property ColumnGroupLevel: int

Column group level.

property ColumnWidth: float

Returns or sets the width of all columns in the specified range. Example:

#Create worksheet
workbook = Workbook()
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")
property Columns: ListXlsRanges
property CombinedAddress: str

Returns the combined range reference in the language. Read-only String.

property Comment: ICommentShape

Returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range.

property ConditionalFormats: ConditionalFormats
ConvertToNumber()

Convert number that stored as text to number

CopyToClipboard()
property Count: int

Returns the number of objects in the collection.

property CurrentRegion: IXLSRange

Get the range associated with a range.

static DEF_MAX_HEIGHT() → float
property DataValidation: Validation

Get dataValidation of the sheet. Read Only. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Data validation for number
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")
property DateTimeValue: DateTime

Gets/sets DateTime value of the range. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set and get the DateTimeValue of specified range
worksheet.Range[2, 4].DateTimeValue = DateTime.Now
dateTime = worksheet.Range[2, 4].DateTimeValue
#Save to file
workbook.SaveToFile("DateTimeValue.xlsx")
property DisplayedText: str

Gets cell displayed text.

Dispose()
property EndCell: IXLSRange
property EntireColumn: IXLSRange
property EntireRow: IXLSRange

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

property EnvalutedValue: str

Returns the calculated value of a formula. Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
worksheet = workbook.Worksheets[0]
#Returns the calculated value of a formula using the most current inputs
calculatedValue = worksheet["C1"].EnvalutedValue
print(calculatedValue)
property ErrorValue: str

Gets or sets error value of this range.

ExpandGroup

Expands current group.

Parameters:

groupBy –

Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
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")
property ExtendedFormatIndex: UInt16
property Formula: str

Returns or sets the object’s formula in A1-style notation and in the language of the macro.

property FormulaArray: str

Returns or sets the array formula of a range. Example:

#Create worksheet
workbook = Workbook()
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")
property FormulaArrayR1C1: str

Returns or sets the formula for the object, using R1C1-style notation in the language of the macro

property FormulaBoolValue: bool
property FormulaDateTime: DateTime

Gets or sets bool value of the formula.

property FormulaErrorValue: str

Gets or sets error value of the formula.

property FormulaNumberValue: float

Gets or sets double value of the formula.

property FormulaR1C1: str

Returns or sets the formula for the object, using R1C1-style notation in the language of the macro

property FormulaStringValue: str

Gets or sets string value of the range.

property FormulaValue: str

Gets formula value.

FreezePanes()

Freezes panes at the current range in the worksheet. current range should be single cell range. Example:

#Create worksheet
workbook = Workbook()
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")
GetConditionFormatsStyle() → CellStyle

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.

GetEnumerator() → EnumeratorXlsRange
GetNamedRange() → INamedRange

Get the named range object of current Range.

GetRectangles() → List[Rectangle]

Gets rectangle information of current range.

Returns:

Rectangles information

GetRectanglesCount() → int

Returns number of rectangles..

Returns:

Number of rectangles.

GroupByColumns(isCollapsed: bool) → XlsRange

Groups columns.

Parameters:

isCollapsed – Indicates whether group should be collapsed.

GroupByRows(isCollapsed: bool) → XlsRange

Groups row.

Parameters:

isCollapsed – Indicates whether group should be collapsed.

property HasBoolean: bool

Indicates whether range contains bool value. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Assigning Value2 property of the Range
worksheet["A3"].Value2 = false
#Checking Range types
isboolean = worksheet["A3"].HasBoolean
#Save to file
workbook.SaveToFile("HasBoolean.xlsx")
property HasComment: bool
property HasConditionFormats: bool

Indicates whether each cell of the range has some conditional formatting.

property HasDataValidation: bool

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.

property HasDateTime: bool

Determines if all cells in the range contain datetime. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Assigning Value2 property of the Range
worksheet["A1"].Value2 = DateTime.Now
#Checking Range types
isDateTime =  worksheet["A1"].HasDateTime
#Save to file
workbook.SaveToFile("HasDateTime.xlsx")
property HasError: bool

Indicates whether range contains error value.

property HasExternalFormula: bool

Check if the formula in the range has external links. Read-only.

property HasFormula: bool

True if all cells in the range contain formulas;

property HasFormulaArray: bool

Determines if all cells in the range contain array-entered formula.

property HasFormulaBoolValue: bool

Determines if all cells in the range contain formula bool value..

property HasFormulaDateTime: bool

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

property HasFormulaErrorValue: bool

Determines if all cells in the range contain error value.

property HasFormulaNumberValue: bool

Indicates whether current range has formula number value.

property HasFormulaStringValue: bool
property HasMerged: bool

Indicates whether this range is part of merged range. Example:

#Create worksheet
workbook = Workbook()
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)
property HasNumber: bool

Determines if any one cell in the range contain number. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Assigning Value2 property of the Range
worksheet["A2"].Value2 = 45
#Checking Range types
isNumber =  worksheet["A2"].HasNumber
#Save to file
workbook.SaveToFile("HasNumber.xlsx")
property HasPictures: bool

Indicates whether the range is blank.

property HasRichText: bool

Determines if all cells in the range contain rich text string. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Create style
style = workbook.Styles.Add("CustomStyle")
#Set rich text
richText = worksheet["C2"].RichText
richText.Text = "Sample"
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")
property HasString: bool

Determines if all cells in the range contain string.

property HasStyle: bool

Determines if all cells in the range contain differs from default style. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Add style
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")
property HorizontalAlignment: HorizontalAlignType

Returns or sets the horizontal alignment for the specified object. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set text
worksheet["A1"].Text = "Test"
#Set alignment
worksheet["A1"].HorizontalAlignment = HorizontalAlignType.Right
#Save to file
workbook.SaveToFile("HorizontalAlignment.xlsx")
property HtmlString: str

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

Returns hyperlinks for this range.

property IgnoreErrorOptions: IgnoreErrorType

Represents ignore error options. If not single cell returs concatenateed flags.

property IndentLevel: int

Returns or sets the indent level for the cell or range. value should be 0 between 15. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set text
worksheet["C2"].Text = "Sample"
#Set indent level
worksheet["C2"].IndentLevel = 2
#Save to file
workbook.SaveToFile("IndentLevel.xlsx")
InsertOrUpdateCellImage

Adds CellImage from the specified file stream. this method only support WPS

Parameters:

  • stream – Represents image stream to set.

  • scale – scale if true scale for cell else clip the image.

Intersect(range: IXLSRange) → IXLSRange

Returns intersection of this range with the specified one.

Parameters:

range – The Range with which to intersect.

Returns:

Range intersection. If there is no intersection, NULL is returned.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get intersect range
range = worksheet.Range["A16:C16"]
commonRange = worksheet.Range["B16:D16"].Intersect(range)
#Save to file
workbook.SaveToFile("Intersect.xlsx")
property IsAllNumber: bool

Determines if all cells in the range contain number.

property IsBlank: bool

Indicates whether the range is blank.

property IsFormulaHidden: bool

Determines if the formula will be hidden when the worksheet is protected.

property IsGroupedByColumn: bool

Indicates whether this range is grouped by column.

property IsGroupedByRow: bool

Indicates whether this range is grouped by row.

property IsInitialized: bool

Indicates whether range has been initialized.

IsIntersect(range: IXLSRange) → bool
property IsStringsPreserved: bool

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

property IsWrapText: bool

Determines if Microsoft Excel wraps the text in the object. Example:

#Create worksheet
workbook = Workbook()
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")
property LastColumn: int

Gets or sets last column of the range.

property LastRow: int

Gets or sets last row of the range.

MeasureString(measureString: str) → SizeF

Measures size of the string.

Parameters:

measureString – String to measure.

Returns:

Size of the string.

Merge

Creates a merged cell from the specified Range object.

Parameters:

range – The Range to merge with.

Returns:

Merged ranges or null if wasn’t able to merge ranges.

property MergeArea: IXLSRange
property NumberFormat: str

Returns or sets the format code for the object. Example:

#Create worksheet
workbook = Workbook()
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")
property NumberText: str

Returns cell text for number format. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Gets cell value with its number format
XlsRange range= worksheet.Range[3, 1]
range.Value = "1/1/2015"
range.NumberFormat = "dd-MMM-yyyy"
numberText = range.NumberText
#Save to file
workbook.SaveToFile("NumberText.xlsx")
property NumberValue: float

Gets or sets number value of the range.

property Parent: SpireObject
PartialClear()

Partially clear range.

property RangeAddress: str

Returns the range reference in the language of the macro. Read-only String. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get RangeAddress
address = worksheet.Range[3, 4].RangeAddress
property RangeAddressLocal: str

Returns the range reference for the specified range in the language of the user. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get RangeAddressLocal
address = worksheet.Range[3, 4].RangeAddressLocal
property RangeGlobalAddress: str

Returns the range reference in the language of the macro. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get RangeAddress
address = worksheet.Range[3, 4].RangeGlobalAddress
property RangeGlobalAddress2007: str

Gets address global in the format required by Excel 2007.

property RangeGlobalAddressWithoutSheetName: str

Return global address without worksheet name.

property RangeR1C1Address: str

Returns the range reference using R1C1 notation. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get RangeR1C1Address
address = worksheet.Range[3, 4].RangeR1C1Address
property RangeR1C1AddressLocal: str

Returns the range reference using R1C1 notation. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get RangeR1C1AddressLocal
address = worksheet.Range[3, 4].RangeR1C1Address
RemoveCellImage()

Remove CellImage.

RemoveCombinedRange(cr: XlsRange)
RemoveMergeComment()
ReparseFormulaString()

Reparses formula.

Replace

Replaces cells’ values with new data.

Parameters:

  • oldValue – Value to search for.

  • newValues – The replacement value.

  • isVertical – Indicates whether to insert values vertically or horizontally.

property RichText: IRichTextString
property Row: int

Returns the number of the first row of the first area in the range.

property RowCount: int

Gets number of rows.

property RowGroupLevel: int

Row group level.

property RowHeight: float

Returns the height of all the rows in the range specified, measured in points. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set text
worksheet["A1"].Text = "Test"
#Set row height
worksheet["A1"].RowHeight = 30
#Save to file
workbook.SaveToFile("RowHeight.xlsx")
property Rows: ListXlsRanges
SetAutoFormat
SetDataValidation(dv: XlsValidation)
SetExtendedFormatIndex(index: int)
SetRowHeight(rowHeight: float, bIsBadFontHeight: bool)
SetSharedFormula(sharedFormula: str, rowNumber: int, columnNumber: int)
property Style: IStyle
property Text: str

Gets / sets text of range.

TextPartReplace(oldPartValue: str, newPartValue: str)

Replaces cell’s part text and reserve text’s format.

Parameters:

  • oldPartValue – Part value of cell’s text to search for.

  • newPartValue – The replacement value.

property TimeSpanValue: TimeSpan

Gets or sets timespan value of cell.

UnMerge()

Separates a merged area into individual cells. Example:

#Create worksheet
workbook = Workbook()
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")
UngroupByColumns() → XlsRange

Ungroups column.

UngroupByRows() → XlsRange

Ungroups row.

Union(range: XlsRange) → RangesCollection
UpdateRange(startRow: int, startColumn: int, endRow: int, endColumn: int)

Update region of range

Parameters:

  • startRow – first Row

  • startColumn – first Column

  • endRow – last Row

  • endColumn – last Column

property Value: str

Returns or sets the value of the specified range. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set value of the range
XlsRange range= worksheet.Range[3, 1]
range.Value = "1/1/2015"
#Save to file
workbook.SaveToFile("Value.xlsx")
property Value2: SpireObject

Returns or sets the cell value. It’s not use for current and datetime types. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Assigning Value2 property of the Range
worksheet["A1"].Value2 = DateTime.Now
worksheet["A3"].Value2 = false
#Checking Range types
print(worksheet["A1"].HasDateTime)
print(worksheet["A3"].HasBoolean)
property VerticalAlignment: VerticalAlignType

Returns or sets the vertical alignment of the specified object. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set text
worksheet["A1"].Text = "Test"
#Set alignment
worksheet["A1"].VerticalAlignment = VerticalAlignType.Top
#Save to file
workbook.SaveToFile("VerticalAlignment.xlsx")
property Worksheet: IWorksheet

Returns a worksheet object that represents the worksheet containing the specified range.

property WorksheetName: str

Returns name of the parent worksheet.

get_Item
set_Item(row: int, column: int, value: IXLSRange)