Python Excel文件读写实战：openpyxl与pandas操作.xlsx格式完全指南

Excel 文件（通常为 .xlsx）是一种基于 XML 的压缩容器格式，用于存储结构化表格数据、工作表、样式、公式与图表等信息。

与 CSV 的“纯文本、单表”不同，Excel 不仅能存储数据，还支持更丰富的结构：

• 多工作表（Sheet）
• 单元格类型（数值、文本、日期、布尔值）
• 公式与格式
• 合并单元格、样式、筛选等结构信息

因此，在处理结构复杂、需要保留格式或计算逻辑的表格数据时，Excel 格式是比 CSV 更优的选择。

一、Excel 文件格式基础

1、常见扩展名

• .xlsx：现代 Excel 格式（Office 2007+），推荐统一使用。
• .xls：旧版二进制格式。
• .xlsm：包含宏（VBA）的 Excel 文件。

2、Excel 文件的结构特征

• 一个文件可包含多个工作表（Sheet）
• 单元格具有明确的数据类型
• 支持公式（如 =SUM(A1:A10)）
• 支持样式、列宽、冻结窗格等布局设置
• 本质上是一个 ZIP 压缩包，内部包含描述结构和内容的 XML 文件

二、在 Python 中表示 Excel 数据

Excel 本质上是二维表格结构，因此在 Python 中通常以列表、字典或更高级的数据结构来表示，方便后续的读写操作。

1、使用列表与字典表示表格
这种方法很直观，每一行对应一个字典，整个表格是一个字典列表。

students = [
    {"id": 1, "姓名": "张三", "成绩": 95},
    {"id": 2, "姓名": "李四", "成绩": 88},
    {"id": 3, "姓名": "王五", "成绩": 90},
    {"id": 4, "姓名": "赵六", "成绩": 70},
]

2、使用二维列表表示单个工作表
这种方式与单元格的“行-列”坐标直接对应，适用于需要精确控制单元格写入的场景。

table = [
    ["id", "姓名", "成绩"],
    [1, "张三", 95],
    [2, "李四", 88],
    [3, "王五", 90],
    [4, "赵六", 70],
]

三、使用 openpyxl 操作 Excel 文件

Python 标准库不支持读写 Excel 文件，我们需要借助第三方库。对于 .xlsx 格式，openpyxl 是一个功能强大且流行的选择，它提供了底层的单元格级操作能力。

1、安装 openpyxl

pip install openpyxl

2、创建并写入 Excel 文件

下面的例子展示了如何创建一个工作簿，写入表头和数据行。

from openpyxl import Workbook

# 1. 创建工作簿并获取默认的活动工作表
wb = Workbook()
ws = wb.active
ws.title = "Students" # 为工作表命名

# 2. 写入表头
ws.append(["id", "姓名", "成绩"])

# 3. 逐行写入数据
ws.append([1, "张三", 95])
ws.append([2, "李四", 88])
ws.append([3, "王五", 90])

# 4. 保存文件
wb.save("students.xlsx")
print("students.xlsx 文件已保存。")

代码说明：

• Workbook() 用于创建一个新的工作簿对象。
• ws.append() 方法非常方便，可以按行写入一个列表，列表中的每个元素对应一个单元格。
• wb.save() 将工作簿保存为指定的 .xlsx 文件。

3、读取 Excel 文件

使用 load_workbook 可以加载已存在的 Excel 文件并读取其中的数据。

from openpyxl import load_workbook

wb = load_workbook(“students.xlsx”)
ws = wb[“Students”] # 通过工作表名称获取

for row in ws.iter_rows(values_only=True):
    print(row)

执行上述代码，输出如下：

('id', ‘姓名‘, ‘成绩‘)
(1, ‘张三‘, 95)
(2, ‘李四‘, 88)
(3, ‘王五‘, 90)

参数 values_only=True 让 iter_rows() 直接返回单元格的值，而不是单元格对象，这在大多数只需要数据的场景下更简洁。

4、多工作表操作

openpyxl 可以轻松管理多个工作表。

# 创建一个名为“Summary”的新工作表
wb.create_sheet(“Summary”)
# 记得保存更改
wb.save(“students.xlsx”)

这对于组织不同类别的数据非常有用，是 Excel 相较于 CSV 的核心优势之一。

四、使用 pandas 处理 Excel 文件

在进行数据分析或需要快速进行数据筛选、统计、转换的场景中，pandas 通常是更高效的选择。pandas 的 read_excel 和 to_excel 方法底层默认使用 openpyxl 引擎来处理 .xlsx 文件。

1、安装 pandas

pip install pandas openpyxl

注意：使用 pandas 读写 .xlsx 也需要安装 openpyxl。

2、读取 Excel 文件

pandas 将整个工作表读取为一个 DataFrame 对象，这是其进行数据分析的基石。

import pandas as pd

df = pd.read_excel(“students.xlsx”)
print(df.head())

输出：

   id 姓名  成绩
0   1  张三  95
1   2  李四  88
2   3  王五  90

3、指定工作表读取

对于多工作表的文件，可以指定要读取的工作表名称。

df = pd.read_excel(“students.xlsx”, sheet_name=“Students”)

4、数据筛选与统计

pandas 强大的查询和计算能力在此得以体现。

# 筛选出成绩大于等于90分的记录
top_students = df[df[“成绩”] >= 90]
# 计算平均成绩
average_score = df[“成绩”].mean()

print(top_students)
print(“平均分：”, average_score)

5、写入 Excel 文件

将 DataFrame 写入 Excel 同样简单。

top_students.to_excel(“top_students.xlsx”, index=False)

与写入 CSV 的关键区别：

• 无需指定编码：.xlsx 是二进制格式，不涉及文本编码问题。
• 参数 index=False：避免将 DataFrame 的行索引作为额外一列写入文件。

6、写入多个工作表

使用 pd.ExcelWriter 可以方便地将多个 DataFrame 写入同一个 Excel 文件的不同工作表。

with pd.ExcelWriter(“report.xlsx”) as writer:
    df.to_excel(writer, sheet_name=“All_Students”)
    top_students.to_excel(writer, sheet_name=“Top_Students”)

这非常适合生成包含汇总表和明细表的报表。

五、综合案例：完整的 Excel 数据处理流程

让我们串联起前面的知识，完成一个从数据构造到分析输出的完整流程。

import pandas as pd

# 1. 构造数据
students = [
    {“id”: 1, “姓名”: “张三”, “成绩”: 95},
    {“id”: 2, “姓名”: “李四”, “成绩”: 88},
    {“id”: 3, “姓名”: “王五”, “成绩”: 90},
    {“id”: 4, “姓名”: “赵六”, “成绩”: 70},
]

# 2. 转换为 DataFrame
df = pd.DataFrame(students)

# 3. 保存为 Excel 文件
df.to_excel(“students_full.xlsx”, index=False)

# 4. 从文件读取数据
df_loaded = pd.read_excel(“students_full.xlsx”)

# 5. 进行数据筛选 (例如，找出成绩大于85分的学生)
df_top = df_loaded[df_loaded[“成绩”] >= 85]

# 6. 将筛选结果写入新的 Excel 文件
df_top.to_excel(“high_score_students.xlsx”, index=False)

print(“数据处理流程完成。”)

六、性能考量与最佳实践建议

1. 解析成本：Excel (.xlsx) 是压缩的 XML 结构，其解析和读写开销远高于纯文本的 CSV。对于超大规模数据（如百万行），Excel 可能不是最合适的存储格式。
2. 库的选择：
   • 需要进行精细的格式控制、公式设置或单元格级操作时，选择 openpyxl。
   • 主要目的是数据分析和批量处理，追求开发效率时，选择 pandas。
3. 写入性能：避免在循环中频繁使用 openpyxl 的单元格级写入操作（如 ws[‘A1’] = value），应优先使用 ws.append(row_data) 或 pandas 的 to_excel 进行批量写入。
4. 公式处理：使用 openpyxl 读取含有公式的文件时，默认获取的是公式字符串本身而非计算结果。如果需要计算结果，需确保文件已被 Excel 计算过，并在加载时指定 data_only=True 参数。

总结

Excel (.xlsx) 格式因其支持多工作表、丰富的数据类型、公式和样式，在报表生成和结构化数据管理中占据重要地位。在 Python 生态中，openpyxl 和 pandas 为我们提供了强大而灵活的操作手段。

• openpyxl 更像一把精细的手术刀，适合需要对 Excel 文件进行底层、精细化控制的程序开发场景。
• pandas 则像一条高效的生产线，专为数据分析和批量处理设计，能极大提升开发效率。

在实际项目中，应根据数据规模、处理需求（是否需要格式）以及对性能的要求，在轻量的 CSV 和功能丰富的 Excel 之间做出明智的选择，找到兼容性、性能与功能表达之间的最佳平衡点。想要查看更多技术干货和开发者讨论，欢迎访问云栈社区。