How To Create Multiple Sheets In Excel Using Python

In this tutorial, you will learn how to write multiple sheets in an Excel file using Python. We can achieve this task by leveraging the powerful pandas and openpyxl libraries. Pandas provide a straightforward and easy way to work with data in various formats, and openpyxl allows us to interact with Excel files in a simple manner.

Step 1: Import Required Libraries

First, you need to import the necessary libraries, which are pandas and openpyxl, into your Python script.

1
2
import pandas as pd
from openpyxl import Workbook

Step 2: Create Sample DataFrames

For this tutorial, let’s create two sample DataFrames with some data that we will write into separate Excel sheets.

1
2
3
4
5
6
7
8
9
10
11
# Sample data for Sheet 1
data1 = {'Name': ['John', 'Sam', 'Alex'],
         'Age': [25, 30, 22]}
 
df1 = pd.DataFrame(data1)
 
# Sample data for Sheet 2
data2 = {'Product': ['Laptop', 'Monitor', 'Keyboard'],
         'Price': [1200, 200, 60]}
 
df2 = pd.DataFrame(data2)

Step 3: Create an Excel Writer Object

We need an Excel writer object to write our DataFrames to an Excel file. The writer object must be created with the desired Excel file name and the engine parameter set to ‘openpyxl’.

1
2
# Create Excel writer object
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')

Step 4: Write DataFrames to Excel Sheets

Now, we will write our DataFrames into separate sheets of the same Excel file. You can do this by calling the to_excel() method on the DataFrame and passing the writer object, sheet name, and index parameter.

1
2
3
# Write DataFrames to Excel sheets
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)

Step 5: Save the Excel File

Finally, you need to save the Excel file by calling the save() method on the writer object.

1
2
# Save the Excel File
writer.save()

After running your script, you should see an Excel file named ‘output.xlsx’ generated in the same directory as your script.

Full Code

Here’s the complete code for the tutorial.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pandas as pd
from openpyxl import Workbook
 
# Sample data for Sheet 1
data1 = {'Name': ['John', 'Sam', 'Alex'],
         'Age': [25, 30, 22]}
df1 = pd.DataFrame(data1)
 
# Sample data for Sheet 2
data2 = {'Product': ['Laptop', 'Monitor', 'Keyboard'],
         'Price': [1200, 200, 60]}
df2 = pd.DataFrame(data2)
 
# Create Excel writer object
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
 
# Write DataFrames to Excel sheets
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
 
# Save the Excel File
writer.save()

Output

Conclusion

In this tutorial, we learned how to write multiple sheets in an Excel file using Python. We leveraged the popular pandas library to create and manipulate the data, and the openpyxl library to write the data to an Excel file. This technique can be extremely useful when you need to store and manage different sets of data in a single file.