结合 Pandas 与 XlsxWriter 生成 Excel 文件

Python Pandas 是 Python 数据分析库，可以读取、筛选和重新组织大小不同的数据集，并输出为 Excel 等多种格式。 （Pandas）

Pandas 使用 openpyxl 或 XlsxWriter 写入 Excel xlsx 文件。 （openpyxl）

在 Pandas 中使用 XlsxWriter

要将 XlsxWriter 与 Pandas 结合使用，将它指定为 Excel 写入引擎即可：

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.close()

输出结果如下：

完整示例参见“Pandas Excel 示例”。 （Example: Pandas Excel example）

从 Pandas 访问 XlsxWriter

要给 Pandas 输出应用图表、条件格式、列格式等 XlsxWriter 功能，需要取得底层 workbook 和 worksheet 对象。之后，就可以将它们当作普通的 XlsxWriter 对象使用。 （workbook；worksheet）

沿用上面的示例，可以这样取得对象：

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

这相当于单独使用 XlsxWriter 时的以下代码：

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()

随后即可通过 Workbook 和 Worksheet 对象使用其他 XlsxWriter 功能，如下所示。

为 DataFrame 输出添加图表

按照上一节取得 Workbook 和 Worksheet 对象后，就可以应用其他功能，例如添加图表：

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Create a chart object.
chart = workbook.add_chart({'type': 'column'})

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Configure the series of the chart from the dataframe data.
chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]})

# Insert the chart into the worksheet.
worksheet.insert_chart(1, 3, chart)

输出结果如下：

完整示例参见“带图表的 Pandas Excel 输出”。 （Example: Pandas Excel output with a chart）

为 DataFrame 输出添加条件格式

另一种做法是应用条件格式：

# Apply a conditional format to the required cell range.
worksheet.conditional_format(1, max_col, max_row, max_col,
                             {'type': '3_color_scale'})

结果如下：

完整示例参见“带条件格式的 Pandas Excel 输出”，并参阅文档中的“使用条件格式”一节。 （Example: Pandas Excel output with conditional formatting；Working with Conditional Formatting）

设置 DataFrame 输出格式

除了表头、索引单元格、日期或日期时间单元格等默认格式之外，XlsxWriter 与 Pandas 对 DataFrame 输出格式的支持很有限。而且，已经应用默认格式的单元格无法重新设置格式。

如果需要精细控制 DataFrame 输出的格式，通常更适合从 Pandas 提取原始数据，再直接使用 XlsxWriter。不过，仍有一些格式选项可用。

例如，可以通过 Pandas 接口设置默认日期和日期时间格式：

writer = pd.ExcelWriter("pandas_datetime.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

结果如下：

完整示例参见“包含日期时间的 Pandas Excel 输出”。 （Example: Pandas Excel output with datetimes）

可以使用 set_column() 设置其他非日期、非日期时间列的数据格式： （set_column()）

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})

# Set the column width and format.
worksheet.set_column(1, 1, 18, format1)

# Set the format but not the column width.
worksheet.set_column(2, 2, None, format2)

完整示例参见“带列格式的 Pandas Excel 输出”。 （Example: Pandas Excel output with column formatting）

设置 DataFrame 表头格式

Pandas 使用默认单元格格式写入 DataFrame 表头。由于这是单元格格式，无法使用 set_row() 覆盖。若要自定义表头格式，最佳做法是关闭 Pandas 的自动表头，再自行写入。例如： （set_row()）

# Turn off the default header and skip one row to allow us to insert a
# user defined header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

完整示例参见“使用自定义表头格式的 Pandas Excel 输出”。 （Example: Pandas Excel output with user defined header format）

将 DataFrame 添加为工作表表格

如“使用工作表表格”一节所述，Excel 表格可以将一片单元格区域组合为单个实体，如下所示： （Working with Worksheet Tables）

对于 Pandas DataFrame，应先写入数据，不包含索引或表头；起始位置向后移动一行，为表格表头留出空间：

df.to_excel(writer, sheet_name='Sheet1',
            startrow=1, header=False, index=False)

接着创建表头列表，供 add_table() 使用：

column_settings = [{'header': column} for column in df.columns]

最后，根据 DataFrame 的形状，以及从其列名生成的表头，添加 Excel 表格结构：

(max_row, max_col) = df.shape

worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

完整示例参见“带工作表表格的 Pandas Excel 输出”。 （Example: Pandas Excel output with a worksheet table）

为 DataFrame 输出添加自动筛选

如“使用自动筛选”一节所述，Excel 自动筛选可以筛选二维数据区域，只显示符合用户定义条件的行。 （Working with Autofilters）

对于 Pandas DataFrame，先写入不含索引的数据；如果希望将索引也纳入筛选区域，则可以保留索引：

df.to_excel(writer, sheet_name='Sheet1', index=False)

然后取得 DataFrame 的形状并添加自动筛选：

worksheet.autofilter(0, 0, max_row, max_col - 1)

也可以添加可选的筛选条件。条件中的占位符“Region”会被忽略，可以替换为任何有助于理解表达式的字符串：

worksheet.filter_column(0, 'Region == East')

但只设置条件还不够，还必须隐藏不匹配的行。这里使用 Pandas 找出需要隐藏的行：

for row_num in (df.index[(df['Region'] != 'East')].tolist()):
    worksheet.set_row(row_num + 1, options={'hidden': True})

得到的已筛选工作表如下：

完整示例参见“带自动筛选的 Pandas Excel 输出”。 （Example: Pandas Excel output with an autofilter）

处理多个 Pandas DataFrame

可以把多个 DataFrame 写入同一张或不同的工作表。例如，将多个 DataFrame 写入多张工作表：

# Write each dataframe to a different worksheet.
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')

完整示例参见“包含多个 DataFrame 的 Pandas Excel 文件”。 （Example: Pandas Excel with multiple dataframes）

也可以在同一张工作表中为多个 DataFrame 指定不同的位置：

# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

# Write the dataframe without the header and index.
df4.to_excel(writer, sheet_name='Sheet1',
             startrow=7, startcol=4, header=False, index=False)

完整示例参见“Pandas Excel DataFrame 定位”。 （Example: Pandas Excel dataframe positioning）

向 Pandas 传递 XlsxWriter 构造函数选项

XlsxWriter 支持多种 Workbook() 构造函数选项，例如 strings_to_urls()。通过 engine_kwargs 关键字，也可以将这些选项应用于 Pandas 创建的 Workbook 对象： （Workbook()）

writer = pd.ExcelWriter('pandas_example.xlsx',
                        engine='xlsxwriter',
                        engine_kwargs={'options': {'strings_to_numbers': True}})

注意，Pandas 1.3.0 之前的版本使用以下语法：

writer = pd.ExcelWriter('pandas_example.xlsx',
                        engine='xlsxwriter',
                        options={'strings_to_numbers': True})

将 DataFrame 输出保存到字符串

也可以将 Pandas XlsxWriter DataFrame 输出写入字节数组：

import pandas as pd
import io

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

output = io.BytesIO()

# Use the BytesIO object as the filehandle.
writer = pd.ExcelWriter(output, engine='xlsxwriter')

# Write the data frame to the BytesIO object.
df.to_excel(writer, sheet_name='Sheet1')

writer.close()
xlsx_data = output.getvalue()

# Do something with the data...

注意：此功能要求 Pandas >= 0.17。

更多 Pandas 与 Excel 资料

以下是一些与 Pandas、Excel 和 XlsxWriter 有关的补充资源。

• 本文档后面的 XlsxWriter Pandas 示例：“Pandas 与 XlsxWriter 示例”。 （Pandas with XlsxWriter Examples）

• Pandas 文档中的 pandas.DataFrame.to_excel() 方法。 （pandas.DataFrame.to_excel() method）

• 更详细的“使用 Pandas 与 XlsxWriter 创建 Excel 图表”教程。 （Using Pandas and XlsxWriter to create Excel
  charts）

• “Practical Business Python”网站上关于“使用 Pandas 与 Excel”的系列文章。 （Using Pandas and Excel）