Python一键批量合并多个Excel表格，职场办公高效神器

前言

日常办公里，我们常会收到多个格式一致的Excel表格，比如各部门报表、每日数据统计表、员工信息表等，手动把这些表格复制粘贴合并，不仅耗时费力，还极易出现数据遗漏、格式错乱的问题。

这篇文章带来超简单的Python办公自动化脚本，无需专业编程基础，代码直接复制运行，就能一键合并任意数量Excel文件，自动保留表头，数据完整不丢失，大幅提升办公效率，零基础也能轻松掌握。

一、脚本核心功能

1. 批量读取同一文件夹下所有Excel文件
2. 自动合并格式一致的表格数据
3. 保留原始表格表头，无需重复设置
4. 生成全新合并后的总Excel表格
5. 不修改原文件，操作安全无风险
6. 支持.xlsx格式Excel文件，适配日常办公场景

二、环境配置

只需安装一个数据处理库，复制下方命令，在终端/CMD直接运行即可安装：
pip install pandas openpyxl

Python版本要求：3.7及以上版本，Windows、Mac、Linux系统均可使用。

三、完整可直接运行代码

import os
import pandas as pd

定义存放待合并Excel的文件夹路径

excel_folder = "./excel_files"

存储所有表格数据

all_data = []

遍历文件夹下所有文件

for file_name in os.listdir(excel_folder):
# 只处理xlsx格式Excel文件
if file_name.endswith(".xlsx"):
file_path = os.path.join(excel_folder, file_name)
# 读取单个Excel数据
df = pd.read_excel(file_path, engine="openpyxl")
all_data.append(df)

合并所有数据

result_df = pd.concat(all_data, ignore_index=True)

导出合并后的总表格

result_df.to_excel("合并结果总表.xlsx", index=False, engine="openpyxl")

print("所有Excel文件合并完成，已生成：合并结果总表.xlsx")

四、超详细使用步骤

1. 在电脑上新建一个文件夹，命名为 excel_files
2. 将所有需要合并的Excel表格，全部放入这个文件夹中
3. 新建Python文件，把上方完整代码直接复制进去
4. 将Python文件和 excel_files 文件夹放在同一目录
5. 直接运行Python代码，等待几秒即可生成合并后的总表格

五、注意事项

1. 待合并的所有Excel文件，表头格式必须完全一致，否则会出现数据错位
2. 文件夹名称不要修改，避免路径报错
3. Excel文件名称可随意设置，不影响合并结果
4. 运行前关闭所有打开的Excel文件，防止文件占用导致读取失败

https://gitee.com/sad-knight1996/drink-tea/issues/IJAULT
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULP
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULO
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULN
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULM
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULJ
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULI
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULH
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULG
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULF
https://gitee.com/sad-knight1996/drink-tea/issues/IJAULC
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUL9
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUL8
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUL6
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUL4
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUL3
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUL1
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKY
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKX
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKW
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKS
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKR
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKQ
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKO
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKM
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKL
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKJ
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKH
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKE
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKD
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUKC
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUK8
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUK5
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUK4
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUK3
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUK0
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJY
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJX
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJW
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJT
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJS
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJP
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJM
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJJ
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJH
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJG
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJF
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJC
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJB
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJ9
https://gitee.com/sad-knight1996/drink-tea/issues/IJAUJ8
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHN
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHM
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHK
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHI
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHG
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHF
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHE
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUHB
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUH9
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUH6
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUH5
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUH1
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGZ
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGX
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGW
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGV
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGU
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGS
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGR
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGQ
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGP
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGN
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGL
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGK
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGJ
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGH
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGF
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGE
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUGB
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUG9
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUG7
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUG5
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUG4
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUG1
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUG0
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFY
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFU
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFS
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFQ
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFP
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFM
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFL
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFI
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFG
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFF
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFC
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUFB
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUF9
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUF7
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUF5
https://gitee.com/hanjianmingg/tea-tasting-takeout/issues/IJAUF2
https://gitee.com/anbang713/drink-tea/issues/IJAUDI
https://gitee.com/anbang713/drink-tea/issues/IJAUDH
https://gitee.com/anbang713/drink-tea/issues/IJAUDF
https://gitee.com/anbang713/drink-tea/issues/IJAUDC
https://gitee.com/anbang713/drink-tea/issues/IJAUDB
https://gitee.com/anbang713/drink-tea/issues/IJAUDA
https://gitee.com/anbang713/drink-tea/issues/IJAUD7
https://gitee.com/anbang713/drink-tea/issues/IJAUD6
https://gitee.com/anbang713/drink-tea/issues/IJAUD5
https://gitee.com/anbang713/drink-tea/issues/IJAUD3
https://gitee.com/anbang713/drink-tea/issues/IJAUD2
https://gitee.com/anbang713/drink-tea/issues/IJAUD0
https://gitee.com/anbang713/drink-tea/issues/IJAUCW
https://gitee.com/anbang713/drink-tea/issues/IJAUCV
https://gitee.com/anbang713/drink-tea/issues/IJAUCS
https://gitee.com/anbang713/drink-tea/issues/IJAUCQ
https://gitee.com/anbang713/drink-tea/issues/IJAUCO
https://gitee.com/anbang713/drink-tea/issues/IJAUCN
https://gitee.com/anbang713/drink-tea/issues/IJAUCM
https://gitee.com/anbang713/drink-tea/issues/IJAUCH
https://gitee.com/anbang713/drink-tea/issues/IJAUCG
https://gitee.com/anbang713/drink-tea/issues/IJAUCF
https://gitee.com/anbang713/drink-tea/issues/IJAUC9
https://gitee.com/anbang713/drink-tea/issues/IJAUC4
https://gitee.com/anbang713/drink-tea/issues/IJAUC0
https://gitee.com/anbang713/drink-tea/issues/IJAUBV
https://gitee.com/anbang713/drink-tea/issues/IJAUBQ
https://gitee.com/anbang713/drink-tea/issues/IJAUBN
https://gitee.com/anbang713/drink-tea/issues/IJAUBJ
https://gitee.com/anbang713/drink-tea/issues/IJAUBE
https://gitee.com/anbang713/drink-tea/issues/IJAUB9
https://gitee.com/anbang713/drink-tea/issues/IJAUB7
https://gitee.com/anbang713/drink-tea/issues/IJAUB6
https://gitee.com/anbang713/drink-tea/issues/IJAUB4
https://gitee.com/anbang713/drink-tea/issues/IJAUB1
https://gitee.com/anbang713/drink-tea/issues/IJAUB0
https://gitee.com/anbang713/drink-tea/issues/IJAUAZ
https://gitee.com/anbang713/drink-tea/issues/IJAUAV
https://gitee.com/anbang713/drink-tea/issues/IJAUAS
https://gitee.com/anbang713/drink-tea/issues/IJAUAQ
https://gitee.com/anbang713/drink-tea/issues/IJAUAO
https://gitee.com/anbang713/drink-tea/issues/IJAUAM
https://gitee.com/anbang713/drink-tea/issues/IJAUAI
https://gitee.com/anbang713/drink-tea/issues/IJAUAH
https://gitee.com/anbang713/drink-tea/issues/IJAUAG
https://gitee.com/anbang713/drink-tea/issues/IJAUAD
https://gitee.com/anbang713/drink-tea/issues/IJAUAB
https://gitee.com/anbang713/drink-tea/issues/IJAUAA
https://gitee.com/anbang713/drink-tea/issues/IJAUA8
https://gitee.com/anbang713/drink-tea/issues/IJAUA6
https://gitee.com/anbang713/drink-tea/issues/IJAU9L
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATU5
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATU1
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATTZ
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATTY
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATTX
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATTT
https://gitee.com/guowebs_admin_admin/tea-tasting/issues/IJATTS

六、常见问题解决

1. 命令安装库失败：检查网络，更换国内镜像源，命令改为 pip install pandas openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple
2. 读取不到文件：确认文件夹名称正确，且Excel文件均为.xlsx格式
3. 合并后数据错乱：核对所有Excel表头是否完全统一
4. 运行无反应：检查Python文件和目标文件夹是否在同一层级目录

七、功能拓展方向

1. 新增数据去重功能，自动删除重复行
2. 支持合并.csv格式表格文件
3. 合并后自动添加合计行、数据统计
4. 适配更多Excel版本，兼容.xls格式文件

八、总结

这款Python批量合并Excel脚本，是职场办公必备的效率工具，短短十几行代码，就能解决手动合并表格的繁琐问题，数据处理精准高效，完全规避人工操作失误。

脚本纯办公实用向，无任何敏感内容，代码简洁易懂，既适合职场人提升工作效率，也适合Python新手入门学习，直接复制即可使用，实用性拉满。

如果觉得实用，欢迎点赞收藏，后续会持续更新更多Python办公自动化实用教程！

#Python #办公自动化 #Excel合并 #Python实战 #职场效率 #数据处理 #Python入门