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

在现代职场中，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：

pip install openpyxl

2.2 理解 excel 的三大核心概念

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

workbook（工作簿）： 整个 excel 文件（例如 data.xlsx）。
worksheet（工作表）： 工作簿下方的标签页（例如 sheet1, 财务表）。
cell（单元格）： 存储数据的最小方格（例如 a1, b2）。

层级关系： workbook -> worksheet -> cell

3. 实战演练：由浅入深

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

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

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

from openpyxl import load_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 允许我们指定读取的范围
for row in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=3):
    # row 是一个包含单元格对象的元组
    row_data = [cell.value for cell in row]
    print(row_data)

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

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

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

from openpyxl import workbook

# 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]
]

for row in data:
    ws.append(row)

# 5. 直接修改特定单元格
ws['d1'] = "备注"
ws['d2'] = "热销"

# 6. 保存文件
# 注意：如果文件已存在，这步操作会直接覆盖原文件
wb.save('sales_report.xlsx')
print("文件已成功生成！")

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

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

from openpyxl import workbook
from openpyxl.styles import font, 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，写入时避免使用该模式覆盖原文件。