Worksheets

Active Sheet

When you create a new Cell or CellRange without specifying a sheet argument, it will be placed on the active sheet. The default active sheet is the one that is visible in Excel when you run a script or open a Python shell.

The active_sheet function interacts with the active sheet. Calling active_sheet without an argument will return the active sheet, and calling it with a sheet name will set the active sheet.

e.g: If Sheet1 is active, active_sheet() will return “Sheet1”. active_sheet(“Sheet2”) will set the active sheet to Sheet2.

Display Sheet

The display sheet is the visible sheet in Excel.

The display_sheet function interacts with the display sheet. Calling display_sheet without an argument will return the display sheet, and calling it with a sheet name will set the display sheet.

e.g: If Sheet1 is displayed, display_sheet() will return “Sheet1”. display_sheet(“Sheet2”) will display Sheet2.

Clear/Delete row/column

The functions clear_row, clear_col, del_row, and del_col will clear or delete a row or column. Rows are specified by number, and columns can be specified by number or letter. You can also pass a cell, which will result in the row or column of that cell being cleared or deleted, or a CellRange, list, or tuple of rows or columns, which will clear or delete all the rows/columns in the CellRange or the list or tuple. You can pass a sheet name as the second argument to specify which a sheet; otherwise the function will interact with the active sheet.

e.g: clear_row(1) and clear_row(Cell(“A1”)) will clear the first row. del_col(3), del_col(“C”), and del_col(Cell(3,3)) will delete the third column. clear_col([1, 2, 3], “Sheet2”) and clear_col(CellRange(“A1:C1”), “Sheet2”) will clear the first three columns on Sheet2.

Note: calling del_row([1, 2]) is not the same as calling del_row(1) and then del_row(2). del_row([1,2]) will delete the first two rows, while del_row(1) will delete the first row, which will shift all rows in the spreadsheet. del_row(2) will then delete the new second row; this was originally the third row, so this is equivalent to calling del_row([1,3]).

Hiding rows or columns

hide_row(row[, hidden, sheet])

hides and unhides rows. hidden is True by default.

>>> hide_row(3)
## hides row 3 on the active sheet
>>> hide_row([4, 5])
## hides rows 4 and 5 on the active sheet
>>> hide_row([3, 4, 5], False, "Sheet2")
## unhides rows 3, 4, and 5 on Sheet2
hide_col(col[, hidden, sheet])

hides and unhides columns. hidden is True by default.

>>> hide_col(2)
## hides column 2 on the active sheet
>>> hide_col([3, "D", "E"], False, "Sheet2")
## unhides rows 3, 4, and 5 on Sheet2
row_is_hidden(row[, sheet])

Returns True if row on sheet is hidden.

>>> row_is_hidden(3)
True ## row 3 on the active sheet is hidden
col_is_hidden(col[, sheet])

Returns True if col on sheet is hidden.

>>> col_is_hidden("A", "Sheet2")
False ## column A on Sheet2 is visible

Insert row/column

insert_row and insert_col insert rows and columns, respectively. They take the same input as clear_row and clear_col, and will insert a row above the row passed (or a column before the column passed).

e.g: insert_row(1) and insert_row(Cell(“A1”)) will insert a new first row. insert_col(3), insert_col(“C”), and insert_col(Cell(3,3)) will insert a new third column. insert_col([1, 2, 3], “Sheet2”) will insert columns before columns ‘A’, ‘B’ and ‘C’. (The result is new_col, A, new_col, B, new_col, C...)

Sheet functions

Autofit

The autofit command resizes sheet cells. It will resize the active sheet unless passed a sheet name. You can pass a range as the second argument, to resize the columns in that range.

e.g: autofit() will resize the current sheet. autofit(‘sheet2’) will resize Sheet2. autofit(‘sheet1’, ‘A1:E5’) will resize columns A through E to match the widest cells in rows 1 through 5 on Sheet1.

Clear Sheet

clear_sheet clears the named sheet. If called with no argument, clears the active sheet.

e.g: clear_sheet(“Sheet2”) will clear sheet 2.

All Sheets

To return a list of all sheets in the workbook, use the all_sheets function.

e.g: all_sheets() returns [‘Sheet1’, ‘Sheet2’, ‘Sheet3’] on a default workbook.

all_sheets([hidden = False]) returns a list of all the sheets in your workbook. Hidden sheets are included only if “hidden” is set to True

Here’s an example with Sheet1, Sheet2 and Sheet 3 visible, and Mystery Sheet hidden:

>>> all_sheets()
['Sheet1', 'Sheet2', 'Sheet3']
>>> all_sheets(True)
['Sheet1', 'Sheet2', 'Sheet3', 'Mystery Sheet']
>>> all_sheets(False)
['Sheet1', 'Sheet2', 'Sheet3']

New Sheet

new_sheet creates a new sheet. The sheet can’t have the same name as an existing sheet. If called with no arguments, will create the next numbered sheet.

e.g: new_sheet(“Data”) will create a sheet named “Data”. new_sheet() will create “Sheet5” in a new workbook.

Remove Sheet

remove_sheet removes a sheet. If there’s only one visible sheet in the workbook, you can’t remove it.

Hide Sheet

hide_sheet(sheet, [hidden = True]) lets you hide a sheet. If you set hidden to False, it’ll unhide the sheet instead.

You can check if a sheet is hidden with the is_hidden command.

>>> is_hidden("Sheet1")
False
>>> hide_sheet("Sheet1")
>>> is_hidden("Sheet1")
True
>>> hide_sheet("Sheet1", False)
>>> is_hidden("Sheet1")
False
>>> hide_sheet("Sheet1", True)
>>> is_hidden("Sheet1")
True

Recalculate Sheet

recalc_sheet([sheet])

recalculates sheet. Uses the active sheet by default.

>>> recalc_sheet("Sheet1")
## recalculates Sheet1
>>> recalc_sheet()
## recalculates the active sheet

Rename Sheet

rename_sheet renames a sheet. The new name can’t match the name of any other sheet.

e.g: rename_sheet(“Sheet1”,”Sheet_over_9000”) will rename Sheet1 to Sheet_over_9000.

All Cells

all_cells([sheet])

returns a CellRange containing all cells on sheet. This might miss hidden cells.

>>> all_cells()
## returns CellRange("A1:J10"), if 10 is the last non-empty row and
## J is the last non-empty column

Merging Cells

merge_range(cellrange)

merges the cells in cellrange. cellrange must be a rectangular box.

>>> merge_cells(CellRange("A1:G10"))
## merges A1 through G10 on the active sheet
>>> merge_cells(CellRange("Sheet1", "A1:G10"))
## merges A1 through G10 on Sheet1
unmerge_range(cell[, copy_values = False])

unmerges the range containing cell.

If copy_values is True, every cell in the range will have the same value as the original merged range. If it’s False, only the top-left cell with.

>>> merge_cells(CellRange("Sheet1", "A1:G10"))
>>> unmerge_range(Cell("Sheet1", "A1"))
## unmerges A1 through G10
>>> merge_cells(CellRange("Sheet1", "A1:G10"))
>>> unmerge_cells(Cell("Sheet1", "E5"), True)
## unmerges A1 through G10. each cell now has
## the same value as the original merged cell.
get_merged_range(cell)

returns the merged range containing cell.

>>> merge_cells(CellRange("A1:G10"))
>>> get_merged_range(Cell("E5"))
A1:G10 ## this is a CellRange object

Last Cell

last_cell_in_col(col, [sheet]) returns the last cell in col. col can be a number or a letter. last_cell_in_row(row, [sheet]) returns the last cell in a row. last_cell([sheet]) returns a cell that’s guaranteed to be to below and to the right of every other nonempty cell on sheet. The active sheet is used if no sheet is passed.

>>> CellRange("A100, B100, A101").value = [1, 2, 3]
>>> last_cell_in_col("A")
A101
>>> last_cell_in_row(100)
B100
>>> last_cell()
B101