如何用Python+Jinja2实现Excel模板公式动态修改与DataFrame填充？

这里提供两种实用方案，既能保留Excel模板的格式，又能实现你提到的动态修改求和公式的功能：

方案一：用openpyxl直接操作Excel模板（简单直观） #

这种方法无需转换格式，直接基于原Excel模板操作，能完美保留原有格式，适合大多数常规场景：

步骤与示例代码： #

import pandas as pd
from openpyxl import load_workbook

# 1. 准备要填充的数据
df = pd.DataFrame({
    '销售额': [1500, 2300, 1800, 3200]
})

# 2. 加载带格式的Excel模板
wb = load_workbook('sales_template.xlsx')
ws = wb.active

# 3. 将DataFrame数据填充到模板指定位置（示例从B3开始）
for row_idx, (_, row_data) in enumerate(df.iterrows(), start=3):
    ws.cell(row=row_idx, column=2, value=row_data['销售额'])

# 4. 动态计算求和公式的范围
start_cell = 'B3'  # 数据起始单元格
# 解析列标识和起始行号
col = ''.join([c for c in start_cell if c.isalpha()])
start_row = int(''.join([c for c in start_cell if c.isdigit()]))
end_row = start_row + len(df) - 1  # 计算数据结束行

# 生成动态求和公式并写入指定单元格（示例写入B7）
sum_formula = f"=SUM({col}{start_row}:{col}{end_row})"
ws['B7'].value = sum_formula

# 5. 保存最终报表
wb.save('sales_report.xlsx')

方案二：基于Jinja2+Excel XML模板（适合复杂批量渲染） #

如果需要批量生成大量格式复杂的报表，可以利用Excel的XML底层结构，将模板转为Jinja2模板后批量渲染：

步骤与示例代码： #

1. 先制作好Excel模板，另存为「Excel XML Spreadsheet (*.xml)」格式
2. 打开XML文件，将数据填充位替换为Jinja2变量，公式部分改为动态占位符

import pandas as pd
from jinja2 import Environment, FileSystemLoader

# 1. 准备数据
df = pd.DataFrame({
    '销售额': [1500, 2300, 1800, 3200]
})
data_records = df.to_dict('records')
data_len = len(df)

# 2. 配置Jinja2环境，添加自定义过滤器计算求和范围
env = Environment(loader=FileSystemLoader('.'))

def get_sum_range(start_cell, row_count):
    col = ''.join([c for c in start_cell if c.isalpha()])
    start_row = int(''.join([c for c in start_cell if c.isdigit()]))
    end_row = start_row + row_count - 1
    return f"{col}{start_row}:{col}{end_row}"

env.filters['get_sum_range'] = get_sum_range

# 3. 加载并渲染XML模板
template = env.get_template('sales_template.xml')
rendered_xml = template.render(data=data_records, data_len=data_len)

# 4. 保存渲染后的XML，直接用Excel打开即可转为XLSX格式
with open('rendered_report.xml', 'w', encoding='utf-8') as f:
    f.write(rendered_xml)

XML模板中的公式示例： #

将原模板中的=SUM(B3)替换为：

<Cell><Data ss:Type="String">=SUM({{ get_sum_range('B3', data_len) }})</Data></Cell>

核心逻辑说明 #

两种方案的核心思路一致：

• 确定数据填充的起始单元格位置
• 根据DataFrame的行数计算出数据的结束单元格
• 拼接成动态公式字符串，替换模板中的固定公式

内容的提问来源于stack exchange，提问作者asmaier