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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 |
[('Employee Name', 'Salary'), ('Mike', 5000), ('Michelle', 4500), ('Ben', 6000), ('Tina', 4500), ('Shabee', 3000), ('Mina', 4000)] |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# 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, 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
# 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.
You may learn more about Coloring Text/Font by openpyxl in its tutorial.
