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