如何实现Excel差异表仅输出JSON对比后修改/新增/删除的行

核心问题梳理 #

当前代码存在2个导致输出全量行的问题：

1. 数据导出笔误：add2excel函数生成new工作表时，错误使用旧版数据集合dfs1拼接，导致新版数据完全错误
2. 差异表未过滤：dfDiff初始直接复制了全部新版数据，且没有剔除新旧表中完全一致的无修改行

具体修改点 #

1. 修复add2excel中新版数据拼接逻辑
2. 差异表生成后过滤comment字段为空的无修改行
3. 替换废弃的append方法为pd.concat适配高版本pandas

修改后完整代码 #

import pandas as pd
from pathlib2 import Path
from xlsxwriter import *
from tkinter.font import BOLD
import ruamel.yaml
import os,json
import numpy as np
from tkinter import *
from tkinter import messagebox
from tkinter import ttk
from tkinter import filedialog

root=Tk()
root.resizable(False,False)
mywidth=550
myheight=460

selectfile=Label(root,text="File1(旧版JSON目录)")
selectfile1=Label(root,text="File2(新版JSON目录)")
selectfile2=Label(root,text="save file(保存路径)")

selectfile.grid(row=1,column=1)
selectfile1.grid(row=2,column=1)
selectfile2.grid(row=3,column=1)

selectfileentry=Entry(root,width=40)
selectfileentry1=Entry(root,width=40)
selectfileentry2=Entry(root,width=40)

selectfileentry.grid(row=1,column=2)
selectfileentry1.grid(row=2,column=2)
selectfileentry2.grid(row=3,column=2)

def open_file1():
    global filepath,filelist
    filepath=filedialog.askdirectory(title="选择旧版JSON所在目录")
    filelist=[]
    for root,dirs,files in os.walk(filepath):
        for file in files:
            if file.endswith(".json"):
                filelist.append(os.path.join(root,file))
    selectfileentry.insert(0,filepath)

def open_file2():
    global filepath2,filelist2
    filepath2=filedialog.askdirectory(title="选择新版JSON所在目录")
    filelist2=[]
    for root,dirs,files in os.walk(filepath2):
        for file in files:
            if file.endswith(".json"):
                filelist2.append(os.path.join(root,file))
    selectfileentry1.insert(0,filepath2)

def buttontoexcel():
    global fvc
    fvc=filedialog.asksaveasfilename(defaultextension=".xlsx",filetypes=[("excel files","*.xlsx")])
    selectfileentry2.insert(0,fvc)

def add2excel():
    dfs1=[]
    writer=pd.ExcelWriter(fvc,engine="xlsxwriter")
    for file in filelist:
        with open(file, encoding='utf-8') as f:
            ab=json.load(f)
            gg=ab["Indicator"]
            json_data=pd.json_normalize(gg)
            dfs1.append(json_data)
    df_old_all = pd.concat(dfs1)
    df_old_all.to_excel(writer,sheet_name="old",index=False)

    dfs2=[]
    for file in filelist2:
        with open(file, encoding='utf-8') as f:
            ab=json.load(f)
            gg=ab["Indicator"]
            json_data=pd.json_normalize(gg)
            dfs2.append(json_data)
    # 修复原笔误：这里用dfs2拼接新版数据
    df_new_all = pd.concat(dfs2)
    df_new_all.to_excel(writer,sheet_name="new",index=False)
    writer.save()
    
def excel_dif():
    df_old=pd.read_excel(fvc,sheet_name=0,keep_default_na=False)
    df_new=pd.read_excel(fvc,sheet_name=1,keep_default_na=False)
    # 对齐两表字段
    all_cols = list(set(df_new.columns)|set(df_old.columns))
    for col in all_cols:
        if col not in df_old.columns:
            df_old[col]=''
        if col not in df_new.columns:
            df_new[col]=''
    key=['Name','NeType','Category']

    df_old=df_old.set_index(key)
    df_new=df_new.set_index(key)

    dfDiff=df_new.copy()
    droppedRows=[]
    newRows=[]
    modifiedRows=[]
    cols_old=df_old.columns
    cols_new=df_new.columns
    sharedcols=list(set(cols_old).intersection(cols_new))
    dfDiff.insert(0,"comment",'')

    for row in dfDiff.index:
        if(row in df_old.index)and(row in df_new.index):
            is_modified = False
            for col in sharedcols:
                value_old=df_old.loc[row,col]
                value_new=df_new.loc[row,col]
                if value_old!=value_new:
                    dfDiff.loc[row,col]=(value_new)
                    is_modified = True
            if is_modified:
                dfDiff.loc[row,'comment']="Modified"
                modifiedRows.append(row)
        else:
            dfDiff.loc[row,'comment']='Added'
            newRows.append(row)

    # 收集删除的行
    dropped_df_list = []
    for row in df_old.index:
        if row not in df_new.index:
            droppedRows.append(row)
            dropped_row = df_old.loc[row,:].copy()
            dropped_row['comment'] = 'removed'
            dropped_df_list.append(dropped_row.to_frame().T)
    
    # 追加删除行，替换废弃的append方法
    if dropped_df_list:
        dropped_df = pd.concat(dropped_df_list)
        dfDiff = pd.concat([dfDiff, dropped_df], axis=0)
    
    # 核心过滤：仅保留comment不为空的行（即修改/新增/删除的行）
    dfDiff = dfDiff[dfDiff['comment']!=''].copy()

    writer2=pd.ExcelWriter(fvc,engine='xlsxwriter')
    df_old.to_excel(writer2,sheet_name="old",index=True)
    df_new.to_excel(writer2,sheet_name="new",index=True)
    dfDiff.to_excel(writer2,sheet_name="delta",index=True)
    writer2.save()


ttk.Button(text="浏览",command=open_file1).grid(row=1,column=3)
ttk.Button(text="浏览",command=open_file2).grid(row=2,column=3)
ttk.Button(text="浏览",command=buttontoexcel).grid(row=3,column=3)
ttk.Button(text="开始生成",command=lambda:[add2excel(),excel_dif()]).grid(row=4,column=3)

root.mainloop()

额外优化说明 #

• 补充了文件读取的encoding='utf-8'参数，避免中文乱码
• 对齐字段逻辑优化，同时补全新旧表缺失的字段
• 按钮文本修改为中文更易理解
• 修改行判断逻辑优化，避免同一行多字段修改时重复添加到modified列表

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