pandas.DataFrame.to_excel#
DataFrame. to_excel ( excel_writer , sheet_name = ‘Sheet1’ , na_rep = » , float_format = None , columns = None , header = True , index = True , index_label = None , startrow = 0 , startcol = 0 , engine = None , merge_cells = True , inf_rep = ‘inf’ , freeze_panes = None , storage_options = None ) [source] #
Write object to an Excel sheet.
To write a single object to an Excel .xlsx file it is only necessary to specify a target file name. To write to multiple sheets it is necessary to create an ExcelWriter object with a target file name, and specify a sheet in the file to write to.
Multiple sheets may be written to by specifying unique sheet_name . With all data written to the file it is necessary to save the changes. Note that creating an ExcelWriter object with a file name that already exists will result in the contents of the existing file being erased.
Parameters excel_writer path-like, file-like, or ExcelWriter object
File path or existing ExcelWriter.
sheet_name str, default ‘Sheet1’
Name of sheet which will contain DataFrame.
na_rep str, default ‘’
Missing data representation.
float_format str, optional
Format string for floating point numbers. For example float_format=»%.2f» will format 0.1234 to 0.12.
columns sequence or list of str, optional
header bool or list of str, default True
Write out the column names. If a list of string is given it is assumed to be aliases for the column names.
index bool, default True
index_label str or sequence, optional
Column label for index column(s) if desired. If not specified, and header and index are True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex.
startrow int, default 0
Upper left cell row to dump data frame.
startcol int, default 0
Upper left cell column to dump data frame.
engine str, optional
Write engine to use, ‘openpyxl’ or ‘xlsxwriter’. You can also set this via the options io.excel.xlsx.writer or io.excel.xlsm.writer .
merge_cells bool, default True
Write MultiIndex and Hierarchical Rows as merged cells.
inf_rep str, default ‘inf’
Representation for infinity (there is no native representation for infinity in Excel).
freeze_panes tuple of int (length 2), optional
Specifies the one-based bottommost row and rightmost column that is to be frozen.
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.
Write DataFrame to a comma-separated values (csv) file.
Class for writing DataFrame objects into excel sheets.
Read an Excel file into a pandas DataFrame.
Read a comma-separated values (csv) file into DataFrame.
Add styles to Excel sheet.
For compatibility with to_csv() , to_excel serializes lists and dicts to strings before writing.
Once a workbook has been saved it is not possible to write further data without rewriting the whole workbook.
Create, write to and save a workbook:
>>> df1 = pd.DataFrame([['a', 'b'], ['c', 'd']], . index=['row 1', 'row 2'], . columns=['col 1', 'col 2']) >>> df1.to_excel("output.xlsx")
To specify the sheet name:
>>> df1.to_excel("output.xlsx", . sheet_name='Sheet_name_1')
If you wish to write to more than one sheet in the workbook, it is necessary to specify an ExcelWriter object:
>>> df2 = df1.copy() >>> with pd.ExcelWriter('output.xlsx') as writer: . df1.to_excel(writer, sheet_name='Sheet_name_1') . df2.to_excel(writer, sheet_name='Sheet_name_2')
ExcelWriter can also be used to append to an existing Excel file:
>>> with pd.ExcelWriter('output.xlsx', . mode='a') as writer: . df.to_excel(writer, sheet_name='Sheet_name_3')
To set the library that is used to write the Excel file, you can pass the engine keyword (the default engine is automatically chosen depending on the file extension):
>>> df1.to_excel('output1.xlsx', engine='xlsxwriter')
pandas.ExcelWriter#
Class for writing DataFrame objects into excel sheets.
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..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 , 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.
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.
Changed in version 1.4.0: 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)
For compatibility with CSV writers, ExcelWriter serializes lists and dicts to strings before writing.
>>> 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 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 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)
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")
Format string for dates written into Excel files (e.g.
Format string for dates written into Excel files (e.g.
How to behave when writing to a sheet that already exists in append mode.
Mapping of sheet names to sheet objects.
Extensions that writer engine supports.
checks that path’s extension against the Writer’s supported extensions.
synonym for save, to make it more file-like
Example: Pandas Excel output with datetimes
An example of converting a Pandas dataframe with datetimes to an Excel file with a default datetime and date format using Pandas and XlsxWriter.
############################################################################## # # An example of converting a Pandas dataframe with datetimes to an xlsx file # with a default datetime and date format using Pandas and XlsxWriter. # # SPDX-License-Identifier: BSD-2-Clause # Copyright 2013-2023, John McNamara, jmcnamara@cpan.org # import pandas as pd from datetime import datetime, date # Create a Pandas dataframe from some datetime data. df = pd.DataFrame( "Date and time": [ datetime(2015, 1, 1, 11, 30, 55), datetime(2015, 1, 2, 1, 20, 33), datetime(2015, 1, 3, 11, 10), datetime(2015, 1, 4, 16, 45, 35), datetime(2015, 1, 5, 12, 10, 15), ], "Dates only": [ date(2015, 2, 1), date(2015, 2, 2), date(2015, 2, 3), date(2015, 2, 4), date(2015, 2, 5), ], > ) # Create a Pandas Excel writer using XlsxWriter as the engine. # Also set the default datetime and date formats. writer = pd.ExcelWriter( "pandas_datetime.xlsx", engine="xlsxwriter", datetime_format="mmm d yyyy hh:mm:ss", date_format="mmmm dd yyyy", ) # Convert the dataframe to an XlsxWriter Excel object. df.to_excel(writer, sheet_name="Sheet1") # Get the xlsxwriter workbook and worksheet objects. in order to set the column # widths, to make the dates clearer. workbook = writer.book worksheet = writer.sheets["Sheet1"] # Get the dimensions of the dataframe. (max_row, max_col) = df.shape # Set the column widths, to make the dates clearer. worksheet.set_column(1, max_col, 20) # Close the Pandas Excel writer and output the Excel file. writer.close()
© Copyright 2013-2023, John McNamara.
Created using Sphinx 1.8.6.