15  写入Excel文件

15.1 引言数据导出的必要性与Excel在金融领域的地位

在金融数据分析和商业智能项目中,数据导出是工作流程的关键环节。分析人员经常需要将处理好的数据导出为Excel格式,原因包括:

  1. 报告生成: 向管理层或客户呈现分析结果
  2. 跨部门协作: 与非技术人员(如业务部门、财务部门)共享数据
  3. 审计合规: 保存分析过程的中间结果和最终输出
  4. 进一步分析: 利用Excel的透视表、图表等功能进行探索性分析

15.2 本章学习目标

通过本章学习,你将能够:

  1. 使用 df.to_excel 的 sheet_name、startrow/startcol、index、header 参数控制导出位置与内容
  2. 使用 na_rep、inf_rep 自定义缺失值与无穷大在 Excel 中的呈现
  3. 使用 pd.ExcelWriter 上下文管理器向同一文件、同一工作表多次写入,并创建多工作表报表
  4. 根据数据量、受众与用途,在 CSV 与 Excel 之间选择导出格式

先修内容:第 章节 14 章的 Excel 读取方法。

补充说明:Excel文件格式的技术演进

Excel文件经历了多次格式演变,每种格式都有其特点:

格式 扩展名 特点 适用场景
XLS .xls Excel 97-2003格式,专有二进制格式 兼容老版本Excel
XLSX .xlsx Excel 2007+格式,基于Office Open XML标准 现代Excel工作的标准格式
XLSB .xlsb 二进制格式,文件更小,加载更快 大数据量场景
CSV .csv 纯文本,逗号分隔值 跨平台数据交换

Pandas主要通过openpyxl引擎写入XLSX文件,通过xlsxwriter引擎实现高级格式化功能。

15.3 to_excel函数基础导出方法

数学背景:数据序列化与持久化

数据持久化(Data Persistence)是将程序中的数据保存到非易失性存储设备(如硬盘)的过程。在写入Excel时,需要处理以下技术挑战:

  1. 数据类型映射: Python类型 → Excel类型
    • int64 → Excel数值
    • float64 → Excel数值
    • datetime64 → Excel日期时间
    • bool → Excel逻辑值
    • str → Excel文本
  2. 特殊值处理:
    • NaN (Not a Number) → 缺失值标记
    • inf (无穷大) → 默认写为字符串 inf(inf_rep 参数默认值),可用 inf_rep='<INF>' 自定义
    • -inf (负无穷大) → 同上

任务要求:构造含日期时间、缺失值(NaN)、无穷大(inf)、整数与布尔列的 3 行数据框 df 并命名其索引;先用 to_excel 导出为 written_with_pandas1.xlsx(从第 2 行第 2 列写起,na_rep='<NA>'、inf_rep='<INF>');再用 ExcelWriter 上下文管理器创建 written_with_pandas2.xlsx,向 Sheet1 两处不同位置各写一次、再向 Sheet2 写一次;最后打印 df。请将代码原样输入教学平台(注释除外),判定以平台为准。

列表 15.1: 平台原始代码 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import numpy as np  # 导入NumPy数值计算库
import pandas as pd  # 导入Pandas数据分析库
import datetime as dt  # 导入日期时间处理模块

data=[[dt.datetime(2020,1,1, 10, 13), 2.222, 1, True],  # 定义列表data
      [dt.datetime(2020,1,2), np.nan, 2, False],  # 第二行数据（含缺失值NaN）
      [dt.datetime(2020,1,2), np.inf, 3, True]]  # 第三行数据（含无穷大inf）
df = pd.DataFrame(data=data,columns=["Dates", "Floats", "Integers", "Booleans"])  # 创建数据框df
df.index.name="index"  # 设置数据框索引列的名称

# 将数据框导出至Excel文件，指定工作表名和写入参数
df.to_excel("written_with_pandas1.xlsx", sheet_name="Output",
            startrow=1, startcol=1, index=True, header=True,  # 设置写入Excel时的起始行列位置和索引/表头选项
            na_rep="<NA>", inf_rep="<INF>")  #
            
with pd.ExcelWriter("written_with_pandas2.xlsx") as writer:  # 使用上下文管理器
  # 将数据框写入Sheet1工作表的第2行第2列位置
  df.to_excel(writer, sheet_name="Sheet1", startrow=1, startcol=1)
  # 将数据框再次写入Sheet1工作表的第11行位置
  df.to_excel(writer, sheet_name="Sheet1", startrow=10, startcol=1)
  df.to_excel(writer, sheet_name="Sheet2")  # 将数据框写入Excel文件

print(df)  # 输出数据框数据

预期输出(本机 Python 实际运行结果,具体以平台运行结果为准):

                    Dates  Floats  Integers  Booleans
index
0     2020-01-01 10:13:00   2.222         1      True
1     2020-01-02 00:00:00     NaN         2     False
2     2020-01-02 00:00:00     inf         3      True

运行后工作目录会生成 written_with_pandas1.xlsx 与 written_with_pandas2.xlsx 两个文件(本机已验证可成功写出):第一个文件的数据从 B2 单元格开始,NaN 所在单元格写入字符串 <NA>、inf 所在单元格写入 <INF>;第二个文件的 Sheet1 中有两段相同数据(分别自第 2 行与第 11 行开始),Sheet2 中有一段。

代码深度解析:

15.3.1 第一部分to_excel函数参数详解

  1. startrow和startcol参数的作用:

    • startrow=1: 数据从Excel的第2行开始(第1行留给其他内容,如报告标题)
    • startcol=1: 数据从Excel的第2列开始(第1列留给行号或其他标识)
    • 这种灵活性允许在一个Excel文件中创建复杂的布局,如多表格报告

  2. 特殊值处理参数:

    • na_rep: 控制缺失值的显示方式
      • 默认:空单元格
      • 本例中: "<NA>"明确标记缺失值
      • 其他常用值: "NA", "-", "NULL"
    • inf_rep: 控制无穷大的显示方式
      • 默认:写为字符串 inf(inf_rep 参数默认值为 'inf')
      • 本例中: "<INF>"自定义表示
      • 其他常用值: "Infinity", "∞"

  3. 数据类型转换:

    # Pandas内部执行的数据类型映射
- datetime64[ns] → Excel日期时间(序列号)
- float64 (NaN) → Excel空单元格(或na_rep指定的值)
- float64 (inf) → 字符串 `inf`(或inf_rep指定的值)
- bool → Excel的TRUE/FALSE

15.3.2 第二部分ExcelWriter类的优势

理论背景:上下文管理器与资源管理

ExcelWriter使用了Python的上下文管理器(Context Manager)模式,通过with语句实现:

with pd.ExcelWriter("file.xlsx") as writer:
    # 执行操作
# 自动关闭文件,释放资源

这种设计的优势:

  • 自动资源管理: 无论是否发生异常,文件都会正确关闭
  • 异常安全: 即使写入过程中出错,也能保证文件完整性
  • 代码简洁: 不需要显式调用close()方法

同一工作表多次写入的原理:

  • ExcelWriter不会覆盖已有内容,而是从指定位置开始写入

  • 这允许创建复杂的报表布局,如:

    [标题区]
[数据表1]
[空行]
[数据表2]

多工作表管理的优势:

  • 数据分区: 将不同类型的数据放在不同工作表
    • Sheet1: 原始数据
    • Sheet2: 计算指标
    • Sheet3: 图表数据
  • 权限控制: 可以对不同工作表设置不同的访问权限
  • 性能优化: 大数据集可以分成多个工作表,提高加载速度

to_excel vs ExcelWriter对比:

特性 to_excel ExcelWriter
单工作表 ✓ ✓
多工作表 ✗ ✓
同表多次写入 ✗ ✓
代码复杂度 简单 稍复杂
资源管理 自动 需用with语句

15.4 实际应用场景

  1. 财务报表导出: 将计算好的财务指标导出为Excel,供审计使用
  2. 交易报告生成: 每日交易结束后,生成交易汇总报告
  3. 数据存档: 将处理后的历史数据保存为Excel,便于离线分析

补充说明:大数据量处理策略

当处理大规模金融数据时(如百万级交易记录),直接导出到Excel可能遇到性能瓶颈:

  1. Excel的行数限制:
    • XLS格式(旧版): 65,536行
    • XLSX格式(新版): 1,048,576行
  2. 性能优化策略:
    • 分批写入:将大数据集分成多个小文件
    • 数据抽样:导出代表性样本
    • 聚合后导出:先汇总再导出
  3. 文件大小优化:
    • 使用XLSB二进制格式(文件更小)
    • 压缩数据(删除不必要的列、降低精度)
    • 分割文件(按时间、类别等)

易混淆概念辨析:to_csv vs to_excel

特性 CSV Excel
文件大小 小(纯文本) 大(包含格式)
读取速度 快 慢
支持多工作表 ✗ ✓
支持格式化 ✗ ✓
跨平台兼容性 极好 需Excel
适用场景 数据交换、大数据集 报告、可视化

选择建议:

  • 数据备份/迁移 → CSV
  • 向非技术人员展示 → Excel
  • 大数据量(>100万行) → CSV或数据库
  • 需要多表格报告 → Excel

15.5 本章小结

要点:

  • to_excel 一次写一个工作表;ExcelWriter 配合 with 支持同文件多工作表、同工作表多处写入
  • startrow/startcol 从 0 计数,startrow=1, startcol=1 表示数据从 B2 单元格开始
  • na_rep/inf_rep 控制缺失值与无穷大落盘时的呈现,便于人工审阅
  • 大数据量导出优先 CSV,正式报告与人机交互优先 Excel

易错点:

  • 不用 with 而直接创建 ExcelWriter 时,文件要等 close() 或进程结束才完整落盘
  • 同一工作表多次写入必须错开 startrow/startcol,写在同一位置会相互覆盖
  • XLSX 单表上限约 1048576 行,超出会报错,此时应改用 CSV 或分批导出

15.6 动手与思考

以下练习每题附参考答案(默认折叠)。请先独立完成并写下你的判断,再点开对照,最后上机验证。

  1. 输出预测:不运行代码,先写出下面代码的输出结果,再上机检验你的判断。

    import pandas as pd

df = pd.DataFrame({'a': [1, 2]})
df.to_excel('t.xlsx', index=False)
back = pd.read_excel('t.xlsx')
print(back)
print(back.columns.tolist())

    参考答案（先写下你的预测再点开）

    解题思路：to_excel('t.xlsx', index=False) 把数据写入文件且不写行索引——A1 单元格是列名 a，A2、A3 是 1 和 2，仅此三格。read_excel 默认 header=0（第 1 行作表头），于是 A1 的 a 成为列名，A2、A3 是两行数据；行索引 0、1 是读入时 pandas 重新生成的 RangeIndex，因为原索引根本没有落盘。back 是 2 行 1 列的数据框；back.columns.tolist() 把列名取成 Python 列表 ['a']。

    # 验证脚本：导出后读回，观察index=False对落盘内容的影响
import pandas as pd  # 导入Pandas库

df = pd.DataFrame({'a': [1, 2]})  # 2行1列的小表，行索引为0、1
df.to_excel('t.xlsx', index=False)  # 导出时不写行索引
back = pd.read_excel('t.xlsx')  # 读回：首行作表头，行索引重新生成
print(back)  # 输出读回的数据框
print(back.columns.tolist())  # 输出列名列表

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

       a
0  1
1  2
['a']

    回扣本章：对应本章小结“要点”第 1 条——index 参数控制是否把行索引写入文件；index=False 写出的文件读回后行索引是重新生成的。

  2. 概念辨析:to_csv 与 to_excel 在文件大小、读写速度、多工作表与格式化能力上如何取舍?to_excel 与 ExcelWriter 的分工是什么?

    参考答案（点开前请先独立完成）

    解题思路：逐点作答。第一，to_csv 与 to_excel 的取舍：CSV 是纯文本，文件小、读写快、跨平台兼容性极好，但只有一张“表”、没有任何格式信息；Excel 文件体积大、读写慢，但支持多工作表、单元格格式与人工审阅批注。选择口径：数据备份、迁移、程序间交换，或数据量超过 XLSX 单表约 1048576 行的上限时，选 CSV；面向管理层或业务部门的正式报告、需要多表格布局与人机交互的场合，选 Excel。第二，to_excel 与 ExcelWriter 的分工：to_excel 是“一次调用写一个工作表”的便捷方法，直接给文件名即可；ExcelWriter 是上下文管理器，必须配合 with 使用，退出 with 时自动保存并关闭——它支持向同一文件的多个工作表写入，也支持向同一工作表的多个位置反复写入（用 startrow/startcol 错开），这是裸 to_excel 做不到的。

    回扣本章：对应本章小结“要点”第 1、4 条——大数据量导出优先 CSV，正式报告与人机交互优先 Excel；多工作表、多处写入必须用 ExcelWriter。

  3. 变式任务(平台任务同型改造):把平台任务中的 df 写入同一文件的 Sheet1 两处(错开位置)与 Sheet2 一处,再用 read_excel 配合 skiprows/nrows 把 Sheet1 的两段数据分别读回来。

    参考答案（点开前请先独立完成）

    解题思路：df 已由 列表 15.1 定义（3 行数据、Dates/Floats/Integers/Booleans 四列、索引名 index）。写入端用 ExcelWriter：Sheet1 第一段 startrow=1, startcol=1（数据自 B2 起），第二段错开到 startrow=6, startcol=1（自 B7 起），每段各占“1 行表头 + 3 行数据”；再向 Sheet2 默认位置写一段。读回端要算清行号：第 1 段表头在 Excel 第 2 行，skiprows=1 跳过上方空行后表头即被正确识别，nrows=3 取回 3 行数据，usecols='B:F' 只取 5 列数据区；第 2 段表头在 Excel 第 7 行，故 skiprows=6。两段读回后与原 df 数值一致。

    # 变式程序：先按 @lst-ch15-platform-task 重建同表 df，再同表两处写入并分别读回
import numpy as np  # 导入NumPy库
import pandas as pd  # 导入Pandas库
import datetime as dt  # 导入日期时间模块
data = [[dt.datetime(2020, 1, 1, 10, 13), 2.222, 1, True],  # 重建3行样例数据（含NaN与inf）
        [dt.datetime(2020, 1, 2), np.nan, 2, False],
        [dt.datetime(2020, 1, 2), np.inf, 3, True]]
df = pd.DataFrame(data=data, columns=['Dates', 'Floats', 'Integers', 'Booleans'])  # 创建数据框df
df.index.name = 'index'  # 设置索引列名称

with pd.ExcelWriter('variant_report.xlsx') as writer:  # 打开上下文管理器，可向同一文件多处写入
    df.to_excel(writer, sheet_name='Sheet1', startrow=1, startcol=1)  # 第1段：数据自B2单元格起
    df.to_excel(writer, sheet_name='Sheet1', startrow=6, startcol=1)  # 第2段：错开位置，自B7单元格起
    df.to_excel(writer, sheet_name='Sheet2')  # Sheet2：默认位置写一段
segment_1 = pd.read_excel('variant_report.xlsx', sheet_name='Sheet1', skiprows=1, nrows=3, usecols='B:F')  # 第1段表头在Excel第2行，跳过上方1行
segment_2 = pd.read_excel('variant_report.xlsx', sheet_name='Sheet1', skiprows=6, nrows=3, usecols='B:F')  # 第2段表头在Excel第7行，故跳过6行
print(segment_1)  # 读回第1段
print(segment_2)  # 读回第2段

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

       index               Dates  Floats  Integers  Booleans
0      0 2020-01-01 10:13:00   2.222         1      True
1      1 2020-01-02 00:00:00     NaN         2     False
2      2 2020-01-02 00:00:00     inf         3      True
   index               Dates  Floats  Integers  Booleans
0      0 2020-01-01 10:13:00   2.222         1      True
1      1 2020-01-02 00:00:00     NaN         2     False
2      2 2020-01-02 00:00:00     inf         3      True

    注意：以上为本题变式的独立代码；列表 15.1 对应的平台原始代码仍须原样输入教学平台，不要用本变式替换。

    回扣本章：对应本章小结“要点”第 1 条与“易错点”第 2 条——ExcelWriter 支持同文件多工作表、同工作表多处写入；多处写入必须错开 startrow/startcol，否则相互覆盖。

  4. 动手验证:分别用 na_rep='<NA>' 与默认方式导出含 NaN 的表,再用 pd.read_excel(..., keep_default_na=False) 读回,比较两种写法下单元格内容的差异。

    参考答案（点开前请先独立完成）

    解题思路：两种写法的落盘内容不同：na_rep='<NA>' 把缺失值写成可见的字符串 <NA>；默认写法把缺失值留成空单元格。keep_default_na=False 关闭读回时“把空单元格与默认 NA 字符串自动识别为缺失”的规则，于是差异被原样暴露：前者读回的 '<NA>' 保持字符串形态；后者读回为空字符串（显示为空白），两者都不再是 NaN。这正说明 na_rep 的定位是“人工可读的缺失标记”——落盘可见，但是否被还原为缺失值取决于读回端的 NA 解析设置；若不加 keep_default_na=False，空单元格与 <NA> 都会被自动还原成 NaN。

    # 验证脚本：对比na_rep与默认写法在读回端的差异
import numpy as np  # 导入NumPy库，用于生成NaN
import pandas as pd  # 导入Pandas库

price_table = pd.DataFrame({'收盘价': [10.5, np.nan, 11.2]})  # 含一个NaN的3行小表
price_table.to_excel('na_tagged.xlsx', index=False, na_rep='<NA>')  # 用'<NA>'显式标记缺失值
price_table.to_excel('na_default.xlsx', index=False)  # 默认写法：缺失值写成空单元格
print(pd.read_excel('na_tagged.xlsx', keep_default_na=False))  # 关闭默认NA识别：'<NA>'按普通字符串读回
print(pd.read_excel('na_default.xlsx', keep_default_na=False))  # 空单元格读回为空字符串，而非NaN

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

        收盘价
0  10.5
1  <NA>
2  11.2
    收盘价
0  10.5
1
2  11.2

    回扣本章：对应本章小结“要点”第 3 条——na_rep/inf_rep 控制缺失值与无穷大落盘时的呈现，便于人工审阅。