使用openpyxl导出DataFrame时指定列并保留模板公式的方法

核心问题分析 #

你的代码使用ws.append(r)会从工作表末尾追加行，容易覆盖模板原有内容，同时可能破坏公式的引用逻辑。要实现精准写入指定列且保留公式，需要逐个单元格定向写入，而非整行追加。

修改后的代码 #

from sqlalchemy import text, create_engine
import pandas as pd
from openpyxl import load_workbook

sql = text('''SELECT * FROM XXX''')
db = create_engine("mysql+pymysql://user:pass@host/dbname")
df = pd.read_sql(sql, db)
# 加载模板时保留公式（data_only=False为默认值，显式声明更清晰）
wb = load_workbook('template.xlsx', data_only=False)
ws = wb["base"]

# 配置写入规则：起始行、目标Excel列（需与DataFrame列顺序对应）
start_row = 2  # 假设模板第1行是表头/固定内容，从第2行开始写数据
target_excel_columns = ['A', 'B', 'C']  # 对应DataFrame的3列数据

# 校验列数匹配，避免索引越界
assert len(target_excel_columns) == len(df.columns), "目标列数与DataFrame列数不匹配"

# 遍历DataFrame逐行写入指定列
for row_num, df_row in enumerate(df.itertuples(index=False), start=start_row):
    for col_idx, cell_value in enumerate(df_row):
        # 获取目标列的单元格
        target_cell = ws[f"{target_excel_columns[col_idx]}{row_num}"]
        target_cell.value = cell_value

# 可选：强制计算所有公式（打开Excel时也会自动计算）
wb.calculate_all()

wb.save("pandas_openpyxl.xlsx")

关键说明 #

• 定向写入单元格：通过ws[f"{col}{row}"]精准定位要写入的单元格，仅修改目标列的内容，完全保留其他列的模板公式。
• 保留公式配置：data_only=False确保加载模板时保留公式本身，而非公式计算后的静态值。
• 公式自动关联：如果模板中的公式使用相对引用（如=A2+B2），写入数据后，公式会自动引用对应行的新数据，打开Excel即可看到计算结果。
• 起始行控制：通过start_row指定数据写入的起始位置，避免覆盖模板的表头或固定内容。

额外注意事项 #

• 确保target_excel_columns的顺序与DataFrame的列顺序完全对应，否则会出现数据错位。
• 如果模板中公式使用绝对引用（如=$A$2），需手动调整公式逻辑适配批量数据，建议使用相对引用更灵活。

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