Python实现自动化操作Excel的方法详解

目录

• 引言
• 为什么选择 Python 自动化 Excel
• 前置准备
• 1. 安装 Python
• 2. 安装必要的库：openpyxl
• Python 操作 Excel 核心库：openpyxl 简介
• 实战演练：用 Python 自动化 Excel
• 1. 创建一个新的 Excel 文件
• 2. 写入数据到单元格
• 2.1. 直接指定单元格写入数据
• 2.2. 使用append()方法写入行数据
• 3. 读取 Excel 文件中的数据
• 4. 修改现有 Excel 文件
• 5. 插入/删除行和列
• 6. 操作多个工作表
• 7. 设置单元格样式 (基础)
• 常见问题与注意事项
• 1. 文件路径问题
• 2. 数据类型转换
• 3. 大文件性能
• 4. 保存文件时覆盖
• 更多高级操作方向
• 总结与展望

你是否还在为 Excel 中的重复性工作而烦恼？每天花费大量时间进行数据整理、格式调整、报表生成？如果是这样，那么恭喜你，你来对地方了！Python 作为一门强大且易学的编程语言，能让你彻底告别这些繁琐的手动操作，实现 Excel 自动化，极大地提高工作效率。

本篇文章将作为一份全面的新手指南，带你从零开始学习如何使用 Python 自动化操作 Excel 文件。无论你是数据分析师、办公室文员，还是任何需要处理 Excel 的人，本文都将为你打开一扇通往高效工作的大门。

在深入学习之前，我们先来了解一下为什么 Python 是自动化 Excel 的绝佳选择：

• 效率提升：将耗时数小时甚至数天的工作自动化，只需几秒钟即可完成。
• 减少错误：机器执行任务比手动操作更精确，大大降低人为错误率。
• 可重复性：编写一次脚本，可反复用于处理类似任务，无需每次从头开始。
• 数据处理能力：Python 拥有强大的数据处理库（如 Pandas），能与 Excel 自动化无缝结合，实现更复杂的数据分析。
• 易学易用：Python 语法简洁明了，即使是编程新手也能快速上手。
• 生态丰富：拥有多个成熟的库来操作 Excel，功能全面。

在开始编写代码之前，我们需要做一些简单的准备工作。

如果你的电脑尚未安装 Python，请前往 Python 官方网站下载并安装最新版本。建议选择 3.x 系列的稳定版本。

• 下载地址：https://www.python.org/downloads/
• 安装时请务必勾选 “Add Python to PATH” 选项，这样可以方便地在命令行中运行 Python。

安装完成后，打开命令行工具（Windows 用户搜索 CMD 或 PowerShell，macOS/Linux 用户打开终端），输入以下命令检查 Python 是否安装成功：

python --version

如果显示 Python 版本号，则表示安装成功。

Python 有多个库可以操作 Excel 文件，其中 openpyxl 是一个功能强大且广泛使用的库，专门用于读写 .xlsx 格式的 Excel 文件（Excel 2010 及更高版本）。对于新手来说，它是非常好的入门选择。

在命令行中输入以下命令来安装 openpyxl：

pip install openpyxl

pip 是 Python 的包管理工具，它会自动下载并安装 openpyxl 及其所有依赖项。

openpyxl 库的核心概念包括：

• Workbook (工作簿)：代表一个完整的 Excel 文件。
• Worksheet (工作表)：代表工作簿中的一个选项卡（如 Sheet1, Sheet2 等）。
• Cell (单元格)：代表工作表中的一个最小单位，存储具体的数据。

理解这三个概念，你就能很好地掌握 openpyxl 的基本操作。

现在，我们准备好通过实际代码来学习如何操作 Excel 了！

我们将从创建一个全新的 Excel 工作簿开始。

from openpyxl import Workbook

# 创建一个新的工作簿对象
# 默认会创建一个名为 'Sheet' 的工作表
wb = Workbook()

# 获取当前活动的工作表 (默认创建的第一个工作表)
ws = wb.active

# 可以给工作表设置一个标题
ws.title = "我的第一个工作表"

# 保存工作簿到文件
# 注意：如果文件已存在，此操作会覆盖原文件
wb.save("我的第一个Excel文件.xlsx")

print("Excel 文件 '我的第一个Excel文件.xlsx' 已成功创建！")

运行这段代码后，你会在脚本所在的目录下找到一个名为 我的第一个Excel文件.xlsx 的文件。

有两种主要方式向单元格写入数据：直接指定单元格坐标或使用 append() 方法。

你可以像操作字典一样，通过 ws['A1'] 的方式来访问和写入单元格。

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "销售数据"

# 写入标题行
ws['A1'] = "产品"
ws['B1'] = "销量"
ws['C1'] = "价格"

# 写入数据
ws['A2'] = "苹果"
ws['B2'] = 100
ws['C2'] = 5.5

ws['A3'] = "香蕉"
ws['B3'] = 150
ws['C3'] = 3.0

ws['A4'] = "橘子"
ws['B4'] = 80
ws['C4'] = 4.0

wb.save("销售报告.xlsx")
print("数据已写入 '销售报告.xlsx'。")

append() 方法非常方便，它会将你传入的列表或元组作为一行数据，添加到工作表的最后一行。

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "员工信息"

# 写入标题行
ws.append(["姓名", "年龄", "部门", "入职日期"])

# 写入多行数据
data = [
    ["张三", 30, "销售部", "2020-01-15"],
    ["李四", 25, "市场部", "2021-03-01"],
    ["王五", 35, "技术部", "2019-07-20"]
]

for row_data in data:
    ws.append(row_data)

wb.save("员工信息表.xlsx")
print("数据已写入 '员工信息表.xlsx'。")

读取数据是自动化任务中非常常见的一步。

from openpyxl import load_workbook

# 加载现有的工作簿
try:
    wb = load_workbook("销售报告.xlsx")
    # 获取活动工作表
    ws = wb.active

    print(f"工作表 '{ws.title}' 中的数据：")

    # 遍历所有行和列读取数据
    # ws.iter_rows() 可以迭代所有行，返回单元格元组
    # min_row, max_row, min_col, max_col 可以指定遍历范围
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
        row_values = [cell.value for cell in row]
        print(row_values)

    # 也可以通过指定单元格读取
    print(f"n特定单元格数据：")
    print(f"A1: {ws['A1'].value}")
    print(f"B2: {ws['B2'].value}")

except FileNotFoundError:
    print("错误：文件 '销售报告.xlsx' 不存在，请先运行写入数据的代码创建文件。")

修改现有文件与创建文件和写入数据类似，只是需要先加载文件。

from openpyxl import load_workbook

try:
    wb = load_workbook("销售报告.xlsx")
    ws = wb.active

    # 修改特定单元格的数据
    ws['B2'] = 120  # 将苹果的销量从100改为120
    ws['C2'] = 5.8  # 修改苹果的价格

    # 添加一行新数据
    ws.append(["梨", 90, 6.2])

    # 保存修改，可以覆盖原文件，也可以保存为新文件
    wb.save("更新后的销售报告.xlsx") # 保存为新文件
    # wb.save("销售报告.xlsx") # 覆盖原文件
    print("Excel 文件 '销售报告.xlsx' 已成功更新并保存为 '更新后的销售报告.xlsx'。")

except FileNotFoundError:
    print("错误：文件 '销售报告.xlsx' 不存在，请先运行写入数据的代码创建文件。")

openpyxl 提供了 insert_rows(), delete_rows(), insert_cols(), delete_cols() 等方法来操作行和列。

from openpyxl import load_workbook

try:
    wb = load_workbook("销售报告.xlsx")
    ws = wb.active

    print("原始数据行数:", ws.max_row)

    # 在第 2 行之前插入 1 行
    # 参数1: 插入起始行号，参数2: 插入行数
    ws.insert_rows(2, 1)
    ws['A2'] = "插入的新行"
    ws['B2'] = "示例数据"

    print("插入行后的数据行数:", ws.max_row)

    # 删除第 4 行 (现在是原来的第 3 行)
    ws.delete_rows(4, 1)
    print("删除行后的数据行数:", ws.max_row)

    # 在第 2 列 (B列) 之前插入 1 列
    ws.insert_cols(2, 1)
    ws['B1'] = "新列标题"
    ws['B2'] = "新列数据"

    # 删除第 3 列 (C列)
    ws.delete_cols(3, 1)

    wb.save("修改行和列的销售报告.xlsx")
    print("Excel 文件 '修改行和列的销售报告.xlsx' 已成功创建。")

except FileNotFoundError:
    print("错误：文件 '销售报告.xlsx' 不存在。")

一个 Excel 文件通常包含多个工作表。

from openpyxl import Workbook

wb = Workbook()

# 获取默认创建的活动工作表
ws1 = wb.active
ws1.title = "数据总览"

# 创建一个新的工作表
ws2 = wb.create_sheet("详细信息")
ws2['A1'] = "这是详细信息表"

# 创建另一个工作表，并指定位置 (索引从0开始)
ws3 = wb.create_sheet("报表", 0) # 插入到第一个位置
ws3['A1'] = "这是报表"

# 打印所有工作表名称
print("所有工作表名称:", wb.sheetnames)

# 通过名称获取工作表
sheet_detail = wb["详细信息"]
sheet_detail['A2'] = "更多数据"

wb.save("多工作表文件.xlsx")
print("Excel 文件 '多工作表文件.xlsx' 已成功创建，包含多个工作表。")

openpyxl 允许你设置单元格的字体、颜色、边框、对齐方式等。这里我们展示一个简单的字体加粗示例。

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

try:
    wb = load_workbook("销售报告.xlsx")
    ws = wb.active

    # 设置 A1 单元格字体为粗体，红色
    font_style = Font(name='Arial', size=12, bold=True, color="FF0000") # FF0000 是红色
    ws['A1'].font = font_style

    # 设置 B1 单元格背景色为黄色
    fill_style = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") # FFFF00 是黄色
    ws['B1'].fill = fill_style

    # 设置 C1 单元格居中对齐
    alignment_style = Alignment(horizontal='center', vertical='center')
    ws['C1'].alignment = alignment_style

    # 设置边框 (以 A1 为例)
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))
    ws['A1'].border = thin_border


    wb.save("带样式销售报告.xlsx")
    print("Excel 文件 '带样式销售报告.xlsx' 已创建，并设置了单元格样式。")

except FileNotFoundError:
    print("错误：文件 '销售报告.xlsx' 不存在。")

在使用 Python 自动化 Excel 时，新手可能会遇到一些常见问题：

相对路径 vs. 绝对路径：当你只写文件名时（如 wb.save("report.xlsx")），Python 会在当前脚本运行的目录下查找或创建文件。如果文件不在当前目录，或者你需要指定一个特定位置，请使用绝对路径 (C:/Users/YourName/Documents/report.xlsx 或 /home/YourName/report.xlsx)。

Windows 路径反斜杠：在 Windows 系统中，路径通常使用反斜杠 。但在 Python 字符串中，反斜杠是转义字符。正确的做法是：

• 使用双反斜杠："C:\Users\..."
• 使用正斜杠："C:/Users/..." (推荐，跨平台兼容性更好)
• 使用原始字符串：r"C:Users..."

openpyxl 会自动处理 Python 数据类型到 Excel 单元格类型的转换（例如，整数、浮点数、字符串、日期）。但如果你从 Excel 读取日期数据，它会返回一个 datetime 对象，你可以根据需要进行格式化。

对于包含数万甚至数十万行数据的大型 Excel 文件，直接读写可能会比较慢并占用大量内存。openpyxl 提供了 read_only 模式用于快速读取和 write_only 模式用于快速写入。对于新手，可以暂时不深入研究，但在处理大文件时请记住这些选项。

wb.save("文件名.xlsx") 操作会覆盖同名文件，请务必小心。在修改现有文件时，最好先保存为新的文件名，确认无误后再考虑覆盖。

本文只是介绍了 openpyxl 的基础功能，你可以进一步探索：

• 使用 Pandas 处理数据：结合 Pandas 库可以更高效地进行数据清洗、分析和转换，然后将结果写入 Excel。
• 创建图表：openpyxl 也可以用来在 Excel 中创建各种图表（如柱状图、折线图）。
• 操作公式：读取或写入 Excel 公式。
• 数据验证：设置单元格的数据验证规则。
• 条件格式：根据条件自动设置单元格格式。
• 使用 xlwings：如果你需要在 Windows 上直接控制已打开的 Excel 应用程序，或者需要与 Excel VBA 宏交互，xlwings 是一个强大的选择。

通过本篇文章的学习，你已经掌握了使用 Python openpyxl 库自动化操作 Excel 的基本技能，包括创建、读写、修改、插入/删除行和列以及设置基础样式。这些技能足以帮助你解决日常工作中大部分的重复性 Excel 任务。

Python 自动化 Excel 的世界广阔而充满可能，它将你的数据处理能力提升到一个新的水平。不要停止探索，尝试将你学到的知识应用到实际工作中，你会发现你的工作效率将得到显著提升！

现在，是时候告别重复劳动，让 Python 成为你的得力助手，让数据处理飞起来吧！

以上就是Python实现自动化操作Excel的方法详解的详细内容，更多关于Python自动化操作Excel的资料请关注风君子博客其它相关文章！

您可能感兴趣的文章:

• Python自动化办公之处理CSV和Excel文件的操作指南
• 利用Python实现Excel报表自动化的操作指南
• 使用Python自动化处理Excel的脏数据
• Python利用xlrd和xlwt模块进行自动化操作读写Excel的完整指南
• 使用Python自动化处理Excel表格的操作方法
• 10个Python Excel自动化脚本分享