Excel快速比对

import pandas as pd
import warnings
import os
import tkinter as tk
from tkinter import filedialog


def diff(row, params):
    s = set(p for p in params if row[p + '_new'] != row[p + '_old'])
    return "Change " + ",".join(s)


def data_compare(old, new):
    old = old.drop(old.columns[0], axis=1)
    new = new.drop(new.columns[0], axis=1)

    delete = old[~old['Port Name'].isin(new['Port Name'])]
    delete['Status'] = 'Delete'

    create = new[~new['Port Name'].isin(old['Port Name'])]
    create['Status'] = 'Create'

    union = pd.merge(old, new, on='Port Name', suffixes=('_old', '_new'))
    res = union.apply(diff, args=(old.columns[1:],), axis=1)
    union['Status_new'] = res
    change = union[union['Status_new'] != 'Change '].filter(regex='Port Name|_new$')
    change.columns = change.columns.str.replace('_new', '')
    return pd.concat([delete, create, change])


if __name__ == '__main__':
    warnings.simplefilter("ignore")
    root = tk.Tk()
    root.withdraw()
    new_folder = filedialog.askdirectory(title="选择new文件夹")
    old_folder = filedialog.askdirectory(title="选择old文件夹")
    res_file = os.path.join(os.getcwd(), 'ChangeLog.xlsx')
    if os.path.isfile(res_file):
        os.remove(res_file)
    models = os.listdir(old_folder)

    AllRport = pd.DataFrame()
    AllPport = pd.DataFrame()

    for f_name in models:
        print("Reading " + f_name)
        f_path = os.path.join(old_folder, f_name)
        if os.path.isdir(f_path) and f_name.startswith("Vc"):
            f_old = os.path.join(f_path, '_doc', f_name + '.xlsx')
            f_new = os.path.join(new_folder, f_name, '_doc', f_name + '.xlsx')
            if not os.path.isfile(f_new):
                print("new文件夹内缺少 " + f_name)
                continue
        else:
            continue

        data_old = pd.read_excel(f_old, sheet_name=['Pport', 'Rport'], keep_default_na=False)
        data_new = pd.read_excel(f_new, sheet_name=['Pport', 'Rport'], keep_default_na=False)
        Rport = data_compare(data_old['Rport'], data_new['Rport'])
        Rport['Model'] = f_name
        AllRport = pd.concat([AllRport, Rport])
        Pport = data_compare(data_old['Pport'], data_new['Pport'])
        Pport['Model'] = f_name
        AllPport = pd.concat([AllPport, Pport])

    with pd.ExcelWriter('ChangeLog.xlsx') as writer:
        AllRport.to_excel(writer, sheet_name='Rport', index=False)
        AllPport.to_excel(writer, sheet_name='Pport', index=False)
    print("Done!")