Python實現簡單的excel對比工具

#!/usr/bin/env python

# -*- coding:utf-8 -*-

# date: 2021/4/17

# filename: xlsx_compare

# author: kplin

import pandas as pd

import os

def my_log(info):

    try:

        with open('error_info.txt', 'w+') as f:

            f.write(info)

            f.close()

    except Exception as e:

        print(' 寫入錯誤日誌時發生以下錯誤： \n%s'%e)

def get_file():

    try:

        # 獲取當前資料夾下的 2 個檔案

        dir_path = os.getcwd()

        files = os.listdir(dir_path)

        ret = []

        for i in files:

            if i.endswith('.xlsx') and not i.endswith('_new.xlsx'):

                ret.append(i)

            if i.endswith('.xlsx') and not i.endswith('_new.xlsx') and '~$' in i:

                info =' 請關閉檔案 %s'%i

                my_log(info)

                return None

        if len(ret) == 0:

            info = ' 找不到待檢測檔案，請將 2 個 xlsx 檔案放入此資料夾 '

            my_log(info)

            return None

        # print(ret)

        return ret[0], ret[1]

    except Exception as e:

        my_log(str(e))

def main(file1, file2):

    try:

        # 1 、獲取原檔案路徑和名稱，先準備即將生成的新檔名和檔案路徑

        fname1, ext = os.path.splitext(os.path.basename(file1))

        new_file1 = 外匯跟單gendan5.comfile1.replace(fname1, fname1 + '_new')

        fname2, ext = os.path.splitext(os.path.basename(file2))

        new_file2 = file2.replace(fname2, fname2 + '_new')

        # 2 、讀取檔案

        df1 = pd.read_excel(file1)

        df2 = pd.read_excel(file2)

        length = len(df1) if len(df1) >= len(df2) else len(df2)

        # 兩個資料塊行數不一致，補成一致的

        if len(df1) - len(df2) > 0:

            # 獲取 DF1 的列名

            d = {}

            for i in df2.columns:

                d[i] = ['' for x in range(len(df1) - len(df2))]

            concat_df = pd.DataFrame(d)

            df2 = pd.concat([df2, concat_df])

        if len(df2) - len(df1) > 0:

            d = {}

            for i in df1.columns:

                d[i] = ['' for x in range(len(df2) - len(df1))]

            concat_df = pd.DataFrame(d)

            df1 = pd.concat([df1, concat_df])

        dis_index = []

        for i in range(len(df1)):

            ret = df1.iloc[i, :]==df2.iloc[i, :]

            if False in ret.tolist():

                dis_index.append(i)

        dis_list = ['' for i in range(length)]

        for i in dis_index:

            dis_list[i] = ' 不一致 '

        df1['Compare Result'] = dis_list

        df2['Compare Result'] = dis_list

        df1.to_excel(new_file1, index=False)

        df2.to_excel(new_file2, index=False)

        my_log(' 校驗成功，本次對比檔案為： %s%s 和 %s%s'%(fname1, ext, fname2, ext))

        print(' 校驗完成，請檢視新檔案 ')

    except Exception as e:

        print(' 出現未知錯誤，請檢視 error_info.txt')

        my_log(str(e))

if __name__ == '__main__':

    if not get_file():

        print(' 讀取檔案時發生錯誤，請檢視 error_info.txt')

    else:

        file1, file2 = get_file()

        main(file1, file2)