Excel文件操作

Python 电子表格处理：Excel 多工作表读写与 ExcelWriter 实战

小象实战讲义 · Python数据分析实战

在企业日常运营、财务核算与跨部门协同中，Microsoft Excel（.xlsx / .xls）是应用最广泛的电子表格载体。与纯文本的 CSV 不同，Excel 不仅能承载数据本身，还集成了多工作表（Sheet）、富文本样式、单元格合并以及可视化图表等复杂特性。作为数据分析师，我们既需要从业务部门发来的复杂多 Sheet 表格中快速提取干净数据，也需要将分析结论格式化输出为多标签页的专业 Excel 报表。本节将带你掌握使用 Pandas 高效读写 Excel 文件的完整实战技能。

💡 核心导读

• Excel vs CSV 深度对比：明确纯数据流与富格式工作簿的适用边界。
• pd.read_excel() 核心参数：精通 sheet_name 的多种取值技巧（按索引、按名称、批量读取多 Sheet 与全量加载）。
• 单工作表与多工作表导出：掌握 df.to_excel() 与上下文管理器 pd.ExcelWriter 的多 Sheet 写入规范。
• 底层引擎与依赖管理：理解 openpyxl（处理 .xlsx）与 xlrd（处理旧版 .xls）的角色。
• 多部门月度考核报表实战：通过 Python 代码实现多 Sheet 数据的拆分计算与聚合大表自动生成。

1. Excel vs CSV：数据分析师的视角

很多初学者容易混淆 Excel 与 CSV 的关系。虽然它们都可以用 Excel 软件打开，但底层机制存在根本差异：

┌─────────────────────────────────────────────────────────────┐
│                       Excel vs CSV 核心对比                  │
├───────────────────────────────┬─────────────────────────────┤
│      CSV 格式 (.csv)          │      Excel 格式 (.xlsx)     │
├───────────────────────────────┼─────────────────────────────┤
│ • 纯文本、逗号分隔            │ • 复杂的二进制/XML压缩包     │
│ • 仅支持单个二维表格          │ • 支持一个工作簿内包含多个 Sheet │
│ • 不包含任何颜色、边框与样式  │ • 支持图表、单元格格式与公式 │
│ • 读写极快、内存占用极低      │ • 读写需解析 XML，开销较大   │
│ • 数据分析与建模首选格式      │ • 业务汇报与跨部门交付首选格式│
└───────────────────────────────┴─────────────────────────────┘

数据分析准则：在数据分析处理过程中，我们只关心纯粹的数据内容，而不关心单元格的字体颜色或边框样式。因此，读取 Excel 时通常直接将各个 Sheet 解析为纯净的 Pandas DataFrame。

2. Pandas 读取 Excel：精通 sheet_name 参数

Pandas 提供了顶层函数 pd.read_excel()，其最为核心的控制参数是 sheet_name：

3. 导出多工作表：pd.ExcelWriter 标准范式

如果只是导出单个表格，直接调用 df.to_excel("out.xlsx", sheet_name="数据", index=False) 即可。但如果需要将多个不同的分析结果（如“概要汇总”与“明细列表”）保存在同一个 Excel 文件的不同 Sheet 中，必须使用 pd.ExcelWriter：

# 多 Sheet 写入标准范式（推荐使用 with 上下文管理器）
with pd.ExcelWriter("monthly_report.xlsx", engine="openpyxl") as writer:
    summary_df.to_excel(writer, sheet_name="总体汇总", index=False)
    detail_df.to_excel(writer, sheet_name="详细清单", index=False)
# 离开 with 块时，ExcelWriter 会自动保存并关闭文件

4. Python 代码实战：多部门月度考核报表的合并与多 Sheet 导出

下面我们编写一段 Python 代码，演示如何使用 openpyxl 引擎创建包含多个部门独立 Sheet 的 Excel 文件，并演示全量加载、跨 Sheet 数据汇总与二次多标签页报表生成。

# 示例 1：创建并导出包含多个部门独立 Sheet 的 Excel 原始文件

import pandas as pd
import os

excel_file_path = "company_performance.xlsx"

# 构造技术部与市场部两个部门的数据
tech_team = pd.DataFrame({
    "emp_id": [101, 102, 103],
    "name": ["张伟", "李磊", "陈晨"],
    "kpi_score": [95.0, 88.5, 91.0],
    "project_count": [4, 3, 5]
})

marketing_team = pd.DataFrame({
    "emp_id": [201, 202, 203],
    "name": ["王芳", "赵敏", "刘强"],
    "kpi_score": [89.0, 93.5, 86.0],
    "project_count": [6, 8, 5]
})

# 使用 pd.ExcelWriter 写入多个工作表
with pd.ExcelWriter(excel_file_path, engine="openpyxl") as writer:
    tech_team.to_excel(writer, sheet_name="技术部", index=False)
    marketing_team.to_excel(writer, sheet_name="市场部", index=False)

print(f"✅ 成功创建多 Sheet 考核表格: {excel_file_path}")

接下来，我们使用 sheet_name=None 一次性读取全部工作表，执行全公司绩效汇总分析，并将结果写入新的多 Sheet 报表：

# 示例 2：使用 sheet_name=None 全量读取所有 Sheet 并做合并汇总

# 1. 一次性加载所有工作表 (返回字典)
all_sheets_dict = pd.read_excel(excel_file_path, sheet_name=None)

print(f"\n=== 检测到 Excel 中包含 {len(all_sheets_dict)} 个工作表: {list(all_sheets_dict.keys())} ===")

# 2. 遍历各部门 Sheet 并合并为全公司总表
combined_list = []
summary_rows = []

for dept_name, df_dept in all_sheets_dict.items():
    # 增加部门标识列
    df_dept["department"] = dept_name
    combined_list.append(df_dept)
    
    # 计算该部门汇总指标
    summary_rows.append({
        "部门名称": dept_name,
        "总人数": len(df_dept),
        "平均KPI得分": round(df_dept["kpi_score"].mean(), 2),
        "总完成项目数": df_dept["project_count"].sum()
    })

company_all_df = pd.concat(combined_list, ignore_index=True)
department_summary_df = pd.DataFrame(summary_rows)

print("\n=== 全公司员工合并大表 ===")
print(company_all_df[["department", "emp_id", "name", "kpi_score", "project_count"]])

print("\n=== 各部门综合指标对比表 ===")
print(department_summary_df)

# 3. 将汇总结果与明细结果保存至最终的交付报表
final_report_path = "final_kpi_summary_report.xlsx"
with pd.ExcelWriter(final_report_path, engine="openpyxl") as writer:
    department_summary_df.to_excel(writer, sheet_name="部门总览", index=False)
    company_all_df.to_excel(writer, sheet_name="全员明细", index=False)

print(f"\n✅ 最终分析报表已输出至: {final_report_path}")

# 清理测试文件
for p in [excel_file_path, final_report_path]:
    if os.path.exists(p):
        os.remove(p)

📝 动手练一练

1. 问答题：如果一个企业财务 Excel 文件中包含了名为 2026年1月、2026年2月、2026年3月… 的 12 个工作表，如果想要用最简洁的代码一次性将这 12 个月份的数据全部读取出来，应该如何设置 pd.read_excel() 的 sheet_name 参数？读取后的数据结构是什么？

   👉 点击查看参考答案

   参考答案： 设置参数 sheet_name=None 即可一次性读取所有工作表。 此时函数会返回一个 Python 字典（dict），字典的键（Key）为各个月份的 Sheet 名称（如 '2026年1月'），字典的值（Value）为对应工作表解析出的 Pandas DataFrame。

2. 编程练习：假设你有以下城市空气质量的两个数据字典：bj_data = {"月份": ["1月", "2月"], "AQI": [85, 62]} 和 sh_data = {"月份": ["1月", "2月"], "AQI": [45, 52]}。请编写 Python 代码，使用 pd.ExcelWriter 将这两个数据集分别写入名为 air_quality.xlsx 文件的 北京 和 上海 两个独立工作表中。

   👉 点击查看参考答案

   参考答案：

   import pandas as pd
   import os
   
   df_bj = pd.DataFrame({"月份": ["1月", "2月"], "AQI": [85, 62]})
   df_sh = pd.DataFrame({"月份": ["1月", "2月"], "AQI": [45, 52]})
   
   out_file = "air_quality.xlsx"
   with pd.ExcelWriter(out_file, engine="openpyxl") as writer:
       df_bj.to_excel(writer, sheet_name="北京", index=False)
       df_sh.to_excel(writer, sheet_name="上海", index=False)
       
   print("✅ 成功输出包含北京与上海工作表的 Excel 文件！")
   if os.path.exists(out_file):
       os.remove(out_file)

本章小结

在本节中，我们系统掌握了 Excel 多工作表的高效处理方案：

• 深刻理解 Excel 富格式工作簿与 CSV 纯数据流的设计取向与应用场景；
• 熟练掌握 pd.read_excel() 的 sheet_name 参数实现单表、多表或全量 Sheet 提取；
• 掌握 pd.ExcelWriter 配合 with 语法导出专业的多 Sheet 分析报表。

📋 行动清单

• 确保本地 Python 环境已安装 openpyxl（pip install openpyxl）。
• 尝试用 Pandas 读取手头一个包含多个工作表的业务 Excel 文件，并尝试合并各个 Sheet。

—— 小象教研组