pandas.ExcelWriter¶

class pandas.ExcelWriter(path, engine=None, **kwargs)[source]¶

Class for writing DataFrame objects into excel sheets.

Default is to use xlwt for xls, openpyxl for xlsx, odf for ods. 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 to io.excel.<extension>.writer. NOTE: can only be passed as a keyword argument.

Deprecated since version 1.2.0: As the xlwt package is no longer maintained, the xlwt engine will be removed in a future version of pandas.
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.

New in version 0.24.0.
storage_optionsdict, optional: Extra options that make sense for a particular storage connection, e.g. host, port, username, password, etc., if using a URL that will be parsed by fsspec, e.g., starting “s3://”, “gcs://”.

New in version 1.2.0.

Notes

None of the methods and properties are considered public.

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

Examples

Default usage:

>>> with ExcelWriter('path_to_file.xlsx') as writer:
...     df.to_excel(writer)

To write to separate sheets in a single file:

>>> with 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:

>>> with 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 ExcelWriter('path_to_file.xlsx', mode='a') as writer:
...     df.to_excel(writer, sheet_name='Sheet3')

You can store Excel file in RAM:

>>> import io
>>> buffer = io.BytesIO()
>>> with pd.ExcelWriter(buffer) as writer:
...     df.to_excel(writer)

You can pack Excel file into zip archive:

>>> import zipfile
>>> 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)

Attributes

None

Methods

None

pandas.ExcelFile.parse pandas.read_json