如何在Python中将DataFrame数据增量写入Excel并避免重复

pip install pandas openpyxl

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

excelpath = 'contact.xlsx'

# 示例新数据
df1 = pd.DataFrame([
    {"alias": "xyz", "fullname": "abc"},
    {"alias": "def", "fullname": "ghi"},
    {"alias": "uvw", "fullname": "xyz"},
    {"alias": "new1", "fullname": "New User 1"}, # 新增数据
    {"alias": "new2", "fullname": "New User 2"}  # 新增数据
])

try:
    # 尝试读取现有Excel数据到DataFrame
    existing_df = pd.read_excel(excelpath, sheet_name='Sheet1') 
except FileNotFoundError:
    # 如果文件不存在，则创建一个空的DataFrame，列名与待写入数据一致
    existing_df = pd.DataFrame(columns=['alias', 'fullname']) 

# 筛选出df1中'alias'列的值不在existing_df中'alias'列的值的行
new_data = df1[~df1['alias'].isin(existing_df['alias'])]

if not new_data.empty:
    # 加载工作簿
    wb = load_workbook(excelpath)
    # 选择目标工作表
    ws = wb['Sheet1']

    # 将新的数据行追加到工作表
    # index=False 避免写入DataFrame索引
    # header=False 避免重复写入列标题，因为Excel中通常已有标题
    for r in dataframe_to_rows(new_data, index=False, header=False):
        ws.append(r)

    # 保存工作簿
    wb.save(excelpath)
    print("数据已成功追加，并跳过重复项。")
else:
    print("没有新的数据需要追加，所有记录均已存在。")

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

excelpath = 'contact.xlsx'

# 示例新数据
df1 = pd.DataFrame([
    {"alias": "xyz", "fullname": "abc"},
    {"alias": "def", "fullname": "ghi"},
    {"alias": "uvw", "fullname": "xyz"},
    {"alias": "new1", "fullname": "New User 1"},
    {"alias": "new2", "fullname": "New User 2"}
])

try:
    # 尝试读取现有Excel数据
    existing_df = pd.read_excel(excelpath, sheet_name='Sheet1') 
    # 确保'alias'列存在，如果不存在则初始化为空列表，防止后续isin操作报错
    if 'alias' not in existing_df.columns:
        existing_df['alias'] = []
except FileNotFoundError:
    # 如果文件不存在，则创建一个空的DataFrame作为基准
    existing_df = pd.DataFrame(columns=['alias', 'fullname']) 

# 筛选出非重复的新数据
# 只有当df1中的'alias'值不在existing_df的'alias'列中时，才保留该行
new_data = df1[~df1['alias'].isin(existing_df['alias'])]

if not new_data.empty:
    # 如果有新的数据需要写入
    wb = load_workbook(excelpath)
    ws = wb['Sheet1']

    # 检查是否需要写入标题行
    # 如果工作表为空（行数为0或只有默认标题），则写入标题
    if ws.max_row == 0 or (ws.max_row == 1 and all(cell.value is None for cell in ws[1])):
        ws.append(list(new_data.columns)) # 写入列标题

    # 将新的数据行追加到工作表，不写入索引和重复标题
    for r in dataframe_to_rows(new_data, index=False, header=False):
        ws.append(r)

    wb.save(excelpath)
    print("数据已成功追加，并跳过重复项。")
else:
    print("没有新的数据需要追加，所有记录均已存在。")