Python使用openpyxl操作Excel的新手入门教程

openpyxl 简介

为什么选择 openpyxl？

Python里能操作Excel的库其实不少，但要说最趁手的，openpyxl绝对算一个。来看一下市面上常见的几个选择：

综合来看，对于大多数场景，openpyxl 是最佳选择——既能读写，又不依赖Excel程序，轻量高效。

安装 openpyxl

pip install openpyxl

安装成功后，你会看到 Successfully installed openpyxl 的提示，直接开干就行。

Excel 核心概念

在动手操作之前，先理清几个关键概念：

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

工作表 (Sheet) - 工作簿里的表格页面

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

重要提醒：

• Excel 的索引从 1 开始（不是从 0 开始！）
• 列用字母表示：A, B, C... Z, AA, AB...
• 行用数字表示：1, 2, 3...
• 单元格坐标格式：列字母 + 行数字，如 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)

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

获取 Excel 数据

获取工作表（Sheet）

一个 Excel 文件里可以有好几个工作表，获取工作表有三种常用方法：

代码示例：

from openpyxl import load_workbook

filePath = r'D:王者小学.xlsx'
workbook = load_workbook(filePath)

# 方法1：通过表名获取
sheet1 = workbook["花名册"]

# 方法2：通过索引获取（从0开始）
sheet2 = workbook.worksheets[0]  # 第一个表

# 方法3：获取当前活动表
sheet3 = workbook.active

print(f"方法1: {sheet1}")
print(f"方法2: {sheet2}")
print(f"方法3: {sheet3}")

运行结果：

方法1:

方法2:

方法3:

提示：

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

获取表格尺寸

使用 sheet.dimensions 可以快速知道表格的数据范围：

from openpyxl import load_workbook

filePath = r'D:王者小学.xlsx'
workbook = load_workbook(filePath)

sheet1 = workbook["花名册"]
sheet2 = workbook["成绩单"]

print(f"花名册: {sheet1.dimensions}")
print(f"成绩单: {sheet2.dimensions}")

运行结果：

花名册: A1:C5

成绩单: A1:D5

含义解析：

• A1:C5 = 从 A1 单元格到 C5 单元格的数据区域
• A1:D5 = 从 A1 单元格到 D5 单元格的数据区域

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

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

获取单元格数据

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

代码示例：

from openpyxl import load_workbook

filePath = 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}")

运行结果：

单元格对象:

单元格值: 李白

单元格值: 李白

后羿的成绩: 96

总结：

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

获取区域数据

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

from openpyxl import load_workbook

filePath = 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)

运行结果：

学号

姓名

性别

李白

男

...

常用切片方式：

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

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

from openpyxl import load_workbook

filePath = 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', '李白', '男')

参数说明：

推荐：需要按行列遍历时，使用 iter_rows() 更直观，尤其配合 values_only=True 可以直接拿到数据元组。

修改和保存 Excel

修改单元格的值

修改单元格有两种方式：

from openpyxl import load_workbook

filePath = r'D:王者小学.xlsx'
workbook = load_workbook(filePath)
sheet = workbook['花名册']

# 方式1：直接赋值
sheet["B2"] = '张三'

# 方式2：通过 cell 对象赋值
cell = sheet["B3"]
cell.value = '李四'

# 保存文件
workbook.sa ve(r'D:王者小学2.xlsx')

对比效果：

重要提醒：

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

批量插入数据

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

from openpyxl import load_workbook

filePath = r'D:王者小学.xlsx'
workbook = load_workbook(filePath)
sheet = workbook['花名册']

# 要插入的数据（列表的列表）
data = [
    ['5', '安琪拉', "女"],
    ['6', '荆轲', "女"],
    ['7', '夏侯惇', "男"],
]

# 逐行追加
for row in data:
    sheet.append(row)

workbook.sa ve(filePath)
print("数据插入成功！")

应用场景：

• 爬虫数据存储
• 批量导入数据
• 日志记录
• 报表生成

实战项目：合并多个 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.sa ve(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 核心操作

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

推荐：简单场景用 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.sa ve('数据.xlsx')

二、常见应用场景

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

三、学习建议

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