Python-openpyxl操作Excel实战解析

本文围绕Python-openpyxl操作Excel实战解析展开，先梳理核心概念，再结合实践场景说明步骤、代码思路和容易忽略的细节，方便后续直接参考。

openpyxl 简介 为什么选择 openpyxl？ Python 能操作 Excel 的库有很多： 库名特点适用场景openpyxl功能强大，支持读写 .xlsx 格式日常办公自动化xlrd/xlwt老牌库，只能读/写 .xls旧版 E

openpyxl 简介

为什么选择 openpyxl？

Python 能操作 Excel 的库有很多：

库名	特点	适用场景
openpyxl	功能强大，支持读写 .xlsx 格式	日常办公自动化
xlrd/xlwt	老牌库，只能读/写 .xls	旧版 Excel 文件
pandas	数据分析神器，可读写 Excel	大数据处理
xlwings	调用 Excel 程序，保留公式	需要复杂公式计算

推荐：对于大多数场景，openpyxl 是最佳选择！

---

安装 openpyxl

 复制代码pip install openpyxl 

安装成功后，你会看到 Successfully installed openpyxl 提示。

---

Excel 核心概念

在操作 Excel 之前，我们先来认识几个核心概念：

工作簿 (Workbook) - 一个 Excel 文件就是一个工作簿

工作表 (Sheet) - 工作簿中的表格

1. 行 (Row) - 横向，用数字 1, 2, 3... 表示
2. 列 (Column) - 纵向，用字母 A, B, C... 表示
3. 单元格 (Cell) - 行和列的交叉点
4. 坐标 (Coordinate) - 如 B4 表示第2列第4行

重要提醒：

1. Excel 的索引从 1 开始（不是从 0 开始！）
2. 列用字母表示：A, B, C... Z, AA, AB...
3. 行用数字表示：1, 2, 3...
4. 单元格坐标格式：列字母 + 行数字，如 B4

示例：图中【后羿】所在的位置是 B4，表示第 B 列第 4 行。

---

打开 Excel 文件

支持的文件格式：.xlsx / .xlsm / .xltx / .xltm

注意：.xls 格式（旧版 Excel）需要先转换为 .xlsx 才能使用 openpyxl

示例文件：王者小学.xlsx，包含两个工作表：

代码示例：

 复制代码from openpyxl import load_workbook# 文件路径（r 前缀表示原始字符串，避免转义问题） filePath = r'D:王者小学.xlsx'# 打开 Excel 文件 workbook = load_workbook(filePath)# 查看所有工作表名称 print(workbook.sheetnames) 

运行结果：

 复制代码['花名册', '成绩单'] 

关键函数：load_workbook(filepath)

1. 作用：加载已存在的 Excel 文件
2. 参数：文件路径（字符串）
3. 返回：Workbook 对象
4. 注意：只能打开已存在的文件，不能创建新文件

获取 Excel 数据

获取工作表（Sheet）

一个 Excel 文件可以包含多个工作表，获取工作表有三种常用方法：

方法	语法	说明
通过名称	workbook['表名']	最常用，直观明了
通过索引	workbook.worksheets[索引]	索引从 0 开始
获取活动表	workbook.active	获取当前打开时显示的表

代码示例：

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath)# 方法1：通过表名获取 sheet1 = workbook["花名册"]# 方法2：通过索引获取（从0开始） sheet2 = workbook.worksheets[0] # 第一个表# 方法3：获取当前活动表 sheet3 = workbook.activeprint(f"方法1: {sheet1}") print(f"方法2: {sheet2}") print(f"方法3: {sheet3}") 

运行结果：

 复制代码方法1: "花名册"> 方法2: "花名册"> 方法3: "成绩单"> 

提示：

1. workbook["花名册"] 和 workbook.worksheets[0] 获取的是同一个表
2. workbook.active 获取的是 Excel 打开时默认显示的表

---

获取表格尺寸

使用 sheet.dimensions 可以获取表格的数据范围：

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath)sheet1 = workbook["花名册"] sheet2 = workbook["成绩单"]print(f"花名册: {sheet1.dimensions}") print(f"成绩单: {sheet2.dimensions}") 

运行结果：

 复制代码花名册: A1:C5 成绩单: A1:D5 

含义解析：

1. A1:C5 = 从 A1 单元格到 C5 单元格的数据区域
2. A1:D5 = 从 A1 单元格到 D5 单元格的数据区域

尺寸格式：左上角坐标:右下角坐标

1. 如 A1:C5 表示 A 列到 C 列，第 1 行到第 5 行的区域

---

获取单元格数据

获取单元格数据有两种常用方法：

方法	语法	适用场景
坐标法	sheet['B2']	知道单元格坐标时
行列法	sheet.cell(row=2, column=2)	需要循环遍历时

代码示例：

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath)sheet = workbook["花名册"] sheet2 = workbook["成绩单"]# 方法1：通过坐标获取 cell1 = sheet["B2"] print(f"单元格对象: {cell1}") print(f"单元格值: {cell1.value}")# 方法2：通过行列获取（B2 = 第2行第2列） cell2 = sheet.cell(row=2, column=2) print(f"单元格值: {cell2.value}")# 获取后羿的成绩（C4 = 第4行第3列） score = sheet2.cell(row=4, column=3).value print(f"后羿的成绩: {score}") 

运行结果：

 复制代码单元格对象: '花名册'.B2> 单元格值: 李白 单元格值: 李白 后羿的成绩: 96 

总结：

1. sheet['B2'] → 获取单元格对象
2. sheet['B2'].value → 获取单元格的值
3. sheet.cell(row=2, column=2) → 通过行列获取（行、列从1开始）

---

获取区域数据

方法1：使用 sheet[]（切片语法）

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath) sheet = workbook["花名册"]# 获取矩形区域 cells = sheet["A1:C4"] # 从A1到C4的区域for row in cells: # 按行遍历 for cell in row: # 遍历行中的每个单元格 print(cell.value) 

运行结果：

 复制代码学号 姓名 性别 1 李白 男 ... 

常用切片方式：

语法	说明	示例
sheet['A1:C4']	获取矩形区域	A1到C4的所有单元格
sheet['A']	获取整列	A列所有数据
sheet['4']	获取整行	第4行所有数据
sheet['A:C']	获取多列	A到C列
sheet[2:4]	获取多行	第2到4行

---

方法2：使用 sheet.iter_rows()（推荐）

iter_rows() 是更灵活的方法，直接用行列数字指定范围：

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath) sheet = workbook["花名册"]# 获取指定范围（第2-4行，第1-2列） for row in sheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=2): for cell in row: print(cell.value)# 获取所有数据 for row in sheet.iter_rows(): for cell in row: print(cell.value)# 直接获取值（不需要 .value） for row in sheet.iter_rows(min_row=2, max_row=4, values_only=True): print(row) # 直接是元组 ('1', '李白', '男') 

参数说明：

参数	说明	默认值
min_row	起始行	1
max_row	结束行	最大行
min_col	起始列	1
max_col	结束列	最大列
values_only	是否只返回值	False

推荐：需要按行列遍历时，使用 iter_rows() 更直观！

修改和保存 Excel

修改单元格的值

修改单元格有两种方式：

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath) sheet = workbook['花名册']# 方式1：直接赋值 sheet["B2"] = '张三'# 方式2：通过 cell 对象赋值 cell = sheet["B3"] cell.value = '李四'# 保存文件 workbook.save(r'D:王者小学2.xlsx') 

对比效果：

重要提醒：

1. 必须保存：修改后调用 workbook.save(路径) 才能生效
2. 另存为：如果路径不同，会创建新文件；路径相同则覆盖原文件
3. 文件关闭：保存前确保 Excel 文件已关闭，否则会报错

---

批量插入数据

使用 append() 方法可以在表格末尾批量插入数据：

 复制代码from openpyxl import load_workbookfilePath = r'D:王者小学.xlsx' workbook = load_workbook(filePath) sheet = workbook['花名册']# 要插入的数据（列表的列表） data = [ ['5', '安琪拉', "女"], ['6', '荆轲', "女"], ['7', '夏侯惇', "男"], ]# 逐行追加 for row in data: sheet.append(row)workbook.save(filePath) print("数据插入成功！") 

应用场景：

1. 爬虫数据存储
2. 批量导入数据
3. 日志记录
4. 报表生成

实战项目：合并多个 Excel 文件

项目背景

假设你是公司的数据分析师，需要将2021年4个季度的销售数据合并成一个总表。每个季度的数据保存在单独的 Excel 文件中：

实现思路

1. 遍历数据文件夹中的所有 Excel 文件
2. 读取每个文件的数据
3. 提取季度信息（从文件名获取）
4. 将所有数据合并到一个新的工作簿
5. 保存汇总文件

核心代码

 复制代码import os from openpyxl import load_workbook, Workbook# 数据文件夹路径 data_dir = r'D:2021年销售明细'# 创建新的工作簿保存汇总数据 summary_wb = Workbook() summary_sheet = summary_wb.active summary_sheet.title = "2021年销售汇总"# 写入表头 headers = ['季度', '产品', '销量', '销售额'] summary_sheet.append(headers)# 遍历所有Excel文件 for filename in os.listdir(data_dir): if filename.endswith('.xlsx'): # 提取季度信息 quarter = filename.replace('.xlsx', '') # 打开文件读取数据 file_path = os.path.join(data_dir, filename) wb = load_workbook(file_path) sheet = wb.active # 读取数据（跳过表头） for row in sheet.iter_rows(min_row=2, values_only=True): # 添加季度信息 row_data = [quarter] + list(row) summary_sheet.append(row_data) wb.close()# 保存汇总文件 summary_wb.save(r'D:2021年销售汇总.xlsx') print("数据合并完成！") 

至此，你已经掌握了 Python 操作 Excel 的核心技能！

文档总结

一、核心知识点回顾

1. Excel 基础概念

 复制代码工作簿 (Workbook) → 一个 Excel 文件 └── 工作表 (Sheet) ├── 行 (Row) - 数字 1, 2, 3... ├── 列 (Column) - 字母 A, B, C... └── 单元格 (Cell) - 坐标如 B4 

重要：Excel 索引从 1 开始，不是 0！

2. openpyxl 核心操作

操作	代码	说明
打开文件	load_workbook('文件.xlsx')	加载已存在的文件
获取工作表	wb['表名'] / wb.worksheets[0]	获取指定工作表
获取单元格	sheet['B2'] / sheet.cell(row=2, column=2)	两种获取方式
获取值	cell.value	获取单元格的值
修改值	sheet['B2'] = '新值'	直接赋值修改
批量插入	sheet.append([数据列表])	在末尾添加一行
保存文件	wb.save('路径.xlsx')	保存修改

3. 区域数据获取方法对比

方法	优点	缺点
sheet['A1:C4']	直观，用坐标	需要转换行列到坐标
sheet.iter_rows()	直接用行列数字	稍微复杂一点

推荐：简单场景用 sheet[]，复杂遍历用 iter_rows()

4. 完整操作流程

 复制代码from openpyxl import load_workbook# 1. 打开文件 wb = load_workbook('数据.xlsx')# 2. 获取工作表 sheet = wb['Sheet1']# 3. 读取/修改数据 value = sheet['A1'].value sheet['B2'] = '新数据'# 4. 批量插入 sheet.append(['数据1', '数据2', '数据3'])# 5. 保存文件 wb.save('数据.xlsx') 

---

二、常见应用场景

1. 数据整理：批量处理多个 Excel 文件
2. 报表生成：自动从数据库导出数据到 Excel
3. 数据清洗：读取 Excel 数据，处理后保存
4. 爬虫存储：将爬取的数据保存到 Excel
5. 办公自动化：自动填充模板、生成报告

---

三、学习建议

1. 多练习：Excel 操作熟能生巧，多写代码
2. 注意索引：时刻记住 Excel 从 1 开始计数
3. 及时保存：修改后记得调用 save()
4. 关闭文件：操作前确保 Excel 文件已关闭
5. 结合 pandas：大数据量时考虑使用 pandas

实际使用时，建议结合项目规模、依赖环境和团队习惯做取舍；先保证流程清晰和结果可验证，再逐步优化细节。