Vous êtes un professionnel et vous avez besoin d'une formation ? Machine Learning
avec Scikit-Learn Voir le programme détaillé

Module « pandas »

Classe « ExcelWriter »

Informations générales

Héritage

builtins.object
    Generic
        ExcelWriter

Définition

class ExcelWriter(Generic):

help(ExcelWriter)

Class for writing DataFrame objects into excel sheets.

Default is to use:

* `xlsxwriter <https://pypi.org/project/XlsxWriter/>`__ for xlsx files if xlsxwriter
  is installed otherwise `openpyxl <https://pypi.org/project/openpyxl/>`__
* `odswriter <https://pypi.org/project/odswriter/>`__ 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
----------
path : str or typing.BinaryIO
    Path to xls or xlsx or ods file.
engine : str (optional)
    Engine to use for writing. If None, defaults to
    ``io.excel.<extension>.writer``.  NOTE: can only be passed as a keyword
    argument.
date_format : str, default None
    Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
datetime_format : str, 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_options : dict, 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
    <https://pandas.pydata.org/docs/user_guide/io.html?
    highlight=storage_options#reading-writing-remote-files>`_.

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.

    .. versionadded:: 1.3.0

    .. versionchanged:: 1.4.0

       Added ``overlay`` option

engine_kwargs : dict, 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)``
    * odswriter: ``odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)``

    .. versionadded:: 1.3.0

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"])  # doctest: +SKIP
>>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
...     df.to_excel(writer)  # doctest: +SKIP

To write to separate sheets in a single file:

>>> df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  # doctest: +SKIP
>>> df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  # doctest: +SKIP
>>> with pd.ExcelWriter("path_to_file.xlsx") as writer:
...     df1.to_excel(writer, sheet_name="Sheet1")  # doctest: +SKIP
...     df2.to_excel(writer, sheet_name="Sheet2")  # doctest: +SKIP

You can set the date format or datetime format:

>>> from datetime import date, datetime  # doctest: +SKIP
>>> 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"],
... )  # doctest: +SKIP
>>> 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)  # doctest: +SKIP

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")  # doctest: +SKIP

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

>>> with ExcelWriter(
...     "path_to_file.xlsx",
...     mode="a",
...     engine="openpyxl",
...     if_sheet_exists="replace",
... ) as writer:
...     df.to_excel(writer, sheet_name="Sheet1")  # doctest: +SKIP

You can also write multiple DataFrames to a single sheet. Note that the
``if_sheet_exists`` parameter needs to be set to ``overlay``:

>>> with 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)  # doctest: +SKIP

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  # doctest: +SKIP
>>> df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  # doctest: +SKIP
>>> 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)  # doctest: +SKIP

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)  # doctest: +SKIP

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")  # doctest: +SKIP

Constructeur(s)

Signature du constructeur	Description
__new__(cls, path: 'FilePath | WriteExcelBuffer | ExcelWriter', engine: 'str | None' = None, date_format: 'str | None' = None, datetime_format: 'str | None' = None, mode: 'str' = 'w', storage_options: 'StorageOptions | None' = None, if_sheet_exists: 'ExcelWriterIfSheetExists | None' = None, engine_kwargs: 'dict | None' = None) -> 'Self'
__init__(self, path: 'FilePath | WriteExcelBuffer | ExcelWriter', engine: 'str | None' = None, date_format: 'str | None' = None, datetime_format: 'str | None' = None, mode: 'str' = 'w', storage_options: 'StorageOptions | None' = None, if_sheet_exists: 'ExcelWriterIfSheetExists | None' = None, engine_kwargs: 'dict[str, Any] | None' = None) -> 'None'

Liste des opérateurs

Opérateurs hérités de la classe object

__eq__, __ge__, __gt__, __le__, __lt__, __ne__

Méthodes héritées de la classe Generic

__init_subclass__, __subclasshook__

Méthodes héritées de la classe object

__delattr__, __dir__, __format__, __getattribute__, __getstate__, __hash__, __init_subclass__, __reduce__, __reduce_ex__, __repr__, __setattr__, __sizeof__, __str__, __subclasshook__

Vous êtes un professionnel et vous avez besoin d'une formation ? Calcul scientifique
avec Python Voir le programme détaillé