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

Exporting a Pandas DataFrame to an Excel file with multiple sheets is a common requirement for data analysis and reporting. Python provides several approaches to accomplish this using libraries like xlsxwriter, openpyxl, or Pandas' built-in Excel writer.

Using xlsxwriter Engine

The xlsxwriter engine provides excellent formatting options and performance for creating Excel files ?

import pandas as pd
import xlsxwriter

# Create sample DataFrame
df = pd.DataFrame({
    'Fruits': ["Apple", "Orange", "Mango", "Kiwi"],
    'City': ["Shimla", "Sydney", "Lucknow", "Wellington"]
})

print("Original DataFrame:")
print(df)

# Create Excel writer object
excel_writer = pd.ExcelWriter('pandas_df.xlsx', engine='xlsxwriter')

# Export to multiple sheets
df.to_excel(excel_writer, sheet_name='first_sheet', index=False)
df.to_excel(excel_writer, sheet_name='second_sheet', index=False)
df.to_excel(excel_writer, sheet_name='third_sheet', index=False)

# Save the Excel file
excel_writer.close()
print("Excel file created successfully with multiple sheets!")

Original DataFrame:
  Fruits       City
0  Apple     Shimla
1 Orange     Sydney
2  Mango    Lucknow
3   Kiwi Wellington
Excel file created successfully with multiple sheets!

Using openpyxl Engine

The openpyxl engine is another popular choice that supports both reading and writing Excel files ?

import pandas as pd

# Create sample DataFrames with different data
sales_data = pd.DataFrame({
    'Product': ['Laptop', 'Mouse', 'Keyboard'],
    'Sales': [1500, 200, 350]
})

inventory_data = pd.DataFrame({
    'Product': ['Laptop', 'Mouse', 'Keyboard'], 
    'Stock': [50, 150, 80]
})

# Using openpyxl engine
with pd.ExcelWriter('business_data.xlsx', engine='openpyxl') as writer:
    sales_data.to_excel(writer, sheet_name='Sales', index=False)
    inventory_data.to_excel(writer, sheet_name='Inventory', index=False)
    
print("Business data exported to Excel with multiple sheets!")

Business data exported to Excel with multiple sheets!

Exporting Different DataFrames to Different Sheets

You can export different DataFrames to separate sheets within the same Excel file ?

import pandas as pd

# Create different DataFrames
students = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Grade': ['A', 'B', 'A']
})

teachers = pd.DataFrame({
    'Name': ['Mr. Smith', 'Ms. Johnson'],
    'Subject': ['Math', 'Science']
})

courses = pd.DataFrame({
    'Course': ['Python', 'Data Science', 'Machine Learning'],
    'Duration': ['3 months', '6 months', '4 months']
})

# Export to multiple sheets
with pd.ExcelWriter('school_data.xlsx', engine='xlsxwriter') as writer:
    students.to_excel(writer, sheet_name='Students', index=False)
    teachers.to_excel(writer, sheet_name='Teachers', index=False)
    courses.to_excel(writer, sheet_name='Courses', index=False)

print("School data exported successfully!")
print(f"Students DataFrame:\n{students}")
print(f"\nTeachers DataFrame:\n{teachers}")

School data exported successfully!
Students DataFrame:
      Name Grade
0    Alice     A
1      Bob     B
2  Charlie     A

Teachers DataFrame:
        Name   Subject
0   Mr. Smith      Math
1  Ms. Johnson   Science

Key Parameters

Conclusion

Use pd.ExcelWriter() with context manager for automatic file closing. Set index=False to exclude row indices and choose xlsxwriter for better formatting options or openpyxl for reading/writing compatibility.