pandas.ExcelWriter — pandas 3.0.0.dev0+2099.g3832e85779 documentation (original) (raw)

class pandas.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 xlsxwriter is installed otherwise openpyxl
• odf for ods files

See DataFrame.to_excel() for typical usage.

The writer should be used as a context manager. Otherwise, call close() to save and 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 keyword argument.

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 pairs are forwarded to urllib.request.Request as header options. For other URLs (e.g. starting with “s3://”, and “gcs://”) the key-value pairs are forwarded to fsspec.open. Please see fsspec and urllib for more details, and for more examples on storage options refer here.

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

How to behave when trying to write to a sheet that already exists (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: Added overlay option

engine_kwargsdict, optional

Keyword arguments to be passed into the engine. These will be passed to the 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)
• odf: odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)

Added in version 1.3.0.

See also

read_excel

Read an Excel sheet values (xlsx) file into DataFrame.

read_csv

Read a comma-separated values (csv) file into DataFrame.

read_fwf

Read a table of fixed-width formatted lines into DataFrame.

Notes

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

Examples

Default usage:

df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
with pd.ExcelWriter("path_to_file.xlsx") as writer: ... 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"])
with pd.ExcelWriter("path_to_file.xlsx") as writer: ... df1.to_excel(writer, sheet_name="Sheet1")
... df2.to_excel(writer, sheet_name="Sheet2")

You can set the date format or datetime format:

from datetime import date, 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"], ... )
with pd.ExcelWriter( ... "path_to_file.xlsx", ... date_format="YYYY-MM-DD", ... datetime_format="YYYY-MM-DD HH:MM:SS", ... ) as writer: ... df.to_excel(writer)

You can also append to an existing Excel file:

with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer: ... df.to_excel(writer, sheet_name="Sheet3")

Here, the if_sheet_exists parameter can be set to replace a sheet if it already exists:

with pd.ExcelWriter( ... "path_to_file.xlsx", ... mode="a", ... engine="openpyxl", ... if_sheet_exists="replace", ... ) as writer: ... 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 to overlay:

with pd.ExcelWriter( ... "path_to_file.xlsx", ... mode="a", ... engine="openpyxl", ... if_sheet_exists="overlay", ... ) as writer: ... df1.to_excel(writer, sheet_name="Sheet1") ... df2.to_excel(writer, sheet_name="Sheet1", startcol=3)

You can store Excel file in RAM:

import io df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"]) buffer = io.BytesIO() with pd.ExcelWriter(buffer) as writer: ... df.to_excel(writer)

You can pack Excel file into zip archive:

import zipfile
df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
with zipfile.ZipFile("path_to_file.zip", "w") as zf: ... with zf.open("filename.xlsx", "w") as buffer: ... with pd.ExcelWriter(buffer) as writer: ... df.to_excel(writer)

You can specify additional arguments to the underlying engine:

with pd.ExcelWriter( ... "path_to_file.xlsx", ... engine="xlsxwriter", ... engine_kwargs={"options": {"nan_inf_to_errors": True}}, ... ) as writer: ... df.to_excel(writer)

In append mode, engine_kwargs are passed through to openpyxl’s load_workbook:

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

Attributes

Methods