目前功能还不完善，只能对比第一个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文件和结果存放路径，点击开始对比：
结果：
