Write a Python program to export dataframe into an Excel file with multiple sheets

Assume, you have a dataframe and the result for export dataframe to multiple sheets as,

To solve this, we will follow the steps given below −

Solution

• import xlsxwriter module to use excel conversion

• Define a dataframe and assign to df

• Apply pd.ExcelWriter function inside name excel name you want to create and set engine as xlsxwriter

excel_writer = pd.ExcelWriter('pandas_df.xlsx', engine='xlsxwriter')

• Convert the dataframe to multiple excel sheets using the below method,

df.to_excel(excel_writer, sheet_name='first_sheet')
df.to_excel(excel_writer, sheet_name='second_sheet')
df.to_excel(excel_writer, sheet_name='third_sheet')

• Finally save the excel_writer

excel_writer.save()

Example

Let’s understand the below code to get a better understanding −

import pandas as pd
import xlsxwriter
df = pd.DataFrame({'Fruits': ["Apple","Orange","Mango","Kiwi"],
                     'City' : ["Shimla","Sydney","Lucknow","Wellington"]
                  })
print(df)
excel_writer = pd.ExcelWriter('pandas_df.xlsx', engine='xlsxwriter')
df.to_excel(excel_writer, sheet_name='first_sheet')
df.to_excel(excel_writer, sheet_name='second_sheet')
df.to_excel(excel_writer, sheet_name='third_sheet')
excel_writer.save()

Output

Vani Nalliappan

Updated on: 24-Feb-2021

731 Views

Kickstart Your Career

Get certified by completing the course

Get Started