Python将数据写入Excel并保留格式的方法

Theo
Theo
发布时间:2026-11-22 | 19人浏览过
原创

本文详细介绍在Python中将数据写入Excel文件并完整保留原有单元格格式、公式及样式的方法,深入讲解openpyxl原位写入与pandas overlay模式的具体步骤及避坑要点。

在日常数据处理与自动化报表开发中,许多业务表格都预先设置好了企业专属的视觉样式,例如统一的字体、表头背景色、单元格边框、对齐方式以及预置公式。直接使用 Python 常见的 pandas.to_excel() 默认操作会导致整个工作表被彻底重建,原有的一切精美排版和公式瞬间丢失。要解决这一痛点,核心思路是“将已有 Excel 当作模板载入,仅对目标单元格的原位数值进行更新”,本文将详细拆解实现保留格式写入的具体方案与操作细节。

一、为什么常规写入方式会破坏格式?

理解格式丢失的原因是选对工具的前提。Excel 文件(.xlsx)本质上是一个遵循 OpenXML 标准的压缩包,其内部将“工作表网格数据”(sheet1.xml)与“全局样式池”(styles.xml)分开存储。单元格通过样式索引关联到字体、字号、边框和填充色等属性。

当开发者调用常规的写入方式时,库的底层行为存在本质差别:

  • 新建覆盖机制(如 Pandas 默认写入):底层引擎会抹去原有的 XML 结构,直接新建一个空的 sheet.xml,并只为导出的纯文本生成最朴素的默认样式,原模板中所有的行高、列宽、合并单元格及公式都会被销毁。
  • 原位更新机制(如 openpyxl 模板化写入):先解析现有的 XML 架构,保留 styles.xml 与结构映射表,仅修改特定坐标单元格的 value 节点,从而使原有的格式属性完整留存。
Python将数据写入Excel并保留格式的方法
图 1:Excel 写入机制差异对比图

二、方法一:使用 openpyxl 进行精准原位写入(首选推荐)

openpyxl 是 Python 生态中处理 .xlsx 格式功能最完备的库。通过“加载现有工作簿 → 选择工作表 → 更改单元格值 → 另存为文件”四步标准流程,可以做到不触碰任何既有格式属性。

1. 核心操作步骤

  1. 载入已有模板:使用 openpyxl.load_workbook() 打开带格式的模板文件。注意不要带破坏样式的参数。
  2. 指定操作工作表:通过工作表名称精确获取 Worksheet 对象。
  3. 按坐标遍历写入数值:通过 ws.cell(row, col).value = new_value 或 ws['B3'] = new_value 仅更新值,避开直接重赋整个单元格对象。
  4. 另存为输出文件:调用 wb.save() 写入目标路径,建议另存为新文件以防破坏原始模板。
Python将数据写入Excel并保留格式的方法
图 2:openpyxl 模板化原位写入四步法流程图

2. 完整代码示例

import openpyxl

# 1. 载入已经由美工或业务人员排版好的 Excel 模板
template_path = "template_report.xlsx"
output_path = "monthly_report_final.xlsx"

wb = openpyxl.load_workbook(template_path)
ws = wb["销售汇总表"]

# 准备需要写入的业务明细数据(例如:销售人员、成单量、销售金额)
data_rows = [
    ["张伟", 15, 125000.0],
    ["李娜", 22, 198000.0],
    ["王强", 18, 142000.0],
]

# 假设模板中:第 1 行为大标题,第 2 行为表头,数据从第 3 行开始填入
start_row = 3
start_col = 2  # 从第 B 列开始填入

for row_idx, record in enumerate(data_rows, start=start_row):
    for col_idx, val in enumerate(record, start=start_col):
        # 核心关键:只对已有 cell 的 value 属性进行赋值
        target_cell = ws.cell(row=row_idx, column=col_idx)
        target_cell.value = val

# 2. 另存为新文件,原始模板结构与样式毫无改动
wb.save(output_path)
print("数据已成功填入,格式完整保留!")
注意事项:如果模板中包含求和等动态公式(如 SUM(D3:D5)),请千万不要使用代码去覆盖公式所在的单元格。Excel 在重新打开该文件时会自动计算这些公式所引用的新数值。

三、方法二:使用 Pandas 结合 ExcelWriter 的 overlay 模式

在处理大规模二维表格时,许多开发者更习惯使用 pandas.DataFrame。在 Pandas 1.4.0 及以上版本中,ExcelWriter 提供了 if_sheet_exists="overlay" 这一关键参数,允许在追加模式(mode="a")下直接将 DataFrame 的网格数据“印”在现有工作表上方,而无需从头新建表格。

1. 关键参数解析

  • mode="a":以追加(Append)模式打开工作簿,保证不会直接清空已有文件。
  • engine="openpyxl":明确指定使用底层支持读取和修改格式的 openpyxl 引擎。
  • if_sheet_exists="overlay":指定当目标工作表已存在时采用“层叠覆盖”行为,而不是默认的重建或报错。
  • header=False, index=False:这是最容易忽略的一点。如果不关闭表头和索引输出,DataFrame 会把默认无样式的列名与行序号强制覆盖在原有的美化表头上。
  • startrow, startcol:指定数据写入的零基坐标起始点。

2. 完整代码示例

import pandas as pd

# 准备待写入的数据集
df = pd.DataFrame({
    "department": ["研发部", "市场部", "运营部"],
    "headcount": [45, 18, 26],
    "budget": [850000, 320000, 210000]
})

target_file = "department_template.xlsx"

# 使用 openpyxl 作为引擎,开启追加与原位覆盖
with pd.ExcelWriter(
    target_file,
    engine="openpyxl",
    mode="a",
    if_sheet_exists="overlay"
) as writer:
    # 假设模板表头位于第 1 行(索引 0),数据从第 2 行(索引 1)、第 A 列(索引 0)写入
    df.to_excel(
        writer,
        sheet_name="部门预算",
        startrow=1,
        startcol=0,
        header=False,  # 极其重要:避免用粗糙文本覆盖精心设计的模板表头
        index=False    # 避免写入多余的数字索引列
    )

print("Pandas 数据已成功覆盖至既有模板中!")

四、深入底层:Excel 样式解耦架构

为什么仅仅更新数值就可以保留边框、底纹和字体?了解 .xlsx 内部的解耦架构有助于从容应对各种复杂的格式保留需求:

Python将数据写入Excel并保留格式的方法
图 3:Excel 内部 XML 样式分离原理图

五、两种实现方案的对比与选型决策

在实际工程项目中,选择 openpyxl 还是 pandas overlay 取决于业务数据的形态与填报的离散度:

对比维度 openpyxl 单元格原位写入 pandas (overlay 模式)
适用场景 复杂表单、非连续填空、卡片式报表、含复杂合并单元格 规整的长列表、批量明细流水、规整多行多列矩阵追加
样式控制粒度 单个单元格级别(可任意精细读取和微调) 整个区块批量写入(无法逐格针对性判断)
对原有公式友好度 极高(只要不触碰公式格,公式与引用完全保留) 较高(需精算起始行列,防止矩形数据块不慎覆盖公式)
代码复杂度 需要编写简单的双重循环遍历或辅助函数 非常精简,仅需几行代码调用 to_excel
内存占用与性能 加载极大文件时较慢(需解析整棵 DOM 树) 中等,适合千行级别的规则结构化数据导入

六、实战中的常见问题与避坑要点

1. 误传 data_only 参数导致模板中所有既有公式丢失

在用 openpyxl.load_workbook() 打开模板时,默认参数是 data_only=False。此时 openpyxl 会保留单元格中的公式文本(如 =SUM(A1:A10))。

若误将参数设为 data_only=True,openpyxl 会将公式单元格直接替换为其上一次保存时的静态计算值。当你再次执行 wb.save() 时,原有公式将永久退化为纯静态数值,后续在 Excel 中修改数据时将无法联动计算。

2. 列宽与换行丢失排查

有时写入长文本后,发现原有的自动换行或列宽表现异常。这是因为部分单元格格式依赖 Excel 软件前端的渲染。若要保持美观,可以在模板中预先拉宽列并勾选“自动换行”,或者在 openpyxl 中通过代码显式声明:

from openpyxl.styles import Alignment

# 确保目标单元格保持自动换行属性
target_cell.alignment = Alignment(wrap_text=True, vertical="center")
Python将数据写入Excel并保留格式的方法
图 4:保留格式写入的三大核心法则与防灾规范

七、总结

在 Python 中将数据写入 Excel 并完整保留格式的最佳实践,本质上是“分工明确的模板化流程”:由业务或设计人员在 Excel 中完成版面排版、字体配色和汇总公式的预制,Python 程序仅充当“精准数据灌入工具”。

对于需要逐项精细填入的凭单、卡片或非连续报表,选用 openpyxl 的 ws.cell().value = val 是最稳定透明的方法;对于规整结构的多行多列批量数据追加,则可以使用 pandas 结合 ExcelWriter 的 if_sheet_exists="overlay" 模式,并在调用时始终带上 header=False, index=False。遵循这两条明确的路径,即可兼顾开发效率与高水准的报表视觉呈现。

相关专题:

本站声明

本文内容仅供参考,不代表本站观点。转载或引用请注明出处。如发现内容问题,请通过联系我们页面反馈。

Python 相关产品和应用

全部下载

专题列表