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

Python办公自动化之openpyxl使用与避坑全面指南

news 2026/6/1 3:36:09

在现代职场中，Excel 无疑是数据处理的霸主。然而，面对成千上万行的数据、每日重复的报表生成任务，手动操作不仅效率低下，而且容易出错。

如果你曾梦想有一只“看不见的手”帮你自动处理 Excel 表格，那么 Python 的openpyxl库就是你的最佳搭档。

本指南专为编程新手设计，将带你从零开始，系统掌握使用 Python 读写、修改和格式化 Excel 文件的核心技能。

1. 什么是 openpyxl？

openpyxl是一个用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。

为什么选择它？

无需安装 Excel：即使电脑上没装 Office，也能处理表格。
不仅是读取：它能修改格式、插入公式、绘制图表。
自动化神器：它可以将原本需要几小时的人工操作缩短至几秒钟。

2. 准备工作

在开始之前，请确保你已经安装了 Python 环境。

2.1 安装库

打开你的终端（Terminal 或 CMD），输入以下命令安装openpyxl：

1	`pipinstallopenpyxl`

2.2 理解 Excel 的三大核心概念

在使用代码操作 Excel 之前，我们需要建立一个心理模型，这与我们手动操作 Excel 是一致的：

Workbook（工作簿）：整个 Excel 文件（例如data.xlsx）。
Worksheet（工作表）：工作簿下方的标签页（例如Sheet1,财务表）。
Cell（单元格）：存储数据的最小方格（例如A1,B2）。

层级关系：Workbook -> Worksheet -> Cell

3. 实战演练：由浅入深

我们将通过三个场景来掌握核心功能。

场景一：读取现有的 Excel 文件

假设你有一个名为sample.xlsx的文件，我们想读取里面的数据。

fromopenpyxlimportload_workbook

# 1. 加载工作簿

# data_only=True 表示读取公式计算后的值，而不是公式本身

wb=load_workbook('sample.xlsx', data_only=True)

# 2. 选择工作表

# 方式A：获取当前活跃的表（通常是第一个）

sheet=wb.active

# 方式B：根据表名获取

# sheet = wb['Sheet1']

# 3. 读取特定单元格的值

print(f"A1单元格的值是: {sheet['A1'].value}")

# 4. 遍历读取多行数据

print("--- 开始遍历数据 ---")

# iter_rows 允许我们指定读取的范围

forrowinsheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=3):

# row 是一个包含单元格对象的元组

row_data=[cell.valueforcellinrow]

print(row_data)

# 5. 关闭工作簿（虽然 Python 会自动回收，但显式关闭是好习惯）

wb.close()

场景二：创建并写入新的 Excel 文件

现在，我们来从头创建一个报表。

fromopenpyxlimportWorkbook

# 1. 创建一个新的工作簿对象（注意：不需要 load_workbook）

wb=Workbook()

# 2. 获取活跃的工作表

ws=wb.active

ws.title="销售统计"# 修改表名

# 3. 写入表头

headers=['日期','产品','销售额']

ws.append(headers)# append 方法会将列表数据添加到当前数据的下一行

# 4. 写入数据

data=[

['2023-10-01','键盘',500],

['2023-10-01','鼠标',120],

['2023-10-02','显示器',1500]

]

forrowindata:

ws.append(row)

# 5. 直接修改特定单元格

ws['D1']="备注"

ws['D2']="热销"

# 6. 保存文件

# 注意：如果文件已存在，这步操作会直接覆盖原文件

wb.save('sales_report.xlsx')

print("文件已成功生成！")

场景三：美化与样式（进阶）

仅仅只有数据是不够的，专业的报表需要字体、颜色和对齐方式。

fromopenpyxlimportWorkbook

fromopenpyxl.stylesimportFont, PatternFill, Alignment

wb=Workbook()

ws=wb.active

# 写入测试数据

ws['A1']="季度总结报告"

ws.merge_cells('A1:C1')# 合并单元格

# --- 设置样式 ---

# 1. 定义字体：加粗，大小14，蓝色

title_font=Font(name='微软雅黑', size=14, bold=True, color="0000FF")

# 2. 定义背景填充：黄色

yellow_fill=PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 3. 定义对齐方式：水平居中，垂直居中

center_align=Alignment(horizontal='center', vertical='center')

# --- 应用样式 ---

cell=ws['A1']

cell.font=title_font

cell.fill=yellow_fill

cell.alignment=center_align

wb.save('styled_report.xlsx')

4. 新手常见的“坑”与避坑指南

在使用openpyxl的过程中，初学者经常会遇到以下问题：

1. 索引从 1 开始，而不是 0

在 Python 的列表（List）中，第一个元素的索引是0。但在openpyxl中，Excel 的行（Row）和列（Column）都是从1开始的。

错误：sheet.cell(row=0, column=0)
正确：sheet.cell(row=1, column=1)(代表 A1)

2..xls和.xlsx的区别

openpyxl不支持旧版的.xls格式（Excel 97-2003）。

解决方案：如果你需要处理.xls文件，请先将其另存为.xlsx，或者使用另一个库xlrd（但xlrd新版也逐渐停止支持.xlsx，建议统一转换为.xlsx处理）。

3. 公式读取问题

当你读取一个包含公式的单元格（如=SUM(A1:A5)）时：

默认情况下，openpyxl会读取到字符串"=SUM(A1:A5)"。
解决方案：在加载文件时使用load_workbook('file.xlsx', data_only=True)，这样读取到的就是计算后的数字结果。注意：使用此模式保存文件后，公式可能会丢失，变成纯数值。通常建议读取用data_only=True，写入时避免使用该模式覆盖原文件。