使用Python Pandas更新Excel时丢失其他工作表的原因与解决方法

一、问题成因 #

1. 文件覆盖导致工作表丢失：当前代码使用pd.ExcelWriter配合xlsxwriter引擎写入文件时，会直接覆盖原Excel文件，仅写入当前处理的单个工作表，原有其他工作表未被重新写入，因此被删除。
2. 逻辑未满足需求：原函数仅检查指定单个工作表中的name值，未实现“检查任意工作表是否存在该name”的核心需求，过滤出的新字典可能不准确。

二、解决方案 #

核心思路 #

• 先读取Excel文件的所有工作表，收集所有已存在的name值，确保过滤逻辑覆盖所有表。
• 写入文件时，将所有原有工作表和更新后的目标表一起写入，避免丢失原有数据。

修改后的代码 #

import pandas as pd

def updater(data, filename, target_sheet, column_name, key):
    # 读取所有工作表，存储为{表名: DataFrame}的字典
    try:
        all_sheets = pd.read_excel(filename, sheet_name=None)
    except FileNotFoundError:
        # 文件不存在时，初始化目标工作表
        all_sheets = {target_sheet: pd.DataFrame()}

    # 收集所有工作表中指定列的已存在值（去重）
    existing_values = set()
    for sheet_df in all_sheets.values():
        if column_name in sheet_df.columns:
            # 过滤空值后加入集合
            existing_values.update(sheet_df[column_name].dropna().values)

    # 过滤出未在任意工作表中出现的新字典
    new_dicts = [d for d in data if d.get(key) not in existing_values]

    # 更新目标工作表的数据（用concat替代已弃用的_append）
    target_df = all_sheets.get(target_sheet, pd.DataFrame())
    new_rows = pd.DataFrame(new_dicts)
    target_df = pd.concat([target_df, new_rows], ignore_index=True)
    all_sheets[target_sheet] = target_df

    # 将所有工作表写入文件，保留原有结构
    with pd.ExcelWriter(filename, engine='openpyxl', mode='w') as writer:
        for sheet_name, df in all_sheets.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)

    print(f"新增了 {len(new_dicts)} 条数据: {new_dicts}")
    print("[INFO] 数据已成功添加，所有工作表均保留")

# 示例调用
data_list = [{'name': 'abc', 'link': 'google.com'}, {'name': 'vba', 'link': 'google.com', 'age': 23}]
updater(data_list, "test.xlsx", "Sheet1", "name", "name")

代码关键点说明 #

• sheet_name=None：读取Excel的所有工作表，返回包含所有表的字典，避免遗漏原有数据。
• 遍历所有工作表收集name值：确保过滤出的新字典是在所有工作表中都不存在的name，符合需求。
• 使用pd.concat：替代已弃用的DataFrame._append方法，更稳定且符合pandas最新规范。
• 写入所有工作表：遍历all_sheets字典，将每个工作表都写入文件，彻底解决原有表丢失的问题。
• 自动处理字典键不匹配：pd.DataFrame(new_dicts)会自动为缺失的键创建列，缺失值填充为NaN，兼容不同结构的字典。

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