pandas.ExcelWriter#

classpandas.ExcelWriter(path,engine=None,date_format=None,datetime_format=None,mode='w',storage_options=None,if_sheet_exists=None,engine_kwargs=None)[source]#

Class for writing DataFrame objects into excel sheets.

Default is to use:

• xlsxwriter for xlsx files if xlsxwriteris installed otherwiseopenpyxl

• odswriter for ods files

SeeDataFrame.to_excel for typical usage.

The writer should be used as a context manager. Otherwise, callclose() to saveand close any opened file handles.

Parameters:

pathstr or typing.BinaryIO

Path to xls or xlsx or ods file.

enginestr (optional)

Engine to use for writing. If None, defaults toio.excel.<extension>.writer. NOTE: can only be passed as a keywordargument.

date_formatstr, default None

Format string for dates written into Excel files (e.g. ‘YYYY-MM-DD’).

datetime_formatstr, default None

Format string for datetime objects written into Excel files.(e.g. ‘YYYY-MM-DD HH:MM:SS’).

mode{‘w’, ‘a’}, default ‘w’

File mode to use (write or append). Append does not work with fsspec URLs.

storage_optionsdict, optional

Extra options that make sense for a particular storage connection, e.g.host, port, username, password, etc. For HTTP(S) URLs the key-value pairsare forwarded tourllib.request.Request as header options. For otherURLs (e.g. starting with “s3://”, and “gcs://”) the key-value pairs areforwarded tofsspec.open. Please seefsspec andurllib for moredetails, and for more examples on storage options referhere.

if_sheet_exists{‘error’, ‘new’, ‘replace’, ‘overlay’}, default ‘error’

How to behave when trying to write to a sheet that alreadyexists (append mode only).

• error: raise a ValueError.

• new: Create a new sheet, with a name determined by the engine.

• replace: Delete the contents of the sheet before writing to it.

• overlay: Write contents to the existing sheet without first removing,but possibly over top of, the existing contents.

Added in version 1.3.0.

Changed in version 1.4.0:Addedoverlay option

engine_kwargsdict, optional

Keyword arguments to be passed into the engine. These will be passed tothe following functions of the respective engines:

• xlsxwriter:xlsxwriter.Workbook(file,**engine_kwargs)

• openpyxl (write mode):openpyxl.Workbook(**engine_kwargs)

• openpyxl (append mode):openpyxl.load_workbook(file,**engine_kwargs)

• odswriter:odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)

Added in version 1.3.0.

Notes

For compatibility with CSV writers, ExcelWriter serializes listsand dicts to strings before writing.

Examples

Default usage:

>>>df=pd.DataFrame([["ABC","XYZ"]],columns=["Foo","Bar"])>>>withpd.ExcelWriter("path_to_file.xlsx")aswriter:...df.to_excel(writer)

To write to separate sheets in a single file:

>>>df1=pd.DataFrame([["AAA","BBB"]],columns=["Spam","Egg"])>>>df2=pd.DataFrame([["ABC","XYZ"]],columns=["Foo","Bar"])>>>withpd.ExcelWriter("path_to_file.xlsx")aswriter:...df1.to_excel(writer,sheet_name="Sheet1")...df2.to_excel(writer,sheet_name="Sheet2")

You can set the date format or datetime format:

>>>fromdatetimeimportdate,datetime>>>df=pd.DataFrame(...[...[date(2014,1,31),date(1999,9,24)],...[datetime(1998,5,26,23,33,4),datetime(2014,2,28,13,5,13)],...],...index=["Date","Datetime"],...columns=["X","Y"],...)>>>withpd.ExcelWriter(..."path_to_file.xlsx",...date_format="YYYY-MM-DD",...datetime_format="YYYY-MM-DD HH:MM:SS"...)aswriter:...df.to_excel(writer)

You can also append to an existing Excel file:

>>>withpd.ExcelWriter("path_to_file.xlsx",mode="a",engine="openpyxl")aswriter:...df.to_excel(writer,sheet_name="Sheet3")

Here, theif_sheet_exists parameter can be set to replace a sheet if italready exists:

>>>withExcelWriter(..."path_to_file.xlsx",...mode="a",...engine="openpyxl",...if_sheet_exists="replace",...)aswriter:...df.to_excel(writer,sheet_name="Sheet1")

You can also write multiple DataFrames to a single sheet. Note that theif_sheet_exists parameter needs to be set tooverlay:

>>>withExcelWriter("path_to_file.xlsx",...mode="a",...engine="openpyxl",...if_sheet_exists="overlay",...)aswriter:...df1.to_excel(writer,sheet_name="Sheet1")...df2.to_excel(writer,sheet_name="Sheet1",startcol=3)

You can store Excel file in RAM:

>>>importio>>>df=pd.DataFrame([["ABC","XYZ"]],columns=["Foo","Bar"])>>>buffer=io.BytesIO()>>>withpd.ExcelWriter(buffer)aswriter:...df.to_excel(writer)

You can pack Excel file into zip archive:

>>>importzipfile>>>df=pd.DataFrame([["ABC","XYZ"]],columns=["Foo","Bar"])>>>withzipfile.ZipFile("path_to_file.zip","w")aszf:...withzf.open("filename.xlsx","w")asbuffer:...withpd.ExcelWriter(buffer)aswriter:...df.to_excel(writer)

You can specify additional arguments to the underlying engine:

>>>withpd.ExcelWriter(..."path_to_file.xlsx",...engine="xlsxwriter",...engine_kwargs={"options":{"nan_inf_to_errors":True}}...)aswriter:...df.to_excel(writer)

In append mode,engine_kwargs are passed through toopenpyxl’sload_workbook:

>>>withpd.ExcelWriter(..."path_to_file.xlsx",...engine="openpyxl",...mode="a",...engine_kwargs={"keep_vba":True}...)aswriter:...df.to_excel(writer,sheet_name="Sheet2")

Attributes

book	Book instance.
date_format	Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
datetime_format	Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
engine	Name of engine.
if_sheet_exists	How to behave when writing to a sheet that already exists in append mode.
sheets	Mapping of sheet names to sheet objects.
supported_extensions	Extensions that writer engine supports.

Methods

check_extension(ext)	checks that path's extension against the Writer's supported extensions.
close()	synonym for save, to make it more file-like