Excel自动化基础：使用openpyxl和pandas读取、写入、修改Excel文件
Excel作为职场中最通用的数据载体，几乎贯穿了日常报表、数据汇总、业务分析的每一个环节。然而，当面对成百上千个文件、跨表引用、格式统一、周期性更新等任务时，纯手工操作不仅效率低下，而且极易出错。Python生态中，openpyxl与pandas是处理Excel的两大核心工具：前者擅长精细控制工作簿的单元格、样式、公式与图表，后者则擅长批量化的数据清洗、聚合与透视。本章将围绕“读取—写入—修改—样式—公式—条件格式”这一完整链路，通过实战案例帮助学员建立Excel自动化的系统化思维。
上图展示了Excel自动化的完整链路：从数据源出发，经过读取层进入处理层，最终输出为新的工作簿或报表。在实际项目中，openpyxl与pandas并非互斥，而是互补关系——pandas负责“数据计算”，openpyxl负责“工作簿呈现”。下面我们将从核心原理开始，逐步拆解每个环节的实战技巧。
3.1 核心原理：openpyxl与pandas的定位差异
要高效使用这两个库，首先需要理解它们的底层设计哲学。openpyxl是一个工作簿级操作库，它直接映射Excel文件中的单元格、行、列、工作表、样式、图表等对象。这意味着你可以像操作真实Excel界面一样，精确控制每一个单元格的字体、边框、填充色、数据验证，甚至插入图片和图表。而pandas是一个数据级操作库，它将Excel表格抽象为DataFrame（二维表结构），专注于数据的筛选、分组、合并、透视等分析操作，但对单元格样式、合并单元格、公式等“外观层”的支持非常有限。
| 对比维度 | openpyxl | pandas |
|---|---|---|
| 核心对象 | Workbook / Worksheet / Cell | DataFrame / Series |
| 擅长领域 | 样式、公式、条件格式、图表、合并单元格 | 数据清洗、聚合、透视、时间序列、统计分析 |
| 读取速度 | 较慢（逐单元格解析） | 较快（基于内存的向量化操作） |
| 写入方式 | 直接修改单元格对象 | 通过to_excel()整体写入 |
| 公式支持 | 支持写入公式字符串，如"=SUM(A1:A10)" | 不支持写入公式，只能写计算结果 |
| 样式支持 | 完整支持字体、边框、填充、对齐、数字格式 | 仅支持简单的列宽、行高设置 |
| 典型场景 | 生成带格式的报表、模板、自动化填表 | 数据清洗、统计分析、批量合并多个Excel |
在实际项目中，推荐采用“pandas计算 + openpyxl呈现”的组合策略：先用pandas完成数据读取、清洗、聚合等重计算任务，再将结果通过openpyxl写入模板，并应用样式、公式和条件格式。这种分层设计既保证了计算效率，又满足了报表的美观与交互需求。
3.2 读取与写入：从基础操作到批量处理
读取Excel文件是自动化流程的第一步。对于openpyxl，使用load_workbook()函数加载工作簿，然后通过sheetnames属性获取所有工作表名称，再通过active或指定名称获取工作表。对于pandas，read_excel()函数更为简洁，只需指定文件路径和sheet_name参数即可返回DataFrame。但需要注意，pandas读取时会自动将首行作为列名，且默认将第一列作为索引，这有时需要额外处理。
# openpyxl 读取示例
from openpyxl import load_workbook
wb = load_workbook('销售数据.xlsx', data_only=False) # data_only=False表示读取公式而非计算结果
print(wb.sheetnames) # ['1月', '2月', '3月']
ws = wb['1月']
for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
print(row)
# pandas 读取示例
import pandas as pd
df = pd.read_excel('销售数据.xlsx', sheet_name='1月', header=0)
print(df.head())写入操作同样有两种路径。openpyxl通过Workbook()创建新工作簿，或通过load_workbook()加载现有文件后修改，最后调用save()保存。pandas则通过DataFrame.to_excel()方法写入，但该方法会覆盖整个工作表，且无法保留原有格式。因此，当需要向已有工作簿的特定工作表追加数据时，必须使用openpyxl。
to_excel()时，如果目标文件已存在，默认会覆盖整个文件。若希望保留其他工作表，应先用openpyxl加载原文件，再通过pd.ExcelWriter配合mode='a'参数追加，但需注意该模式仅支持openpyxl引擎。
批量处理多个Excel文件是办公自动化的高频需求。假设一个文件夹下有12个月度销售报表，需要将所有数据汇总到一个总表中。此时，使用glob.glob()匹配文件路径，循环读取每个文件，用pandas的concat()合并，最后一次性写入汇总表。这种模式将处理时间从手工操作的数小时压缩到几秒钟。
3.3 修改与样式：让报表具备专业质感
仅仅有数据是不够的，一份专业的报表需要清晰的标题、合适的列宽、醒目的表头、以及数据条或色阶等可视化元素。openpyxl提供了完整的样式控制能力。通过Font、PatternFill、Border、Alignment等类，可以精确设置单元格的字体、背景色、边框和对齐方式。此外，column_dimensions和row_dimensions属性可以调整列宽和行高。
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
# 设置表头样式
header_font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
center_align = Alignment(horizontal='center', vertical='center')
thin_border = Border(
left=Side(style='thin', color='999999'),
right=Side(style='thin', color='999999'),
top=Side(style='thin', color='999999'),
bottom=Side(style='thin', color='999999')
)
# 应用到表头区域
for col in range(1, 6):
cell = ws.cell(row=1, column=col)
cell.font = header_font
cell.fill = header_fill
cell.alignment = center_align
cell.border = thin_border
# 设置列宽
for col in range(1, 6):
ws.column_dimensions[get_column_letter(col)].width = 15除了静态样式，openpyxl还支持条件格式，即根据单元格的值动态改变其外观。例如，将销售额低于目标的单元格标红，将高于目标的标绿。通过CellIsRule或FormulaRule可以轻松实现。条件格式在数据监控、异常告警场景中非常实用，它让报表从“静态记录”升级为“动态看板”。
keep_vba=True参数）。因此，对于包含复杂宏或高级图表的工作簿，建议先备份原文件，再在副本上进行自动化操作。
3.4 公式与函数：让Excel自己“计算”
在自动化报表中，有时我们希望Excel文件保留公式，以便用户在打开文件后可以动态调整参数并自动重算。openpyxl允许直接向单元格写入公式字符串，例如ws['C10'] = '=SUM(C2:C9)'。但需要注意，openpyxl本身不会计算公式结果，它只是将公式文本写入文件。当用户用Excel打开文件时，Excel会自动计算公式并显示结果。如果使用data_only=True读取，则只能获取上次Excel保存时的缓存结果，若文件从未被Excel打开过，则读取到的值为None。
# 写入公式
ws['F2'] = '=IF(E2>=100000, "达标", "未达标")'
ws['G2'] = '=ROUND(D2*0.15, 2)' # 计算15%的提成
# 使用pandas计算结果后，再通过openpyxl写入公式
import pandas as pd
df = pd.read_excel('数据.xlsx')
df['提成'] = df['销售额'] * 0.15
# 将计算结果写入Excel，同时保留公式列
with pd.ExcelWriter('输出.xlsx', engine='openpyxl', mode='a') as writer:
df.to_excel(writer, sheet_name='汇总', index=False)公式的进阶用法包括跨工作表引用（如=SUM('1月'!B2:B10)）、数组公式、以及使用_xlfn前缀调用新函数（如_xlfn.CONCAT）。在自动化生成报表时，建议将公式与静态值混合使用：对于需要用户交互的单元格使用公式，对于纯计算结果的单元格则直接写入数值，以减少文件体积并提高打开速度。
3.5 实战案例：构建月度销售分析看板
下面通过一个完整的案例，将上述知识点串联起来。假设某公司有12个月份的销售明细表，每张表包含“日期、产品、区域、销售员、数量、单价、销售额”七列。现在需要生成一份季度销售分析看板，要求包含：①各区域季度销售额汇总；②销售员排名Top5；③销售额低于目标的区域标红；④表头带颜色、数据带边框。
背景与约束：原始文件为12个独立的.xlsx文件，存放在./sales/目录下。目标输出为一个新的工作簿季度看板.xlsx，包含三个工作表：“汇总”、“区域分析”、“销售员排名”。由于公司要求保留公式以便动态更新，因此汇总表中的“目标销售额”列使用公式引用参数表。
实施步骤：
- 数据读取与合并：使用
glob匹配所有文件，pandas循环读取并添加“月份”列，然后concat合并为总表。 - 数据透视：使用
pivot_table按区域汇总销售额，按销售员汇总销售额并排序取Top5。 - 创建输出工作簿：使用openpyxl新建工作簿，创建三个工作表。
- 写入数据与公式：将pandas计算结果写入对应工作表，在“汇总”表中添加“目标”列，并写入公式
=IF(C2>=D2,"达标","未达标")。 - 应用样式：为表头设置深蓝色背景、白色加粗字体；为数据区域设置边框；设置列宽和行高。
- 添加条件格式：在“区域分析”表中，对“销售额”列使用
CellIsRule，当值小于目标值时填充浅红色。 - 保存并验证：保存文件，用Excel打开检查公式是否正确计算、样式是否完整。
| 步骤 | 关键代码/操作 | 预期结果 |
|---|---|---|
| 1. 合并数据 | pd.concat([pd.read_excel(f) for f in files]) | 得到包含所有月份的总DataFrame |
| 2. 区域汇总 | pd.pivot_table(df, values='销售额', index='区域', aggfunc='sum') | 各区域销售额汇总表 |
| 3. 销售员排名 | df.groupby('销售员')['销售额'].sum().nlargest(5) | Top5销售员及销售额 |
| 4. 写入公式 | ws['D2'] = '=IF(C2>=100000,"达标","未达标")' | Excel打开后自动计算 |
| 5. 条件格式 | ws.conditional_formatting.add('C2:C10', CellIsRule(...)) | 低于目标自动标红 |
结果对比与复盘：手工制作该看板大约需要2小时，且容易遗漏格式或公式错误。使用脚本后，整个过程在10秒内完成，且每次更新数据只需重新运行脚本。在实施过程中，遇到的主要问题是pandas读取日期列时自动转换为Timestamp类型，导致写入Excel后格式异常。解决方案是在read_excel()中指定dtype={'日期': str}，或使用parse_dates=False。另一个教训是，当使用ExcelWriter的mode='a'追加时，如果目标工作表已存在，会抛出ValueError，需要先删除原工作表或使用if_sheet_exists='replace'参数。
3.6 常见误区与总结复盘
在Excel自动化实践中，初学者常陷入以下几个误区：误区一：试图用pandas完成所有操作，包括样式和公式，结果发现pandas根本不支持这些功能，最终不得不返工。误区二：使用openpyxl读取大量数据时，采用逐单元格遍历的方式，导致性能极低。正确做法是使用ws.values或pandas.read_excel批量读取。误区三：忽略data_only参数的含义，导致读取公式单元格时得到None值。误区四：在保存文件时覆盖了原文件，且没有备份，一旦脚本有bug，原始数据丢失。