夜雨聆风问题场景
作为一名数据分析师或行政人员，你是否经常遇到这样的场景：
每月需要汇总20多个部门的Excel报表，每个报表格式相同，数据却分散在不同的文件中
需要从一个大表格中筛选出符合条件的数据，然后拆分到多个文件中
每周要给不同的客户发送格式相同但数据不同的定制化报表
这些问题有一个共同的特点：重复性的Excel操作占据了大量时间。
一个典型的场景是：假设你每月需要合并30个部门的销售报表，每个报表都有"部门名称"、"销售额"、"客户数"三列。如果手动操作，你需要：
打开第一个文件，复制数据
打开汇总表，粘贴数据
重复30次...
这至少需要30分钟，而且容易出错。但如果用Python，3分钟就能自动完成，而且绝对不会出错。
今天我们就来解决这个问题。
解决方案
环境准备
在开始之前，确保安装了必要的库：
pip install openpyxl pandasopenpyxl：用于读写Excel文件（.xlsx格式）pandas：强大的数据处理库，简化数据操作
场景一：批量合并多个Excel文件
假设文件夹中有多个部门报表，格式相同，现在需要合并成一个汇总表。
importpandasaspd
importos
frompathlibimportPath
defmerge_excel_files(folder_path, output_file):
"""
合并指定文件夹下所有Excel文件
folder_path: 文件夹路径
output_file: 输出文件路径
"""
# 获取文件夹下所有xlsx文件
folder = Path(folder_path)
excel_files = list(folder.glob("*.xlsx"))
ifnotexcel_files:
print("未找到Excel文件")
return
# 存储所有数据
all_data = []
# 遍历每个文件读取数据
forfileinexcel_files:
print(f"正在读取: {file.name}")
df = pd.read_excel(file)
# 新增一列标记数据来源
df['来源文件'] = file.name
all_data.append(df)
# 合并所有数据
merged_df = pd.concat(all_data, ignore_index=True)
# 保存结果
merged_df.to_excel(output_file, index=False)
print(f"合并完成！共处理 {len(excel_files)} 个文件，{len(merged_df)} 条数据")
print(f"结果已保存至: {output_file}")
# 使用示例
merge_excel_files(
folder_path="./部门报表",
output_file="./汇总报表/月度汇总.xlsx"
)
场景二：按条件拆分数据到不同文件
如果需要根据某一列的值，将数据拆分到不同的文件中，比如按部门拆分成独立报表：
importpandasaspd
defsplit_excel_by_column(input_file, split_column, output_folder):
"""
根据指定列的值拆分Excel文件
split_column: 用于分组的列名
output_folder: 输出文件夹路径
"""
# 读取原始数据
df = pd.read_excel(input_file)
# 获取该列的所有唯一值
unique_values = df[split_column].unique()
forvalueinunique_values:
# 筛选出当前值对应的数据
subset = df[df[split_column] == value]
# 生成文件名（处理特殊字符）
safe_name = str(value).replace('/', '-').replace('\\', '-')
output_file = f"{output_folder}/{safe_name}.xlsx"
# 保存
subset.to_excel(output_file, index=False)
print(f"已生成: {output_file} ({len(subset)} 条数据)")
# 使用示例
split_excel_by_column(
input_file="./汇总报表/月度汇总.xlsx",
split_column="部门",
output_folder="./部门报表单独"
)
场景三：批量格式化报表
有时候需要对多个文件进行格式调整，比如统一设置列宽、字体、添加表头样式等：
importopenpyxl
fromopenpyxl.stylesimportFont, Alignment, PatternFill
frompathlibimportPath
defformat_excel_report(file_path):
"""
为Excel报告添加格式化样式
"""
wb = openpyxl.load_workbook(file_path)
ws = wb.active
# 定义样式
header_font = Font(bold=True, size=12, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_alignment = Alignment(horizontal="center", vertical="center")
# 设置表头样式
forcellinws[1]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
# 自动调整列宽
forcolumninws.columns:
max_length = 0
column_letter = column[0].column_letter
forcellincolumn:
ifcell.value:
max_length = max(max_length, len(str(cell.value)))
adjusted_width = min(max_length+2, 50)
ws.column_dimensions[column_letter].width = adjusted_width
wb.save(file_path)
print(f"格式已更新: {file_path}")
defbatch_format_excel(folder_path):
"""
批量格式化文件夹下的所有Excel文件
"""
folder = Path(folder_path)
excel_files = list(folder.glob("*.xlsx"))
forfileinexcel_files:
format_excel_report(file)
# 使用示例
batch_format_excel("./待格式化报表")
代码详解
1. pandas读取Excel
df = pd.read_excel(file)pd.read_excel() 是pandas读取Excel的核心函数，它会自动识别Excel的工作表结构，返回一个DataFrame对象。DataFrame是pandas中最核心的数据结构，可以理解为一张表格。
常用参数：
sheet_name：指定读取哪个工作表，默认为0（第一个）header：指定哪一行作为表头，默认0
2. 数据合并
merged_df = pd.concat(all_data, ignore_index=True)pd.concat() 用于拼接多个DataFrame。关键参数：
ignore_index=True：重置索引，避免合并后索引重复如果数据列不完全一致，缺失的列会自动填充NaN
3. 数据筛选
subset = df[df[split_column] == value]这是pandas的条件筛选语法。df[条件] 会返回满足条件的行，组成新的DataFrame。
4. openpyxl样式设置
cell.font = Font(bold=True)
cell.fill = PatternFill(start_color="4472C4", ...)
openpyxl提供了丰富的样式设置接口，可以单独设置单元格的字体、填充、对齐方式等属性。
扩展思考
掌握了这几个基础操作后，你可以组合出更多实用功能：
1. 定时自动处理配合Python的schedule库或系统的定时任务，可以实现：
每天早上8点自动汇总昨日数据
每周一自动发送上周报表
2. 数据统计与可视化在pandas基础上加上matplotlib或plotly，可以：
自动生成数据透视表
生成统计图表嵌入Excel
3. 多Sheet操作如果每个部门的报表有多个Sheet（原始数据、明细、汇总），可以使用：
# 读取所有Sheet
excel_file = pd.ExcelFile("file.xlsx")
for sheet_name in excel_file.sheet_names:
df = pd.read_excel(excel_file, sheet_name=sheet_name)
4. 与邮件系统集成结合smtplib和email库，可以实现：
自动生成报表后直接发送邮件
根据数据内容动态生成邮件正文
Python批量处理Excel的核心就是：把重复性的操作交给代码去执行。当你需要处理的文件超过5个，手动操作的时间成本就已经超过了写代码的时间成本。
建议从小处入手，先用第一个脚本解决一个具体问题，尝到甜头后再逐步扩展。编程效率的提升往往就是从这种"偷懒"开始的。
