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

目录

• 1. 什么是 openpyxl？
• 2. 准备工作
• 2.1 安装库
• 2.2 理解 Excel 的三大核心概念
• 3. 实战演练：由浅入深
• 场景一：读取现有的 Excel 文件
• 场景二：创建并写入新的 Excel 文件
• 场景三：美化与样式（进阶）
• 4. 新手常见的“坑”与避坑指南
• 1. 索引从 1 开始，而不是 0
• 2..xls和.xlsx的区别
• 3. 公式读取问题
• 4. 忘记保存
• 5. 总结与下一步

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

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

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

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

为什么选择它？

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

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

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

pip install openpyxl

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

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

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

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

假设你有一个名为 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()

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

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')

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

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

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

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

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

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

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

所有的修改都在内存中进行，直到你执行 wb.save('filename.xlsx') 之前，硬盘上的文件不会有任何变化。

恭喜你！你已经掌握了 Python 操作 Excel 的核心技能。

回顾一下我们学到的内容：

• 加载与创建： 使用 load_workbook 和 Workbook。
• 读写数据： 使用 sheet['A1'] 定位，使用 append 批量写入。
• 样式美化： 使用 Font, PatternFill, Alignment 让表格更专业。

下一步建议：尝试找一个你工作中真实的 Excel 任务（比如合并三个表格的数据），试着用 Python 脚本来完成它。刚开始写代码可能会比手动操作慢，但一旦脚本写好，以后成百上千次的操作都将瞬间完成。

到此这篇关于Python办公自动化之openpyxl使用与避坑全面指南的文章就介绍到这了,更多相关Python openpyxl内容请搜索风君子博客以前的文章或继续浏览下面的相关文章希望大家以后多多支持风君子博客！

您可能感兴趣的文章:

• 从基础到高级技巧详解Python openpyxl设置Excel边框的完全指南
• Python操作Excel的实用工具与库openpyxl/pandas的详细指南
• Python使用openpyxl设置Excel单元格公式和工作簿合并
• Python使用openpyxl读取Excel的操作详解
• Python中openpyxl和pandas的使用示例详解
• python使用Openpyxl操作Excel文件的实现
• Python使用OpenPyXL库操作Excel表的操作指南