Load One or Multiple Sheets of Excel by Pandas sheet_name

How to load multiple sheets in Excel using Python Pandas library

In this tutorial, we will show you how to use read_excel method of Pandas library to load:

• One excel sheet
• Two Excel sheets
• All excel sheets

From the specified Workbook.

Loading one excel worksheet example

By default, Pandas read_excel loads the first worksheet (active or not) from the specified Excel file.

For our examples, we have the following worksheets in our “test_Excel.xlsx” workbook:

Python program below loads the default sheet (one sheet) only by using read_excel method.

Code:

import pandas as pan_excel

#Loading Single/Default sheet

df_single = pan_excel.read_excel('test_Excel.xlsx')

#Display Sheet's contents

print(df_single)

Output:

Loading two Sheets by sheet_name parameter example

As we know, read_excel method has many parameters and for the purpose of loading two sheets, our solution is using sheet_name.

By using sheet_name parameter, you may specify one or more sheets in the Workbook.

You may provide the sheet names as follows:

• sheet_name=[0,1,2...]
• sheet_name=['0,'Product Information']
• sheet_name=['Sales','Product Information']

So, the index number of the sheet starting from 0 can be given. Also, you may also use sheet names or mix of both as well.

We will use the third way of loading two worksheets and display their data in the example below.

Python program:

import pandas as pan_excel


#Loading Two/Multiple sheets by names

df_multiple = pan_excel.read_excel('test_Excel.xlsx',sheet_name=['Sales','Product Information'])

#Display Sheets contents

print(df_multiple)

Output:

An important note: If sheet contains charts then it is not counted in the indexed-based position. We will show you in the example later.

Using sheet number and name example

Now we used sheet number (index starting at zero) and name in the example below to load two sheets from our workbook and then display data:

import pandas as pan_excel

#Loading Two/Multiple sheets by index and name

df_multiple = pan_excel.read_excel('test_Excel.xlsx',sheet_name=[2, 'Sales'])




#Display Sheets contents

print(df_multiple)

Output:

The index position 2 represents the “Employee” sheet in our sample Workbook.

Loading all sheets example

For loading all sheets from the Workbook, just specify sheet_name = None:

sheet_name=None

and you are done.

The program below loads all three sheets from our sample Workbook:

import pandas as pan_excel

#Loading All sheets without column index

df_all = pan_excel.read_excel('test_Excel.xlsx',sheet_name=None, index_col=0)


#Display All Sheets data

print(df_all)

Output:

In our sample sheets, you can see we have three sheets and all are loaded in the same sequence.

As we also specified the index_col parameter, so this time row number (or column index) is not displayed.

What if we have a sheet with charts?

For this example, we are adding another sheet in between the above sample sheets. It contains a chart for the demo only:

The date for the chart comes from “Sales” sheet.

As we fetch a program to get all sheets by Pandas read_excel, this is what we get the output:

import pandas as pan_excel

#Loading All sheets including a chart sheet

df_all_chart = pan_excel.read_excel('test_Excel.xlsx',sheet_name=None, index_col=0)

#Display All Sheets data - what about chart sheet?

print(df_all_chart)

Output: