Python openpyxl教程：从读写到自动化报表生成完整指南

为什么你需要掌握 openpyxl？

在日常办公中，Excel 是绕不开的工具。财务对账、销售报表、库存统计、考勤汇总——几乎每个岗位都要和 Excel 打交道。当你面对几十个甚至上百个 Excel 文件需要统一处理时，手工操作不仅耗时，还容易出错。

openpyxl 是 Python 生态中最成熟的 Excel 操作库，支持 .xlsx 格式的读写、样式设置、图表生成、公式计算等全部功能。掌握了它，你就拥有了批量处理 Excel 的超能力。

本文将从零开始，带你用 openpyxl 完成从基础读写到自动化报表生成的完整实战。所有代码均可直接复制运行。

1. 环境准备

安装只需一行命令：

pip install openpyxl

验证安装：

python -c "import openpyxl; print(openpyxl.__version__)"

本文使用的测试文件结构如下，你可以用代码自动生成，省去手动创建：

import openpyxl

# 创建测试用 Excel 文件
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "销售数据"

headers = ["日期", "产品", "销量", "单价", "销售额"]
data = [
    ["2026-01-05", "产品A", 120, 89.5, None],
    ["2026-01-06", "产品B", 85, 156.0, None],
    ["2026-01-07", "产品A", 200, 89.5, None],
    ["2026-01-08", "产品C", 65, 320.0, None],
    ["2026-01-09", "产品B", 150, 156.0, None],
]

# 写入表头
for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)

# 写入数据 + 自动计算销售额
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)
    # 销售额 = 销量 × 单价
    ws.cell(row=row_idx, column=5, value=row_data[1] * row_data[2])

wb.save("sales_data.xlsx")
print("✅ 测试文件 sales_data.xlsx 已生成")

2. 读取 Excel：从简单到高效

2.1 基础读取

from openpyxl import load_workbook

wb = load_workbook("sales_data.xlsx")
ws = wb.active  # 获取活动工作表

print(f"工作表名称: {ws.title}")
print(f"数据范围: {ws.dimensions}")
print(f"总行数: {ws.max_row}, 总列数: {ws.max_column}")

2.2 逐行遍历（推荐方式）

# 使用 iter_rows 按行迭代，高效且节省内存
for row in ws.iter_rows(min_row=2, values_only=True):
    date, product, sales, price, revenue = row
    print(f"{date} | {product} | 销量:{sales} | 销售额:¥{revenue:,.2f}")

# 输出示例:
# 2026-01-05 | 产品A | 销量:120 | 销售额:¥10,740.00
# 2026-01-06 | 产品B | 销量:85  | 销售额:¥13,260.00

为什么要用 values_only=True？ 不加这个参数，iter_rows 返回的是 Cell 对象，需要通过 .value 获取内容。加了之后直接拿到 Python 原生数据类型（str、int、float），代码更简洁，性能也更好。

2.3 按列读取与切片

# 只读取"产品"和"销量"两列（第2、3列）
for row in ws.iter_rows(min_row=2, min_col=2, max_col=3, values_only=True):
    product, sales = row
    print(f"{product}: {sales}件")

# 按列遍历
for col in ws.iter_cols(min_col=1, max_col=1, min_row=2, values_only=True):
    dates = [d for d in col]
    print(f"所有日期: {dates}")

2.4 Pandas 联动：一键转 DataFrame

import pandas as pd

# 直接将工作表转为 DataFrame
df = pd.DataFrame(ws.values)
df.columns = df.iloc[0]  # 第一行作为列名
df = df[1:].reset_index(drop=True)

# 现在可以用 Pandas 做数据分析
print(df.groupby("产品")["销售额"].sum())
print(f"\n总销售额: ¥{df['销售额'].sum():,.2f}")

3. 写入 Excel：从零创建专业报表

3.1 基础写入

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "月度汇总"

# 方式1：按单元格赋值
ws["A1"] = "月份"
ws["B1"] = "总销售额"
ws["A2"] = "2026年1月"
ws["B2"] = 157800.50

# 方式2：按行列索引赋值（1-indexed）
ws.cell(row=3, column=1, value="2026年2月")
ws.cell(row=3, column=2, value=183200.00)

# 方式3：批量追加行
rows = [
    ["2026年3月", 210500.00],
    ["2026年4月", 198700.50],
    ["2026年5月", 235100.00],
]
for row_data in rows:
    ws.append(row_data)

wb.save("monthly_report.xlsx")
print("✅ 报表已生成")

4. 样式美化：让报表告别”素颜”

一张专业的报表，样式和排版同样重要。openpyxl 提供了完整的样式 API：

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter

# ── 字体样式 ──
header_font = Font(name="微软雅黑", size=12, bold=True, color="FFFFFF")
data_font = Font(name="微软雅黑", size=11)

# ── 填充色 ──
header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
even_row_fill = PatternFill(start_color="D6E4F0", end_color="D6E4F0", fill_type="solid")

# ── 边框 ──
thin_border = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)

# ── 应用到表头 ──
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = thin_border

# ── 应用到数据行（斑马纹）──
for row in ws.iter_rows(min_row=2):
    for cell in row:
        cell.font = data_font
        cell.border = thin_border
        cell.alignment = Alignment(horizontal="center")
    # 偶数行加底色
    if row[0].row % 2 == 0:
        for cell in row:
            cell.fill = even_row_fill

# ── 自适应列宽 ──
for col_idx in range(1, ws.max_column + 1):
    max_length = 0
    col_letter = get_column_letter(col_idx)
    for cell in ws[col_letter]:
        if cell.value:
            max_length = max(max_length, len(str(cell.value)))
    ws.column_dimensions[col_letter].width = max_length + 4

ws.row_dimensions[1].height = 30  # 表头行高
wb.save("monthly_report_styled.xlsx")
print("✅ 带样式的报表已生成")

5. 实战一：批量合并多个部门报表

场景：公司有三个部门各自提交了月报（销售部.xlsx、研发部.xlsx、运营部.xlsx），你需要将它们合并到一张总表。

import os
from openpyxl import Workbook, load_workbook

output_wb = Workbook()
output_ws = output_wb.active
output_ws.title = "全公司汇总"

current_row = 1

folder = "./部门报表"  # 三个文件放在此目录
for filename in sorted(os.listdir(folder)):
    if not filename.endswith(".xlsx"):
        continue

    filepath = os.path.join(folder, filename)
    source_wb = load_workbook(filepath)
    source_ws = source_wb.active

    # 写入部门名称作为分隔标题
    dept_name = filename.replace(".xlsx", "")
    output_ws.cell(row=current_row, column=1, value=f"📂 {dept_name}")
    current_row += 1

    # 复制该部门的所有数据
    for row in source_ws.iter_rows(values_only=True):
        for col_idx, value in enumerate(row, 1):
            output_ws.cell(row=current_row, column=col_idx, value=value)
        current_row += 1

    current_row += 1  # 空行分隔

output_wb.save("全公司汇总报表.xlsx")
print(f"✅ 合并完成，共 {current_row - 1} 行数据")

6. 实战二：自动生成带图表的销售趋势报告

openpyxl 支持直接在 Excel 中插入图表，省去手动制图步骤：

from openpyxl.chart import BarChart, Reference

# 先准备数据（复用前面的 sales_data.xlsx）
wb = load_workbook("sales_data.xlsx")
ws = wb.active

# 创建柱状图
chart = BarChart()
chart.title = "各产品销售总额"
chart.x_axis.title = "产品"
chart.y_axis.title = "销售额 (¥)"
chart.style = 10

# 数据源：销售额列（第5列，行2~6）
data_ref = Reference(ws, min_col=5, min_row=1, max_row=ws.max_row)
categories_ref = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)

# 将图表插入到 H2 位置
ws.add_chart(chart, "H2")

wb.save("sales_report_with_chart.xlsx")
print("✅ 带图表的销售报告已生成")

7. 避坑指南：5个常见错误

#	错误	原因	修复
1	FileNotFoundError	文件路径写错或不存在	用绝对路径，或先 os.path.exists() 检查
2	写入后文件打不开	忘记调用 wb.save()	所有修改完成后必须执行 save()
3	TypeError: 'NoneType'	访问了空单元格的样式属性	先检查 cell.value is not None
4	公式不自动计算	openpyxl 写入的公式需 Excel 打开才计算	如需程序计算结果，用 Python 算好再写入
5	大文件处理慢/内存溢出	一次性加载整个工作簿	用 read_only=True 模式读取，或改用 write_only=True

FAQ

Q1: openpyxl 能处理 .xls 格式的老文件吗？

不能。 openpyxl 只支持 Office 2007+ 的 .xlsx 格式。处理 .xls 文件请使用 xlrd 库读取，再用 xlwt 写入。如果已在项目中大量使用 openpyxl，建议先用 Excel 打开 .xls 另存为 .xlsx，再交给 openpyxl 处理。

Q2: 如何保护工作表，防止别人修改？

from openpyxl.worksheet.protection import SheetProtection

ws.protection = SheetProtection(
    sheet=True,           # 启用工作表保护
    selectLockedCells=True,   # 允许选择锁定单元格
    selectUnlockedCells=True  # 允许选择未锁定单元格
)
ws.protection.set_password("mypassword123")
# 注意：openpyxl 的密码保护是弱保护，不能替代真正的安全措施

Q3: 和 Pandas 的 to_excel() 相比，什么时候用 openpyxl？

简单导出数据 → 用 Pandas。 一行 df.to_excel("output.xlsx", index=False) 搞定。但如果你需要：设置单元格颜色、添加边框、调整列宽、插入图表、合并单元格、设置数据验证（下拉菜单）——这些 Pandas 都做不到，必须用 openpyxl。

📚 延伸阅读：学完本文后，建议配合本站的
Git 入门完全指南 管理你的自动化脚本，以及
VS Code 高效编程技巧 20 条 提升编码效率。
如果你需要对更大规模的数据做分析，Pandas 系列教程 会是很好的进阶方向。