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.

1. 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')

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.