How to Add Multiple Sheets in Excel Using Python

This tutorial is designed to help Python users who need to manipulate Excel files. More specifically, it focuses on how to add multiple sheets to an Excel file using the pandas and openpyxl libraries in Python. Let’s get started!

Step 1: Install Required Python Libraries

The first step is to install the necessary Python libraries. This process can be completed with pip, Python’s package installer. This tutorial uses two libraries: Pandas and openpyxl. Pandas is a powerful data manipulation library, and openpyxl is a library for reading and writing Excel files.

1 2	pip install pandas pip install openpyxl

Step 2: Import the Libraries

After the libraries are installed successfully, the next step is to import them into your Python script.

1 2	import pandas as pd from openpyxl import Workbook

Step 3: Create DataFrame

Create the dataset required to write into the Excel file. This is formed using the pandas DataFrame.

data1 = [['Tom', 10], ['Nick', 15], ['John', 20]]

data2 = [['Adam', 30], ['Smith', 45], ['Ryan', 50]]

df1 = pd.DataFrame(data1, columns = ['Name', 'Age'])

df2 = pd.DataFrame(data2, columns = ['Name', 'Age'])

Step 4: Write DataFrame to Excel

Next, write the DataFrame into an Excel file. If you want to write the DataFrame into multiple sheets, you can do this in pandas using the ExcelWriter function.

with pd.ExcelWriter('output.xlsx') as writer:

df1.to_excel(writer, sheet_name='Sheet 1')

df2.to_excel(writer, sheet_name='Sheet 2')

Step 5: Run Your Code

Finally, run your Python code and check the Excel file. You will find that the data frames have been written into two different sheets named ‘Sheet 1’ and ‘Sheet 2’.

Full code

Here is the complete Python code as one coherent script:

import pandas as pd

from openpyxl import Workbook