openpyxl 使用完整手册

openpyxl 使用完整手册

一、简介

openpyxl 是 Python 生态中官方标准、最主流、最贴合原生 Excel 的 xlsx 操作库，专门用于读写、编辑 .xlsx/.xlsm/.xltx/.xltm 格式 Excel 文件（支持 Excel2010 及以上新版格式），是目前 Python 表格自动化、模板填充、格式精细化操作的首选底层库。

该库原生基于 Office Open XML 标准开发，完整保留 Excel 所有原生文档特性：单元格内容、合并单元格、行高列宽、字体、边框、对齐、背景色、冻结窗格、数据验证、批注、多工作表等，区别于高层数据库，不丢失任何表格格式、不自动删减空行空列、不篡改原生结构。

官方文档地址（英文最新稳定版）：https://openpyxl.readthedocs.io/en/stable/

官方中文镜像文档：https://openpyxl.readthedocs.io/zh_CN/stable/

核心限制：不支持旧版二进制 .xls 格式文件，仅支持基于 XML 的新版 xlsx 系列文件。

二、安装

Python 标准第三方库，无额外依赖，一键安装：

pip install openpyxl

国内加速安装（推荐）：

pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

三、核心基础对象（官方标准四层结构）

openpyxl 所有操作均围绕官方定义的四大核心对象，层级清晰、贴合 Excel 原生逻辑：

1. Workbook：工作簿对象，对应一整个 Excel 文件，是所有操作的根对象，可包含多个工作表。

2. Worksheet：工作表对象（Sheet），一个工作簿可创建、删除、复制多个工作表，承载所有单元格内容。

3. Cell：单元格对象，最小操作单元，存储文本、数字、日期、公式及独立样式。

4. Styles 样式体系：官方统一样式模块，包含 Font（字体）、Alignment（对齐）、Border/Side（边框）、PatternFill（背景填充）等，支持精细化样式定制。

四、基础读写操作

4.1 创建全新 Excel 文件（官方标准写法）

from openpyxl import Workbook

# 创建空白工作簿
wb = Workbook()
# 获取默认激活的工作表
ws = wb.active
# 重命名工作表
ws.title = "排班表"

# 三种单元格写入方式（官方兼容写法）
ws["A1"] = "姓名"
ws.cell(row=1, column=2, value="账号")
ws.cell(1, 3, "部门")

# 保存并关闭文件
wb.save("demo.xlsx")
wb.close()

4.2 读取已有 Excel 文件

普通读写模式（可修改、可保存）

from openpyxl import load_workbook

# 加载已有文件
wb = load_workbook("demo.xlsx")
# 通过名称指定工作表
ws = wb["排班表"]

# 读取单元格内容
val = ws["A1"].value
val2 = ws.cell(row=1, column=2).value

# 批量遍历行数据
for row in ws.iter_rows(min_row=2, values_only=True):
    name, account, dept = row
    print(name, account, dept)

wb.close()

只读模式（大文件提速、只读不写）

官方推荐超大文件读取方案，减少内存占用、提升解析速度，无法修改和保存。

from openpyxl import load_workbook

wb = load_workbook("demo.xlsx", read_only=True)
ws = wb.active

# 仅读取数据，禁止写入操作
for row in ws.iter_rows(min_row=1, values_only=True):
    print(row)

wb.close()

4.3 工作表常用官方操作

# 新建工作表（index 指定插入位置）
ws2 = wb.create_sheet("统计页", index=1)

# 获取所有工作表名称列表
sheet_names = wb.sheetnames

# 删除指定工作表
wb.remove(ws2)

# 复制已有工作表（完整复刻内容+样式）
wb.copy_worksheet(ws)

五、单元格常用操作

5.1 批量读写单元格

# 整行批量写入
ws.append(["刘纯", "ShiYinHao", "萧兮的小屋"])

# 指定区域遍历读取
for row in ws["A2:F30"]:
    for cell in row:
        print(cell.value)

5.2 合并 / 取消合并单元格

# 两种等价合并写法（官方支持）
ws.merge_cells("A1:D1")
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=4)

# 取消合并单元格
ws.unmerge_cells("A1:D1")

5.3 行高、列宽精细化设置

# 单列宽度
ws.column_dimensions["A"].width = 15
# 单行高度
ws.row_dimensions[1].height = 22

# 批量设置多列宽度
cols = ["E", "F", "G"]
for c in cols:
    ws.column_dimensions[c].width = 10

5.4 冻结窗格

# 冻结 E5 上方、左侧区域
ws.freeze_panes = "E5"
# 取消冻结
ws.freeze_panes = None

六、样式系统（官方高频工具）

6.1 字体 Font

from openpyxl.styles import Font

# 自定义字体样式
title_font = Font(name="宋体", size=14, bold=True, color="000000")
cell = ws["A1"]
cell.font = title_font

6.2 对齐 Alignment

from openpyxl.styles import Alignment

# 居中对齐
center = Alignment(horizontal="center", vertical="center", wrap_text=False)
ws["A1"].alignment = center

6.3 边框 Border + Side

from openpyxl.styles import Border, Side

# 细边框通用样式
thin_line = Side(style="thin", color="000000")
border = Border(left=thin_line, right=thin_line, top=thin_line, bottom=thin_line)
ws["A1"].border = border

6.4 单元格背景填充

from openpyxl.styles import PatternFill

# 灰色背景填充
gray_fill = PatternFill(start_color="EEEEEE", end_color="EEEEEE", fill_type="solid")
ws["A1"].fill = gray_fill

七、官方实用工具函数

7.1 列字母 ↔ 数字互转（官方工具）

from openpyxl.utils import get_column_letter, column_index_from_string

# 数字列号 → 字母列号  5 → E
print(get_column_letter(5))
# 字母列号 → 数字列号  E → 5
print(column_index_from_string("E"))

7.2 中文自适应列宽（通用封装）

import unicodedata

def auto_fit_column(ws):
    """中文、英文混合内容自动适配列宽"""
    def get_text_width(text):
        w = 0
        for ch in str(text or ""):
            # 区分全角、半角字符宽度
            if unicodedata.east_asian_width(ch) in ("F", "W"):
                w += 2
            else:
                w += 1
        return w

    for col in ws.columns:
        max_w = 0
        for cell in col:
            if cell.value is not None:
                max_w = max(max_w, get_text_width(cell.value))
        col_letter = get_column_letter(col[0].column)
        ws.column_dimensions[col_letter].width = max(min(max_w * 1.1 + 2, 50), 6)

八、特殊场景高级工具

8.1 数据验证（下拉选项，企业模板常用）

from openpyxl.worksheet.datavalidation import DataValidation

# 创建下拉校验规则
dv = DataValidation(type="list", formula1='"早班,午班,晚班,休息"', allow_blank=True)
dv.promptTitle = "班次选择"
dv.prompt = "仅可选择早班、午班、晚班、休息"
ws.add_data_validation(dv)
# 绑定作用单元格范围
dv.add("E5:AH10")

8.2 复制单元格完整样式

from copy import copy

# 复制A1单元格所有样式到A2（官方推荐拷贝方式）
src_cell = ws["A1"]
dst_cell = ws["A2"]
dst_cell.font = copy(src_cell.font)
dst_cell.alignment = copy(src_cell.alignment)
dst_cell.border = copy(src_cell.border)
dst_cell.fill = copy(src_cell.fill)

九、官方避坑要点（高频报错解决方案）

1. 文件操作必须 close()：保存后务必关闭工作簿，否则会导致文件缓存残留、文件损坏、无法二次打开。

2. read_only 模式只读不写：开启只读模式后，禁止所有写入、保存、修改样式操作，否则直接报错。

3. 样式对象不可直接复用：样式为独立对象，多单元格复用必须使用 copy() 拷贝，否则样式错乱。

4. 损坏文件数据验证报错：部分模板存在非法 XML 数据验证规则，openpyxl 原生加载会报错；解决方案：手动清除 Excel 数据验证 或 解压 XML 底层解析读取。

5. 合并单元格取值规则：合并区域仅左上角单元格有值，其余单元格为空占位对象，读取时需特殊判断。

6. 空值判断必做：空白单元格 .value 默认为 None，字符串分割、判断前必须做空值处理，避免报错。

十、完整极简模板填充实战示例

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# 加载模板文件
wb = load_workbook("模板.xlsx")
ws = wb["sheet1"]

# 待填充数据
data_list = [["张三", "admin", "技术部"], ["李四", "user", "运营部"]]
start_row = 5

# 批量填充单元格
for idx, row_data in enumerate(data_list):
    r = start_row + idx
    for c_idx, val in enumerate(row_data):
        c = c_idx + 1
        ws.cell(row=r, column=c, value=val)

# 自动适配列宽
auto_fit_column(ws)

# 保存输出
wb.save("输出.xlsx")
wb.close()

十一、官方特性总结

• 底层原生：直接操作 Excel 标准 XML 结构，是 Python 最贴近原生 Excel 的操作库。

• 格式无损：完整保留所有表格样式、结构、占位列、空行，无自动删减篡改。

• 精细可控：支持单单元格精准修改，适配模板填充、办公自动化、报表生成。

• 稳定规范：长期维护、官方迭代更新，是工业级表格自动化标准工具。

（注：部分内容可能由 AI 生成）

---

本文由萧兮的博客原创发布，欢迎转载，转载务必保留原文链接。

萧兮的博客：https://www.20010515.xyz · 原文：https://www.20010515.xyz/posts/019f1cda-070b-7262-b96d-e070c4e9a98c