Pandas Series: to_excel() function

Last update on September 15 2022 12:57:05 (UTC/GMT +8 hours)

Series-to_excel() function

The to_excel() function is used to 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.

Syntax:

Series.to_excel(self, 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, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

Parameters:

Name	Description	Type/Default Value	Required / Optional
excel_writer	File path or existing ExcelWriter.	str or ExcelWriter object	Required
sheet_name	Name of sheet which will contain DataFrame.	str, default 'Sheet1'	Required
na_rep	File path or existing ExcelWriter.	str, default''	Required
excel_writer	Missing data representation.	str or ExcelWriter object	Required
float_format	Format string for floating point numbers. For example float_format="%.2f" will format 0.1234 to 0.12.	str	Optional
columns	Write out the column names. If a list of string is given it is assumed to be aliases for the column names.	bool or list of str, default True	Required
index	Write row names (index).	bool, default True	Required
index_label	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.	str or sequence	Optional
startrow	Upper left cell row to dump data frame.	int, default 0	Required
startcol	Upper left cell column to dump data frame.	int, default 0	Required
engine	Write engine to use, 'openpyxl' or 'xlsxwriter'. You can also set this via the options io.excel.xlsx.writer, io.excel.xls.writer, and io.excel.xlsm.writer.	str	Optional
merge_cells	Write MultiIndex and Hierarchical Rows as merged cells.	bool, default True	Required
encoding	Encoding of the resulting excel file. Only necessary for xlwt, other writers support unicode natively.	str	Optional
inf_rep	Representation for infinity (there is no native representation for infinity in Excel).	str, default 'inf'	Required
verbose	Display more information in the error logs.	bool, default True	Required
freeze_panes	Specifies the one-based bottommost row and rightmost column that is to be frozen.	tuple of int (length 2)	Optional

Note: 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 write further data without rewriting the whole workbook.

Example - Create, write to and save a workbook:

Python-Pandas Code:

import numpy as np
import pandas as pd
df1 = pd.DataFrame([['p', 'q'], ['r', 's']],
                   index=['row 1', 'row 2'],
                   columns=['col 1', 'col 2'])
df1.to_excel("output.xlsx")  # doctest: +SKIP

Example - To specify the sheet name:

Python-Pandas Code:

import numpy as np
import pandas as pd
df1 = pd.DataFrame([['p', 'q'], ['r', 's']],
                   index=['row 1', 'row 2'],
                   columns=['col 1', 'col 2'])
df1.to_excel("output.xlsx",
             sheet_name='Sheet_name_1')  # doctest: +SKIP

Example - If you wish to write to more than one sheet in the workbook, it is necessary to specify an ExcelWriter object:

Python-Pandas Code:

import numpy as np
import pandas as pd
df2 = df1.copy()
with pd.ExcelWriter('output.xlsx') as writer:  # doctest: +SKIP
    df1.to_excel(writer, sheet_name='Sheet_name_1')
    df2.to_excel(writer, sheet_name='Sheet_name_2')

Example - 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):

Python-Pandas Code:

import numpy as np
import pandas as pd
df1 = pd.DataFrame([['p', 'q'], ['r', 's']],
                   index=['row 1', 'row 2'],
                   columns=['col 1', 'col 2'])
df1.to_excel('output1.xlsx', engine='xlsxwriter')  # doctest: +SKIP

Previous: Series-to_dict() function
Next: Series-to_frame() function