Pandas ExcelWriter Explained with Examples

Naveen Nelamali

2 years ago

pandas ExcelWriter() class is used to save DataFrame to an Excel sheet. 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
Supports saving multiple DataFrames to a single sheet.
You can raise an error when the file already exists
Save multiple sheets, and append existing sheets or files.

1. Quick Examples of Pandas ExcelWriter()

Following are some examples of how to use the Pandas ExcelWriter() class with examples.


# 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

2. ExcelWriter() Syntax

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 –

3. 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'.

4. Save to Multiple 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.


# Write 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')

3. Append 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 e.t.c.