Python读写Excel文件 2
Python 读写 Excel 文件（二）：openpyxl 教学与实战指南
📘 一、openpyxl 操作 Excel 的意义与应用场景
✅ 为什么要用 Python 操作 Excel？
在日常办公、数据报表、教育、科研、财务、业务系统等场景中，经常需要批量处理 Excel 文件，例如：
- 自动化生成成绩单、工资单、进度表。
- 汇总多个部门的 Excel 表格。
- 从报表中提取特定信息，如“所有销售额大于 1 万的记录”。
- 将数据库或 API 的数据导出为 Excel。
- 动态生成带图表、公式、格式的专业文档。
openpyxl 是处理 .xlsx 文件的主流 Python 库，支持读取、修改、写入、格式设置、公式与图表，是办公自动化与数据分析中不可或缺的工具。
🛠️ 二、openpyxl 基础操作教学
1️⃣ 安装 openpyxl
pip install openpyxl
📂 三、读取 Excel 文件的实用方式
示例代码讲解：
import openpyxl
# 加载现有 Excel 工作簿
wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
# 获取工作表
sheet = wb.active # 或用 wb['Sheet名称']
# 读取特定单元格
print(sheet['C3'].value)
# 遍历所有单元格数据
for row in sheet.iter_rows(min_row=2, values_only=True):
print(row)
教学提示：
sheet['C3']是使用 Excel 坐标定位。sheet.cell(3, 3)是用数字索引，注意从 1 开始。iter_rows()是处理大表格的最佳实践，values_only=True直接返回值。
✍️ 四、写入 Excel 文件的正确流程
示例代码：
import openpyxl
import random
# 创建新工作簿和工作表
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '期末成绩'
# 写入表头
headers = ['姓名', '语文', '数学', '英语']
sheet.append(headers)
# 写入学生数据
students = ['关羽', '张飞', '赵云', '马超', '黄忠']
for name in students:
scores = [random.randint(50, 100) for _ in range(3)]
sheet.append([name] + scores)
wb.save('考试成绩表.xlsx')
最佳实践建议：
- 使用
sheet.append()写入整行更简洁。 - 随机数生成常用于模拟数据。
- 保证标题行总是第一行，以便后续处理（如图表或筛选）。
🎨 五、单元格样式与公式应用
示例代码：
from openpyxl.styles import Font, Alignment, Border, Side
wb = openpyxl.load_workbook('考试成绩表.xlsx')
sheet = wb.active
# 设置表头样式
sheet['E1'] = '平均分'
sheet['E1'].font = Font(size=14, bold=True, color='FF0000')
sheet['E1'].alignment = Alignment(horizontal='center')
sheet['E1'].border = Border(
left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin')
)
# 插入公式
for row in range(2, 7):
sheet[f'E{row}'] = f'=AVERAGE(B{row}:D{row})'
wb.save('考试成绩表.xlsx')
核心知识点：
- 样式对象不可复用，需单独设置。
- 公式可直接写入字符串，Excel 会自动计算。
- 结合颜色、字体和对齐方式可以让输出更美观。
📊 六、插入统计图表（柱状图）
示例代码：
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
sheet = wb.active
data = [
['类别', '销售A组', '销售B组'],
['手机', 40, 30],
['平板', 50, 60],
['笔记本', 80, 70],
['外设', 20, 10],
]
for row in data:
sheet.append(row)
# 创建柱状图
chart = BarChart()
chart.title = '销售统计图'
chart.x_axis.title = '产品类别'
chart.y_axis.title = '销量'
data_ref = Reference(sheet, min_col=2, max_col=3, min_row=1, max_row=5)
cat_ref = Reference(sheet, min_col=1, min_row=2, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cat_ref)
sheet.add_chart(chart, "E2")
wb.save('销售图表.xlsx')
教学说明：
- 使用
Reference来绑定数据和类别。 - 图表插入位置如
'E2'，支持指定到任意位置。 - 图表类型还支持折线图、饼图、雷达图等。
✅ 七、常见实战案例
| 场景 | 用法 |
|---|---|
| 多文件汇总 | openpyxl.load_workbook() 读取多个文件合并 |
| 数据报表自动生成 | 配合随机数、公式与样式 |
| 周报自动填充 | 模板复制 + 数据绑定 |
| 图表自动汇总 | add_chart() 添加每周销售图表 |
| 数据可视化输出 | 将 pandas 数据透视表导出至 Excel |
📚 八、最佳实践与注意事项
- 避免频繁写入磁盘：所有操作尽量在内存中完成后一次性保存。
- 优先使用
iter_rows()或iter_cols()：性能更高。 - 明确工作簿与工作表对象分离：避免混淆
wb.active与wb[sheetname]。 - 慎用公式引用：跨文件或跨工作表引用需额外处理。
- 读写模式区分：若仅写入建议使用
Workbook(write_only=True)。
🧠 九、进阶推荐
对于更大数据量、更复杂逻辑：
- 使用 pandas + openpyxl 组合导出。
xlsxwriter：更高性能写入支持。ExcelWriter：pandas 接口更方便处理多个 sheet。
🧾 十、小结
openpyxl 是处理 .xlsx 文件的核心利器。通过本文你应已掌握：
✅ 读取与写入 Excel 表格 ✅ 设置样式、插入公式 ✅ 自动化生成图表 ✅ 应对常见办公场景
在自动化、可视化、动态报表等方面，Python + Excel 的组合仍然是最具实用价值的工具之一。
如需进一步结合 pandas 使用，请继续学习下一章：《Python 与 pandas 高效处理 Excel 文件》。