pandas pd.ExcelWriter 对象

看过来

《pandas 教程》持续更新中，提供建议、纠错、催更等加作者微信: gr99123（备注：pandas教程）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《Python之光》。

pandas.ExcelWriter 是 Pandas 库中用于将 DataFrame 对象写入 Excel 文件的类。通过 ExcelWriter，用户可以灵活地控制写入过程，例如选择写入引擎、设置日期格式、选择写入模式（覆盖或追加）、处理多个工作表等。它在生成报表、数据备份以及将分析结果导出为 Excel 文件时非常有用。

构造类

类的构造参数如下：

class pandas.ExcelWriter(
    path, 
    engine=None, 
    date_format=None, 
    datetime_format=None, 
    mode='w', 
    storage_options=None, 
    if_sheet_exists=None, 
    engine_kwargs=None
)

属性和方法

- pandas pd.ExcelWriter 对象的属性和方法

参数详解

path
- 类型: str、pathlib.Path、file-like object
- 默认值: 无
- 意义: 指定要写入的 Excel 文件的路径、URL 或文件对象。可以是本地文件路径、远程存储路径（如 S3）、URL 链接或已打开的文件对象。
- 示例:

# 本地文件路径
writer = pd.ExcelWriter('output.xlsx')

# 使用 pathlib.Path
from pathlib import Path
writer = pd.ExcelWriter(Path('output.xlsx'))

engine
- 类型: {'auto', 'openpyxl', 'xlsxwriter', 'odf', 'pyxlsb'}
- 默认值: 'auto'
- 意义: 指定用于写入 Excel 文件的引擎。不同的引擎支持不同的文件格式和特性。
  - 'auto': 自动选择可用的引擎（优先选择 openpyxl，如果不可用则选择其他可用引擎）。
  - 'openpyxl': 用于 .xlsx 文件，支持丰富的格式和功能。
  - 'xlsxwriter': 用于 .xlsx 文件，提供高级格式控制和图表功能。
  - 'odf': 用于 OpenDocument 格式（如 .ods）。
  - 'pyxlsb': 用于二进制 Excel 文件（.xlsb）。
- 示例:

# 明确指定使用 openpyxl 引擎
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')

# 使用 xlsxwriter 引擎
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')

date_format
- 类型: str 或 None
- 默认值: None
- 意义: 指定日期格式字符串，用于格式化写入 Excel 文件中的日期列。例如，'%Y-%m-%d' 表示“年-月-日”格式。
- 示例:

writer = pd.ExcelWriter('output.xlsx', date_format='%Y-%m-%d')

datetime_format
- 类型: str 或 None
- 默认值: None
- 意义: 指定日期时间格式字符串，用于格式化写入 Excel 文件中的日期时间列。例如，'%Y-%m-%d %H:%M:%S' 表示“年-月-日时:分:秒”格式。
- 示例:

writer = pd.ExcelWriter('output.xlsx', datetime_format='%Y-%m-%d %H:%M:%S')

mode
- 类型: {'w', 'a'}
- 默认值: 'w'
- 意义: 指定写入模式。
  - 'w': 写入模式，若文件存在则覆盖。
  - 'a': 追加模式，向现有文件中添加数据（仅支持部分引擎，如 openpyxl）。
- 示例:

# 覆盖写入
writer = pd.ExcelWriter('output.xlsx', mode='w')

# 追加写入
writer = pd.ExcelWriter('output.xlsx', mode='a', engine='openpyxl')

storage_options
- 类型: dict 或 None
- 默认值: None
- 意义: 指定与存储后端连接的额外选项，如认证信息、访问权限等。适用于写入远程存储（如 S3、GCS）中的 Excel 文件。
- 示例:

# 写入存储在 S3 上的 Excel 文件
storage_opts = {
    'key': 'your_access_key',
    'secret': 'your_secret_key',
    'client_kwargs': {
        'endpoint_url': 'https://s3.amazonaws.com'
    }
}
writer = pd.ExcelWriter('s3://my-bucket/output.xlsx', storage_options=storage_opts)

if_sheet_exists
- 类型: {'error', 'new', 'replace'}
- 默认值: 'error'
- 意义: 指定当要写入的工作表已经存在时的处理方式。
  - 'error': 抛出错误，默认行为。
  - 'new': 创建一个新的工作表。
  - 'replace': 替换已存在的工作表。
- 示例:

# 替换已存在的工作表
writer = pd.ExcelWriter('output.xlsx', if_sheet_exists='replace')

engine_kwargs
- 类型: dict 或 None
- 默认值: None
- 意义: 传递给引擎的其他关键字参数，用于进一步控制 Excel 文件的写入方式。具体参数取决于所使用的引擎。
- 示例:

# 使用 openpyxl 引擎并传递额外参数
engine_kwargs = {
    'options': {'strings_to_numbers': True}
}
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl', engine_kwargs=engine_kwargs)

返回值

类型: pandas.ExcelWriter 对象
意义: 返回一个 ExcelWriter 对象，用于进一步操作和写入数据。

使用场景

生成报表: 将分析结果导出为 Excel 文件，方便分享和存档。
数据备份: 将数据持久化存储到 Excel 文件中，防止数据丢失。
多工作表写入: 在同一个 Excel 文件中写入多个工作表，组织不同类型的数据。
格式控制: 通过指定日期格式、数据类型等参数，控制写入 Excel 文件的数据格式。
追加数据: 向已有的 Excel 文件中追加新的数据，而不覆盖原有内容（需支持的引擎）。

示例

示例 1：将单个 DataFrame 写入 Excel 文件

import pandas as pd

# 构造示例数据
data = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '年龄': [30, 25, 35],
    '城市': ['纽约', '洛杉矶', '芝加哥']
}
df = pd.DataFrame(data)

# 创建 ExcelWriter 对象
with pd.ExcelWriter('output.xlsx') as writer:
    # 将 DataFrame 写入 'Sheet1'
    df.to_excel(writer, sheet_name='Sheet1', index=False)

输出文件: output.xlsx 包含一个名为 Sheet1 的工作表，数据如下：

姓名    年龄    城市
艾丽丝   30    纽约
鲍勃    25   洛杉矶
查理    35   芝加哥

示例 2：将多个 DataFrame 写入不同工作表

import pandas as pd

# 构造示例数据
data1 = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '年龄': [30, 25, 35],
    '城市': ['纽约', '洛杉矶', '芝加哥']
}
df1 = pd.DataFrame(data1)

data2 = {
    '产品': ['A', 'B', 'C'],
    '销量': [1000, 800, 600],
    '收入': [50000, 40000, 30000]
}
df2 = pd.DataFrame(data2)

# 创建 ExcelWriter 对象
with pd.ExcelWriter('output_multiple_sheets.xlsx') as writer:
    # 将第一个 DataFrame 写入 'Sheet1'
    df1.to_excel(writer, sheet_name='人员信息', index=False)
    
    # 将第二个 DataFrame 写入 'Sheet2'
    df2.to_excel(writer, sheet_name='销售数据', index=False)

输出文件: output_multiple_sheets.xlsx 包含两个工作表：

人员信息
销售数据

工作表名称: 人员信息
姓名    年龄    城市
艾丽丝   30    纽约
鲍勃    25   洛杉矶
查理    35   芝加哥


工作表名称: 销售数据
产品   销量    收入
A    1000  50000
B     800  40000
C     600  30000

示例 3：指定日期格式写入 Excel 文件

import pandas as pd
from datetime import datetime

# 构造示例数据
data = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '出生日期': [datetime(1990, 1, 1), datetime(1995, 5, 15), datetime(1985, 7, 23)]
}
df = pd.DataFrame(data)

# 创建 ExcelWriter 对象并指定日期格式
with pd.ExcelWriter('output_dates.xlsx', date_format='%Y-%m-%d') as writer:
    df.to_excel(writer, sheet_name='出生信息', index=False)

输出文件: output_dates.xlsx 中的 出生信息 工作表，日期格式为 YYYY-MM-DD。

姓名    出生日期
艾丽丝   1990-01-01
鲍勃    1995-05-15
查理    1985-07-23

示例 4：向现有 Excel 文件追加工作表

假设已有一个名为 output_append.xlsx 的 Excel 文件，包含一个工作表 Sheet1。现在需要向其中追加一个新的工作表 Sheet2。

import pandas as pd

# 构造新数据
data_new = {
    '产品': ['D', 'E'],
    '销量': [400, 200],
    '收入': [20000, 10000]
}
df_new = pd.DataFrame(data_new)

# 创建 ExcelWriter 对象，指定模式为追加
with pd.ExcelWriter('output_append.xlsx', mode='a', engine='openpyxl') as writer:
    # 将新 DataFrame 写入 'Sheet2'
    df_new.to_excel(writer, sheet_name='Sheet2', index=False)

输出文件: output_append.xlsx 现在包含两个工作表 Sheet1 和 Sheet2。

工作表名称: Sheet1
姓名    年龄    城市
艾丽丝   30    纽约
鲍勃    25   洛杉矶
查理    35   芝加哥

工作表名称: Sheet2
产品   销量    收入
D    400   20000
E    200   10000

示例 5：指定写入模式和处理已存在的工作表

import pandas as pd

# 构造示例数据
data = {
    '姓名': ['大卫', '伊丽莎'],
    '年龄': [28, 32],
    '城市': ['波士顿', '旧金山']
}
df = pd.DataFrame(data)

# 创建 ExcelWriter 对象，指定模式为覆盖，并替换已存在的工作表
with pd.ExcelWriter('output_if_exists.xlsx', mode='w', if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name='人员信息', index=False)

输出文件: output_if_exists.xlsx 中的 人员信息 工作表被替换为新的数据。

姓名    年龄    城市
大卫    28    波士顿
伊丽莎   32   旧金山

示例 6：将多个 DataFrame 写入同一个工作表中的不同区域

import pandas as pd

# 构造示例数据
data1 = {
    '姓名': ['艾丽丝', '鲍勃', '查理'],
    '年龄': [30, 25, 35],
    '城市': ['纽约', '洛杉矶', '芝加哥']
}
df1 = pd.DataFrame(data1)

data2 = {
    '产品': ['A', 'B', 'C'],
    '销量': [1000, 800, 600],
    '收入': [50000, 40000, 30000]
}
df2 = pd.DataFrame(data2)

# 创建 ExcelWriter 对象
with pd.ExcelWriter('output_multiple_regions.xlsx', engine='xlsxwriter') as writer:
    # 将第一个 DataFrame 写入 'Sheet1' 的 A1 单元格
    df1.to_excel(writer, sheet_name='Sheet1', startrow=0, startcol=0, index=False)
    
    # 将第二个 DataFrame 写入 'Sheet1' 的 E1 单元格
    df2.to_excel(writer, sheet_name='Sheet1', startrow=0, startcol=4, index=False)

输出文件: output_multiple_regions.xlsx 的 Sheet1 中，df1 数据位于 A1 单元格开始，df2 数据位于 E1 单元格开始。

A1 单元格开始:
姓名    年龄    城市
艾丽丝   30    纽约
鲍勃    25   洛杉矶
查理    35   芝加哥

E1 单元格开始:
产品   销量    收入
A    1000  50000
B     800  40000
C     600  30000

示例 7：使用自定义引擎参数写入 Excel 文件

import pandas as pd

# 构造示例数据
data = {
    '姓名': ['张三', '李四', '王五'],
    '部门': ['IT', '销售', '人事'],
    '工资': [7000, 6500, 6000]
}
df = pd.DataFrame(data)

# 自定义引擎参数，例如 xlsxwriter 的格式设置
engine_kwargs = {
    'options': {'strings_to_numbers': True}
}

# 创建 ExcelWriter 对象并传递引擎参数
with pd.ExcelWriter('output_engine_kwargs.xlsx', engine='xlsxwriter', engine_kwargs=engine_kwargs) as writer:
    df.to_excel(writer, sheet_name='员工信息', index=False)

输出文件: output_engine_kwargs.xlsx 的 员工信息 工作表，数据按照自定义引擎参数进行格式设置。

员工信息工作表内容:
姓名    部门    工资
张三    IT    7000
李四   销售    6500
王五   人事    6000

示例 8：结合 `ExcelWriter` 和 `ExcelFile` 进行高级操作

import pandas as pd

# 构造示例数据
data_existing = {
    '姓名': ['赵六', '周七'],
    '年龄': [28, 32],
    '城市': ['北京', '上海']
}
df_existing = pd.DataFrame(data_existing)

data_new = {
    '姓名': ['孙八', '吴九'],
    '年龄': [26, 29],
    '城市': ['广州', '深圳']
}
df_new = pd.DataFrame(data_new)

# 首先，创建一个包含初始数据的 Excel 文件
with pd.ExcelWriter('output_combined.xlsx', mode='w', engine='openpyxl') as writer:
    df_existing.to_excel(writer, sheet_name='人员信息', index=False)

# 使用 ExcelWriter 追加新的数据到同一个工作表
with pd.ExcelWriter('output_combined.xlsx', mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    # 获取现有的人员信息工作表
    book = writer.book
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    
    # 追加新的数据到 '人员信息' 工作表
    startrow = writer.sheets['人员信息'].max_row
    
    df_new.to_excel(writer, sheet_name='人员信息', index=False, header=False, startrow=startrow)

输出文件: output_combined.xlsx 的 人员信息 工作表包含初始数据和追加的数据。

人员信息工作表内容:
姓名    年龄    城市
赵六    28    北京
周七    32    上海
孙八    26    广州
吴九    29    深圳

总结

ExcelWriter 类 提供了一种灵活而强大的方法，将 Pandas 的 DataFrame 数据写入 Excel 文件。通过配置不同的参数，用户可以控制写入过程中的各种细节，如选择写入引擎、设置日期格式、决定写入模式（覆盖或追加）等。
使用场景 包括生成复杂的报表、备份数据、向多个工作表中写入数据以及与其他系统集成等。特别是在需要写入多个工作表或处理大型数据集时，ExcelWriter 显得尤为重要。
结合 ExcelFile 使用，可以实现更高级的数据读取和写入操作，提升数据处理的效率和灵活性。

掌握 pandas.ExcelWriter 的使用方法，能够帮助用户高效地将数据导出为 Excel 格式，满足各种数据分享、报告生成和存档需求。

参考

https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html