Default is to use : * xlwt for xls * xlsxwriter for xlsx if xlsxwriter is installed otherwise openpyxl * odf for ods. See DataFrame.to_excel for typical usage.
The writer should be used as a context manager. Otherwise, call :None:None:`close()` to save and close any opened file handles.
None of the methods and properties are considered public.
For compatibility with CSV writers, ExcelWriter serializes lists and dicts to strings before writing.
Path to xls or xlsx or ods file.
Engine to use for writing. If None, defaults to io.excel.<extension>.writer
. NOTE: can only be passed as a keyword argument.
As the :None:None:`xlwt <https://pypi.org/project/xlwt/>` package is no longer maintained, the xlwt
engine will be removed in a future version of pandas.
Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').
Format string for datetime objects written into Excel files. (e.g. 'YYYY-MM-DD HH:MM:SS').
File mode to use (write or append). Append does not work with fsspec URLs.
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://".
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 removing the old contents.
Added overlay
option
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)
Keyword arguments to be passed into the engine.
Use engine_kwargs instead.
Class for writing DataFrame objects into excel sheets.
Default usage:
This example is valid syntax, but we were not able to check execution>>> 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:
This example is valid syntax, but we were not able to check execution>>> 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:
This example is valid syntax, but we were not able to check execution>>> 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:
This example is valid syntax, but we were not able to check execution>>> with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
... df.to_excel(writer, sheet_name="Sheet3") # doctest: +SKIP
Here, the :None:None:`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:
This example is valid syntax, but we were not able to check execution>>> 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:
This example is valid syntax, but we were not able to check execution>>> 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:
This example is valid syntax, but we were not able to check execution>>> 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(See :
... "path_to_file.xlsx",
... engine="openpyxl",
... mode="a",
... engine_kwargs={"keep_vba": True}
... ) as writer:
... df.to_excel(writer, sheet_name="Sheet2") # doctest: +SKIP
The following pages refer to to this document either explicitly or contain code examples using this.
pandas.core.generic.NDFrame.to_excel
pandas.io.excel._base.ExcelWriter
Hover to see nodes names; edges to Self not shown, Caped at 50 nodes.
Using a canvas is more power efficient and can get hundred of nodes ; but does not allow hyperlinks; , arrows or text (beyond on hover)
SVG is more flexible but power hungry; and does not scale well to 50 + nodes.
All aboves nodes referred to, (or are referred from) current nodes; Edges from Self to other have been omitted (or all nodes would be connected to the central node "self" which is not useful). Nodes are colored by the library they belong to, and scaled with the number of references pointing them