openpyxl Part 9 – Managing Sheets

In the previous part of the openpyxl series we were inserting and deleting rows and columns. We didn’t save the changes and even if we had, there wouldn’t have been any because we deleted all the rows and columns that we had inserted before. So far, we’ve been working on just one sheet. But as you know, there may be more sheets in a spreadsheet. In this part we’ll see how to manage sheets.

Anyway, the workbook now contains one sheet with the following data:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	911500.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474	40

Let’s start by loading the workbook:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")

Sheet Names

You can easily check the names of the sheets. In our case there’s just one:

>>> workbook.sheetnames
['Sheet']

Now suppose you want to change the name of the sheet. You can do it like so. First you have to select the active sheet:

>>> sheet = workbook.active

Next you can change its title:

>>> sheet.title = 'General Info'

Now the new title should appear:

>>> workbook.sheetnames
['General Info']

Adding Sheets

Let’s add two more sheets to our workbook, ‘Employees’ and ‘Products’, for example. We can do it using the create_sheet method:

>>> employees_sheet = workbook.create_sheet('Employees')
>>> products_sheet = workbook.create_sheet('Products')

Now we should see all three sheets:

>>> workbook.sheetnames
['General Info', 'Employees', 'Products']

If you want to insert a sheet at a specific position, you can pass its index to the create_sheet method. Suppose we want to add the ‘Partners’ sheet before the ‘Employees’ sheet. The indices are 0-based, so the index we should use is 1:

>>> partners_sheet = workbook.create_sheet('Partners', 1)
>>> workbook.sheetnames
['General Info', 'Partners', 'Employees', 'Products']

Selecting Sheets

Once you’ve added some sheets to your workbook, you may want to be able to select a specific sheet. It’s very easy, you just have to use its name. You can do it in a couple ways. In our case we assigned the newly created sheet to a variable, so we can access it using that variable:

>>> employees_sheet
<Worksheet "Employees">

We can also assign the sheet to another variable:

>>> sheet = employees_sheet
>>> sheet
<Worksheet "Employees">

If we want to use an existing sheet, we can select it using dictionary notation:

>>> sheet = workbook['Employees']
>>> sheet
<Worksheet "Employees">

Removing Sheets

If you don’t need a sheet anymore, you can remove it using the remove method. Let’s remove the ‘Partners’ sheet, for example:

>>> workbook.remove(partners_sheet)
>>> workbook.sheetnames
['General Info', 'Employees', 'Products']

You can also remove a sheet by its title:

>>> workbook.remove(workbook['Employees'])
>>> workbook.sheetnames
['General Info', 'Products']

Copying Sheets

The last operation I’d like to mention is copying a sheet. You can do it using the copy_worksheet method. It will create a copy of the worksheet and add the word ‘Copy’ to the name of the original one:

>>> workbook.copy_worksheet(products_sheet)
<Worksheet "Products Copy">
>>> workbook.sheetnames
['General Info', 'Products', 'Products Copy']

You can also use the dictionary notation with the sheet’s title:

>>> workbook.copy_worksheet(workbook['General Info'])
<Worksheet "General Info Copy">
>>> workbook.sheetnames
['General Info', 'Products', 'Products Copy', 'General Info Copy']

Now, after all these modifications, we have a workbook with four sheets, but we don’t need them, so I’m not going to save the workbook. This means that when I load the workbook next time, all the changes will be lost. Always remember to save the workbook if you want the changes to be permanent, but it’s not the case with our workbook here.