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%sn%s"%(filename1,filename2),file = f)

print ("n对比内容为：行高，列宽，单元格合并，背景格式，字体信息，边框格式，单元格格式，内容nn对比异常结果如下：",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,列数为：%sn%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文件和结果存放路径，点击开始对比：

结果：

内容来源于网络如有侵权请私信删除