Python实现Excel多表合并与自动化报表生成

案例背景与需求分析

想象一下这个场景：你是一名数据运营，每个月各个城市的分公司都会发来一张当月的销售明细表，存在sales_reports文件夹下。

文件的命名很规则，比如北京_202310.xlsx、上海_202310.xlsx、广州_202310.xlsx。但数量可能多达几十个，每个都得手动打开。

老板的要求也很明确：

1. 把文件夹里所有的Excel数据合并成一张大表。
2. 清洗数据：剔除“销售额”为空的无效记录。
3. 统计出各个城市的总销售额和订单数量。
4. 把合并后的明细和统计结果导出到一个新的Excel文件里。
5. （加分项）把统计结果的表头加粗，背景标黄。

手动操作？打开几十个文件，复制粘贴，不仅容易出错，而且每个月都得重复这一套流程。现在，我们用Python一键解决。

核心模块引入

除了大家熟悉的pandas和openpyxl，还需要用到Python的内置库os和glob，它们专门处理文件和路径，是自动化流程的得力助手。

import os
import glob
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

第一步：批量读取与合并数据

# 1. 定义文件夹路径
folder_path = "sales_reports"

# 2. 使用 glob 匹配文件夹下所有的 .xlsx 文件
# 假设当前目录下有 sales_reports 文件夹
file_paths = glob.glob(os.path.join(folder_path, "*.xlsx"))

# 用于存放每个读取出来的 DataFrame
df_list = []

print("开始批量读取文件...")
for file in file_paths:
    print(f"正在读取: {file}")
    # 读取 Excel
    temp_df = pd.read_excel(file)
    
    # 【小技巧】可以从文件名中提取城市名，作为新的一列加上去
    # 比如从 "sales_reports\北京_202310.xlsx" 中提取 "北京"
    basename = os.path.basename(file) # 获取文件名: 北京_202310.xlsx
    city_name = basename.split("_")[0] # 以下划线分割，取第一部分: 北京
    temp_df["文件来源城市"] = city_name 
    
    df_list.append(temp_df)

# 3. 使用 concat 将所有数据上下拼接合并
master_df = pd.concat(df_list, ignore_index=True)
print(f"合并完成，共计 {len(master_df)} 条数据。")

第二步：数据清洗与统计分析

# 1. 数据清洗：剔除销售额为空的行
master_df.dropna(subset=["销售额"], inplace=True)

# 2. 统计分析：按“文件来源城市”分组，计算总销售额和订单数
summary_df = master_df.groupby("文件来源城市").agg({
    "销售额": "sum",
    "订单号": "count" # 假设原始表有订单号这一列，计数即可代表订单数量
}).reset_index()

summary_df.rename(columns={"订单号": "订单总数", "销售额": "总销售额"}, inplace=True)

第三步：导出数据并使用 openpyxl 调整格式

为了同时写入多个Sheet并进行格式美化，分两步走：先用 pandas 保存基础数据，再用 openpyxl 打开进行格式渲染。

output_file = "月度汇总报告.xlsx"

# 1. pandas 写入数据
with pd.ExcelWriter(output_file) as writer:
    summary_df.to_excel(writer, sheet_name="城市汇总", index=False)
    master_df.to_excel(writer, sheet_name="所有明细", index=False)

# 2. openpyxl 修改格式
wb = load_workbook(output_file)
ws_summary = wb["城市汇总"]

# 定义样式：加粗，黄色背景
header_font = Font(bold=True)
header_fill = PatternFill(fill_type="solid", start_color="FFFF00")

# 遍历第一行（表头），设置样式
for cell in ws_summary[1]:
    cell.font = header_font
    cell.fill = header_fill

# 调整列宽使数据显示更完整
ws_summary.column_dimensions['A'].width = 15
ws_summary.column_dimensions['B'].width = 15
ws_summary.column_dimensions['C'].width = 15

# 保存最终结果
wb.sa ve(output_file)
print("自动化报表生成完毕，格式已美化！")

方法补充

使用 Python 将 Excel 多表合并并生成自动化报表，主要分为四个阶段：批量读取 → 数据合并 → 数据清洗 → 报表生成与样式美化。

核心合并函数：concat 与 merge

在具体实现前，快速了解一下 pandas 中两个核心合并函数的区别：

对于大多数日常场景（合并同结构的多份报表），pd.concat() 是首选。

批量读取与垂直合并

基础版：合并多个同结构 Excel 文件

import pandas as pd
from pathlib import Path
folder_path = "./sales_reports"
# 用 Path.glob 批量获取所有 xlsx 文件
files = list(Path(folder_path).glob("*.xlsx"))
# 收集所有 DataFrame 到列表
dataframes = []
for file in files:
    df = pd.read_excel(file)
    # 【可选】从文件名提取标识列，如月份/城市
    df["来源文件"] = file.stem   # 提取文件名不含扩展名
    dataframes.append(df)
# 一次性垂直合并，性能远优于逐行 append
merged = pd.concat(dataframes, ignore_index=True, sort=False)
# 保存结果
merged.to_excel("合并结果.xlsx", index=False)

进阶版：兼容多场景的成熟方案

在实际生产中，直接逐文件读取有两个隐患：

• 丢数据风险：pd.concat(dataframes, ignore_index=True) 是标准做法。必须注意如果你用老写的 df_total = df_total.append(df)，新版本 pandas 会警告性能问题。
• 格式不统一：需要提前处理数据类型、排除损坏文件：

以下是带进度监控和错误容错的生产级脚本：

import pandas as pd
from pathlib import Path
from tqdm import tqdm   # pip install tqdm

def merge_excels(folder_path, output_name="merged_output.xlsx"):
    print(f"正在扫描: {folder_path}")
    files = list(Path(folder_path).rglob("*.xlsx"))  # 递归查找子文件夹中的文件
    if not files:
        print("未找到 Excel 文件")
        return

    print(f"发现 {len(files)} 个文件")
    all_data = []
    error_files = []

    for file in tqdm(files, desc="处理进度"):
        try:
            df = pd.read_excel(file, dtype=str)       # 统一先按字符串读取，避免类型冲突
            all_data.append(df)
        except Exception as e:
            error_files.append((file.name, str(e)))  # 记录错误文件

    if not all_data:
        print("没有成功读取任何文件")
        return

    # 一次性合并
    merged = pd.concat(all_data, ignore_index=True, sort=False)
    merged.to_excel(output_name, index=False)

    print(f"合并完成！共 {len(merged)} 行。")
    if error_files:
        print(f"警告：{len(error_files)} 个文件读取失败")

同工作簿内多 Sheet 合并

import pandas as pd

file_path = "同一个工作簿.xlsx"
sheets = pd.read_excel(file_path, sheet_name=None)   # sheet_name=None 读取所有 sheet

all_dfs = []
for sheet_name, df in sheets.items():
    all_dfs.append(df)

result = pd.concat(all_dfs, ignore_index=True)
result.to_excel("合并后.xlsx", index=False)

数据清洗与预处理

从不同来源合并的数据通常很“脏”，必需先做四大基本清洗才能保证报表质量。

【注意】所有清洗必须按去重 → 填缺 → 修类型 → 删不可用行的顺序：删完重复和空字段后再修正类型最省事。

水平关联合并

在合并“不同维度的信息”（例如销售明细 + 城市信息）时，使用 pd.merge()：

# 左表：明细表，右表：城市分类映射表
merged_df = pd.merge(sales_detail, city_mapping, on="城市", how="left")

how 参数选择：

• inner：两张表都匹配的记录才保留
• left：以左表为主，右表匹配不到的显示 NaN
• outer：保留所有记录（少用）

自动生成汇总与透视表

# 1. 分组汇总
summary = merged.groupby("城市")["销售额"].agg(["sum", "mean", "count"]).reset_index()

# 2. 数据透视表
pivot = pd.pivot_table(merged, values="销售额", index="城市", columns="季度", aggfunc="sum", fill_value=0)

导出与样式美化（pandas + openpyxl 两步走）

df.to_excel() 能保存数据，但导出的格式存在明显的样式缺陷：

推荐做法：用 pandas 写数据，再用 openpyxl 加格式。

写入数据（pandas）

with pd.ExcelWriter("报表.xlsx") as writer:
    merged.to_excel(writer, sheet_name="明细", index=False)
    summary.to_excel(writer, sheet_name="汇总", index=False)

美化格式（openpyxl）

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("报表.xlsx")
ws = wb["汇总"]   # 或 "明细"

# 设置表头样式：加粗 + 黄色背景
header_font = Font(bold=True)
header_fill = PatternFill(fill_type="solid", start_color="FFFF00")  # 黄色

for cell in ws[1]:   # 第一行是表头
    cell.font = header_font
    cell.fill = header_fill

# 调整列宽
for col in ws.columns:
    max_length = 0
    col_letter = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = min(max_length + 2, 30)
    ws.column_dimensions[col_letter].width = adjusted_width

wb.sa ve("报表_美化.xlsx")

总结

回顾整个系列的核心路径：从环境搭建开始，到用openpyxl精细控制单元格，再到pandas的高效筛选与排序，最后完成这个包含批量文件读取、数据清洗、分组统计、多表页导出及自动化排版的终极实战案例。

这套代码完全可以作为“模板”保存下来。未来遇到类似需求，只需要简单修改一下字段名，就能帮你节省成百上千个小时的重复劳动。这才是自动化的真正魅力。