向已有Excel追加带背景色的DataFrame时如何保留列边线？

解决办法1：通过Styler统一添加边框 #

直接在样式配置中加入边框属性，让追加的单元格和原有单元格保持一致的侧边线样式：

with pd.ExcelWriter(file_path, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
    style_config = {
        'background-color': '#FFFF00',
        'color': 'black',
        'border': '1px solid black'  # 匹配原文件的细黑线侧边线
    }
    pre_info_df.style.set_properties(**style_config).to_excel(
        writer, sheet_name="Sheet1", header=False,
        startrow=row_to_start_index + 1, startcol=4, index=False
    )

解决办法2：精准匹配原Excel的边框样式 #

如果原文件的边框不是默认细黑线，可读取原有单元格的边框样式再应用到新数据上：

from openpyxl import load_workbook

# 加载工作簿获取原有单元格的边框样式
wb = load_workbook(file_path)
ws = wb['Sheet1']
# 取原有数据区域的一个单元格作为样式参考
sample_cell = ws.cell(row=row_to_start_index, column=4)
target_border = sample_cell.border

# 写入数据并设置边框
with pd.ExcelWriter(file_path, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
    pre_info_df.style.set_properties(**{'background-color': '#FFFF00', 'color': 'black'}).to_excel(
        writer, sheet_name="Sheet1", header=False,
        startrow=row_to_start_index + 1, startcol=4, index=False
    )
    # 获取当前工作表并遍历新写入的单元格设置边框
    current_ws = writer.sheets['Sheet1']
    for row_num in range(row_to_start_index + 1, row_to_start_index + 1 + len(pre_info_df)):
        for col_num in range(4, 4 + len(pre_info_df.columns)):
            current_ws.cell(row=row_num, column=col_num).border = target_border

wb.save(file_path)

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