Pandas ExcelWriter Explained with Examples

Pandas ExcelWriter() class is used to save DataFrame objects into Excel sheets. This class is mainly used when you want to save multiple sheets and append data to an existing Excel sheet.

Pandas ExcelWriter Key Points –

By default, it uses xlsxwriter if it is installed otherwise it uses openpyxl
Use ExcelWriter to write DataFrame objects to Excel sheets, facilitating the creation of complex Excel reports programmatically.
ExcelWriter allows writing to multiple sheets within the same Excel file, enabling organized and structured data storage.
Integrate with the xlsxwriter engine to apply various formats and styles, enhancing the readability and presentation of the Excel reports.
Supports different engines like xlsxwriter, openpyxl, and xlwt, providing flexibility in handling various Excel file formats and features.

Quick Examples of Pandas ExcelWriter()

If you are in a hurry, below are some quick examples of how to use the Pandas ExcelWriter() class with examples.


# Quick examples of pandas excelWriter()

# Write excel file with default behaviour
with pd.ExcelWriter("courses.xlsx") as writer:
    df.to_excel(writer) 

# Write to Multiple Sheets
with pd.ExcelWriter('Courses.xlsx') as writer:
    df.to_excel(writer, sheet_name='Technologies')
    df2.to_excel(writer, sheet_name='Schedule')

# Append DataFrame to existing excel file
with pd.ExcelWriter('Courses.xlsx',mode='a') as writer:  
    df.to_excel(writer, sheet_name='Technologies')

Let's create a pandas DataFrame from the list and use the examples specified above to run and explore the output.


import pandas as pd
import numpy as np
# Create DataFrame from multiple lists
technologies =  ['Spark','Pandas','Java','Python', 'PHP']
fee = [25000,20000,15000,15000,18000]
duration = ['5o Days','35 Days',np.nan,'30 Days', '30 Days']
discount = [2000,1000,800,500,800]
columns=['Courses','Fee','Duration','Discount']
df = pd.DataFrame(list(zip(technologies,fee,duration,discount)), columns=columns)
print(df)

# Output:
#  Courses    Fee Duration  Discount
# 0   Spark  25000  5o Days      2000
# 1  Pandas  20000  35 Days      1000
# 2    Java  15000      NaN       800
# 3  Python  15000  30 Days       500
# 4     PHP  18000  30 Days       800

Syntax of ExcelWriter()

Following is the syntax of the ExcelWrite class. The object of this class is used as an argument to the DataFrame.to_excel() function to write DataFrame to excel sheet.


# Syntax of pandas.ExcelWriter
class pandas.ExcelWriter(path, engine=None, date_format=None, datetime_format=None, mode='w', storage_options=None, if_sheet_exists=None, engine_kwargs=None, **kwargs)

Following are some parameters.

path - Path where to save file.
if_sheet_exists - Takes values {‘error’, ‘new’, ‘replace’, ‘overlay’}, default ‘error’. Use this to raise an error if sheets exist, replace existing contents with the new data. overlay contents at the end without erasing contents.
mode - {‘w’, ‘a’}, default ‘w’. W for write, a for append.
storage_options - dict, optional. Extra options that make sense for a particular storage connection, e.g., host, port, username, password, etc., for specifying a connection to a cloud storage service.
if_sheet_exists - str, default None. How to handle writing to a sheet that already exists ('error', 'new', 'replace').
engine_kwargs - dict, optional. Additional arguments to pass to the engine.

ExcelWriter() Examples

By using pandas ExcelWrite() let's see how to save a DataFrame to excel with default parameters.


# Write excel file with default behaviour.
with pd.ExcelWriter("courses.xlsx") as writer:
    df.to_excel(writer)

This creates an Excel file with the contents as below. By default, It exports column names, indexes, and data to a sheet named 'Sheet1'.

Save to Different Sheets

The ExcelWriter class allows you to save or export multiple Pandas DataFrames to separate sheets. First, you need to create an object for ExcelWriter.

The below example save data from df object to a sheet named Technologies and df2 object to a sheet named Schedule.


# save to multiple sheets
df2 = df.clone()
with pd.ExcelWriter('Courses.xlsx') as writer:
    df.to_excel(writer, sheet_name='Technologies')
    df2.to_excel(writer, sheet_name='Schedule')

Appending DataFrame to Existing Excel File

ExcelWriter can be used to append DataFrame to an Excel file. Use mode param with value 'a' to append. The code below opens an existing file and adds data from the DataFrame to the specified sheet.


# Append DataFrame to existing excel file
with pd.ExcelWriter('Courses.xlsx',mode='a') as writer:  
    df.to_excel(writer, sheet_name='Technologies')

FAQ on Pandas ExcelWriter

ExcelWriter is a class in the Pandas library that allows you to write DataFrame objects to an Excel file. It is particularly useful when you need to write to multiple sheets in one Excel workbook.

ExcelWriter allows you to write multiple DataFrames to different sheets in a single Excel file.

By default, ExcelWriter overwrites an existing file. However, to append new data, use the openpyxl engine.

Use the openpyxl engine and the mode='a' parameter to append data in chunks. For large data, consider splitting the DataFrame into smaller parts and writing them sequentially.

ExcelWriter can handle large data, but keep in mind that writing very large data may take significant time or cause memory issues. It is recommended to write in chunks if the dataset is large.

Conclusion

In this article, you have learned the syntax of the ExcelWrite class, and its usage to save multiple DataFrames to Excel files, append to an existing sheet etc.