How to get list of sheets in an Excel Workbook
In this tutorial, we will show you getting the list of Sheets in an Excel Workbook by using Pandas.
For the examples, we will use the following Excel Workbook:
Workbook name: “Excel_pandas.xlsx”
It contains the following sheets:
Using read_excel method of Data Frame to get sheets list
The example below loads the workbook by read_excel method.
Then we used the data frame’s keys() method to get the list of sheets as follows:
import pandas as pd #Loading our sample workbook df_sheets = pd.read_excel("test_Excel.xlsx", None) #Using keys() method of data frame sheet_list = list(df_sheets.keys()) #Display all sheet names print (sheet_list)
['Product Information', 'Sales', 'Sale Chart', 'Employees', 'Sheet1', 'Sheet2']
How to get last sheet name example
The following program displays the last sheet name in the sample Excel Workbook. For that, we used Python len() function as follows:
import pandas as pd #Loading our sample workbook df_sheets = pd.read_excel("test_Excel.xlsx", None) #Using keys() method of data frame sheet_list = list(df_sheets.keys()) #Get the last sheet name in Workbook print("Last Sheet Name: ",sheet_list[len(sheet_list)-1])
Last Sheet Name: Sheet2
Using openpyxl library to get Sheet name in the Workbook
If you are using openpyxl for working with Excel files, it has also an easy way of getting the list of sheet names in the Workbook.
The following example loads the Excel Workbook by load_workbook and then uses its sheetnames method is used to get the list of sheets.
#import openpyxl from openpyxl import load_workbook #Getting list of sheets sheet_list = load_workbook("test_Excel.xlsx", read_only=True).sheetnames #Display sheet list print("Following are sheet names in the given Workbook:") for sht in sheet_list: print (sht)
Third way: using Pandas Excel_File to get sheet names
You may also get the sheet names by using the Pandas ExcelFile object.
Use the sheet_names method and you are done:
#import pandas library import pandas as pd #getting sheet names sheet_list = pd.ExcelFile("test_Excel.xlsx").sheet_names #Dislay sheet list by for loop for sht in sheet_list: print (sht)
Product Information Sales Sale Chart Employees Sheet1 Sheet2