Python处理Excel汇总时怎么保留单元格格式

图 0：带格式 Excel 汇总流程全貌（多源表排版样式经由 Python 引擎无损汇入目标总表）

一、格式丢失的技术根源：数据抽象与文档模型的冲突

要解决单元格格式丢失的问题，首先需要理清为什么常用的 pandas.read_excel() 无法满足需求。Excel 的 .xlsx 文件本质上是一个经过 ZIP 压缩的 OpenXML 文档包，内部由存放单元格数值的 sheet1.xml、记录共享字符串的 sharedStrings.xml 以及专门存储字体、填充底色、边框、对齐与数字掩码的 styles.xml 共同构成。

当执行 pandas.read_excel() 时，其内部引擎的目标是构建高性能的二维数据分析容器（DataFrame）。为了内存效率和矩阵运算性能，它主动过滤并丢弃了所有的 XML 样式标签，只将单元格内的原始文本或纯数字装载到 NumPy 数组中。随后的 df.to_excel() 则相当于打开一个空白工作簿从零写入纯文本，这就是“汇总后格式全无”的底层原因。

相比之下，openpyxl 将整个工作簿解析为一颗完整的文档对象树（Document Object Tree）。在 openpyxl 中，每一个单元格（Cell）不仅拥有 value 属性，还附带了指向样式实例的独立属性：font（字体族、字号、颜色、粗细）、fill（填充图案与背景色）、border（上下左右边框线型与色值）、alignment（水平/垂直对齐、自动换行）以及 number_format（数字展示格式代码）。只要在合并数据的同时将这些样式对象克隆并赋予目标单元格，就能实现真正意义上的样式保留。

二、三大技术选型路径与适用场景

在实际工程开发中，保留格式的实现方式并不是单一的。根据运行环境是否包含图形界面、服务器是否为 Linux 系统，以及报表版式的复杂度，主要有以下三条技术路径：

三、方案一（推荐）：预置模板填数法（Template Injection）

在企业生产中最优雅、最不容易出错的做法是模板法。与其在 Python 代码里费尽周折地逐个设置边框、颜色和字体，不如直接在 Excel 软件中预先手工设计好一张汇总表模板（template.xlsx），设置好表头美化、合计行底色、公式预埋以及列宽。

随后，Python 脚本只需使用 openpyxl.load_workbook("template.xlsx") 打开该模板，只在指定的数据区域写入新汇总的值。由于 openpyxl 默认遵循“就地修改”原则，未被覆写的单元格样式、公式和页面属性都会被完好无损地保留下来。

from openpyxl import load_workbook
import glob

def merge_by_template(source_files, template_path, output_path):
    # 加载带有精美样式的母版，注意保留原始公式和样式
    wb_target = load_workbook(template_path)
    ws_target = wb_target.active

    # 确定模板中数据开始填充的起始行号（假设第 3 行为首行数据）
    target_row = 3

    for file_path in source_files:
        wb_src = load_workbook(file_path, data_only=True)
        ws_src = wb_src.active

        # 读取源数据内容（跳过第 1、2 行表头）
        for row in ws_src.iter_rows(min_row=3, values_only=True):
            if not any(row):  # 忽略全空行
                continue
            
            # 将数值写入模板对应的单元格
            for col_idx, val in enumerate(row, start=1):
                cell = ws_target.cell(row=target_row, column=col_idx)
                cell.value = val
                # 如果模板预留了样式行，单元格将自动继承预设的数字格式与边框
            
            target_row += 1
        
        wb_src.close()

    # 保存为最终汇总报表
    wb_target.save(output_path)
    print(f"汇总完成，已保留模板格式并输出至: {output_path}")

# 示例调用
# source_files = glob.glob("branches/*.xlsx")
# merge_by_template(source_files, "templates/summary_template.xlsx", "output/2026年度汇总表.xlsx")

四、方案二：基于 openpyxl 的逐格样式深克隆（多表动态追加）

当面临源文件版式动态变化、需要将多个独立分表的行垂直拼接成一个大表时，就需要使用 openpyxl 的样式对象克隆技术。由于 openpyxl 的样式对象（如 Font, PatternFill, Border）是不可变命名元组或特定结构体，不能直接对两个单元格进行浅引用赋值，否则可能在写出时引发样式污染或属性覆盖，因此标准做法是借助标准库 copy() 进行深层属性传递。

完整实现代码

以下代码演示了如何汇总多个结构相同的分支 Excel 表，保留表头样式、数据行字体、数字格式、背景底色，并自动重映射合并单元格区域：

import os
from copy import copy
from openpyxl import Workbook, load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.utils import get_column_letter

def copy_cell_style(src_cell, tgt_cell):
    """安全拷贝单元格的全部视觉样式与数字格式"""
    if src_cell.has_style:
        tgt_cell.font = copy(src_cell.font)
        tgt_cell.border = copy(src_cell.border)
        tgt_cell.fill = copy(src_cell.fill)
        tgt_cell.number_format = copy(src_cell.number_format)
        tgt_cell.protection = copy(src_cell.protection)
        tgt_cell.alignment = copy(src_cell.alignment)

def merge_excel_with_styles(file_list, output_file):
    target_wb = Workbook()
    target_ws = target_wb.active
    target_ws.title = "汇总数据"

    current_target_row = 1
    is_first_file = True

    for file_path in file_list:
        if not os.path.exists(file_path):
            continue

        src_wb = load_workbook(file_path, data_only=True)
        src_ws = src_wb.active

        # 若是首个文件，先同步列宽设置
        if is_first_file:
            for col_letter, col_dimension in src_ws.column_dimensions.items():
                target_ws.column_dimensions[col_letter].width = col_dimension.width

        # 决定从源表的哪一行开始读取：首个文件保留表头（第1行），后续文件跳过表头
        start_row = 1 if is_first_file else 2
        file_start_row = current_target_row

        # 1. 逐行读取单元格数据与样式
        for r_idx in range(start_row, src_ws.max_row + 1):
            # 同步源表行高
            src_row_height = src_ws.row_dimensions[r_idx].height
            if src_row_height:
                target_ws.row_dimensions[current_target_row].height = src_row_height

            for c_idx in range(1, src_ws.max_column + 1):
                src_cell = src_ws.cell(row=r_idx, column=c_idx)
                tgt_cell = target_ws.cell(row=current_target_row, column=c_idx)

                # 处理常规单元格的值与样式
                if not isinstance(src_cell, MergedCell):
                    tgt_cell.value = src_cell.value
                    copy_cell_style(src_cell, tgt_cell)
                else:
                    # 对于非首格的合并附属单元格，仅复制样式占位
                    copy_cell_style(src_cell, tgt_cell)

            current_target_row += 1

        # 2. 重新映射合并单元格区域（加上行偏移量）
        row_offset = file_start_row - start_row
        for merge_range in list(src_ws.merged_cells.ranges):
            # 只有当合并区域落在读取区间内时才迁移
            if merge_range.min_row >= start_row:
                new_min_row = merge_range.min_row + row_offset
                new_max_row = merge_range.max_row + row_offset
                new_min_col = merge_range.min_col
                new_max_col = merge_range.max_col

                target_ws.merge_cells(
                    start_row=new_min_row,
                    end_row=new_max_row,
                    start_column=new_min_col,
                    end_column=new_max_col
                )

        src_wb.close()
        is_first_file = False

    target_wb.save(output_file)
    print(f"已完成多表深度格式汇总，输出文件：{output_file}")

# 执行示例
# merge_excel_with_styles(["华东.xlsx", "华北.xlsx"], "完整汇总.xlsx")

五、方案三：在 Windows/Mac 环境下借助 xlwings 驱动原生 Excel

如果源 Excel 表中包含了极为复杂的条件格式（数据条、色阶、图标集）、内嵌迷你图（Sparklines）或复杂的打印分页符，任何纯 Python 开源库（无论是 openpyxl 还是 xlsxwriter）在反序列化复杂 OOXML 时都可能发生微小偏差。

此时最优解是使用 xlwings。xlwings 通过 Windows COM 或 macOS AppleScript 调用本机的实际 Excel 应用程序进程，利用 Excel 自带的剪贴板接口进行 Range.Copy() 和 Range.PasteSpecial()，实现物理级的无损粘贴。

import xlwings as xw

def merge_with_xlwings(source_files, output_path):
    # 启动后台静默 Excel 进程（不闪现界面，关闭屏幕刷新以提升速度）
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False

    try:
        target_wb = app.books.add()
        target_ws = target_wb.sheets[0]
        current_row = 1

        for idx, file_path in enumerate(source_files):
            src_wb = app.books.open(file_path)
            src_ws = src_wb.sheets[0]

            # 获取有效数据区域
            used_range = src_ws.used_range
            row_count = used_range.rows.count
            col_count = used_range.columns.count

            # 首表包含标题行，后续表跳过第 1 行标题
            if idx == 0:
                copy_range = src_ws.range((1, 1), (row_count, col_count))
                dest_range = target_ws.range((current_row, 1))
                copy_range.api.Copy(dest_range.api)  # 利用底层 COM 复制全部属性
                current_row += row_count
            else:
                if row_count > 1:
                    copy_range = src_ws.range((2, 1), (row_count, col_count))
                    dest_range = target_ws.range((current_row, 1))
                    copy_range.api.Copy(dest_range.api)
                    current_row += (row_count - 1)

            src_wb.close()

        # 自动调整列宽以匹配内容
        target_ws.autofit(axis="columns")
        target_wb.save(output_path)
        target_wb.close()
    finally:
        app.quit()
        print("xlwings 汇总执行完毕。")

六、汇总保留格式的四大关键排查与避坑指南

在实际编写脚本时，处理样式往往会遇到很多非预期的报错或格式失效。以下是排查与规避这些问题的四个关键矩阵：

1. 合并单元格引发的 AttributeError

当使用 openpyxl 遍历含有合并单元格的区域时，合并块内只有左上角第一个格是常规的 Cell，其余格在内部表示为 MergedCell 实例。MergedCell 是只读占位符，若尝试直接执行 cell.value = "xxx"，代码会抛出 AttributeError: 'MergedCell' object has no attribute 'value'。

正确做法：在循环内部通过 isinstance(cell, MergedCell) 做防御判断。汇总时应先收集原表的合并区域元数据（ws.merged_cells.ranges），在计算目标表当前行偏移量后，统一调用 target_ws.merge_cells() 重建合并块。

2. 数字格式（number_format）丢失导致日期变纯数

Excel 内部对于日期时间与百分比的处理并不是独立的数据类型，而是基于浮点数加上格式掩码。例如 2026-10-31 在 Excel 底层是一个序列号整数 46326，而 12.5% 存储的是 0.125。如果在复制单元格时遗漏了 number_format，写出后的 Excel 文件就会展示为干燥的浮点数。

正确做法：必须显式转移 target_cell.number_format = src_cell.number_format。切勿在 Python 代码中通过 strftime() 将日期转换为字符串写入，否则该单元格在 Excel 中将沦为纯文本，失去按时间筛选和公式运算的能力。

3. 公式与计算缓存值（data_only 参数）

在 load_workbook() 中，data_only 参数是一个关键开关：

• data_only=False（默认值）：读出的是公式原文字符串（如 =SUM(D2:D10)）。在多表拼接行号变动后，如果原公式包含绝对或相对行号引用，汇总后极易出现公式错位或 #REF! 报错。
• data_only=True：读取上一次在 Excel 中打开保存时缓存的计算结果数值。若不需要总表实时联动公式，推荐开启 data_only=True，以确保数值快照准确，再通过样式克隆保持原报表的视觉专业度。

4. 批量多表汇总的内存释放（GC 压力）

由于 openpyxl 中每个 Cell 及其附带的 Font、PatternFill 都是完整的 Python 内存对象，若汇总几十个上百兆的 Excel 文件，很容易引发内存溢出（OOM）。

优化建议：在处理每个源工作簿的循环末尾，务必显式调用 src_wb.close() 并主动触发垃圾回收 import gc; gc.collect()；如果仅汇总纯数据且行数极大，建议采用方案一（模板法），避免在内存中实例化数百万个重复的样式对象。