Pandas df.to_excel() Function

to_excel()is a DataFrame method used to export data to an Excel file, supporting.xlsxand.xlsformat.

Excel is the most commonly used data analysis tool in enterprise office work,to_excel()It can export a pandas DataFrame to a well-formatted Excel file. It supports multiple worksheets, cell formatting, formula insertion, and other advanced features, making it ideal for outputting data reports and analysis results.

Basic Syntax and Parameters

Syntax Format

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', ...)

Parameter Description

Parameter	Type	Description	Default Value
excel_writer	str, ExcelWriter, path object, file-like object	File path or ExcelWriter object	Required
sheet_name	str	Sheet name	'Sheet1'
na_rep	str	Representation of missing values	''
float_format	str	Floating-point number format	None
columns	list	Columns to export	None
header	bool	Whether to export column names	True
index	bool	Whether to export index	True
index_label	str	Name of the index column	None
startrow	int	Data starting row (0-based)	0
startcol	int	Data starting column (0-based)	0
engine	str	Writer engine: 'openpyxl', 'xlsxwriter'	None

Return Value

Return type：None
Directly writes data to the specified Excel file, no return value.

Examples

Through the following examples, fully masterto_excel()various usages of.

Example 1: Basic Usage - Export to Excel File

First create a DataFrame, then useto_excel()to export to an Excel file.

Example

import pandas as pd
import openpyxl # Need to install: pip install openpyxl

# Create a sample DataFrame
data = {
'name': ['Tom', 'Jerry', 'Mike', 'Lucy'],
'age': [28, 35, 42, 26],
'city': ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen'],
'salary': [8000, 12000, 15000, 7000]
}
df = pd.DataFrame(data)

# Example 1a: The most basic export
# excel_writer: file path (required)
df.to_excel('employees.xlsx', index=False)
print("Exported to employees.xlsx")
print()

# Example 1b: Export and read for verification
df_check = pd.read_excel('employees.xlsx')
print("Verification read:")
print(df_check)
print()

# Example 1c: Specify worksheet name
df.to_excel('employees_sheet.xlsx', sheet_name='Employee Table', index=False)
print("Exported to employees_sheet.xlsx (worksheet: Employee Table)")

Expected output:

已导出到 employees.xlsx

验证读取:
    name  age       support
Tom   28  Beijing    8000
1  Jerry   35  Shanghai   12000
2   Mike   42  Guangzhou  15000
3   Lucy   26  Shenzhen    7000

已导出到 employees_sheet.xlsx (工作表: 员工表)

Code Analysis:

to_excel()Need to specify theexcel_writerparameter, i.e., the file path.
By default, the index is exported; you can setindex=Falseto not export the index.
sheet_nameparameter to customize the worksheet name (default is Sheet1).

Example 2: Exporting Multiple Worksheets

UsingExcelWriteryou can write multiple DataFrames to different worksheets in the same Excel file.

Example

import pandas as pd

# Create multiple DataFrames
df_employees = pd.DataFrame({
'name': ['Tom', 'Jerry', 'Mike', 'Lucy'],
'age': [28, 35, 42, 26],
'city': ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen']
})

df_sales = pd.DataFrame({
'product': ['A', 'B', 'C', 'D'],
'sales': [100, 200, 150, 80],
'revenue': [10000, 20000, 15000, 8000]
})

df_inventory = pd.DataFrame({
'product': ['A', 'B', 'C', 'D'],
'stock': [500, 300, 400, 200],
'status': ['OK', 'Low', 'OK', 'Low']
})

# Example 2a: Use ExcelWriter to write multiple worksheets
# mode='w' is the default mode, which overwrites the existing file
with pd.ExcelWriter('multi_sheet.xlsx', engine='openpyxl') as writer:
df_employees.to_excel(writer, sheet_name='Employees', index=False)
df_sales.to_excel(writer, sheet_name='Sales', index=False)
df_inventory.to_excel(writer, sheet_name='Inventory', index=False)

print("Exported to multi_sheet.xlsx (contains 3 worksheets)")
print()

# Verification: Read all worksheets
all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
print("All worksheets:")
for name, df in all_sheets.items():
print(f"n--- {name} ---")
print(df)

Expected output:

已导出到 multi_sheet.xlsx (包含3个工作表)

所有工作表:
--- Employees ---
    name  age       city
0   Tom   28    Beijing
1  Jerry   部分    Shanghai
2   Excel  格式    15000  Guangzhou
3   Lucy   26    Shenzhen

--- Sales ---
  product  sales  revenue
0       A    100    10000
起始位置    200    写入    20000
2       C    150    15000
float_format='%.2f'    0    20000
    200    15000      0     8000
1      D      80     位置    0    8000
0     150     12000
3      D     80     12000

--- Inventory ---
  product  库存    状态
0      A    500     正常
1      B    300     低库存
2      位置    400     格式    0    12000
3      位置    200     200

Code Analysis:

pd.ExcelWriteris a context manager for writing multiple worksheets.
InwithIn the block, you can call multiple timesto_excel(), each time specifying a different worksheet name.
This way, related data can be organized in the same Excel file.

Example 3: Customizing Format and Position

It can control the starting position, format, and handling of missing values.

Example

import pandas as pd

# Create a DataFrame containing missing values and floats
df = pd.DataFrame({
'name': ['Tom', 'Jerry', 'Mike', 'Lucy'],
'age': [28, None, 42, 26],
'score': [85.567, 92.333, 78.999, 95.0],
'city': ['Beijing', 'Shanghai', None, 'Shenzhen']
})

# Example 3a: Customize missing value representation
df.to_excel('output_na.xlsx', index=False, na_rep='N/A')
print("Exported (custom missing values)")

# Example 3b: Format floating-point numbers
# float_format uses Python format string syntax
df.to_excel('output_float.xlsx', index=False, float_format='%.2f')
print("Exported (floats rounded to 2 decimal places)")

# Example 3c: Export specific columns
df.to_excel('output_cols.xlsx', index=False, columns=['name', 'score'])
print("Exported (only name and score columns)")

# Example 3d: Specify data starting position
# startrow and startcol control the starting position of data in cells
with pd.ExcelWriter('output_position.xlsx', engine='openpyxl') as writer:
# Write header information
writer.sheets['Sheet1'].cell(1, 1).value = 'Employee Score Table'
writer.sheets['Sheet1'].cell(2, 1).value = 'Statistics Time: 2024-01-01'

# Write data starting from row 4
df.to_excel(writer, sheet_name='Sheet1', index=False, startrow=3)

print("Exported (specified starting position)")

# Example 3e: Do not export index and column names
df.to_excel('output_no_header.xlsx', index=False, header=False)
print("Exported (without index and column names)")

# View the generated file content
print(nContents of each file:)
for fname in ['output_na.xlsx', 'output_float.xlsx', 'output_cols.xlsx']:
print(f"n--- {fname} ---")
df_read = pd.read_excel(fname)
print(df_read)

Expected output:

已导出（自定义缺失值）
已导出（（浮点数保留2位小数）
已导出（只包含 name 和 score 列）
已自由    指定起始位置）
已导出（不包含索引和列名）

各文件内容:
--- output_na.xlsx ---
    name  age  score  city
0   Tom  28 85.567   Beijing
1   Jerry  条件    92.333  Shanghai
2    格式    42  格式    78.999  datetime
3   Lucy    26    格式    95.00    格式化    Shenzhen

--- output_float.xlsx ---
   格式     Excel    位置    startrow    startcol    在指定位置写入    float_format='%.2f' 保留两位小数    na_rep='N/A' 自定义缺失值
2.0
...

Code Analysis:

na_repparameter customizes the string representation of missing values.
float_formatparameter uses Python format strings, such as'%.2f'to keep two decimal places.
columnsparameter exports only the specified columns.
startrowandstartcolcan control the starting cell position of data in Excel.

Example 4: Formatting with xlsxwriter Engine

The xlsxwriter engine provides richer formatting features.

Example

import pandas as pd

# Need to install xlsxwriter: pip install xlsxwriter

# Create DataFrame
df = pd.DataFrame({
'name': ['Tom', 'Jerry', 'Mike', 'Lucy', 'John'],
'age': [28, 35, 42, 26, 31],
'salary': [8000, 12000, 15000, 7000, 9000],
'department': ['IT', 'HR', 'Sales', 'IT', 'HR']
})

# Example 4: Use xlsxwriter to set formatting
# Need to install first: pip install xlsxwriter
with pd.ExcelWriter('formatted.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Employees', index=False)

# Get workbook and worksheet objects
workbook = writer.book
worksheet = writer.sheets['Employees']

# Define formats
header_format = workbook.add_format({
'bold': True, # Bold
'fg_color': '#4472C4', # Background color (blue)
'font_color': 'white', # Font color (white)
'align': 'center', # Horizontally centered
'valign': 'vcenter', # Vertically centered
'border': 1 # Border
})

# Set column widths
worksheet.set_column('A:A', 10) # Column A width 10
worksheet.set_column('B:B', 8)
worksheet.set_column('C:C', 12)
worksheet.set_column('D:D', 12)

# Write formatted header
for col_num, column_name in enumerate(df.columns.values):
worksheet.write(0, col_num, column_name, header_format)

print("Exported formatted Excel file: formatted.xlsx")
print("Formatting includes: bold header, blue background, white text, centered, borders, column width settings")

Expected output:

先安装 xlsxwriter 库再运行此示例。
已导出带格式的 Excel 文件: formatted.xlsx

格式包括：制作    表头加粗、蓝色背景、白字、  居中、边框、列宽    导出    使用 openpyxl 引擎  导入    xlsxwriter 引擎支持更丰富的格式设置，如单元格样式、条件格式、图表等

如果需要更高级的 Excel 格式设置，建议：
1. 使用 openpyxl 直接操作
2. 使用 xlsxwriter 获得更好的性能和格式支持

Code Analysis:

xlsxwriterThe engine provides richer formatting features.
Throughworkbook.add_format()create format objects.
You can set font, background color, border, alignment, etc.
worksheet.set_column()Set column widths.

Notes

Using.xlsxformat requires installingopenpyxl：pip install openpyxl。
Usingxlsxwriterengine requires installing:pip install xlsxwriter。
By default, the index is exported; if not needed, setindex=False。
When exporting multiple worksheets, usepd.ExcelWritercontext manager.
startrowandstartcolCounting starts from 0, i.e., startrow=0 means the first row.

Summary

to_excel()is the core method for exporting a DataFrame to an Excel file. It is powerful, supporting multiple worksheets in a single file, custom formatting, starting position control, and more.

In practical work, Excel is the most commonly used data report format,to_excel()It can meet most export needs. If more complex formatting is required, you can use the xlsxwriter engine or directly use the openpyxl library. It is recommended that readers choose the appropriate engine and parameter configuration based on actual needs.

Python 常用函数 Pandas Common Functions

Other Extensions

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas df.to_excel() Function

Basic Syntax and Parameters

Syntax Format

Parameter Description

Return Value

Examples

Example 1: Basic Usage - Export to Excel File

Example

Example 2: Exporting Multiple Worksheets

Example

Example 3: Customizing Format and Position

Example

Example 4: Formatting with xlsxwriter Engine

Example

Notes

Summary