利用Python制作Excel对比工具 |
您所在的位置：网站首页 › excel比对工具 › 利用Python制作Excel对比工具 |
利用Python制作Excel对比工具
|
目前功能还不完善，只能对比第一个sheet的内容，代码如下： # -*- coding: utf-8 -*- # 博客园 C丶小明 import xlrd,time from docx import Document from tkinter import * from googletrans import Translator import tkinter.messagebox import tkinter.filedialog class Comp: def __init__(self,master): self.master = master def label(self,properties,padx = 3,pady = 2,ipadx = 5,ipady = 1): for name,x,y in properties: self.label = Label(self.master,text = name) self.label.grid(row = x,column = y,padx = padx,pady = pady,ipadx = ipadx,ipady = ipady) def button(self,properties,padx = 1,pady = 2,ipadx = 2,ipady = 1): for text,x,y,command in properties: self.button = Button(self.master,text = text,command = command) self.button.grid(row = x,column = y,padx = padx,pady = pady,ipadx = ipadx,ipady = ipady) class Tr: def __init__(self): self.main = Tk() self.mainwindow = Comp(self.main) self.main.geometry('454x400') self.main.title(u"对比Excel工具-小明") self.main.resizable(width=False,height=False) # 对比excel内容部分 def Compared(self,filename1,filename2,savefile): Logtime = time.strftime('%Y-%m-%d %H%M%S', time.localtime(time.time())) with open(r"%s/%s.txt"%(savefile,Logtime),"w") as f: print ("当前对比Excel：\n%s\n%s"%(filename1,filename2),file = f) print ("\n对比内容为：行高，列宽，单元格合并，背景格式，字体信息，边框格式，单元格格式，内容\n\n对比异常结果如下：",file = f) book1 = xlrd.open_workbook(filename1,formatting_info=True) sheet1 = book1.sheet_by_index(0) book2 = xlrd.open_workbook(filename2,formatting_info=True) sheet2 = book2.sheet_by_index(0) rows = sheet1.nrows cols = sheet1.ncols # 判断两个excel行数和列数是否相等 if sheet1.nrows == sheet2.nrows and sheet1.ncols == sheet2.ncols: # 对比合并单元格 hb1 = sheet1.merged_cells hb2 = sheet2.merged_cells hb3 = [] hb4 = [] if hb1 != hb2: for i in hb1: for j in hb2: if i == j: hb3.append(i) for i in (hb1 + hb2): if i not in hb3: hb4.append(i) with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("单元格(%s),单元格合并不同"%(hb4),file = f) # 对比行高 for i in sheet1.rowinfo_map: if sheet1.rowinfo_map[i].height != sheet2.rowinfo_map[i].height: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("行(%s),行高不同"%(i),file = f) # 对比列宽（这个库有bug，默认的列宽会获取不到） lk1 = [] lk2 = [] if list(sheet1.colinfo_map.keys()) == list(sheet2.colinfo_map.keys()): for i in sheet1.colinfo_map: if sheet1.colinfo_map[i].width != sheet2.colinfo_map[i].width: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("列(%s),列宽不同"%(i),file = f) else: for i in sheet1.colinfo_map: for j in sheet2.colinfo_map: if i == j: lk1.append(i) for j in (list(sheet1.colinfo_map.keys()) + list(sheet2.colinfo_map.keys())): if j not in lk1: lk2.append(j) for k in lk1: if sheet1.colinfo_map[k].width != sheet2.colinfo_map[k].width: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("列(%s),列宽不同"%(i),file = f) with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("列%s,列宽不同"%(lk2),file = f) # 循环对比最大行和列 for row in range(0,rows): for col in range(0,cols): xfx1 = sheet1.cell_xf_index(row, col) xfx2 = sheet2.cell_xf_index(row, col) xf1 = book1.xf_list[xfx1] xf2 = book2.xf_list[xfx2] # 背景格式 a1 = xf1.background a2 = xf2.background # 字体 b1 = book1.font_list[xf1.font_index] b2 = book2.font_list[xf2.font_index] # 边框格式 c1 = xf1.border c2 = xf2.border # 单元格格式 d1 = xf1.alignment d2 = xf2.alignment # 内容 e1 = sheet1.cell_value(row, col) e2 = sheet2.cell_value(row, col) # 对比 if a1.background_colour_index != a2.background_colour_index or a1.fill_pattern != a2.fill_pattern or a1.pattern_colour_index != a2.pattern_colour_index: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("单元格(%s,%s),背景格式不同"%(row,col),file = f) if b1.bold != b2.bold or b1.character_set != b2.character_set or b1.colour_index != b2.colour_index or b1.escapement != b2.escapement or b1.family != b2.family or b1.font_index != b2.font_index or b1.height != b2.height or b1.italic != b2.italic or b1.name != b2.name or b1.outline != b2.outline or b1.shadow != b2.shadow or b1.struck_out != b2.struck_out or b1.underline_type != b2.underline_type or b1.underlined != b2.underlined or b1.weight != b2.weight: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("单元格(%s,%s),字体信息不同"%(row,col),file = f) if c1.bottom_colour_index != c2.bottom_colour_index or c1.bottom_line_style != c2.bottom_line_style or c1.diag_colour_index != c2.diag_colour_index or c1.diag_down != c2.diag_down or c1.diag_line_style != c2.diag_line_style or c1.diag_up != c2.diag_up or c1.left_colour_index != c2.left_colour_index or c1.left_line_style != c2.left_line_style or c1.right_colour_index != c2.right_colour_index or c1.right_line_style != c2.right_line_style or c1.top_colour_index != c2.top_colour_index or c1.top_line_style != c2.top_line_style: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("单元格(%s,%s),边框格式不同"%(row,col),file = f) if d1.hor_align != d2.hor_align or d1.indent_level != d2.indent_level or d1.rotation != d2.rotation or d1.shrink_to_fit != d2.shrink_to_fit or d1.text_direction != d2.text_direction or d1.text_wrapped != d2.text_wrapped or d1.vert_align != d2.vert_align: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("单元格(%s,%s),单元格格式不同"%(row,col),file = f) if e1 != e2: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("单元格(%s,%s),内容不同"%(row,col),file = f) with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("\n***对比完成***",file = f) else: with open(r"%s/%s.txt"%(savefile,Logtime),"a+") as f: print ("两个excel获取到的最大行和列不相同,\n%s行数为：%s,列数为：%s\n%s行数为：%s,列数为：%s"%(filename1,sheet1.nrows,sheet1.ncols,filename2,sheet2.nrows,sheet2.ncols),file = f) # 界面部分 def interface(self): # 文本 self.mainwindow.label([ ("Excel路径一：",0,0), ("Excel路径二：",1,0), ("结果存放路径：",2,0) ],ipady = 10,ipadx = 8) path = StringVar() path1 = StringVar() savepath = StringVar() def selectPath(): # 文件选择框 path_ = tkinter.filedialog.askopenfilename(filetypes=[("",".xls")]) # path = StringVar() 配合使用，更新显示地址 path.set(path_) # 选择文档的路径 self.t = path_ def selectPath1(): # 文件选择框 path1_ = tkinter.filedialog.askopenfilename(filetypes=[("",".xls")]) # path = StringVar() 配合使用，更新显示地址 path1.set(path1_) # 选择文档的路径 self.t1 = path1_ def savePath(): # 文件选择框 savepath_ = tkinter.filedialog.askdirectory(initialdir='C:/') # path = StringVar() 配合使用，更新显示地址 savepath.set(savepath_) # 选择文档的路径 self.t2 = savepath_ def start(): try: self.Compared(self.t,self.t1,self.t2) tkinter.messagebox.showinfo('提示','对比成功！') except: tkinter.messagebox.showinfo('提示','对比出错，请先正确选择路径或文件内容！') # 显示选择文件地址 Label(self.main,width =35,height = 3,wraplength = 230,textvariable = path).grid(row = 0,column = 1,padx = 8) Label(self.main,width =35,height = 3,wraplength = 230,textvariable = path1).grid(row = 1,column = 1,padx = 8) Label(self.main,width =35,height = 3,wraplength = 230,textvariable = savepath).grid(row = 2,column = 1,padx = 8) # 按钮 self.mainwindow.button([ ("选择",0,2,selectPath), ("选择",1,2,selectPath1), ("选择",2,2,savePath), ("开始对比",3,1,start) ],ipadx = 8,ipady = 1) self.main.mainloop() if __name__ == "__main__": t = Tr() t.interface()
准备两个需要对比的Excel：
运行后，选择需要对比的Excel文件和结果存放路径，点击开始对比：
结果：
|
【本文地址】
今日新闻 |
推荐新闻 |