Save multiple sheets to one Excel file in Python

Sharing is caring!

Last Updated on July 14, 2022 by Jay

This short article is part of the Transition from Excel to Python series. Last time we learned how to save one sheet into an Excel file. Now we’ll save multiple sheets to one (the same) Excel file using Python pandas.

Just a reminder: df stands for dataframe, and pd is short for pandas.

We’ll still use the df.to_excel() method, but we’ll need help from another class pd.ExcelWriter(). As its name suggests, this class writes to Excel files. If you read carefully the pd.to_excel() documentation, the ExcelWriter is actually the first argument.

Mock-up dataframes

Let’s create some mock-up dataframes, so we have something to work with. We create 2 dataframes, the first one is a 20 row by 10 columns random numbers; and the second dataframe is 10 rows by 1 column.

import pandas as pd
import numpy as np


df_1 = pd.DataFrame(np.random.rand(20,10))
df_2 = pd.DataFrame(np.random.rand(10,1))

We’ll go through 2 methods of saving multi-sheets Excel files. The idea is pretty much the same between the 2 methods: we create an ExcelWriter, then pass it into the df.to_excel() for saving dataframe into an Excel file. The two methods are slightly different in syntax but they work the same way.

Method 1

This is the method demonstrated on the official pandas documentation.

with pd.ExcelWriter('mult_sheets_1.xlsx') as writer1:
    df_1.to_excel(writer1, sheet_name = 'df_1', index = False)
    df_2.to_excel(writer1, sheet_name = 'df_2', index = False)

Method 2

This is my personal preferred method. Let me show you how it looks like then tell you why I prefer this over method 1.

writer2 = pd.ExcelWriter('mult_sheets_2.xlsx')

df_1.to_excel(writer2, sheet_name = 'df_1', index = False)
df_2.to_excel(writer2, sheet_name = 'df_2', index = False)

writer2.save()

By now you must think that these two methods are the same! Well, yes and no.

It’s true that both methods do exactly the same thing – saving the two dataframes into a single Excel file. however, the mechanism is quite different.

The difference

First of all, because of the with block in method 1, all your dataframes have to be in the same scope. This means if you have a dataframe outside of the current scope, you will have to bring it in first.

Whereas for method 2, the dataframes can be in different scopes and it will still work. This is especially useful when your code is complicated.