先写文件对比的逻辑代码，包括读取文件，对比文件，将对比出来的差异写入另一个excel文件
1.读取文件，我这里是选取自己需要的不同的列，选定了指定的sheet列表，读者可根据需求更改
def readexcel(file): # 打开Excel文件 workbook = openpyxl.load_workbook(file) # 选择指定的Sheet sheet = workbook['缺陷列表_1'] # 选择需要获取的列的列号 col_numbers = [3, 5, 6] # 列名 titles = ['二级分类', '等级', '爆发行'] result = [] # 从第2行开始获取数据 for i in range(2, sheet.max_row + 1): # 每一行的数据 row_data = [] for col in col_numbers: row_data.append(sheet.cell(row=i, column=col).value) # 构建字典 row_dict = dict(zip(titles, row_data)) result.append(row_dict) return result
2.对比两个文件，获取到两个表对比的不同的地方，组合成元素为字典的列表，需要注意两个文件的长度大小
1 def comparefile(list1, list2): 2 list1 = tuple(tuple(item.items()) for item in list1) 3 list2 = tuple(tuple(item.items()) for item in list2) 4 5 match = len(set(list1) & set(list2)) # 记录重合数 6 diff = len(set(list1) - set(list2)) + len(set(list2) - set(list1)) # 记录差异数 7 differ1 = set(list1) - set(list2) # 记录list1的差异元素 8 differ2 = set(list2) - set(list1) # 记录list2的差异元素 9 differ1_dicts = [dict(item) for item in list(differ1)] 10 differ2_dicts = [dict(item) for item in list(differ2)] 11 12 print("1111", differ1_dicts, differ2_dicts) 13 print(f"重合数：{match}，差异数：{diff}") 14 # differ1=list(differ1) 15 # differ2=list(differ2) 16 return differ1_dicts, differ2_dicts,match,diff
3.将获取到的两个不同的列表写入到新的excel文件中，注意需要判断两个文件是否为空
def writeexcel2(differ1, differ2,filewname): global flag # 创建一个新的Excel工作簿 wb = openpyxl.Workbook() # 获取正在使用的工作表 sheet = wb.active # 写入differ1 # 获取所有的key,先判断是否为空 if differ1 and differ2: keys = differ1[0].keys() # 首先将key作为表头写入第一行 for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行数据 for row_num, row_data in enumerate(differ1, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # 差异表1和差异表2之间有两个空列，写入空列数据 sheet.cell(row=row_num, column=len(keys) + 3, value="") # 为当前行的所有单元格添加淡蓝色填充 for col_num in range(1, len(keys) + 1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid') # 写入differ2 # 得到所有的key # 首先把key作为表头写入第一行 for col_num, key in enumerate(keys,start=len(differ1[0].keys()) + 3): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行的数据 for row_num, row_data in enumerate(differ2, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num+ len(differ1[0].keys()) + 2) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # 为当前行的所有单元格添加淡黄色填充 for col_num in range(len(differ1[0].keys()) + 3, len(keys) + len(differ1[0].keys()) + 4): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0', fill_type='solid') #如果differ1不为空,differ2为空 elif differ1 and not differ2: keys = differ1[0].keys() # 首先将key作为表头写入第一行 for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行数据 for row_num, row_data in enumerate(differ1, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # 差异表1和差异表2之间有两个空列，写入空列数据 sheet.cell(row=row_num, column=len(keys) + 3, value="") # 为当前行的所有单元格添加淡蓝色填充 for col_num in range(1, len(keys) + 1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid') #如果differ1为空。differ2不为空 elif len(differ2)!=0 and len(differ1)==0: keys = differ2[0].keys() # 首先将key作为表头写入第一行 for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行数据 for row_num, row_data in enumerate(differ2, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # # 差异表1和差异表2之间有两个空列，写入空列数据 sheet.cell(row=row_num, column=len(keys) + 3, value="") # 为当前行的所有单元格添加淡黄色填充 for col_num in range(1, len(keys) + 1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0', fill_type='solid') #两个列表都为空 else: print("表1和表2数据完全重合") # 保存Excel工作簿 wb.save(filewname) flag = True print("保存差异值到excel文件完成") return flag
以上就是文件对比的逻辑代码。现在我们需要使用tkinter构建前端小工具
def FileOpen(): #文件对话框,能打开写死的文件框 return_value = tkinter.filedialog.askopenfilename() if return_value.strip()!=" ": filename1.set(return_value) else: print("未上传所需文件") #直接打开文件选择框 print(type(return_value),return_value) def FileOpen2(): #文件对话框,能打开写死的文件框 return_value = tkinter.filedialog.askopenfilename() if return_value.strip()!=" ": filename2.set(return_value) else: print("未上传所需文件") #直接打开文件选择框 print(type(return_value),return_value) def FileSave(): #设置保存文件，并返回文件名，指定文件名后缀为“.xls r=tkinter.filedialog.asksaveasfilename(title="保存文件",defaultextension='.xls' # initialdir="E:\Tools\files", # initialfile='test.py' ) if r.strip()!=' ': filewname.set(r) else: print("未选择需要保存的文件") print(r) root = Tk() # 创建主窗口【必要步骤】 # 将该窗口赋值给root变量，方便后续使用 root.title('主窗口') root.geometry('960x480+150+100')#设置窗口大小及位置 root.wm_attributes('-alpha')#设置透明度为0.7 root.resizable(1,1)#窗口大小不可改变两个布尔值分别代表窗口的长和宽是否可改变（可以用0和1代替布尔值） filename1 = tkinter.StringVar() filename2=tkinter.StringVar() filewname=tkinter.StringVar() # toplevel=Toplevel(root) # toplevel.title('子窗口') #打开文件 #Entry：文本框输入框，Label：标签熟悉 def ui(): tkinter.Label(root, text='选择第一个文件').grid(row=1, column=1, padx=5, pady=7) #第一个输入框 filename1path=tkinter.Entry(root, textvariable=filename1, width=40 ) filename1path.grid(row=1, column=2, padx=5, pady=5) b1=Button(root,relief='groove',activebackground='pink',bg='lightblue',overrelief='ridge',text='上传文件',command=FileOpen).grid(row=1, column=3, padx=5, pady=5)#创建按钮 # b1.pack(side="left") tkinter.Label(root, text='选择第二个文件').grid(row=2, column=1, padx=5, pady=5) #第二个输入框 filename2path=tkinter.Entry(root, textvariable=filename2, width=40 ) filename2path.grid(row=2, column=2, padx=5, pady=5) b2=Button(root,relief='groove',activebackground='pink',bg='lightblue',overrelief='ridge',text='上传文件',command=FileOpen2).grid(row=2, column=3, padx=5, pady=5) # b2.pack(side="left") # b.place(width=100,height=30,x=150,y=200)#防止按钮 tkinter.Label(root, text='保存文件').grid(row=5, column=1, padx=5, pady=5) #第三个输入框 filewnamepath=tkinter.Entry(root, textvariable=filewname, width=40 ) filewnamepath.grid(row=5, column=2, padx=5, pady=5) b3=Button(root,text='保存文件',command=FileSave,relief='groove',activebackground='pink',bg='orange',overrelief='ridge',).grid(row=5, column=3, padx=5, pady=5) # b3.pack(side="left") tkinter.Button(root,text="开始对比",activebackground='pink',bg='lightblue',overrelief='ridge',command=lambda :contrast_button_clicked(filename1path.get(),filename2path.get(),filewnamepath.get())).grid(row=6, column=3, padx=5, pady=5) #窗口主循环 root.mainloop() # 主窗口进入消息事件循环【必要步骤】放在最后
tkinter界面和逻辑都构建完成，就是将以上的函数组合起来，主函数为main
def main(filename1path,filename2path,filewnamepath): list1 = readexcel(filename1path) list2 = readexcel(filename2path) print(list1) print(list2) differ1, differ2,match,diff = comparefile(list1, list2) flag=writeexcel2(differ1, differ2,filewnamepath) return match,diff,flag
main函数的返回值需要显示在tkinker小工具的界面上，于是
def contrast_button_clicked(filename1path, filename2path, filewnamepath): match, diff,flag = main(filename1path, filename2path, filewnamepath) if flag: tkinter.messagebox.showinfo("提示", "对比成功！共有"+str(match)+"组数据重合，"+"共有" +str(diff)+"组数据不一致，详细情况请到"+ filewnamepath + "文件查看") else: tkinter.messagebox.showinfo("提示", "对比失败！请检查输入路径是否正确")
最后
if __name__ == '__main__': ui()
整体的代码为
import tkinter.filedialog from tkinter import messagebox from tkinter import * # 导入tkinter模块【必要步骤】 import xlrd import xlwt import openpyxl from openpyxl.styles import PatternFill def FileOpen(): #文件对话框,能打开写死的文件框 return_value = tkinter.filedialog.askopenfilename() if return_value.strip()!=" ": filename1.set(return_value) else: print("未上传所需文件") #直接打开文件选择框 print(type(return_value),return_value) def FileOpen2(): #文件对话框,能打开写死的文件框 return_value = tkinter.filedialog.askopenfilename() if return_value.strip()!=" ": filename2.set(return_value) else: print("未上传所需文件") #直接打开文件选择框 print(type(return_value),return_value) def FileSave(): #设置保存文件，并返回文件名，指定文件名后缀为“.xls r=tkinter.filedialog.asksaveasfilename(title="保存文件",defaultextension='.xls' # initialdir="E:\Tools\files", # initialfile='test.py' ) if r.strip()!=' ': filewname.set(r) else: print("未选择需要保存的文件") print(r) root = Tk() # 创建主窗口【必要步骤】 # 将该窗口赋值给root变量，方便后续使用 root.title('主窗口') root.geometry('960x480+150+100')#设置窗口大小及位置 root.wm_attributes('-alpha')#设置透明度为0.7 root.resizable(1,1)#窗口大小不可改变两个布尔值分别代表窗口的长和宽是否可改变（可以用0和1代替布尔值） filename1 = tkinter.StringVar() filename2=tkinter.StringVar() filewname=tkinter.StringVar() # toplevel=Toplevel(root) # toplevel.title('子窗口') #打开文件 #Entry：文本框输入框，Label：标签熟悉 def ui(): tkinter.Label(root, text='选择第一个文件').grid(row=1, column=1, padx=5, pady=7) #第一个输入框 filename1path=tkinter.Entry(root, textvariable=filename1, width=40 ) filename1path.grid(row=1, column=2, padx=5, pady=5) b1=Button(root,relief='groove',activebackground='pink',bg='lightblue',overrelief='ridge',text='上传文件',command=FileOpen).grid(row=1, column=3, padx=5, pady=5)#创建按钮 # b1.pack(side="left") tkinter.Label(root, text='选择第二个文件').grid(row=2, column=1, padx=5, pady=5) #第二个输入框 filename2path=tkinter.Entry(root, textvariable=filename2, width=40 ) filename2path.grid(row=2, column=2, padx=5, pady=5) b2=Button(root,relief='groove',activebackground='pink',bg='lightblue',overrelief='ridge',text='上传文件',command=FileOpen2).grid(row=2, column=3, padx=5, pady=5) # b2.pack(side="left") # b.place(width=100,height=30,x=150,y=200)#防止按钮 tkinter.Label(root, text='保存文件').grid(row=5, column=1, padx=5, pady=5) #第三个输入框 filewnamepath=tkinter.Entry(root, textvariable=filewname, width=40 ) filewnamepath.grid(row=5, column=2, padx=5, pady=5) b3=Button(root,text='保存文件',command=FileSave,relief='groove',activebackground='pink',bg='orange',overrelief='ridge',).grid(row=5, column=3, padx=5, pady=5) # b3.pack(side="left") tkinter.Button(root,text="开始对比",activebackground='pink',bg='lightblue',overrelief='ridge',command=lambda :contrast_button_clicked(filename1path.get(),filename2path.get(),filewnamepath.get())).grid(row=6, column=3, padx=5, pady=5) #窗口主循环 root.mainloop() # 主窗口进入消息事件循环【必要步骤】放在最后 def contrast_button_clicked(filename1path, filename2path, filewnamepath): match, diff,flag = main(filename1path, filename2path, filewnamepath) if flag: tkinter.messagebox.showinfo("提示", "对比成功！共有"+str(match)+"组数据重合，"+"共有" +str(diff)+"组数据不一致，详细情况请到"+ filewnamepath + "文件查看") else: tkinter.messagebox.showinfo("提示", "对比失败！请检查输入路径是否正确") def readexcel(file): # 打开Excel文件 workbook = openpyxl.load_workbook(file) # 选择指定的Sheet sheet = workbook['缺陷列表_1'] # 选择需要获取的列的列号 col_numbers = [3, 5, 6] # 列名 titles = ['二级分类', '等级', '爆发行'] result = [] # 从第2行开始获取数据 for i in range(2, sheet.max_row + 1): # 每一行的数据 row_data = [] for col in col_numbers: row_data.append(sheet.cell(row=i, column=col).value) # 构建字典 row_dict = dict(zip(titles, row_data)) result.append(row_dict) return result def comparefile(list1, list2): list1 = tuple(tuple(item.items()) for item in list1) list2 = tuple(tuple(item.items()) for item in list2) match = len(set(list1) & set(list2)) # 记录重合数 diff = len(set(list1) - set(list2)) + len(set(list2) - set(list1)) # 记录差异数 differ1 = set(list1) - set(list2) # 记录list1的差异元素 differ2 = set(list2) - set(list1) # 记录list2的差异元素 differ1_dicts = [dict(item) for item in list(differ1)] differ2_dicts = [dict(item) for item in list(differ2)] print("1111", differ1_dicts, differ2_dicts) print(f"重合数：{match}，差异数：{diff}") # differ1=list(differ1) # differ2=list(differ2) return differ1_dicts, differ2_dicts,match,diff def writeexcel2(differ1, differ2,filewname): global flag # 创建一个新的Excel工作簿 wb = openpyxl.Workbook() # 获取正在使用的工作表 sheet = wb.active # 写入differ1 # 获取所有的key,先判断是否为空 if differ1 and differ2: keys = differ1[0].keys() # 首先将key作为表头写入第一行 for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行数据 for row_num, row_data in enumerate(differ1, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # 差异表1和差异表2之间有两个空列，写入空列数据 sheet.cell(row=row_num, column=len(keys) + 3, value="") # 为当前行的所有单元格添加淡蓝色填充 for col_num in range(1, len(keys) + 1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid') # 写入differ2 # 得到所有的key # 首先把key作为表头写入第一行 for col_num, key in enumerate(keys,start=len(differ1[0].keys()) + 3): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行的数据 for row_num, row_data in enumerate(differ2, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num+ len(differ1[0].keys()) + 2) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # 为当前行的所有单元格添加淡黄色填充 for col_num in range(len(differ1[0].keys()) + 3, len(keys) + len(differ1[0].keys()) + 4): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0', fill_type='solid') #如果differ1不为空,differ2为空 elif differ1 and not differ2: keys = differ1[0].keys() # 首先将key作为表头写入第一行 for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行数据 for row_num, row_data in enumerate(differ1, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # 差异表1和差异表2之间有两个空列，写入空列数据 sheet.cell(row=row_num, column=len(keys) + 3, value="") # 为当前行的所有单元格添加淡蓝色填充 for col_num in range(1, len(keys) + 1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid') #如果differ1为空。differ2不为空 elif len(differ2)!=0 and len(differ1)==0: keys = differ2[0].keys() # 首先将key作为表头写入第一行 for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}1".format(col_letter)] = key # 依次写入每一行数据 for row_num, row_data in enumerate(differ2, start=2): for col_num, key in enumerate(keys, start=1): col_letter = openpyxl.utils.get_column_letter(col_num) if type(key) != str: # 添加判断语句，确保key是字符串类型 key = str(key) sheet["{0}{1}".format(col_letter, row_num)] = row_data[key] # # 差异表1和差异表2之间有两个空列，写入空列数据 sheet.cell(row=row_num, column=len(keys) + 3, value="") # 为当前行的所有单元格添加淡黄色填充 for col_num in range(1, len(keys) + 1): col_letter = openpyxl.utils.get_column_letter(col_num) sheet["{0}{1}".format(col_letter, row_num)].fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0', fill_type='solid') #两个列表都为空 else: print("表1和表2数据完全重合") # 保存Excel工作簿 wb.save(filewname) flag = True print("保存差异值到excel文件完成") return flag def main(filename1path,filename2path,filewnamepath): # file1 = filename1.get() # file2 = filename2.get() # resultfile = filewname.get() list1 = readexcel(filename1path) list2 = readexcel(filename2path) print(list1) print(list2) differ1, differ2,match,diff = comparefile(list1, list2) flag=writeexcel2(differ1, differ2,filewnamepath) # print("flag",flag) return match,diff,flag if __name__ == '__main__': ui()
运行的tkinter界面为
看右半边
最后对比的结果的界面为
