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

案例背景与需求分析

场景说明：你是一名数据运营，每个月各个城市的分公司都会给你发一份当月的销售明细表，放在 sales_reports 文件夹下。

文件命名规则如：北京_202310.xlsx，上海_202310.xlsx，广州_202310.xlsx 等等。可能多达几十个文件。

你的老板提出了以下需求：

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

如果手动操作，打开几十个文件复制粘贴极易出错，而且每个月都要重复劳动。现在，我们用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.save(output_file)
print("自动化报表生成完毕，格式已美化！")

方法补充

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

核心合并函数：concat 与 merge

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

函数	用途	适用场景
`pd.concat()`	垂直拼接（行追加）或水平拼接，把多个框架按轴堆叠在一起	多份格式相同的 excel 文件需要上下拼接成大表（最常用）
`pd.merge()`	类似 sql 的 join，基于共同列进行数据关联	两个表需要根据某一列（如用户id）进行左右匹配

对于大多数日常场景（合并同结构的多份报表），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)

数据清洗与预处理

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

清洗任务	常用代码	注意事项
去重	`df.drop_duplicates(inplace=true)`	默认整行判断为重复才删除；可用 `subset=["列名"]` 指定列去重
删除缺行	`df.dropna(subset=["关键列"])`	不强删全局，只删某列缺失的行；若某列如手机号为空的确实可删除
填充缺值	`df["列"].fillna(value)`	数字类用 `0` 或均值，分类用 `"未知"`「保持数据量」——切勿随意删掉太多行，会使统计基数错位
类型纠正	`df["日期"] = pd.to_datetime(df["日期"], errors="coerce")` + 清理非标准条目	先转 datetime 能自动将非法日期转成 nat；再检测是否为 `nat`，把明显错误的行剔除，统一日期格式

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

水平关联合并

在合并“不同维度的信息”（例如销售明细 + 城市信息）时，使用 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() 能保存数据，但导出的格式存在明显的样式缺陷：

问题	现象	原因
格式丢失	列宽显示异常、日期显示为数字、长文本被截断	`to_excel` 不保留原表任何样式
索引误写	第一列出现 `0, 1, 2...`（当成真实数据列）	忘记使用 `index=false`

推荐做法：用 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)) &gt; 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.save("报表_美化.xlsx")