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

PythonPandasServer Side ProgrammingProgramming

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

raja
Published on 11-Feb-2021 10:46:48
Advertisements