如何使用openpyxl与Python扩展表格时保留原有样式及公式？

openpyxl不会像Excel手动操作那样自动为新增行继承表格的格式和公式，需要手动编写代码同步这两部分内容，具体实现步骤如下：

一、同步公式 #

先提取表格原有数据行的公式模板，再将模板适配到新增行：

1. 获取表格扩展前的最后一行，以此行的公式作为模板
2. 替换公式中的固定行号为新增行的行号（如果是结构化引用则无需替换）
3. 将适配后的公式写入新增行对应单元格

二、同步格式 #

复制原有最后一行的单元格样式，应用到所有新增行：

1. 提取原有最后一行的字体、填充、边框、对齐方式、数字格式等样式属性
2. 遍历新增行，将样式逐一应用到对应单元格

完整代码示例 #

from openpyxl import load_workbook

# 加载目标工作簿与工作表
wb = load_workbook("your_file.xlsx")
bs_sheet = wb.active

# 获取表格扩展前的最后一行
table_ref = bs_sheet.tables['Table1'].ref
original_last_row = int(table_ref.split(':')[1][1:])
# 新增行的范围
new_rows_start = original_last_row + 1
new_rows_end = bs_sheet.max_row

# --- 同步公式 ---
column_formulas = {}
# 遍历A到H列（对应1-8列）提取公式模板
for col in range(1, 9):
    template_cell = bs_sheet.cell(row=original_last_row, column=col)
    # 判断单元格是否为公式类型
    if template_cell.data_type == 'f':
        # 如果是普通单元格引用，替换行号为占位符；结构化引用可直接保留
        formula_template = template_cell.value.replace(str(original_last_row), "{row}")
        column_formulas[col] = formula_template

# 为新增行设置公式
for row in range(new_rows_start, new_rows_end + 1):
    for col, formula in column_formulas.items():
        bs_sheet.cell(row=row, column=col).value = formula.format(row=row)

# --- 同步格式 ---
column_styles = {}
# 提取原有最后一行的样式
for col in range(1, 9):
    template_cell = bs_sheet.cell(row=original_last_row, column=col)
    column_styles[col] = {
        "font": template_cell.font.copy(),
        "fill": template_cell.fill.copy(),
        "border": template_cell.border.copy(),
        "alignment": template_cell.alignment.copy(),
        "number_format": template_cell.number_format
    }

# 将样式应用到新增行
for row in range(new_rows_start, new_rows_end + 1):
    for col, style in column_styles.items():
        target_cell = bs_sheet.cell(row=row, column=col)
        target_cell.font = style["font"]
        target_cell.fill = style["fill"]
        target_cell.border = style["border"]
        target_cell.alignment = style["alignment"]
        target_cell.number_format = style["number_format"]

# 扩展表格引用
bs_sheet.tables['Table1'].ref = f"A1:H{new_rows_end}"

# 保存修改后的工作簿
wb.save("your_file_updated.xlsx")

注意事项 #

• 如果表格使用结构化引用（如=Table1[@[销售额]]*0.1），公式无需替换行号，直接复制即可自动适配新增行
• 若存在条件格式，需额外调整条件格式规则的应用范围，可通过bs_sheet.conditional_formatting对象修改规则的sqref属性
• 确保新增数据的行号范围准确，避免出现越界错误

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