Worksheet module

class Worksheet.Worksheet

Bases: XlsWorksheet

property AllocatedRange: CellRange

Returns a Range object that represents the used range on the specified worksheet. Read-only.

ApplyStyle(style: CellStyle)

Apply style to whole sheet.

Parameters:

style – style to apply

property AutoFilters: AutoFiltersCollection

CalculateAndGetRowHeight(rowIndex: int) → float

property Cells: ListCellRanges

Returns all used cells in the worksheet. Read-only.

property Charts: WorksheetChartsCollection

Returns charts collection. Read-only.

property Columns: ListCellRanges

Rrepresents all used columns on the specified worksheet. Read-only Range object.

property Comments: CommentsCollection

Returns comments collection for this worksheet. Read-only. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Adding comments to a cell.
comment1 = worksheet.Range["A1"].AddComment()
comment2 = worksheet.Range["B1"].AddComment()
#Set comment text
comment1.Text = "Comment1"
comment2.Text = "Comment2"
#Check count
Console.Write(worksheet.Comments.Count)
#Save to file
workbook.SaveToFile("Comments.xlsx")

Copy

Copys data from a source range to a destination range.

Parameters:

• sourceRange – Source range.

• destRange – Destination range.

CopyColumn(sourceColumn: CellRange, destSheet: Worksheet, destColIndex: int, copyOptions: CopyRangeOptions)

Copys data from a source column to a destination column.

Parameters:

• sourceColumn – Source column.

• destSheet – Destination sheet

• destColIndex – Destination column index

• copyOptions – Copy options.

CopyFrom(worksheet: Worksheet)

Copy data from specified worksheet.

Parameters:

worksheet – worksheet object

CopyRow(sourceRow: CellRange, destSheet: Worksheet, destRowIndex: int, copyOptions: CopyRangeOptions)

Copys data from a source row to a destination row.

Parameters:

• sourceRow – Source row.

• destSheet – Destination sheet

• destRowIndex – Destination row index

• copyOptions – Copy options.

FindAll(findValue: str, flags: FindType, findOptions: ExcelFindOptions) → ListCellRanges

Finds the all cells with specified string value.

Parameters:

• findValue – Value to search.

• flags – Type of value to search.

• findOptions – Way to search.

Returns:

All found cells, or Null if value was not found.

FindAllBool(boolValue: bool) → ListCellRanges

Finds the cell with the input bool.

Parameters:

boolValue – Bool value to search for.

Returns:

Found ranges.

FindAllDateTime(dateTimeValue: DateTime) → ListCellRanges

Finds the cell with the input date time.

Parameters:

dateTimeValue – Datetime value to search for.

Returns:

Found ranges.

FindAllNumber(doubleValue: float, formulaValue: bool) → ListCellRanges

Finds the cell with the input number.

Parameters:

• doubleValue – Double value to search for.

• formulaValue – Indicates if includes formula value.

Returns:

Found ranges.

FindAllString

Finds the cell with the input string.

Parameters:

• stringValue – String value to search for.

• formula – Indicates if includes formula.

• formulaValue – Indicates if includes formula value.

Returns:

Found ranges.

FindAllTimeSpan(timeSpanValue: TimeSpan) → ListCellRanges

Finds the cell with the input time span.

Parameters:

timeSpanValue – Time span value to search for.

Returns:

Found ranges.

FindBool(boolValue: bool) → CellRange

Finds the cell with the input bool.

Parameters:

boolValue – Bool value to search for.

Returns:

Found range.

FindDateTime(dateTimeValue: DateTime) → CellRange

Finds the cell with the input date time.

Parameters:

dateTimeValue – DateTime value to search for.

Returns:

Found range.

FindNumber(doubleValue: float, formulaValue: bool) → CellRange

Finds the cell with the input double.

Parameters:

• doubleValue – Double value to search for.

• formulaValue – Indicates if includes formula value.

Returns:

Found range.

FindString(stringValue: str, formula: bool, formulaValue: bool) → CellRange

Finds the cell with the input string.

Parameters:

• stringValue – String value to search for.

• formula – Indicates whether includes formula.

• formulaValue – Indicates whether includes formula value.

Returns:

Found range.

FindTimeSpan(timeSpanValue: TimeSpan) → CellRange

Finds the cell with the input time span.

Parameters:

timeSpanValue – Time span value to search for.

Returns:

Found range.

FreezePanes(rowIndex: int, columnIndex: int)

Freezes panes at the specified cell in the worksheet.

Parameters:

• rowIndex – Row index.

• columnIndex – Column index.

GetDefaultColumnStyle(columnIndex: int) → CellStyle

Returns default column style.

Parameters:

columnIndex – Column index.

Returns:

Default column style or null if default style is not exists.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Create style
style = workbook.Styles.Add("CustomStyle")
#Set Color
style.Color = Color.Red
#Set default style
worksheet.SetDefaultColumnStyle(2,style)
#Get default style
defaultStyle = worksheet.GetDefaultColumnStyle(2)
#Set color
defaultStyle.Color = Color.Blue
worksheet.SetDefaultColumnStyle(3, defaultStyle)
#Save to file
workbook.SaveToFile("GetDefaultColumnStyle.xlsx")

GetDefaultRowStyle(rowIndex: int) → CellStyle

Returns default row style.

Parameters:

rowIndex – Row index.

Returns:

Default row style or null if default style is not set.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Create style
style = workbook.Styles.Add("CustomStyle")
#Set Color
style.Color = Color.Red
#Set default style
worksheet.SetDefaultRowStyle(2,style)
#Get default style
defaultStyle = worksheet.GetDefaultRowStyle(2)
#Set color
defaultStyle.Color = Color.Blue
worksheet.SetDefaultRowStyle(3, defaultStyle)
#Save to file
workbook.SaveToFile("GetDefaultColumnStyle.xlsx")

GetFreezePanes() → List[int]

GetIntersectRanges(range1: CellRange, range2: CellRange) → CellRange

Intersects two ranges.

Parameters:

• range1 – First range.

• range2 – Second range.

Returns:

Intersection of two ranges

property HPageBreaks: HPageBreaksCollection

Returns an HPageBreaks collection that represents the horizontal page breaks on the sheet.

property HyperLinks: HyperLinksCollection

Collection of all worksheet’s hyperlinks.

Merge(range1: CellRange, range2: CellRange) → CellRange

Combines a range of cells into a single cell.

Parameters:

• range1 – First range.

• range2 – Second range.

Returns:

Merged ranges

property MergedCells: ListCellRanges

Returns all merged ranges. Read-only. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Merge cells
worksheet["C2:D2"].Merge()
worksheet["F3:G3"].Merge()
#Get merged ranges
IXLSRange[] mergedRanges = worksheet.MergedCells
#Get merged range count . Output will be 2
Console.Write(mergedRanges.Length)
#Save to file
workbook.SaveToFile("MergedCells.xlsx")

Move

Move data from source range to destination range.

Parameters:

• sourceRange – Source range.

• destRange – Destination range.

property PageSetup: PageSetup

Returns a PageSetup object that contains all the page setup settings for the specified object. Read-only.

property ParentWorkbook: Workbook

property Pictures: PicturesCollection

Pictures collection. Read-only.

property PrintRange: CellRange

Print area of worksheet.

property QueryTables: QueryTableCollection

property Range: CellRange

Returns a Range object that represents the used range on the specified worksheet. Read-only. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set text
worksheet["B2"].Text = "Text"
#Set Color
worksheet["J3"].Style.Color = Color.Red
#Get used range . Output will be B2:J3
Console.Write(worksheet.Range.RangeAddressLocal)
#Save to file
workbook.SaveToFile("UsedRange.xlsx")

RemoveMergedCells(range: CellRange)

RemovePicture

Remove picture from this worksheet.

Parameters:

index – Picture ID

RemoveRange

Removes range from list.

Parameters:

range – Specified range.

property Rows: ListCellRanges

Represents all the rows on the specified worksheet. Read-only Range object.

SetActiveCell(range: CellRange)

Sets active cell

Parameters:

range – Cell to activate.

SetDefaultColumnStyle

Sets default style for column.

Parameters:

• columnIndex – Column index.

• defaultStyle – Default style.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Create style
style = workbook.Styles.Add("CustomStyle")
#Set Color
style.Color = Color.Red
#Set default style
worksheet.SetDefaultColumnStyle(2, style)
#Save to file
workbook.SaveToFile("SetDefaultColumnStyle.xlsx")

SetDefaultRowStyle

Sets default style for row.

Parameters:

• rowIndex – Row index.

• defaultStyle – Default style.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Create style
style = workbook.Styles.Add("CustomStyle")
#Set Color
style.Color = Color.Red
#Set default style
worksheet.SetDefaultRowStyle(2, style)
#Save to file
workbook.SaveToFile("SetDefaultRowStyle.xlsx")

property VPageBreaks: VPageBreaksCollection

Returns a VPageBreaks collection that represents the vertical page breaks on the sheet. Read-only.

property Workbook: Workbook

get_Item

Get cell range.

Parameters:

• row –

• column –

• lastRow –

• lastColumn –