openpyxl全面教程：常用 API 串联与实战指南大家好，我是jobleap.cn的小九。 openpyxl 是 P

大家好，我是jobleap.cn的小九。 openpyxl 是 Python 中处理 Excel 2007+ 格式（.xlsx/.xlsm）的核心库，支持工作簿、工作表、单元格的全维度操作，包括数据读写、样式设置、公式计算、图表生成等，是自动化处理 Excel 的首选工具。本文将系统梳理 openpyxl 所有常用 API，并通过实战案例串联核心用法，帮助你一站式掌握。

一、前置准备：安装与核心概念

1. 安装 openpyxl

pip install openpyxl  # 基础安装
# 如需处理图片（可选），额外安装：pip install openpyxl[image]

2. 核心概念

Workbook（工作簿）：整个 Excel 文件，一个 Workbook 包含多个 Worksheet。
Worksheet（工作表）：工作簿中的单个表格（如 Sheet1），是数据操作的核心载体。
Cell（单元格）：工作表的最小单元，通过行/列索引或坐标（如 A1）定位。

二、核心 API 详解与基础操作

1. 工作簿（Workbook）操作

（1）创建新工作簿

from openpyxl import Workbook

# 创建空白工作簿（默认包含一个名为 Sheet 的工作表）
wb = Workbook()
# 查看所有工作表名称
print(wb.sheetnames)  # 输出: ['Sheet']

（2）打开已有工作簿

from openpyxl import load_workbook

# 打开现有文件（data_only=True 表示读取公式计算后的结果，而非公式本身）
wb = load_workbook(&#34;销售报表.xlsx&#34;, data_only=True, read_only=False)
# read_only=True：只读模式，适合处理超大文件，内存占用低（但部分写操作不可用）

（3）保存与关闭工作簿

# 保存（新文件直接命名，已有文件覆盖）
wb.save(&#34;销售报表.xlsx&#34;)
# 关闭工作簿（释放文件锁，避免文件被占用）
wb.close()

2. 工作表（Worksheet）操作

工作表是数据操作的核心，所有单元格操作都基于工作表对象。

（1）选择工作表

# 方式1：通过名称选择（推荐）
ws = wb[&#34;Sheet&#34;]
# 方式2：选择当前激活的工作表
ws = wb.active
# 方式3：遍历所有工作表
for sheet in wb.worksheets:
    print(f&#34;工作表名称：{sheet.title}&#34;)

（2）新建/重命名/删除工作表

# 新建工作表（参数1：名称；参数2：位置，0 表示第一个）
ws_new = wb.create_sheet(&#34;2025年销售数据&#34;, 0)
# 重命名工作表
ws_new.title = &#34;2025销售报表&#34;
# 复制工作表（仅复制数据和样式，不复制图表/筛选等）
ws_copy = wb.copy_worksheet(ws_new)
ws_copy.title = &#34;2025销售报表-副本&#34;
# 移动工作表（offset=1 右移1位，负数左移）
wb.move_sheet(ws_new, offset=1)
# 删除工作表
wb.remove(ws_copy)  # 或 del wb[ws_copy.title]

3. 单元格（Cell）操作

单元格是数据存储的最小单元，支持精准定位、赋值、取值等操作。

（1）单元格定位

# 方式1：坐标定位（最常用）
cell_a1 = ws[&#34;A1&#34;]
# 方式2：行/列索引定位（row=行号，column=列号，均从1开始）
cell_a1 = ws.cell(row=1, column=1)
# 方式3：批量定位单元格区域
cell_range = ws[&#34;A1:C5&#34;]  # A1到C5的所有单元格
row_range = ws[2:5]       # 第2行到第5行的所有单元格
col_range = ws[&#34;B:D&#34;]     # B列到D列的所有单元格

（2）单元格赋值与取值

# 直接赋值
ws[&#34;A1&#34;] = &#34;2025年销售报表&#34;  # 文本
ws[&#34;B2&#34;] = &#34;产品A&#34;           # 文本
ws[&#34;C2&#34;] = 15800.5           # 数值
ws[&#34;D2&#34;] = &#34;2025-01-01&#34;      # 日期
# 通过 cell 对象赋值
ws.cell(row=1, column=1, value=&#34;2025年销售报表&#34;)

# 取值
value_a1 = ws[&#34;A1&#34;].value
value_c2 = ws.cell(row=2, column=3).value
print(f&#34;A1单元格值：{value_a1}，C2单元格值：{value_c2}&#34;)

# 批量赋值（按行赋值）
ws[&#34;A2:D2&#34;] = [&#34;日期&#34;, &#34;产品名称&#34;, &#34;销售额（元）&#34;, &#34;区域&#34;]
# 批量赋值（循环赋值）
sales_data = [
    (&#34;2025-01-01&#34;, &#34;产品A&#34;, 15800.5, &#34;华东&#34;),
    (&#34;2025-01-02&#34;, &#34;产品B&#34;, 23400.8, &#34;华北&#34;),
    (&#34;2025-01-03&#34;, &#34;产品C&#34;, 18900.0, &#34;华南&#34;)
]
for row_idx, row_data in enumerate(sales_data, start=3):  # 从第3行开始
    ws.cell(row=row_idx, column=1, value=row_data[0])
    ws.cell(row=row_idx, column=2, value=row_data[1])
    ws.cell(row=row_idx, column=3, value=row_data[2])
    ws.cell(row=row_idx, column=4, value=row_data[3])

（3）单元格属性

cell = ws[&#34;C3&#34;]
print(f&#34;单元格坐标：{cell.coordinate}&#34;)  # 输出: C3
print(f&#34;行号：{cell.row}&#34;)              # 输出: 3
print(f&#34;列号：{cell.column}&#34;)           # 输出: 3
print(f&#34;数据类型：{cell.data_type}&#34;)    # 输出: n（数值）/s（文本）/d（日期）

4. 单元格样式设置

样式是 Excel 美化的核心，需导入 openpyxl.styles 中的类。

from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle

# 1. 字体样式（名称、大小、加粗、颜色）
title_font = Font(name=&#34;微软雅黑&#34;, size=16, bold=True, color=&#34;FFFFFF&#34;)  # 白色加粗
normal_font = Font(name=&#34;微软雅黑&#34;, size=12)

# 2. 对齐样式（水平居中、垂直居中、自动换行）
center_align = Alignment(horizontal=&#34;center&#34;, vertical=&#34;center&#34;, wrap_text=True)

# 3. 边框样式（细边框、黑色）
thin_side = Side(style=&#34;thin&#34;, color=&#34;000000&#34;)  # style可选：thin/thick/dashed/dotted
border = Border(
    left=thin_side, right=thin_side,
    top=thin_side, bottom=thin_side
)

# 4. 填充样式（纯色填充）
title_fill = PatternFill(start_color=&#34;2E86AB&#34;, end_color=&#34;2E86AB&#34;, fill_type=&#34;solid&#34;)  # 蓝色填充
data_fill = PatternFill(start_color=&#34;F2F2F2&#34;, end_color=&#34;F2F2F2&#34;, fill_type=&#34;solid&#34;)   # 浅灰色填充

# 5. 数字格式（销售额保留2位小数，日期格式）
ws[&#34;C3&#34;].number_format = &#34;#,##0.00&#34;  # 千分位+2位小数
ws[&#34;A3&#34;].number_format = &#34;YYYY-MM-DD&#34;  # 日期格式

# 6. 批量应用样式（表头A1:D1）
ws[&#34;A1&#34;].font = title_font
ws[&#34;A1&#34;].alignment = center_align
ws[&#34;A1&#34;].fill = title_fill
ws[&#34;A1:D1&#34;].border = border  # 批量设置边框
# 批量应用样式到数据区域
for row in ws[&#34;A2:D5&#34;]:
    for cell in row:
        cell.font = normal_font
        cell.alignment = center_align
        cell.border = border
        if cell.column == 3:  # 销售额列
            cell.number_format = &#34;#,##0.00&#34;

5. 数据进阶操作

（1）行/列插入与删除

# 在第3行前插入1行
ws.insert_rows(3)
# 在第2列前插入2列
ws.insert_cols(2, amount=2)
# 删除第10行
ws.delete_rows(10)
# 删除第4列
ws.delete_cols(4)

（2）合并/取消合并单元格

# 合并A1:D1单元格（表头合并）
ws.merge_cells(&#34;A1:D1&#34;)
# 取消合并
ws.unmerge_cells(&#34;A1:D1&#34;)

（3）冻结窗格

冻结指定单元格上方/左侧的区域，方便滚动查看数据：

# 冻结B3单元格上方（第1-2行）和左侧（A列）的区域
ws.freeze_panes = &#34;B3&#34;
# 取消冻结
ws.freeze_panes = None

（4）添加筛选

# 为数据区域（A2:D5）添加自动筛选
ws.auto_filter.ref = &#34;A2:D5&#34;  # 或 ws.dimensions（整个工作表数据区域）

6. 公式与函数操作

openpyxl 支持插入 Excel 原生公式，需注意公式语法与 Excel 一致（英文逗号分隔）。

# 1. 插入求和公式（计算C3:C5的总销售额，写入C6）
ws[&#34;C6&#34;] = &#34;=SUM(C3:C5)&#34;
# 2. 插入平均值公式（写入C7）
ws[&#34;C7&#34;] = &#34;=AVERAGE(C3:C5)&#34;

# 3. 读取公式计算结果（需打开文件时设置 data_only=True）
wb = load_workbook(&#34;销售报表.xlsx&#34;, data_only=True)
ws = wb.active
total_sales = ws[&#34;C6&#34;].value
print(f&#34;总销售额：{total_sales}&#34;)

7. 图表生成

openpyxl 支持插入柱状图、折线图、饼图等，以柱状图为例：

from openpyxl.chart import BarChart, Reference
from openpyxl.chart.label import DataLabelList

# 1. 创建柱状图对象
chart = BarChart()
chart.type = &#34;col&#34;  # 柱形图（bar为条形图）
chart.style = 10    # 内置样式

# 2. 定义数据区域（销售额列：C3:C5）
values = Reference(
    ws,
    min_col=3,    # 起始列
    min_row=2,    # 起始行（包含表头）
    max_col=3,    # 结束列
    max_row=5     # 结束行
)
# 3. 定义分类轴（产品名称列：B3:B5）
categories = Reference(
    ws,
    min_col=2,
    min_row=3,
    max_row=5
)

# 4. 配置图表
chart.add_data(values, titles_from_data=True)  # 从数据表头获取系列名称
chart.set_categories(categories)              # 设置X轴分类
chart.title = &#34;2025年1月产品销售额&#34;           # 图表标题
chart.y_axis.title = &#34;销售额（元）&#34;            # Y轴标题
chart.x_axis.title = &#34;产品名称&#34;                # X轴标题
# 显示数据标签
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True

# 5. 将图表插入工作表（位置E3）
ws.add_chart(chart, &#34;E3&#34;)

三、实战综合案例：制作完整销售报表

以下案例串联所有常用 API，实现「创建工作簿→写入数据→设置样式→公式计算→生成图表→保存文件」全流程：

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.label import DataLabelList

# 步骤1：创建工作簿并初始化工作表
wb = Workbook()
ws = wb.active
ws.title = &#34;2025年1月销售报表&#34;

# 步骤2：定义样式
# 表头样式
title_font = Font(name=&#34;微软雅黑&#34;, size=16, bold=True, color=&#34;FFFFFF&#34;)
title_fill = PatternFill(start_color=&#34;2E86AB&#34;, end_color=&#34;2E86AB&#34;, fill_type=&#34;solid&#34;)
center_align = Alignment(horizontal=&#34;center&#34;, vertical=&#34;center&#34;)
thin_side = Side(style=&#34;thin&#34;, color=&#34;000000&#34;)
border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
# 数据样式
normal_font = Font(name=&#34;微软雅黑&#34;, size=12)
data_fill = PatternFill(start_color=&#34;F2F2F2&#34;, end_color=&#34;F2F2F2&#34;, fill_type=&#34;solid&#34;)

# 步骤3：写入表头并合并单元格
ws[&#34;A1&#34;] = &#34;2025年1月销售报表&#34;
ws.merge_cells(&#34;A1:D1&#34;)
ws[&#34;A1&#34;].font = title_font
ws[&#34;A1&#34;].fill = title_fill
ws[&#34;A1&#34;].alignment = center_align
ws[&#34;A1&#34;].border = border

# 步骤4：写入二级表头
ws[&#34;A2:D2&#34;] = [&#34;日期&#34;, &#34;产品名称&#34;, &#34;销售额（元）&#34;, &#34;区域&#34;]
for cell in ws[&#34;A2:D2&#34;]:
    cell.font = normal_font
    cell.alignment = center_align
    cell.border = border
    cell.fill = data_fill

# 步骤5：写入销售数据
sales_data = [
    (&#34;2025-01-01&#34;, &#34;产品A&#34;, 15800.5, &#34;华东&#34;),
    (&#34;2025-01-02&#34;, &#34;产品B&#34;, 23400.8, &#34;华北&#34;),
    (&#34;2025-01-03&#34;, &#34;产品C&#34;, 18900.0, &#34;华南&#34;),
    (&#34;2025-01-04&#34;, &#34;产品D&#34;, 21500.2, &#34;西南&#34;)
]
for row_idx, row_data in enumerate(sales_data, start=3):
    for col_idx, value in enumerate(row_data, start=1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.font = normal_font
        cell.alignment = center_align
        cell.border = border
        # 销售额列设置数值格式
        if col_idx == 3:
            cell.number_format = &#34;#,##0.00&#34;
        # 日期列设置格式
        if col_idx == 1:
            cell.number_format = &#34;YYYY-MM-DD&#34;

# 步骤6：插入求和公式（计算总销售额）
ws[&#34;C7&#34;] = &#34;总销售额&#34;
ws[&#34;D7&#34;] = &#34;=SUM(C3:C6)&#34;
ws[&#34;D7&#34;].number_format = &#34;#,##0.00&#34;
ws[&#34;D7&#34;].font = normal_font
ws[&#34;D7&#34;].alignment = center_align
ws[&#34;D7&#34;].border = border

# 步骤7：设置冻结窗格和筛选
ws.freeze_panes = &#34;B3&#34;  # 冻结表头
ws.auto_filter.ref = &#34;A2:D6&#34;  # 数据区域添加筛选

# 步骤8：生成销售额柱状图
chart = BarChart()
chart.type = &#34;col&#34;
chart.style = 10
# 数据区域
values = Reference(ws, min_col=3, min_row=2, max_col=3, max_row=6)
# 分类轴
categories = Reference(ws, min_col=2, min_row=3, max_row=6)
# 配置图表
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)
chart.title = &#34;产品销售额对比&#34;
chart.y_axis.title = &#34;销售额（元）&#34;
chart.x_axis.title = &#34;产品名称&#34;
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
# 插入图表
ws.add_chart(chart, &#34;F3&#34;)

# 步骤9：保存并关闭工作簿
wb.save(&#34;2025年1月销售报表.xlsx&#34;)
wb.close()
print(&#34;销售报表生成完成！&#34;)

四、注意事项与最佳实践

文件锁问题：操作完成后务必调用 wb.close()，否则Excel文件会被锁定，无法手动编辑。
超大文件处理：使用 load_workbook(read_only=True) 只读模式，配合逐行遍历（for row in ws.iter_rows()），减少内存占用。
公式读取：需打开文件时设置 data_only=True，但仅能读取已计算的结果（需先手动打开文件并保存一次）。
样式批量应用：优先使用循环或 NamedStyle 批量设置样式，避免逐个单元格设置，提升效率。
兼容性：openpyxl 不支持 .xls 格式（Excel 97-2003），如需处理可使用 xlrd/xlwt 库。

五、总结

openpyxl 覆盖了 Excel 操作的全场景，核心 API 可归纳为「工作簿→工作表→单元格」三层结构，配合样式、公式、图表等进阶功能，能满足自动化生成报表、数据清洗、批量处理等绝大多数业务需求。本文的综合案例串联了所有常用 API，你可基于此案例扩展（如批量导入数据、生成多工作表报表、添加折线图等），快速适配实际业务场景。