openpyxl Set Active Sheet

Question

http://openpyxl.readthedocs.io/en/default/_modules/openpyxl/workbook/workbook.html?highlight=set%20active%20sheet

The documentation shows a Workbook object has an active property

@property
def active(self):
    """Get the currently active sheet"""
    return self._sheets[self._active_sheet_index]

@active.setter
def active(self, value):
    """Set the active sheet"""
    self._active_sheet_index = value

If wb = openpyxl.Workbook() calling wb.active give the title of the default first worksheet which is Sheet. Say I create another sheet ws1 = wb.create_sheet('another sheet'), how to I "set" this to be the active sheet?

The documentation shows there is an active "setter" also called active. It takes an extra argument, an integer index value.

How come wb.active(1) does not work? Am I not calling the function with

Answer 1

Update: openpyxl was updated to allow active sheet to be set directly:

wb.active = wb['sheet_name']

Use the following in Demonstration:

# Set Active Sheet
wb.active = wb['charlie']

---

Original outdated answer with demonstration changed to save workbook allowing any chosen process to be verified:

This is a different approach of setting active sheet in workbook by sheetname. There are similar answers (openpyxl get sheet by name for example involves opening a closed file) and others did not have enough detail for me to understand this functionality.

The Manipulating a workbook in memory tutorial is the place to start and under the answer I have used this tutorial to demonstrate there is no active sheet name, it is actually the sheet at the active index. A different sheet will become active if adding or deleting a sheet changes the sheet at the index position.

Initially I thought .create_sheet made the sheet active but then I realised I had only created the sheet at the active sheet index which happened to be 0. The index can be set to a value greater than the number of sheets and the docs also contain a note that "If the sheet set to active is hidden return the next visible sheet or None".

verbose short answer

for i, s in enumerate(wb.sheetnames):
    if s == 'charlie':
        break
wb.active = i

Feel free to improve this answer.

Demonstration

C:\Users\User\Documents>py
Python 3.10.2 (tags/v3.10.2:a58ebcc, Jan 17 2022, 14:12:15) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> # http://openpyxl.readthedocs.io/en/2.5/tutorial.html#create-a-workbook
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> print(wb.sheetnames)
['Sheet']
>>>
>>> print(wb.active)
<Worksheet "Sheet">
>>> ws = wb.active
>>> ws.title = "alpha"
>>>
>>> ws = wb.create_sheet('bravo')
>>> print(wb.sheetnames)
['alpha', 'bravo']
>>> print(wb.active)
<Worksheet "alpha">
>>>
>>> ws = wb.create_sheet('charlie',0)  # insert at index 0
>>> print(wb.sheetnames)
['charlie', 'alpha', 'bravo']
>>> print(wb.active)
<Worksheet "charlie">
>>>
>>>
>>> wb.active = 1
>>> print(wb.active)
<Worksheet "alpha">
>>>
>>> wb.active = 2
>>> print(wb.active)
<Worksheet "bravo">
>>>
>>> wb.active = 0
>>> print(wb.active)
<Worksheet "charlie">
>>>
>>> wb.active = 3
>>> print(wb.active)
None
>>>
>>> ws = wb.create_sheet(index=0)  # insert at index
>>> print(wb.active)
<Worksheet "bravo">
>>> print(wb.sheetnames)
['Sheet', 'charlie', 'alpha', 'bravo']
>>>
>>>
>>> ws_active = wb.get_sheet_by_name('charlie')
<stdin>:1: DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
>>> ws_active = wb['charlie']
>>> print(wb.active)
<Worksheet "bravo">
>>> ws4 = wb["charlie"] # from https://stackoverflow.com/a/36814135/4539999
>>> print(wb.active)
<Worksheet "bravo">
>>>
>>> # Set Active Sheet
>>> for i, s in enumerate(wb.sheetnames):
...     if s == 'charlie':
...         break
...
>>> wb.active = i
>>>
>>> # Confirm Active Sheet
>>> print(wb.active)
<Worksheet "charlie">
>>>
>>> # Open workbook to verify
>>> wb.save("Demo.xlsx")
>>>

Answer 2

I'm unfamiliar with the whole getters and setters thing. However, I figured out the answer. It's horribly confusing to me right now why it works this way - because it doesn't look like a typical function call. In any case, to use the "setter" you would write

wb.active = 1

which uh "eats" the 1 =) and changes the active sheet. Just writing this up thinking at least one other person may ask something similar.

Answer 3

sheets = wb.get_sheet_names()
for i in sheets:
    if i == 'xyz':
        wb.active = i
        break

or you can simply do

xyz_sheet = wb.get_sheet_by_name('xyz')
wb.active = xyz_sheet

the workbook (i.e wb) has the xyz sheet active now

Answer 4

For reference, another useful way to do it - particularly when you want to call the sheet by its name:

wb = openpyxl.load_workbook(path/to/your/spreadsheet)
worksheet_names = wb.sheetnames # this variable is presented as a list, so we can manipulate it accordingly, but finding the index number using the name.
sheet_index = worksheet_names.index(<name of your target sheet>) # this will return the index number where your worksheet resides.  It should be mentioned that this sheet name must exist in the worksheet_names list variable
wb.active = sheet_index # this will activate the worksheet by index

you should now be working in the sheet of your choice, but the benefit here is that you have been able to call the sheet by name rather than by number. This point is the reason that I have provided this additional answer.

Answer 5

If you know the name of tab, you can write one line:

book.active = book.sheetnames.index('YourSheetName')

Answer 6

This is the simplest way to set an active worksheet by name:

active_sheet = wb['sheet name']