3 Ways to Get Excel Sheets List using Pandas/openpyxl

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)

Output:

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

Result:

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.

Code:

#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)

Output:

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)

Result:

Product Information

Sales

Sale Chart

Employees

Sheet1

Sheet2