从零到精通：用 Python openpyxl 批量处理 Excel，彻底告别重复劳动

从零到精通：用 Python openpyxl 批量处理 Excel，彻底告别重复劳动

前言：我们为何要与重复劳动说再见？

想象一下这些场景：

• 每月底：你需要从20个部门的Excel报表中，手动汇总销售数据到一个总表，并计算环比、同比。
• 每周一：你要将系统导出的原始数据，进行格式清洗、空值填充、错误修正，生成给领导看的周报。
• 每天下班前：你要将几十个同事提交的进度表合并，并高亮显示延迟的任务。

这些工作技术含量不高，但极其耗时、枯燥且容易出错。一旦某个步骤出错，可能需要从头再来。更令人沮丧的是，这类“表哥表姐”的工作占据了大量本该用于思考和创造的时间。

Python 的 openpyxl 库正是为此而生。它允许我们以编程方式操作 .xlsx 格式的 Excel 文件，实现读取、写入、修改、格式化和图表生成等几乎所有手动操作。学习它，不是要成为Excel专家，而是要成为自动化流程的设计师，将规则固定成代码，一劳永逸。本文将带你从零开始，深入原理，掌握用代码驾驭Excel的完整能力。

第一章：环境搭建与核心概念初探

在开始编写自动化脚本之前，我们需要确保环境正确。本文所有代码基于 Python 3.10+ 和 openpyxl 3.1+。

安装 openpyxl： 打开你的终端或命令提示符，执行以下命令：

pip install openpyxl

理解 openpyxl 的核心对象模型： openpyxl 将 Excel 文件抽象为三个核心层级，理解它们对后续编程至关重要：

1. Workbook（工作簿）：对应一个 .xlsx 文件。
2. Worksheet（工作表）：对应工作簿里的一个 Sheet（如 Sheet1）。
3. Cell（单元格）：工作表中最基本的单元，通过列字母和行号定位（如 A1）。

当我们用 openpyxl 加载一个 Excel 文件时，内存中就会建立起这样一个对象树，我们的所有操作都是对这些对象的属性进行修改，最后再保存到磁盘。

第一个“Hello World”程序： 让我们创建一个新的Excel文件并写入内容。

from openpyxl import Workbook

# 1. 创建一个新的工作簿（Workbook）对象
wb = Workbook()
# 默认会创建一个名为‘Sheet’的工作表，我们通过 active 属性获取它
ws = wb.active
ws.title = "我的第一个Sheet" # 给工作表重命名

# 2. 操作单元格（Cell）
# 方法一：通过类似字典的键（单元格坐标）来访问
ws['A1'] = "你好，Excel！"
# 方法二：使用 .cell(row, column, value) 方法，更便于循环
ws.cell(row=2, column=1, value="这是第二行") # 相当于 A2

# 3. 保存工作簿到文件
wb.save("hello_openpyxl.xlsx")
print("Excel文件已生成！")

运行这段代码，你会在当前目录下看到 hello_openpyxl.xlsx，打开它，A1和A2单元格已成功写入数据。

小结：Workbook() 创建，ws['A1'] 或 ws.cell() 写入，wb.save() 保存，这是最基础的操作三部曲。

第二章：深入读写——驾驭数据的输入与输出

自动化处理的核心是数据。本章我们学习如何从现有文件读取数据，以及如何将处理好的数据写入指定位置。

加载已有工作簿： 使用 load_workbook 函数，注意 data_only 参数：为 False（默认）时，读取的是单元格的原始内容（如公式=A1+B1）；为 True 时，读取的是单元格计算后的值（如 5）。

from openpyxl import load_workbook

# 加载一个已存在的Excel文件
wb = load_workbook(filename='example.xlsx') # 假设此文件存在
# 获取所有工作表的名称
sheet_names = wb.sheetnames
print(f"所有工作表：{sheet_names}")

# 通过名称获取特定工作表
ws = wb['Sheet1'] # 或 wb[sheet_names[0]]

# 读取单元格内容
cell_value = ws['B5'].value
print(f"B5单元格的值是：{cell_value}")

# 读取一系列单元格：使用切片或iter_rows
for row in ws['A1':'C3']: # 读取A1到C3这个矩形区域
    for cell in row:
        print(cell.coordinate, cell.value) # 打印坐标和值
    print("---行结束---")

高效遍历与写入数据： 对于大数据量，使用 iter_rows 或 iter_cols 方法进行逐行/逐列遍历，性能更好。

# 假设我们要处理一个员工信息表
data_to_write = [
    ['工号', '姓名', '部门', '薪资'],
    ['001', '张三', '技术部', 15000],
    ['002', '李四', '市场部', 12000],
    ['003', '王五', '技术部', 18000],
]

# 从第1行开始，写入数据
start_row = 1
for i, row_data in enumerate(data_to_write, start=start_row):
    for j, cell_value in enumerate(row_data, start=1): # 列从1开始
        ws.cell(row=i, column=j, value=cell_value)

# 在末尾追加一行数据（获取最大行号）
max_row = ws.max_row
ws.cell(row=max_row+1, column=1, value='004')
ws.cell(row=max_row+1, column=2, value='赵六')
# ... 可以继续写入其他列

wb.save('updated_example.xlsx')

注意事项：ws.max_row 和 ws.max_column 返回的是工作表中有数据的最大行和列，是动态获取的，非常有用。

第三章：样式与格式——让报表“专业”起来

一份给领导看的报表，光有数据还不够，还需要清晰的格式。openpyxl 的样式功能非常强大。

核心样式对象：

• Font（字体）：大小、颜色、加粗、斜体。
• PatternFill（填充）：单元格背景色。
• Border（边框）：单元格的边框线。
• Alignment（对齐）：水平对齐、垂直对齐、自动换行。
• NamedStyle（命名样式）：可以复用的样式集合。

给单元格“化妆”：

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

# 1. 设置字体和填充（表头样式）
header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF') # 白色加粗
header_fill = PatternFill(fill_type='solid', fgColor='366092') # 深蓝色填充

for cell in ws[1]: # 遍历第一行的所有单元格
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center', vertical='center') # 居中

# 2. 设置数字格式（例如，将薪资列设置为千位分隔符和两位小数）
salary_column = 4 # 假设薪资在第4列
for row in ws.iter_rows(min_row=2, min_col=salary_column, max_col=salary_column):
    for cell in row:
        cell.number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1 # '#,##0.00'

# 3. 设置边框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
# 为A1到D5区域设置边框
for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=4):
    for cell in row:
        cell.border = thin_border

# 4. 调整列宽和行高
ws.column_dimensions['B'].width = 15 # 设置B列宽
ws.row_dimensions[1].height = 25 # 设置第1行高

小结：样式操作虽然代码稍多，但逻辑清晰。通常我们会将常用的样式（如表头、高亮、警告）定义为函数或 NamedStyle，方便全局调用，保持报表风格统一。

第四章：公式、图表与高级操作

Excel的灵魂在于公式和图表，openpyxl 同样支持。

插入与计算公式： 只需像在Excel里一样，将公式字符串赋值给单元格即可。注意，公式以等号 = 开头。

# 在E2单元格插入求和公式，计算B2到D2的和
ws['E2'] = '=SUM(B2:D2)'
# 在F列计算薪资的税率（假设税率为10%）
for row in range(2, ws.max_row + 1):
    salary_cell = ws.cell(row=row, column=4) # 薪资列
    tax_cell = ws.cell(row=row, column=6) # 税率结果列
    tax_cell.value = f'={salary_cell.coordinate} * 0.1' # 如 =D2*0.1

重要提示：openpyxl 只负责写入公式字符串。当你在Excel中打开文件时，Excel会计算公式结果。如果你需要在Python中获取公式的计算结果，必须在Excel中计算并保存后，再用 load_workbook(filename, data_only=True) 加载，才能读到值。

创建图表： openpyxl 支持创建柱状图、折线图、饼图等多种图表。

from openpyxl.chart import BarChart, Reference

# 假设数据在A1到D4区域，A列为类别，B-D列为数据系列
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=4) # B1:D4
categories = Reference(ws, min_col=1, min_row=2, max_row=4) # A2:A4

# 创建柱状图
chart = BarChart()
chart.title = "部门业绩对比"
chart.x_axis.title = "部门"
chart.y_axis.title = "业绩"

chart.add_data(data, titles_from_data=True) # 从数据第一行读取系列标题
chart.set_categories(categories)

# 将图表插入到工作表的 F1 单元格位置
ws.add_chart(chart, "F1")

第五章：批量处理的精髓——操作多个文件与工作表

真正的自动化是处理成百上千个文件。这需要结合Python的文件操作（os 或 pathlib 模块）。

批量合并多个Excel文件： 假设有一个文件夹，里面是所有销售员的每日业绩表（格式相同），我们需要合并到一个总表。

import os
from openpyxl import load_workbook

source_folder = './daily_reports/'
output_file = './merged_report.xlsx'

# 创建一个新的工作簿用于存放合并结果
merged_wb = Workbook()
merged_ws = merged_wb.active
merged_ws.title = '合并数据'
header_written = False # 标记表头是否已写入

# 遍历文件夹下所有.xlsx文件
for filename in os.listdir(source_folder):
    if filename.endswith('.xlsx'):
        filepath = os.path.join(source_folder, filename)
        print(f'正在处理：{filename}')
        
        wb = load_workbook(filepath, data_only=True)
        ws = wb.active
        
        # 如果是第一个文件，写入表头
        if not header_written:
            for row in ws.iter_rows(min_row=1, max_row=1, values_only=True):
                merged_ws.append(row) # append方法可以按行添加一个可迭代对象
            header_written = True
        
        # 从第二行开始，写入数据
        for row in ws.iter_rows(min_row=2, values_only=True):
            merged_ws.append(row)

merged_wb.save(output_file)
print(f'所有文件合并完成，结果保存在：{output_file}')

操作工作簿内的多个工作表：

# 1. 创建和删除工作表
wb.create_sheet(title='月度汇总', index=0) # 在第一个位置创建
if '无用Sheet' in wb.sheetnames:
    useless_sheet = wb['无用Sheet']
    wb.remove(useless_sheet) # 删除工作表

# 2. 复制工作表内容
source = wb['原始数据']
target = wb.create_sheet('备份数据')
for row in source.iter_rows(values_only=True):
    target.append(row)

完整实战案例：自动生成月度部门薪资报告

场景：你作为HR，每月需要从财务系统导出原始薪资数据（raw_salary.xlsx），然后进行以下操作：

1. 清洗数据（删除测试部门、填充空值）。
2. 按部门分类汇总，计算各部门平均薪资、最高薪资。
3. 生成一个格式美观的汇总报告，并高亮显示平均薪资高于公司平均的部门。
4. 将每个部门的数据单独保存到一个新的工作表。

代码实现：

import os
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment, numbers
from openpyxl.chart import BarChart, Reference

def generate_monthly_salary_report():
    """生成月度薪资报告主函数"""
    # 1. 加载原始数据
    raw_wb = load_workbook('raw_salary.xlsx', data_only=True)
    raw_ws = raw_wb.active
    
    # 数据结构：按部门分组，存储员工薪资列表
    dept_salary_dict = {}
    company_total = 0
    employee_count = 0
    
    # 2. 数据清洗与分组 (假设数据从第2行开始，A:姓名 B:部门 C:薪资)
    for row in raw_ws.iter_rows(min_row=2, values_only=True):
        name, dept, salary = row
        # 清洗：跳过测试部门和薪资为空的数据
        if dept == '测试部' or salary is None:
            continue
        # 分组
        if dept not in dept_salary_dict:
            dept_salary_dict[dept] = []
        dept_salary_dict[dept].append(salary)
        
        # 计算公司总和，用于后续求平均
        company_total += salary
        employee_count += 1
    
    if employee_count == 0:
        print("没有有效数据！")
        return
    
    company_avg = company_total / employee_count
    
    # 3. 创建报告工作簿
    report_wb = Workbook()
    # 移除默认sheet，创建汇总sheet
    default_sheet = report_wb.active
    report_wb.remove(default_sheet)
    summary_ws = report_wb.create_sheet('部门汇总')
    
    # 4. 写入汇总表头并设置样式
    headers = ['部门', '员工数', '总薪资', '平均薪资', '最高薪资', '超过公司平均']
    for col_idx, header in enumerate(headers, start=1):
        cell = summary_ws.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(fill_type='solid', fgColor='DDDDDD')
        cell.alignment = Alignment(horizontal='center')
    
    # 5. 计算并写入各部门数据
    row_idx = 2
    highlight_fill = PatternFill(fill_type='solid', fgColor='FFFF00') # 黄色高亮
    
    for dept, salaries in dept_salary_dict.items():
        emp_count = len(salaries)
        dept_total = sum(salaries)
        dept_avg = dept_total / emp_count
        dept_max = max(salaries)
        is_above_avg = dept_avg > company_avg
        
        summary_ws.cell(row=row_idx, column=1, value=dept)
        summary_ws.cell(row=row_idx, column=2, value=emp_count)
        summary_ws.cell(row=row_idx, column=3, value=dept_total).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
        summary_ws.cell(row=row_idx, column=4, value=dept_avg).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
        summary_ws.cell(row=row_idx, column=5, value=dept_max).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
        summary_ws.cell(row=row_idx, column=6, value='是' if is_above_avg else '否')
        
        # 如果超过公司平均，高亮该行平均薪资单元格
        if is_above_avg:
            summary_ws.cell(row=row_idx, column=4).fill = highlight_fill
        
        # 6. 为每个部门创建详细数据工作表
        detail_ws = report_wb.create_sheet(title=dept[:31]) # 工作表名最多31字符
        detail_ws.append(['姓名', '薪资'])
        for salary in salaries:
            # 在真实场景中，这里应该对应姓名，本例简化处理
            detail_ws.append([f'员工{row_idx}', salary])
        
        row_idx += 1
    
    # 7. 在汇总表末尾添加公司平均数据
    footer_row = row_idx + 1
    summary_ws.cell(row=footer_row, column=1, value='公司整体')
    summary_ws.cell(row=footer_row, column=2, value=employee_count)
    summary_ws.cell(row=footer_row, column=3, value=company_total).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
    summary_ws.cell(row=footer_row, column=4, value=company_avg).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
    summary_ws.cell(row=footer_row, column=4).font = Font(bold=True, color='FF0000')
    
    # 8. 为汇总表创建图表（各部门平均薪资对比）
    chart = BarChart()
    chart.title = "各部门平均薪资对比"
    chart.x_axis.title = "部门"
    chart.y_axis.title = "平均薪资"
    
    # 数据范围：A列部门名（从第2行到倒数第二行），D列平均薪资
    data = Reference(summary_ws, min_col=4, min_row=2, max_row=row_idx-1)
    categories = Reference(summary_ws, min_col=1, min_row=2, max_row=row_idx-1)
    chart.add_data(data, titles_from_data=False)
    chart.set_categories(categories)
    summary_ws.add_chart(chart, f"H2")
    
    # 9. 调整列宽
    for column in summary_ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2)
        summary_ws.column_dimensions[column_letter].width = adjusted_width
    
    # 10. 保存报告
    report_wb.save('月度薪资分析报告.xlsx')
    print(f"报告生成成功！已处理 {len(dept_salary_dict)} 个部门的数据。")

if __name__ == '__main__':
    generate_monthly_salary_report()

这个案例综合运用了数据读取、清洗、分组计算、多工作表操作、样式设置和图表生成，是一个接近真实场景的自动化脚本。你可以根据实际数据格式调整列索引和清洗逻辑。

常见问题 & 踩坑记录

1. 文件被占用，无法保存：这是最常见的问题。确保你的Excel文件没有被其他程序（如微软Excel、WPS）打开。在代码中，完成所有操作后，确保执行了 wb.save('新文件名.xlsx')，Python会正确关闭文件句柄。
2. 读取公式得到的是公式本身，而不是计算结果：这是因为加载工作簿时使用了默认参数 data_only=False。如果需要读取计算后的值，必须在Excel中计算并保存文件后，使用 load_workbook(filename, data_only=True) 来加载。openpyxl 本身不计算Excel公式。
3. 修改后保存，原文件格式丢失（如行高列宽）：openpyxl 在保存时，会保留它能够识别的所有属性。但一些非常复杂或特定的格式（如某些条件格式、自定义视图）可能会丢失。对于重要文件，建议先备份。
4. 处理大量数据时内存占用高或速度慢：openpyxl 默认将整个工作簿加载到内存。对于超大型文件（如几十MB以上），可以考虑：
   • 使用 read_only=True 模式只读加载，仅用于读取数据。
   • 使用 write_only=True 模式创建仅用于写入的工作簿，适合生成大型报表。
   • 对于纯数据读写，pandas 库的 read_excel 和 to_excel 函数性能可能更优，但会丢失格式。
5. 中文字体或编码问题：在设置字体时，使用系统内存在的字体名称（如 Font(name='Microsoft YaHei')）。如果单元格显示乱码，检查Python脚本文件的保存编码是否为UTF-8。

总结 & 延伸阅读

通过本文，我们系统地掌握了使用 openpyxl 自动化处理Excel的核心技能：从基础读写、样式美化，到公式图表、批量操作，最后完成了一个综合实战案例。自动化不是要替代Excel，而是将你从重复、机械的劳动中解放出来，让你有更多时间专注于数据分析、逻辑判断和决策本身。

进阶方向：

1. 结合其他库：将 openpyxl 与 pandas 结合。用 pandas 做复杂的数据分析和转换（如数据透视表、分组聚合），再用 openpyxl 进行精细的格式调整和输出。
2. 开发GUI工具：使用 PyQt、Tkinter 或 Streamlit 为你的自动化脚本制作一个可视化界面，交给非技术同事使用。
3. 定时任务与邮件发送：使用 schedule 或操作系统的定时任务（如cron, Windows任务计划程序），让脚本定期自动运行，并结合 smtplib 库将生成的报告自动发送给相关人员。
4. 处理更老格式：如果需要处理 .xls 格式的文件，可以了解 xlrd 和 xlwt 库。

记住，自动化的第一步，就是把今天手动做的事情，用代码描述出来。从一个小任务开始，逐步构建你的自动化工具箱吧！