How to Access Excel Sheets by Name in Openpyxl

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:

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.

Code:

from openpyxl import load_workbook

wb_access = load_workbook('test_Excel.xlsx')

#Accessing sheet by name

sheet = wb_access["Employees"]

#Display sheet's contents

print(list(sheet.values))

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:

# Append data to sheet by name (non-active)

from openpyxl import load_workbook

#Specify the Workbook

wb_add = load_workbook("openpyxl_ex.xlsx")


sheet = wb_add["Employees"]

#Row's data

sheet["A8"] = "Sohaib"
sheet["B8"] = "$5500.00"

#Save data in the file

wb_add.save('openpyxl_ex.xlsx')

Output:

You can see that 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:

# Modify Font of non-active sheet's column

from openpyxl import load_workbook

from openpyxl.styles import Font


#Loading the Workbook

wb_font = load_workbook('openpyxl_ex.xlsx')


#Sepcify sheet namae

sheet = wb_font["Employees"]

#Using iter_cols to change column color

for rows in sheet.iter_cols(min_col=2, max_col=2, min_row=2, max_row=None):

   for cell in rows:

     cell.font = Font(color="008040")




#Save workbook    

wb_font.save("openpyxl_ex.xlsx")

Output:

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