当前位置：首页 > news >正文

Python3 模块精讲｜openpyxl 万字实战：全自动读写 Excel，办公效率直接起飞

news 2026/4/27 0:07:58

文章标签：#Python #openpyxl #Excel #办公自动化 #数据处理

📝 本章学习目标：本章聚焦 Python 办公自动化核心技能，帮助读者从零到一完全掌握openpyxl模块的创建、读取、修改、样式、图表、批量处理等全套能力。通过本章学习，你将独立完成企业级 Excel 自动化任务，告别手动制表、数据录入、格式调整。

一、引言：为什么 openpyxl 是职场必备神器

在日常办公、数据分析、报表导出、财务统计、数据汇总等场景中，Excel 处理是最高频的刚需。手动复制粘贴、改格式、算数据不仅效率极低，还极易出错。openpyxl 让 Python 直接操控 Excel，把几小时工作压缩到几秒完成。

1.1 背景与意义

💡 核心认知：openpyxl 是目前 Python 生态最主流、最稳定、功能最完整的 Excel 处理库，专门支持.xlsx格式（Excel 2007 及以上）。它可以实现：读写单元格、合并单元格、设置字体颜色、边框底纹、公式计算、批量插入数据、生成图表、读取大量数据等几乎所有 Excel 常用功能。

据行业统计，80% 以上的 Python 数据处理与办公自动化项目依赖 openpyxl，是职场提效、自动化办公的核心利器。

1.2 本章结构概览

为帮助你系统性掌握 openpyxl，本章严格按以下路线学习：

plaintext

📊 环境安装 → 核心概念 → 写入操作 → 读取操作 → 样式设置 → 公式图表 → 批量实战 → 最佳实践 → 常见问题 → 总结展望

二、核心概念解析

2.1 基本定义

概念一：openpyxl 核心能力清单

表格

能力	说明	典型应用场景
创建工作簿	新建空白 xlsx 文件	自动日报、周报、报表
写入数据	单元格、行、列批量写入	数据导出、结果统计
读取数据	按行 / 按列 / 按区域读取	数据采集、解析上报
样式美化	字体、颜色、边框、对齐	统一报表格式
公式计算	直接写 Excel 公式自动计算	财务报表、合计统计
图表生成	柱状图、折线图、饼图	数据可视化报告
批量处理	循环生成、批量修改	自动化业务系统

概念二：Excel 文档结构（openpyxl 视角）

Workbook（工作簿）：整个 Excel 文件
Worksheet（工作表）：文件内的 Sheet 页
Cell（单元格）：最小数据单元，如 A1、B2
Row/Column：行与列
Range：单元格区域，如 A1:C10

2.2 关键术语解释

⚠️ 注意：以下术语是看懂 openpyxl 代码的基础，必须掌握。

wb（Workbook）：代表整个 Excel 文件对象
ws（Worksheet）：代表当前操作的工作表
cell：单元格，由行号列号定位
offset：单元格偏移，用于相对定位
freeze panes：冻结窗格，方便查看表头

2.3 技术架构概览

💡 架构理解：

plaintext

┌─────────────────────────────────────────┐ │ 工作簿 Workbook │ │ openpyxl.Workbook() │ ├─────────────────────────────────────────┤ │ 工作表 Worksheet │ │ wb.active / wb['Sheet1'] │ ├─────────────────────────────────────────┤ │ 单元格 Cell │ │ ws['A1']、ws.cell() │ ├─────────────────────────────────────────┤ │ 样式与格式 │ │ 字体、边框、颜色、对齐、公式 │ └─────────────────────────────────────────┘

三、环境安装与快速入门

3.1 安装 openpyxl

bash

运行

# 安装稳定版 pip install openpyxl # 验证安装 pip show openpyxl

3.2 第一个程序：创建 Excel 并写入数据

python

运行

# 导入工作簿类 from openpyxl import Workbook # 1. 创建工作簿对象 wb = Workbook() # 2. 获取默认工作表 ws = wb.active # 3. 修改工作表名称 ws.title = "第一个工作表" # 4. 写入单元格数据 ws['A1'] = '姓名' ws['B1'] = '年龄' ws['C1'] = '城市' ws['A2'] = '张三' ws['B2'] = 22 ws['C2'] = '北京' # 5. 保存文件 wb.save('my_first_excel.xlsx') print("✅ Excel创建成功！")

四、写入操作：从零构建 Excel

4.1 单元格写入

python

运行

from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = "单元格写入" # 方式1：直接赋值 ws['A1'] = '直接赋值写入' # 方式2：使用cell函数（行，列） ws.cell(row=2, column=1, value='cell函数写入') # 方式3：批量写入一行 row_data = ['小明', 25, '上海', '工程师'] ws.append(row_data) wb.save('单元格写入.xlsx')

4.2 批量插入多行数据

python

运行

from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = "批量数据" # 表头 header = ['ID', '姓名', '成绩', '等级'] ws.append(header) # 模拟数据 data = [ (1, '张三', 92, '优秀'), (2, '李四', 85, '良好'), (3, '王五', 76, '合格'), (4, '赵六', 62, '合格'), ] # 批量追加 for item in data: ws.append(item) wb.save('批量插入.xlsx')

4.3 合并单元格

python

运行

from openpyxl import Workbook wb = Workbook() ws = wb.active # 合并 A1 到 C1 ws.merge_cells('A1:C1') ws['A1'] = '合并单元格标题' # 合并多行多列 ws.merge_cells('A3:C5') ws['A3'] = '大区域合并' wb.save('合并单元格.xlsx')

五、读取操作：提取 Excel 数据

5.1 读取指定单元格

python

运行

from openpyxl import load_workbook # 加载已存在的Excel wb = load_workbook('批量插入.xlsx') ws = wb.active # 读取方式1 print(ws['A2'].value) # 读取方式2 print(ws.cell(row=2, column=2).value)

5.2 按行读取所有数据

python

运行

from openpyxl import load_workbook wb = load_workbook('批量插入.xlsx') ws = wb.active # 遍历所有行 for row in ws.iter_rows(min_row=1, max_row=ws.max_row, values_only=True): print(row)

5.3 获取最大行 / 最大列

python

运行

from openpyxl import load_workbook wb = load_workbook('批量插入.xlsx') ws = wb.active print("最大行数：", ws.max_row) print("最大列数：", ws.max_column)

六、样式设置：企业级报表美化

6.1 字体样式

python

运行

from openpyxl import Workbook from openpyxl.styles import Font wb = Workbook() ws = wb.active # 设置字体：微软雅黑、14号、加粗、蓝色 font = Font( name='微软雅黑', size=14, bold=True, color='FF0000FF' ) ws['A1'] = '报表标题' ws['A1'].font = font wb.save('字体样式.xlsx')

6.2 对齐方式

python

运行

from openpyxl.styles import Alignment from openpyxl import Workbook wb = Workbook() ws = wb.active # 水平居中 + 垂直居中 align = Alignment(horizontal='center', vertical='center') ws['A1'] = '居中对齐' ws['A1'].alignment = align wb.save('对齐样式.xlsx')

6.3 边框设置

python

运行

from openpyxl.styles import Border, Side from openpyxl import Workbook wb = Workbook() ws = wb.active # 边框样式 side = Side(style='thin', color='000000') border = Border(left=side, right=side, top=side, bottom=side) ws['A1'] = '带边框单元格' ws['A1'].border = border wb.save('边框样式.xlsx')

6.4 背景色填充

python

运行

from openpyxl.styles import PatternFill from openpyxl import Workbook wb = Workbook() ws = wb.active # 黄色背景 fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') ws['A1'] = '高亮单元格' ws['A1'].fill = fill wb.save('背景填充.xlsx')

七、公式与图表

7.1 Excel 公式计算

python

运行

from openpyxl import Workbook wb = Workbook() ws = wb.active ws['A1'] = 10 ws['A2'] = 20 ws['A3'] = 30 # 求和公式 ws['A4'] = '=SUM(A1:A3)' # 平均值 ws['A5'] = '=AVERAGE(A1:A3)' wb.save('公式计算.xlsx')

7.2 生成柱状图

python

运行

from openpyxl import Workbook from openpyxl.chart import BarChart, Reference wb = Workbook() ws = wb.active # 写入数据 data = [ ['姓名', '成绩'], ['张三', 92], ['李四', 85], ['王五', 76], ] for row in data: ws.append(row) # 创建图表 chart = BarChart() data_ref = Reference(ws, min_col=2, min_row=1, max_row=4) labels_ref = Reference(ws, min_col=1, min_row=2, max_row=4) chart.add_data(data_ref, titles_from_data=True) chart.set_categories(labels_ref) ws.add_chart(chart, 'E1') wb.save('图表.xlsx')

八、批量自动化实战

8.1 批量生成 10 份成绩表

python

运行

from openpyxl import Workbook import os # 确保输出目录存在 if not os.path.exists('./output'): os.mkdir('./output') # 学生名单 students = ['张三', '李四', '王五', '赵六', '钱七'] for name in students: wb = Workbook() ws = wb.active ws.title = name ws['A1'] = f'{name} 个人成绩单' ws['A2'] = '科目' ws['B2'] = '分数' ws['A3'] = '语文' ws['A4'] = '数学' ws['A5'] = '英语' wb.save(f'./output/{name}_成绩表.xlsx') print("✅ 批量生成完成！")

8.2 批量读取汇总 Excel

python

运行

from openpyxl import load_workbook import os # 汇总表 wb_total = Workbook() ws_total = wb_total.active ws_total.title = '汇总表' ws_total.append(['姓名', '语文', '数学', '英语']) # 遍历文件夹 for filename in os.listdir('./output'): if filename.endswith('.xlsx'): wb = load_workbook(f'./output/{filename}') ws = wb.active name = filename.split('_')[0] yw = ws['B3'].value sx = ws['B4'].value yy = ws['B5'].value ws_total.append([name, yw, sx, yy]) wb_total.save('汇总结果.xlsx')