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:

Let’s start by loading the workbook:

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

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

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']

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

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">

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

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']

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.

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.