Add New Sheet to Excel Using Pandas

The article aims to implement a function in Python using Pandas to add a new sheet to an existing Excel file, facilitating efficient data organization and management within Excel workbooks.

Using openpxl to add a new sheet to Excel Using Pandas

Pandas offer a seamless interface for manipulating Excel files programmatically that allows automated and reproducible data management workflow.

To demonstrate how to add a new sheet to excel, we are going to take a blank excel sheet and modify it using the following steps:

1. We will import necessary libraries like pandas and openpyxl.
2. Next, we will load an existing excel file using load_workbook() and create a pandas excel writer object named “writer” for the same excel file using the openpyxl engine. This will allow us to write the data to the Excel file.
3. We define two dictionaries (data_1 and data_2) containing data for our DataFrames. Each dictionary represents a DataFrame with columns and corresponding values. We then create DataFrames (df_1 and df_2) from these dictionaries using Pandas.
4. Then, using the to_excel() method of Pandas DataFrame, we add our DataFrames (df_1 and df_2) as new sheets to the Excel writer object writer. We specify the sheet names (‘Sheet1’ and ‘Sheet2’) and set index=False to exclude DataFrame indices from the Excel sheets.
5. Finally, we save the changes made to the Excel file by calling writer.save() and close the writer object using writer.close().

Python3

import pandas as pd
from openpyxl import load_workbook

# Generating workbook and writer engine
excel_workbook = load_workbook("xl_file.xlsx")
writer = pd.ExcelWriter("xl_file.xlsx", engine='openpyxl')
writer.book = excel_workbook

# Creating dataframes with different values
data_1 = {'Column1': [11, 22, 33], 'Column2': ['value11', 'value22', 'value33']}
data_2 = {'A': [111, 222, 333], 'B': ['X', 'Y', 'Z']}
df_1 = pd.DataFrame(data_1)
df_2 = pd.DataFrame(data_2)

# Adding dataframes to Excel as new sheets
df_1.to_excel(writer, sheet_name='Sheet1', index=False)
df_2.to_excel(writer, sheet_name='Sheet2', index=False)

# Saving changes and closing writer
writer.save()
writer.close()

Changes in Excel File:

Finally, we added the DataFrames as new sheets to existing excel file.