ARTICLE · 1075393
用 openpyxl 合并多份 Excel 报表:统一字段、处理空值并导出结果
如果你需要把销售表、项目表或月度报表汇总到一个文件，openpyxl 可以完成从读取多个工作簿、统一表头、跳过空行到导出结果的完整流程。下面的脚本重点处理最容易出错的字段对齐，并在导出后自动输出基本核对信息，适合用于报表自动化。
01
PART
一、准备文件和运行环境
1. 安装 openpyxl
在命令行中执行：
纯文本复制代码
pip install openpyxl
如果你使用的是 Anaconda，也可以执行：
纯文本复制代码
conda install openpyxl
openpyxl 主要用于读写 .xlsx 文件。旧版 .xls 文件不能直接使用它读取，需要先在 Excel 中另存为 .xlsx，或者使用其他工具完成格式转换。
2. 统一文件命名和目录
建议建立如下目录：
纯文本复制代码
excel_merge/
├── input/
│ ├── 销售表_1.xlsx
│ ├── 销售表_2.xlsx
│ └── 销售表_3.xlsx
└── merge_excel.py
脚本会读取 input 文件夹下所有 .xlsx 文件，并生成：
纯文本复制代码
excel_merge/
└── 合并结果.xlsx
输出文件最好放在输入文件夹之外，避免脚本下一次运行时把上一次的结果再次读入。
02
PART
二、先确定统一字段
多份报表合并时，最关键的不是读取文件，而是明确最终结果需要哪些字段。例如，不同文件中的表头可能分别写成：
不要直接按照每个文件的列号合并。例如第一份文件的第 3 列是“金额”，第二份文件的第 3 列可能是“负责人”。应该先根据表头名称建立字段映射，再按照统一字段写入结果。
下面的示例将最终字段设为：
纯文本复制代码
统一字段 = [
"销售日期",
"客户名称",
"产品名称",
"销售金额",
"负责人",
]
你可以根据自己的报表修改这组字段。
03
PART
三、可复制的 Excel 合并脚本
将下面代码保存为 merge_excel.py：
纯文本复制代码
from pathlib import Path
from collections import Counter
from datetime import datetime
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter
# 输入文件夹和输出文件
INPUT_DIR = Path("input")
OUTPUT_FILE = Path("合并结果.xlsx")
# 每个工作表的表头所在行，从 1 开始
HEADER_ROW = 1
# 最终输出的统一字段和顺序
STANDARD_HEADERS = [
"销售日期",
"客户名称",
"产品名称",
"销售金额",
"负责人",
]
# 不同写法映射到统一字段
HEADER_ALIASES = {
"日期": "销售日期",
"销售日期": "销售日期",
"下单日期": "销售日期",
"客户": "客户名称",
"客户名称": "客户名称",
"产品": "产品名称",
"产品名称": "产品名称",
"商品名称": "产品名称",
"金额": "销售金额",
"销售金额": "销售金额",
"订单金额": "销售金额",
"负责人": "负责人",
"销售员": "负责人",
"业务员": "负责人",
}
def normalize_header(value):
"""统一处理表头中的空格和空值。"""
if value is None:
return ""
return str(value).strip().replace(" ", "").replace("u3000", "")
def clean_cell_value(value):
"""清理普通文本中的首尾空格，保留日期、数字和公式类型。"""
if isinstance(value, str):
value = value.strip()
return value if value else None
return value
def is_empty_row(values):
"""判断一行是否为空行。"""
return all(
value is None or (isinstance(value, str) and not value.strip())
for value in values
)
def get_excel_files():
"""获取输入目录中的 Excel 文件，并排除输出文件。"""
if not INPUT_DIR.exists():
raise FileNotFoundError(f"找不到输入文件夹：{INPUT_DIR.resolve()}")
files = sorted(INPUT_DIR.glob("*.xlsx"))
files = [file for file in files if file.name != OUTPUT_FILE.name]
if not files:
raise FileNotFoundError(
f"{INPUT_DIR.resolve()} 中没有找到 .xlsx 文件"
)
return files
def build_header_map(header_values, source_file):
"""
将源文件表头转换为：
{
"销售日期": 0,
"客户名称": 1,
...
}
"""
header_map = {}
unknown_headers = []
for column_index, raw_header in enumerate(header_values):
normalized = normalize_header(raw_header)
if not normalized:
continue
standard_header = HEADER_ALIASES.get(normalized)
if standard_header is None:
unknown_headers.append(normalized)
continue
if standard_header in header_map:
raise ValueError(
f"{source_file.name} 中出现重复字段：{standard_header}"
)
header_map[standard_header] = column_index
if not header_map:
raise ValueError(
f"{source_file.name} 没有识别到有效表头，请检查 HEADER_ROW。"
)
return header_map, unknown_headers
def merge_workbooks():
source_files = get_excel_files()
result_workbook = Workbook()
result_sheet = result_workbook.active
result_sheet.title = "合并结果"
# 写入统一表头
result_sheet.append(STANDARD_HEADERS)
# 表头样式
header_fill = PatternFill(
fill_type="solid",
fgColor="D9EAF7"
)
for cell in result_sheet[1]:
cell.font = Font(bold=True)
cell.fill = header_fill
total_rows = 0
file_row_counts = Counter()
warnings = []
for source_file in source_files:
workbook = load_workbook(
source_file,
read_only=True,
data_only=False
)
file_data_rows = 0
try:
for worksheet in workbook.worksheets:
if worksheet.max_row < HEADER_ROW:
warnings.append(
f"{source_file.name}/{worksheet.title} 没有足够的行。"
)
continue
header_values = next(
worksheet.iter_rows(
min_row=HEADER_ROW,
max_row=HEADER_ROW,
values_only=True
)
)
header_map, unknown_headers = build_header_map(
header_values,
source_file
)
if unknown_headers:
warnings.append(
f"{source_file.name}/{worksheet.title} "
f"忽略未知字段：{', '.join(unknown_headers)}"
)
for row_values in worksheet.iter_rows(
min_row=HEADER_ROW + 1,
values_only=True
):
# 跳过完全为空的行
if is_empty_row(row_values):
continue
output_row = []
for standard_header in STANDARD_HEADERS:
source_index = header_map.get(standard_header)
if source_index is None:
# 当前文件缺少该字段时填空
value = None
elif source_index >= len(row_values):
value = None
else:
value = row_values[source_index]
output_row.append(clean_cell_value(value))
result_sheet.append(output_row)
file_data_rows += 1
total_rows += 1
finally:
workbook.close()
file_row_counts[source_file.name] = file_data_rows
# 设置筛选和冻结首行
result_sheet.freeze_panes = "A2"
result_sheet.auto_filter.ref = result_sheet.dimensions
# 根据内容设置适中的列宽
for column_cells in result_sheet.columns:
column_letter = get_column_letter(column_cells[0].column)
max_length = 0
for cell in column_cells:
if cell.value is not None:
max_length = max(max_length, len(str(cell.value)))
result_sheet.column_dimensions[column_letter].width = min(
max(max_length + 2, 12),
30
)
result_workbook.save(OUTPUT_FILE)
print(f"已读取文件数量：{len(source_files)}")
print(f"合并数据行数：{total_rows}")
print(f"输出文件：{OUTPUT_FILE.resolve()}")
print("n各文件读取行数：")
for file_name, row_count in file_row_counts.items():
print(f"- {file_name}：{row_count} 行")
if warnings:
print("n注意事项：")
for warning in warnings:
print(f"- {warning}")
if __name__ == "__main__":
merge_workbooks()
在脚本所在目录执行：
纯文本复制代码
python merge_excel.py
脚本会为每个源文件建立表头映射，然后按照 STANDARD_HEADERS 指定的顺序输出。即使不同文件的列顺序不同，也不会因为列号变化而错位。
04
PART
四、字段不一致时如何处理
缺少字段
如果某个文件没有“负责人”字段，脚本会在该文件对应的数据行中填入空值，不会改变其他字段的位置。
例如：
纯文本复制代码
文件 A：销售日期、客户名称、销售金额、负责人
文件 B：销售日期、客户名称、销售金额
文件 B 导入后，“负责人”列会保留，但对应单元格为空。
表头名称不同
把不同写法添加到 HEADER_ALIASES 中即可：
纯文本复制代码
HEADER_ALIASES = {
"金额": "销售金额",
"销售额": "销售金额",
"成交金额": "销售金额",
}
左侧是源文件中可能出现的表头，右侧必须是 STANDARD_HEADERS 中的统一字段。
出现未知字段
如果源文件中有“备注”“地区”等字段，但这些字段没有加入统一字段列表，脚本会忽略它们，并在运行结果中提示：
纯文本复制代码
忽略未知字段：备注、地区
如果这些字段也需要保留，应同时修改两处：
纯文本复制代码
STANDARD_HEADERS = [
"销售日期",
"客户名称",
"产品名称",
"销售金额",
"负责人",
"备注",
]
然后在 HEADER_ALIASES 中加入对应映射。
出现重复字段
如果同一张表中同时有两个“金额”列，脚本会停止并报错，而不是擅自选择其中一列。这种处理更安全，因为两个字段可能分别代表含税金额和未税金额，需要你先明确业务含义。
05
PART
五、空行、空值和数据类型
空行
脚本会跳过整行为空的记录，包括以下情况：
所有单元格都是空值；
单元格为空字符串；
单元格只有普通空格或全角空格。
但如果一行中只有“客户名称”有值、其他字段为空，它仍然会被保留，因为这可能是一条不完整但需要人工检查的数据。
单元格中的空值
缺失字段和空单元格会写入 None，在 Excel 中显示为空白。脚本不会把它们强制转换为字符串 "None"。
日期
如果源文件中的日期单元格本身是 Excel 日期格式，openpyxl 通常会将其读取为 datetime 或 date 对象，写入新工作簿后仍可作为日期使用。
如果日期在源文件中是文本，例如：
纯文本复制代码
2024/01/08
它会继续作为文本写入。需要统一日期格式时，可以增加转换逻辑：
纯文本复制代码
from datetime import datetime
def clean_date(value):
if isinstance(value, datetime):
return value
if isinstance(value, str):
for date_format in ("%Y/%m/%d", "%Y-%m-%d", "%Y.%m.%d"):
try:
return datetime.strptime(value.strip(), date_format)
except ValueError:
pass
return value
然后在处理“销售日期”字段时调用：
纯文本复制代码
if standard_header == "销售日期":
value = clean_date(value)
不要在不了解原始格式的情况下直接把所有数字转换成日期，因为 Excel 内部日期序列值可能会被误判。
公式
示例脚本使用：
纯文本复制代码
data_only=False
因此读取到的是公式本身，例如：
纯文本复制代码
=SUM(C2:D2)
需要注意，openpyxl 可以读取和写入公式，但不会像 Excel 一样计算公式。导出的工作簿打开后，Excel 可能会重新计算公式；如果需要导出已经计算好的结果，可以在 Excel 或 LibreOffice 中打开并保存源文件后再处理。
如果你只想读取 Excel 中已经保存的公式结果，可以使用：
纯文本复制代码
workbook = load_workbook(
source_file,
read_only=True,
data_only=True
)
但这种方式读取的是缓存结果。对于没有保存过计算结果的文件，公式单元格可能得到 None。因此，data_only=True 和 data_only=False 应根据目标选择，不能混用后再期待同时获得公式和结果。
06
PART
六、导出后进行结果校验
多表合并完成后，至少应检查以下几项。
1. 检查源文件行数和合并行数
脚本会输出每个文件读取了多少行，例如：
纯文本复制代码
已读取文件数量：3
合并数据行数：268
各文件读取行数：
- 销售表_1.xlsx：80 行
- 销售表_2.xlsx：92 行
- 销售表_3.xlsx：96 行
如果源文件中人工确认有 270 条数据，但脚本只读取到 268 行，应重点检查：
是否有两行完全为空；
表头行是否设置错误；
数据是否位于其他工作表；
文件是否实际为 .xls；
末尾数据是否只存在格式而没有值。
2. 抽样核对字段位置
建议从每个源文件随机选择一到三条记录，对照合并结果检查：
日期是否仍然是日期；
客户名称是否没有错位；
金额是否进入“销售金额”列；
缺失字段是否为空；
不同列顺序的文件是否都能正确对应。
尤其要检查字段名称相似的列，例如“销售金额”和“回款金额”，不要仅凭列的位置判断。
3. 检查关键字段是否为空
如果“销售日期”“客户名称”是必填字段，可以在结果工作簿中筛选空值，也可以在脚本中增加统计：
纯文本复制代码
missing_customer_count = 0
# 在写入每一行前统计
if not output_row[1]:
missing_customer_count += 1
最后输出：
纯文本复制代码
print(f"客户名称为空的记录：{missing_customer_count} 行")
对于销售金额，还应额外检查文本金额、负数和异常符号。这些属于数据清洗规则，不建议在合并脚本中未经确认就自动修改。
4. 检查是否重复导入
每次运行前确认输入目录中没有上一次的 合并结果.xlsx。示例代码已经排除了同名输出文件，但如果你更换了输出文件名，仍然要同步修改排除逻辑。
如果需要识别重复订单，可以在合并后根据订单号建立集合：
纯文本复制代码
seen_order_ids = set()
if order_id in seen_order_ids:
print(f"发现重复订单号：{order_id}")
else:
seen_order_ids.add(order_id)
前提是所有源文件都包含“订单号”字段，并且你已经明确订单号的唯一性规则。
07
PART
七、常见问题排查
找不到文件
确认脚本运行位置和目录结构一致。也可以使用绝对路径：
纯文本复制代码
INPUT_DIR = Path(r"D:工作excel_mergeinput")
OUTPUT_FILE = Path(r"D:工作excel_merge合并结果.xlsx")
Windows 路径建议使用原始字符串 r"..."，避免反斜杠被误认为转义字符。
表头识别失败
如果表头位于第 2 行或第 3 行，修改：
纯文本复制代码
HEADER_ROW = 2
同时确认表头没有合并单元格、隐藏字符或完全不同的命名方式。
文件正在被占用
如果 合并结果.xlsx 正在 Excel 中打开，脚本可能无法覆盖保存。关闭该文件后重新运行。
合并结果样式没有保留
示例脚本只合并数据，不会复制源文件的单元格样式、批注、图表、数据透视表或页面设置。如果你只需要统一数据并继续分析，这种方式更简单；如果需要完整保留原始模板，应单独设计样式复制逻辑，不能只依赖 iter_rows()。
文件中有多个工作表
脚本会遍历每个源文件中的所有工作表。如果某些工作表是“说明”“字典”或“汇总”页面，而不是明细表，需要增加筛选条件，例如：
纯文本复制代码
WORKSHEET_NAMES = {"销售明细", "数据"}
for worksheet in workbook.worksheets:
if worksheet.title not in WORKSHEET_NAMES:
continue
这样可以避免把不应合并的工作表读入结果。
08
PART
八、适合长期使用的改进方式
当报表格式逐渐固定后，可以把以下内容单独配置起来：
输入文件夹；
表头所在行；
统一字段；
表头别名；
必填字段；
允许读取的工作表名称；
输出文件名。
这样每月只需把新文件放入 input 文件夹，再执行一次脚本即可。对于字段经常变化的团队，建议保留每次运行的文件清单、读取行数和异常字段记录，方便在出现数据校验问题时追溯来源。
用 openpyxl 做 Excel 合并时，真正需要优先保证的是字段对齐和结果可核对，而不是单纯把多个文件追加到一起。先定义统一表头，再按名称映射字段，并保留缺失字段和未知字段的提示，才能让这类报表自动化流程稳定运行。