先写文件对比的逻辑代码，包括读取文件，对比文件，将对比出来的差异写入另一个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界面为
看右半边
最后对比的结果的界面为
标签：文件,letter,column,excel,列表,num,key,col,row From： https://www.cnblogs.com/zhongxiaoyan/p/17679436.html