Pandas ExcelWriter Explained with Examples

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.

Related Articles

• Write pandas DataFram to Excel Sheet
• Pandas Write to Excel with Examples
• How to Read Excel Multiple Sheets in Pandas
• How to Create Pandas Pivot Multiple Columns
• Pandas Handle Missing Data in Dataframe
• Pandas Pivot Table Explained with Examples
• Pandas Read Text with Examples
• Read Excel into pandas DataFrame

References