How to Access Excel Sheets by Name in Openpyxl

In this tutorial, we will look at how to get a sheet by name and then perform some operations like adding a row, coloring data, setting interior/background color to that sheet – rather than active sheet.

How to access Excel sheets by name using openpyxl

Generally, an Excel Workbook has two or more sheets. By default, openpyxl works in the active worksheet.

In this tutorial, we will look at how to get a sheet by name and then perform some operations like adding a row, coloring data, and setting interior/background color to that sheet – rather than an active sheet.

Accessing a non-active sheet by name using openpyxl

For the example, we are using this sample Workbook:

sheet-name-openpyxl-s

You can see, it has six sheets, and “Product Information” is the active sheet.

Our task is to work with the “Employees” sheet.

The example of accessing and displaying Employees sheet

First, let us load a non-active sheet “Employees” in our sample Workbook.

In the program

  • We will load the Workbook
  • Specify Employees sheet
  • Display its contents by using “values”

Code:

Result:

Adding a new row to the non-active Employees sheet

We will add a new row to our Employees sheet. As we have seen above, it has two columns:

  • Employee Name
  • Salary

The program adds a new row to the Employees sheet:

Output:

openpy-sheet-name-add

You can see, we accessed the sheet by its name (which is non-active sheet in the Workbook). In the above program, we added and saved the record in that sheet as well.

Changing the font color of the salary column example

Now we will perform some formatting on a sheet that is not active and we will access it by name.

We will change the font column color of the Salary column.

Code:

Output:

sheet-name-format

You can see, the font color of the price column from the second row is changed to green.

You may learn more about Coloring Text/Font by openpyxl in its tutorial.