用 openpyxl 合并多份 Excel 报表：统一字段、处理空值并导出结果

AI智能摘要

使用openpyxl合并多份Excel报表时，关键在于通过表头名称建立字段映射而非依赖列号，从而统一不同文件中的字段名称。脚本可自动读取输入文件夹下的所有xlsx文件，根据预设的统一字段对齐数据，并处理空行和空值，最终导出合并结果并输出基本核对信息，适用于销售表、项目表等月度报表的自动化汇总。

— 此摘要由AI分析文章内容生成，仅供参考。

如果你需要把销售表、项目表或月度报表汇总到一个文件，openpyxl 可以完成从读取多个工作簿、统一表头、跳过空行到导出结果的完整流程。下面的脚本重点处理最容易出错的字段对齐，并在导出后自动输出基本核对信息，适合用于报表自动化。

一、准备文件和运行环境

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

输出文件最好放在输入文件夹之外，避免脚本下一次运行时把上一次的结果再次读入。

二、先确定统一字段

多份报表合并时，最关键的不是读取文件，而是明确最终结果需要哪些字段。例如，不同文件中的表头可能分别写成：

文件中的表头	统一后的字段
日期、销售日期、下单日期	销售日期
客户、客户名称	客户名称
金额、销售金额、订单金额	销售金额
负责人、销售员、业务员	负责人

不要直接按照每个文件的列号合并。例如第一份文件的第 3 列是“金额”，第二份文件的第 3 列可能是“负责人”。应该先根据表头名称建立字段映射，再按照统一字段写入结果。

下面的示例将最终字段设为：

统一字段 = [
    "销售日期",
    "客户名称",
    "产品名称",
    "销售金额",
    "负责人",
]

你可以根据自己的报表修改这组字段。

三、可复制的 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 指定的顺序输出。即使不同文件的列顺序不同，也不会因为列号变化而错位。

四、字段不一致时如何处理

缺少字段

如果某个文件没有“负责人”字段，脚本会在该文件对应的数据行中填入空值，不会改变其他字段的位置。

例如：

文件 A：销售日期、客户名称、销售金额、负责人
文件 B：销售日期、客户名称、销售金额

文件 B 导入后，“负责人”列会保留，但对应单元格为空。

表头名称不同

把不同写法添加到 HEADER_ALIASES 中即可：

HEADER_ALIASES = {
    "金额": "销售金额",
    "销售额": "销售金额",
    "成交金额": "销售金额",
}

左侧是源文件中可能出现的表头，右侧必须是 STANDARD_HEADERS 中的统一字段。

出现未知字段

如果源文件中有“备注”“地区”等字段，但这些字段没有加入统一字段列表，脚本会忽略它们，并在运行结果中提示：

忽略未知字段：备注、地区

如果这些字段也需要保留，应同时修改两处：

STANDARD_HEADERS = [
    "销售日期",
    "客户名称",
    "产品名称",
    "销售金额",
    "负责人",
    "备注",
]

然后在 HEADER_ALIASES 中加入对应映射。

出现重复字段

如果同一张表中同时有两个“金额”列，脚本会停止并报错，而不是擅自选择其中一列。这种处理更安全，因为两个字段可能分别代表含税金额和未税金额，需要你先明确业务含义。

五、空行、空值和数据类型

空行

脚本会跳过整行为空的记录，包括以下情况：

所有单元格都是空值；
单元格为空字符串；
单元格只有普通空格或全角空格。

但如果一行中只有“客户名称”有值、其他字段为空，它仍然会被保留，因为这可能是一条不完整但需要人工检查的数据。

单元格中的空值

缺失字段和空单元格会写入 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 应根据目标选择，不能混用后再期待同时获得公式和结果。

六、导出后进行结果校验

多表合并完成后，至少应检查以下几项。

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)

前提是所有源文件都包含“订单号”字段，并且你已经明确订单号的唯一性规则。

七、常见问题排查

找不到文件

确认脚本运行位置和目录结构一致。也可以使用绝对路径：

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

这样可以避免把不应合并的工作表读入结果。

八、适合长期使用的改进方式

当报表格式逐渐固定后，可以把以下内容单独配置起来：

输入文件夹；
表头所在行；
统一字段；
表头别名；
必填字段；
允许读取的工作表名称；
输出文件名。

这样每月只需把新文件放入 input 文件夹，再执行一次脚本即可。对于字段经常变化的团队，建议保留每次运行的文件清单、读取行数和异常字段记录，方便在出现数据校验问题时追溯来源。

用 openpyxl 做 Excel 合并时，真正需要优先保证的是字段对齐和结果可核对，而不是单纯把多个文件追加到一起。先定义统一表头，再按名称映射字段，并保留缺失字段和未知字段的提示，才能让这类报表自动化流程稳定运行。