使用Python向Excel表格追加行时保留公式不被覆盖的实现问题

嘿，这个坑我之前刚踩过！你现在的问题核心在于：用xlwings把Excel表格转成pandas.DataFrame的时候，默认读取的是单元格的计算结果值，而不是公式本身，所以你的df里根本没带着公式信息，之后再把df写回表格，自然就把原来的公式列覆盖成静态值了。

我给你两个实用的解决方案，都是亲测有效的：

方案一：直接操作Excel表格对象（最推荐） #

这个方法完全绕开DataFrame的坑，直接定位到表格末尾插入新行，只写入需要更新的纯数据列，公式列原封不动（甚至Excel会自动帮你填充公式，如果是结构化引用的话）。

修改后的代码如下：

import xlwings as xw

planilla = 'planilla.xlsx'
sheet = 'Hoja1'
tabla = "empleados"

with xw.App(visible=True) as xl:
    book = xl.books.open(planilla)
    ws = book.sheets[sheet]
    table = ws.tables[tabla]  # 直接拿到Excel的表格对象（ListObject）
    
    # 定位到表格最后一行的下一行，也就是要插入新行的位置
    new_row_rng = table.range.offset(rows=table.range.rows.count)
    
    # 准备新行的纯数据（公式列不用写进去）
    new_data = {
        "nam": "Carlos",
        "años": 23
        # 比如像"salario_calculado"这种公式列直接跳过
    }
    
    # 遍历新数据，找到对应列写入值
    for col_name, value in new_data.items():
        # 找到该列在表格中的位置（xlwings是1-based索引）
        col_idx = table.header_row_range.value.index(col_name)
        target_cell = new_row_rng.columns(col_idx + 1)
        target_cell.value = value
    
    # ！如果你的公式列不是用结构化引用（比如不是`=[@años]*1200`这种），那需要手动复制上一行的公式
    # 举个例子，假设最后一列是公式列：
    # last_col_idx = table.range.columns.count
    # 取上一行的公式
    # formula = table.range.offset(rows=table.range.rows.count-1).columns(last_col_idx).formula
    # 把公式写入新行对应列
    # new_row_rng.columns(last_col_idx).formula = formula
    
    # 保险起见，手动让表格扩展包含新行
    table.resize(table.range.resize(table.range.rows.count + 1))

为什么这个方法有效？ #

• 直接操作Excel原生的表格对象，没有把表格转成DataFrame，完全保留了原有的公式结构
• 只写入需要更新的纯数据列，公式列要么由Excel自动填充（结构化引用公式），要么手动复制上一行的公式，根本不会被覆盖

方案二：必须用DataFrame处理数据时的折中方案 #

如果你确实需要用DataFrame先处理原有数据，那记得只读写纯数据列，公式列单独保留，不要碰它们：

import xlwings as xw
import pandas as pd

planilla = 'planilla.xlsx'
sheet = 'Hoja1'
tabla = "empleados"

with xw.App(visible=True) as xl:
    book = xl.books.open(planilla)
    ws = book.sheets[sheet]
    table = ws.tables[tabla]
    
    # 先拿到表格所有列名，区分纯数据列和公式列
    all_col_names = table.header_row_range.value
    # 这里把你的公式列列出来，比如["salario_calculado"]
    formula_cols = ["salario_calculado"]
    data_cols = [col for col in all_col_names if col not in formula_cols]
    
    # 只读取纯数据列到DataFrame，公式列跳过
    df = table.range.options(
        pd.DataFrame, 
        index=False, 
        headers=True,
        exclude=formula_cols  # 排除公式列，只读纯数据
    ).value
    
    # 处理你的数据，比如追加新行
    new_row = pd.DataFrame([{
        "nam": "Carlos",
        "años": 23
    }])
    df = pd.concat([df, new_row], ignore_index=True)
    
    # 把处理后的纯数据列写回表格，公式列不动
    for col_name in data_cols:
        col_idx = all_col_names.index(col_name)
        # 定位到表格中该列的所有数据行（从表头下一行开始）
        target_rng = table.range.offset(rows=1).columns(col_idx + 1).resize(rows=len(df))
        target_rng.value = df[col_name].values
    
    # 同样，如果公式列需要手动复制，参考方案一里的代码

你原来代码的问题出在哪？ #

你原来的rng.expand().options(pd.DataFrame, index=False).value这一步，xlwings会把单元格的计算结果读取到DataFrame里，而不是公式本身。之后你把这个带静态值的DataFrame写回表格，自然就把原来的公式列完全覆盖成静态值了，公式肯定就没了。

备注：内容来源于stack exchange，提问作者Osinaga