Python处理Excel：批量替换内容与合并多个文件（附完整代码）
Python处理Excel：批量替换内容与合并多个文件（附完整代码）
一、知识点简介
日常工作中，Excel几乎是每个职场人的必备工具：报表汇总、数据清洗、模板填充……但当你要把1000行数据里的"北京市"全部改成"北京"，或者把10个部门的Excel合并成一张总表时，手动操作简直是噩梦。
本文教你用 Python 搞定这两件事：
- 替换内容：按关键词/正则/条件精准替换，支持整表或指定列
- 合并内容：把多个Excel合并成一个，支持按 sheet、按列去重
全程零门槛，代码复制就能跑，不装任何付费插件。
二、适用场景
| 场景 | 用 Python 解决什么问题 |
|---|---|
| 数据清洗 | 把"北京市"/"北京"/"Beijing"统一替换成"北京" |
| 报表合并 | 月末把10个分公司的Excel合并成一张总表 |
| 模板填充 | 批量把模板中的占位符 {name} 替换成真实姓名 |
| 字段统一 | 把"男/女"改成"1/0"，把"是/否"改成布尔值 |
| 数据脱敏 | 把手机号、身份证号中间几位打码 |
| 去重合并 | 把多张表合并后按某列（如工号）去重 |
💡 为什么用 Python 而不是 Excel 内置功能？
- Excel 替换不支持正则（Python 的
re模块支持），批量处理多个文件要写 VBA，Python 一行命令搞定- 合并时自动去重、多 sheet 处理，VBA 写30行，Python 10行搞定
三、核心原理
3.1 用什么库？
Python 操作 Excel 主要两个库，选哪个看情况：
| 库 | 特点 | 适用场景 |
|---|---|---|
| openpyxl | 读写字.xlsx/.xlsm，功能全 | 99%的场景用它 |
| xlrd/xlwt | 只读/只写老格式 .xls | 老文件（.xls）才用 |
本文全部用 openpyxl，安装一行命令：
pip install openpyxl
3.2 Excel 文件在你电脑上是什么结构？
一个Excel文件（workbook）
├── Sheet1（工作表）
│ ├── A列 B列 C列
│ ├── 张三 北京 28
│ └── 李四 上海 35
├── Sheet2
│ └── ...
└── Sheet3
Python 操作的核心逻辑就是：
- 打开文件 → 拿到 workbook 对象
- 定位 sheet → 拿到 worksheet 对象
- 读写单元格 →
sheet['A1']或sheet.cell(row=1, column=1) - 保存文件 →
wb.save('output.xlsx')
3.3 替换的原理
Python 里字符串替换就是一行代码的事：
# 基础替换
text = "北京市朝阳区"
text = text.replace("北京", "天津") # "天津市朝阳区"
# 正则替换（支持复杂规则）
import re
text = re.sub(r"1[3-9]\d{9}", "1XXXXXXXXXX", text) # 手机号脱敏
Excel 替换就是把表格里每个单元格都遍历一遍，满足条件的就改掉。
3.4 合并的原理
合并多个文件的本质是把数据都读到内存里，然后用 extend() 或 pandas.concat() 拼接成一张大表。
# 伪代码逻辑
all_rows = []
for file in [file1, file2, file3]: # 遍历每个文件
rows = 读取该文件的所有行
all_rows.extend(rows) # 追加到列表
合并结果 = 去掉all_rows里的重复行
写入新的Excel文件
四、完整实操步骤
环境准备
pip install openpyxl pandas
pandas用来合并去重，比纯 openpyxl 方便很多。
五、代码实战
5.1 实战一：批量替换内容
场景：把"北京市"替换成"北京"，"男/女"替换成"1/0"
import openpyxl
import re
from typing import Union, List, Dict
def replace_cell_value(cell, old_text=None, new_text=None,
regex_pattern=None, regex_repl=None,
replace_map: Dict[str, str] = None):
"""
替换单个单元格的值（核心替换逻辑）
参数（至少传一种）：
old_text / new_text : 普通文本替换
regex_pattern / regex_repl: 正则替换（支持复杂规则）
replace_map : 字典映射替换（适合多对一场景）
"""
if cell.value is None:
return
val = str(cell.value)
if replace_map:
# 字典映射替换：{"北京市":"北京", "男":"1", "女":"0"}
for k, v in replace_map.items():
val = val.replace(k, v)
elif regex_pattern is not None:
# 正则替换：支持复杂匹配规则
val = re.sub(regex_pattern, regex_repl, val)
elif old_text is not None:
# 普通文本替换
val = val.replace(old_text, new_text if new_text else "")
cell.value = val
def replace_in_excel(input_path: str,
output_path: str,
sheet_names: Union[str, List[str]] = None,
columns: List[int] = None,
**kwargs):
"""
替换Excel文件内容
参数：
input_path : 输入文件路径
output_path : 输出文件路径
sheet_names : 要处理的工作表名，None表示全部sheet
传字符串表示单个，传列表表示多个
columns : 要处理的列索引（从1开始），None表示全部列
例如：[1, 3, 5]表示只处理第1、3、5列
**kwargs : 透传给 replace_cell_value（见上方函数参数）
"""
wb = openpyxl.load_workbook(input_path)
# 确定要处理哪些 sheet
if sheet_names is None:
sheets = wb.worksheets
elif isinstance(sheet_names, str):
sheets = [wb[sheet_names]]
else:
sheets = [wb[name] for name in sheet_names]
for sheet in sheets:
print(f" 📄 正在处理 Sheet: {sheet.title}")
# 遍历所有行
for row in sheet.iter_rows():
for cell in row:
# 如果指定了列号，跳过不在范围内的单元格
if columns and cell.column not in columns:
continue
replace_cell_value(cell, **kwargs)
wb.save(output_path)
print(f"✅ 完成！已保存至: {output_path}")
# ====================== 调用示例 ======================
# 【示例1】普通文本替换：把"北京市"改成"北京"
replace_in_excel(
input_path="员工信息表.xlsx",
output_path="员工信息表_替换后.xlsx",
old_text="北京市",
new_text="北京"
)
# 【示例2】多对一映射替换：男→1，女→0
replace_in_excel(
input_path="员工信息表.xlsx",
output_path="员工信息表_性别编码.xlsx",
replace_map={"男": "1", "女": "0"}
)
# 【示例3】正则替换：手机号中间4位打码
replace_in_excel(
input_path="客户列表.xlsx",
output_path="客户列表_脱敏.xlsx",
regex_pattern=r"(1[3-9]\d)(\d{4})(\d{4})",
regex_repl=r"\1****\3"
)
# 【示例4】只处理指定 sheet 和指定列（第1列和第3列）
replace_in_excel(
input_path="数据.xlsx",
output_path="数据_已处理.xlsx",
sheet_names=["销售数据", "退货数据"],
columns=[1, 3],
old_text="2023",
new_text="2024"
)
# 【示例5】把多个关键词统一替换（字典法）
replace_in_excel(
input_path="地址表.xlsx",
output_path="地址表_统一.xlsx",
replace_map={
"北京市": "北京",
"上海市": "上海",
"广州市": "广州",
"深圳市": "深圳",
"北京市朝阳区": "北京-朝阳区",
"北京市海淀区": "北京-海淀区",
}
)
运行效果
📄 正在处理 Sheet: 员工信息
📄 正在处理 Sheet: 部门信息
✅ 完成！已保存至: 员工信息表_替换后.xlsx
替换前后对比（员工信息表.xlsx）：
| 姓名 | 城市 | 性别 |
|---|---|---|
| 张三 | 北京市朝阳区 | 男 |
| 李四 | 北京市海淀区 | 女 |
↓ 替换后 ↓
| 姓名 | 城市 | 性别 |
|---|---|---|
| 张三 | 北京朝阳区 | 1 |
| 李四 | 北京海淀区 | 0 |
5.2 实战二：合并多个Excel文件
场景：把多个分公司月报合并成一张总表，按"工号"列去重
import openpyxl
import pandas as pd
import os
from typing import List, Optional
def merge_excel_files(file_paths: List[str],
output_path: str,
sheet_name: str = 0,
drop_duplicates_by: Optional[List[str]] = None,
ignore_sheets: List[str] = None):
"""
合并多个Excel文件（支持去重）
参数：
file_paths : 要合并的文件路径列表
output_path : 合并后的输出文件路径
sheet_name : 读取哪个工作表（名字或索引，默认第0个）
drop_duplicates_by: 按哪些列去重，传None表示不去重
例如：["工号"] 或 ["姓名", "部门"]
ignore_sheets : 忽略的工作表名列表（这些sheet不合并）
"""
all_dataframes = []
for file_path in file_paths:
if not os.path.exists(file_path):
print(f" ⚠️ 文件不存在，跳过: {file_path}")
continue
try:
# 用 pandas 一次性把指定 sheet 读成 DataFrame
df = pd.read_excel(file_path, sheet_name=sheet_name)
df["来源文件"] = os.path.basename(file_path) # 标记来源
all_dataframes.append(df)
print(f" ✓ 读取: {os.path.basename(file_path)} ({len(df)} 行)")
except Exception as e:
print(f" ✗ 读取失败: {file_path} - {e}")
if not all_dataframes:
print("❌ 没有成功读取任何文件！")
return
# 合并所有 DataFrame
merged_df = pd.concat(all_dataframes, ignore_index=True)
print(f"\n📊 合并完成，总计 {len(merged_df)} 行（去重前）")
# 去重处理
if drop_duplicates_by:
before_count = len(merged_df)
merged_df = merged_df.drop_duplicates(subset=drop_duplicates_by, keep="first")
removed = before_count - len(merged_df)
print(f"🗑️ 去重完成，删除了 {removed} 条重复数据，剩余 {len(merged_df)} 行")
# 保存结果
merged_df.to_excel(output_path, index=False)
print(f"✅ 已保存至: {output_path}")
return merged_df
def merge_all_sheets_in_file(file_path: str,
output_path: str,
drop_duplicates_by: Optional[List[str]] = None):
"""
把单个Excel文件的所有 sheet 合并成一个（保留 sheet 名作为来源标记）
适用场景：一个Excel文件里有很多 sheet，每个 sheet 结构相同，
想合并到一起分析。
"""
wb = openpyxl.load_workbook(file_path)
all_rows = []
for sheet in wb.worksheets:
for row in sheet.iter_rows(values_only=True):
# 追加来源 sheet 名称作为最后一列
all_rows.append(list(row) + [sheet.title])
if not all_rows:
print("❌ 该文件没有内容！")
return
# 第一行是表头，后面是数据
headers = list(all_rows[0]) + ["来源Sheet"]
data_rows = all_rows[1:]
df = pd.DataFrame(data_rows, columns=headers)
if drop_duplicates_by:
df = df.drop_duplicates(subset=drop_duplicates_by)
df.to_excel(output_path, index=False)
print(f"✅ Sheet合并完成，已保存至: {output_path}")
return df
# ====================== 调用示例 ======================
# 【示例1】合并多个文件，按"工号"列去重
merge_excel_files(
file_paths=[
"月报/北京分公司_6月.xlsx",
"月报/上海分公司_6月.xlsx",
"月报/广州分公司_6月.xlsx",
"月报/深圳分公司_6月.xlsx",
],
output_path="月报合并/全国汇总_6月.xlsx",
sheet_name=0,
drop_duplicates_by=["工号"] # 工号相同的数据只保留一条
)
# 【示例2】合并文件，不去重（保留所有数据）
merge_excel_files(
file_paths=[
"数据/2024Q1.xlsx",
"数据/2024Q2.xlsx",
"数据/2024Q3.xlsx",
"数据/2024Q4.xlsx",
],
output_path="年度汇总_2024.xlsx",
drop_duplicates_by=None # 不去重
)
# 【示例3】把一个文件里的所有 sheet 合并
merge_all_sheets_in_file(
file_path="销售明细表_2024.xlsx",
output_path="销售明细表_2024_合并.xlsx",
drop_duplicates_by=["订单号"] # 按订单号去重
)
运行效果
✓ 读取: 北京分公司_6月.xlsx (128 行)
✓ 读取: 上海分公司_6月.xlsx (95 行)
✓ 读取: 广州分公司_6月.xlsx (110 行)
✓ 读取: 深圳分公司_6月.xlsx (87 行)
📊 合并完成，总计 420 行（去重前）
🗑️ 去重完成，删除了 23 条重复数据，剩余 397 行
✅ 已保存至: 月报合并/全国汇总_6月.xlsx
5.3 实战三：替换 + 合并一站式处理
有些场景下需要先替换再合并，或者批量处理整个文件夹下的所有 Excel：
import glob
import os
def batch_replace_and_merge(input_folder: str,
output_path: str,
replace_map: dict,
drop_duplicates_by: list = None):
"""
一站式处理：把文件夹下所有Excel替换后合并
适用场景：每月汇总前，需要先把各分公司的
"简称"替换成"全称"，再合并成一张大表
"""
# 找出文件夹下所有 .xlsx 文件
all_files = glob.glob(os.path.join(input_folder, "*.xlsx"))
if not all_files:
print(f"❌ 文件夹中没有找到 .xlsx 文件: {input_folder}")
return
print(f"🔍 发现 {len(all_files)} 个Excel文件\n")
for file_path in all_files:
# 替换内容（生成临时文件）
temp_path = file_path + ".tmp.xlsx"
replace_in_excel(file_path, temp_path, replace_map=replace_map)
# 读取替换后的数据备用
df = pd.read_excel(temp_path, sheet_name=0)
df["来源"] = os.path.basename(file_path)
# 删除临时文件
os.remove(temp_path)
# 合并逻辑（这里用内存合并，不用落盘）
if file_path == all_files[0]:
merged_df = df
else:
merged_df = pd.concat([merged_df, df], ignore_index=True)
# 去重
if drop_duplicates_by:
before = len(merged_df)
merged_df = merged_df.drop_duplicates(subset=drop_duplicates_by)
print(f"\n🗑️ 去重: {before} → {len(merged_df)} 行")
merged_df.to_excel(output_path, index=False)
print(f"\n✅ 全部完成，已保存至: {output_path}")
# 调用示例：把各月报中的简称统一后合并
batch_replace_and_merge(
input_folder="月报/2024年",
output_path="2024年度汇总_统一版.xlsx",
replace_map={
"北京": "北京市",
"上海": "上海市",
"广深": "广州市+深圳市",
"总": "汇总",
},
drop_duplicates_by=["工号", "月份"]
)
六、常见踩坑问题
❌ 问题1：文件报错"文件已存在"或"被其他程序占用"
现象：保存时提示权限错误。
原因：你打开了这个 Excel 文件还没关，或者上一次 Python 异常退出没释放。
解决方案：
- 先把打开的 Excel 关掉
- 换个输出文件名（加
_v2或时间戳后缀）
from datetime import datetime
output_path = f"结果_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
❌ 问题2：替换后数字变成了字符串
现象：原来单元格是 2024，替换后变成了 "2024"（文本格式），导致无法做 SUM 计算。
原因：cell.value 读写时类型丢失了。
解决方案：替换时判断原始类型，保持不变：
def smart_replace(cell, old_text, new_text):
if cell.value is None:
return
# 如果是数字，不做字符串替换，避免类型改变
if isinstance(cell.value, (int, float)):
return
val = str(cell.value).replace(old_text, new_text)
cell.value = val
❌ 问题3：合并后表头错位
现象：第一个文件的表头是 姓名|城市|工号，第二个文件的表头是 姓名|城市，合并后列对不上。
原因：不同文件的列数/列顺序不一样。
解决方案：用 pandas 读取时指定 header=0，并对所有文件统一补齐列名：
# 统一列名：缺的列补 NaN
all_cols = set()
for df in all_dfs:
all_cols.update(df.columns)
for df in all_dfs:
for col in all_cols:
if col not in df.columns:
df[col] = None
❌ 问题4：文件很大，读取很慢
现象：一个 50MB 的 Excel，打开要等很久。
原因：Excel 里有很多公式、格式、超链接等额外数据。
解决方案：用 read_only=True 跳过格式只读数据：
wb = openpyxl.load_workbook(input_path, read_only=True)
# 但注意：read_only=True 时不支持写入
如果只需要读数据做分析，用 pandas 比 openpyxl 快很多：
df = pd.read_excel("大文件.xlsx") # 比 openpyxl 快
❌ 问题5：正则替换不生效
现象：正则表达式写对了，但没匹配到任何内容。
原因：单元格里的内容可能有前后空格或不可见字符。
解决方案：先 strip() 去掉首尾空格，或者正则里加 \s*：
# 改进版正则：兼容空格
val = re.sub(r"\s*1[3-9]\s*\d{4}\s*\d{4}\s*", "1XXXXXXXXXX", val)
七、总结
核心要点回顾
- 替换操作的核心是遍历单元格：
openpyxl.load_workbook打开 →sheet.iter_rows()遍历 →cell.value读写 - 字典映射（
replace_map）是最高效的替换方式：一次传多个对应关系，不用写一堆replace链式调用 - 正则替换解决复杂场景：手机号脱敏、日期格式统一、非标准空格处理都用正则
pandas.concat()是合并的简洁之道：比手动拼接行列表可靠太多，还自带对齐列的功能- 去重的关键是选对关键列：按业务主键去重（如工号、订单号），不要按整行去重
给你的学习建议
- 从替换开始：先跑通
replace_in_excel，换个关键词试试，感受到"代码改数据"的爽感 - 理解 DataFrame：pandas 的 DataFrame 就是表格的 Python 表示，学好
df.head()/df.info()/df.describe()这三个命令，可以随时检查数据长什么样 - 结合工作场景：想一想你每月/每周要重复做哪些 Excel 操作，那些"复制粘贴改名称"的活儿，80%都能用脚本自动化
- 进阶方向：学会后可以进一步学
xlwings（用 Python 操作 Excel 界面）、python-docx（处理 Word），三件套一起用，你就是办公室自动化高手
工具的价值不在于它有多复杂，而在于它能不能真正替你省时间。跑通一个脚本，解放你每周2小时的重复劳动，这就是技术落地的意义。
代码在 Python 3.8+ / openpyxl 3.1 / pandas 2.0 下测试通过。有问题欢迎评论区留言！
浙公网安备 33010602011771号