使用Python解决对比出两个Excel文件中的不同项并将结果重新写入一个新的Excel文件

使用Python解决对比出两个Excel文件中的不同项并将结果重新写入一个新的Excel文件

因为有统计成员到会情况的任务，每次汇总时都很麻烦，需要一个个对应腾讯会议导出名单的成员，然后在总表上进行标记，所以就写了本程序来减少统计的复杂度。

使用​​xlrd​​​和​​xlwt​​包

首先安装两个包

pip install xlrd == 1.2.0pip install xlwt == 0.7.5

定义​​contrast​​函数

def contrast(processed_export_excel_file,all_number_file,different_name_file): """ @param processed_export_excel_file:处理后的导出名单 @param all_number_file:总人数的名单 @param different_name_file:导出文件的地址 """ # 打开Excel文件 # 打开处理后的导出名单 data1 = xlrd.open_workbook(processed_export_excel_file) # 打开总人数的名单 data2 = xlrd.open_workbook(all_number_file) # 获取第一个sheet sheet1 = data1.sheet_by_index(0) sheet2 = data2.sheet_by_index(0) # 获取两个Excel文件的行数和列数 grows1 = sheet1.nrows grows2 = sheet2.nrows # 创建一个新的Excel文件 new_excel = xlwt.Workbook() new_sheet = new_excel.add_sheet('未参会人员') # 相同项 same_content = [] # sheet2中的所有人员 excel_2_content = [] # 未参会人员 diff_content = [] for i in range(grows2): excel_2_content.append(sheet2.cell_value(i, 0)) for i in range(grows1): for j in range(grows2): sheet1_value = sheet1.cell_value(i, 0) sheet2_value = sheet2.cell_value(j, 0) # sheet1的字符串包含sheet2的字符串 if str(sheet2_value) in str(sheet1_value): same_content.append(sheet2_value) # 找出excel_2_content中不在same_content中的内容 for i in excel_2_content: if i not in same_content: diff_content.append(i) print("原有内容：", excel_2_content) print("相同项：" + str(same_content)) print("不同项：" + str(diff_content)) print("总共有" + str(len(diff_content)) + "个不同项") # 将不同项写入新的Excel文件 for i in range(len(diff_content)): new_sheet.write(i, 0, diff_content[i]) new_excel.save(different_name_file)

测试​​contrast​​函数

if __name__ == '__main__': file1 = r"C:/Users/MSI/Desktop/test1.xlsx" file2 = r"C:/Users/MSI/Desktop/test2.xlsx" outfile = r"C:/Users/MSI/Desktop/diff.xlsx" contrast(file1, file2, outfile)

完整代码：

import xlrdimport xlwt"""pip3 install xlrd == 1.2.0pip3 install xlwt == 0.7.5"""def contrast(processed_export_excel_file, all_number_file, different_name_file): """ @param processed_export_excel_file: 导出名单处理后 @param all_number_file: 总人数的名单 @param different_name_file: 导出文件名 """ # 打开Excel文件 # 打开处理后的导出名单 data1 = xlrd.open_workbook(processed_export_excel_file) # 打开总人数的名单 data2 = xlrd.open_workbook(all_number_file) # 获取第一个sheet sheet1 = data1.sheet_by_index(0) sheet2 = data2.sheet_by_index(0) # 获取两个Excel文件的行数和列数 grows1 = sheet1.nrows grows2 = sheet2.nrows # 创建一个新的Excel文件 new_excel = xlwt.Workbook() new_sheet = new_excel.add_sheet('未参会人员') # 相同项 same_content = [] # sheet2中的所有人员 excel_2_content = [] # 未参会人员 diff_content = [] for i in range(grows2): excel_2_content.append(sheet2.cell_value(i, 0)) for i in range(grows1): for j in range(grows2): sheet1_value = sheet1.cell_value(i, 0) sheet2_value = sheet2.cell_value(j, 0) # sheet1的字符串包含sheet2的字符串 if str(sheet2_value) in str(sheet1_value): same_content.append(sheet2_value) # 找出excel_2_content中不在same_content中的内容 for i in excel_2_content: if i not in same_content: diff_content.append(i) print("原有内容：", excel_2_content) print("相同项：" + str(same_content)) print("不同项：" + str(diff_content)) print("总共有" + str(len(diff_content)) + "个不同项") # 将不同项写入新的Excel文件 for i in range(len(diff_content)): new_sheet.write(i, 0, diff_content[i]) new_excel.save(different_name_file)if __name__ == '__main__': file1 = r"C:/Users/MSI/Desktop/test1.xlsx" file2 = r"C:/Users/MSI/Desktop/test2.xlsx" outfile = r"C:/Users/MSI/Desktop/diff.xlsx" contrast(file1, file2, outfile)

到这里，核心功能已经做出来了。但还不是很方便，每次都需要打开程序，重新输入Excel所在的路径。那就再加上一点细节，做个界面，把程序打包成exe文件吧。 下面是详细步骤：

安装tkinter包

​​pip install tkinter == 8.6.7​​

导入​​tkinter​​包

import tkinter as tkimport tkinter.filedialogfrom tkinter import *from tkinter import messagebox

写个函数用来选择路径

def select_export_path(): temp = tk.filedialog.askopenfilename() export_path.set(temp)def select_all_number_path(): temp = tk.filedialog.askopenfilename() all_number_Path.set(temp)def select_diff_path(): temp = tk.filedialog.askopenfilename() diff_path.set(temp)

初始化变量

root = tk.Tk()export_path = StringVar()all_number_Path = StringVar()diff_path = StringVar()

画出UI界面

def ui(): """ 选择界面设计以及路径功能 """ root.title("对比工具") root.geometry("400x200") # 标签 tk.Label(root, text="导出参会成员名单：").grid(row=0, column=0) tk.Label(root, text="全部成员名单：").grid(row=1, column=0) tk.Label(root, text="未参会成员名单：").grid(row=2, column=0) # 输入框 processed_export_excel_file = tk.Entry(root, textvariable=export_path) processed_export_excel_file.grid(row=0, column=1) all_number_file = tk.Entry(root, textvariable=all_number_Path) all_number_file.grid(row=1, column=1) different_name_file = tk.Entry(root, textvariable=diff_path) different_name_file.grid(row=2, column=1) # 按钮 tk.Button(root, text="选择文件", command=select_export_path).grid(row=0, column=2) tk.Button(root, text="选择文件", command=select_all_number_path).grid(row=1, column=2) tk.Button(root, text="选择文件", command=select_diff_path).grid(row=2, column=2) tk.Button(root, text="开始对比", command=lambda: contrast_button_clicked(processed_export_excel_file.get(), all_number_file.get(), different_name_file.get())).grid(row=3, column=1) root.mainloop()

def contrast_button_clicked(processed_export_excel_file, all_number_file, different_name_file): contrast_result, number = contrast(processed_export_excel_file, all_number_file, different_name_file) if contrast_result: tk.messagebox.showinfo("提示", "对比成功！共有"+str(number)+"人缺会，详细情况请到" + different_name_file + "文件查看") else: tk.messagebox.showinfo("提示", "对比失败！请检查输入路径是否正确")

看一下效果吧接下来就是把这个py程序打包，使用​​pyinstaller​​这个包

​​pip install pyinstaller​​。

安装成功之后，按键盘​​win+R​​​打开运行，输入​​cmd​​，回车运行。

进入程序所在文件夹：

然后输入​​pyinstaller -F -w contrast.py --hidden-import=pandas._libs.tslibs.timedeltas ​​

成功之后便可在程序根目录​​dist​​文件夹里边便可看到封装好的exe文件。

完整代码如下：

import xlrdimport xlwtimport tkinter as tkimport tkinter.filedialogfrom tkinter import *from tkinter import messagebox"""pip3 install xlrd == 1.2.0pip3 install xlwt == 0.7.5pip3 install tkinter == 8.6.7"""def select_export_path(): temp = tk.filedialog.askopenfilename() export_path.set(temp)def select_all_number_path(): temp = tk.filedialog.askopenfilename() all_number_Path.set(temp)def select_diff_path(): temp = tk.filedialog.askopenfilename() diff_path.set(temp)root = tk.Tk()export_path = StringVar()all_number_Path = StringVar()diff_path = StringVar()def ui(): """ 选择界面设计以及路径功能 """ root.title("对比工具") root.geometry("400x200") # 标签 tk.Label(root, text="导出参会成员名单：").grid(row=0, column=0) tk.Label(root, text="全部成员名单：").grid(row=1, column=0) tk.Label(root, text="未参会成员名单：").grid(row=2, column=0) # 输入框 processed_export_excel_file = tk.Entry(root, textvariable=export_path) processed_export_excel_file.grid(row=0, column=1) all_number_file = tk.Entry(root, textvariable=all_number_Path) all_number_file.grid(row=1, column=1) different_name_file = tk.Entry(root, textvariable=diff_path) different_name_file.grid(row=2, column=1) # 按钮 tk.Button(root, text="选择文件", command=select_export_path).grid(row=0, column=2) tk.Button(root, text="选择文件", command=select_all_number_path).grid(row=1, column=2) tk.Button(root, text="选择文件", command=select_diff_path).grid(row=2, column=2) tk.Button(root, text="开始对比", command=lambda: contrast_button_clicked(processed_export_excel_file.get(), all_number_file.get(), different_name_file.get())).grid(row=3, column=1) root.mainloop()def contrast_button_clicked(processed_export_excel_file, all_number_file, different_name_file): contrast_result, number = contrast(processed_export_excel_file, all_number_file, different_name_file) if contrast_result: tk.messagebox.showinfo("提示", "对比成功！共有"+str(number)+"人缺会，详细情况请到" + different_name_file + "文件查看") else: tk.messagebox.showinfo("提示", "对比失败！请检查输入路径是否正确")def contrast(processed_export_excel_file, all_number_file, different_name_file): """ @param processed_export_excel_file: 导出名单处理后 @param all_number_file: 总人数的名单 @param different_name_file: 导出文件名 """ # 打开Excel文件 # 打开处理后的导出名单 global result data1 = xlrd.open_workbook(processed_export_excel_file) # 打开总人数的名单 data2 = xlrd.open_workbook(all_number_file) # 获取第一个sheet sheet1 = data1.sheet_by_index(0) sheet2 = data2.sheet_by_index(0) # 获取两个Excel文件的行数和列数 grows1 = sheet1.nrows grows2 = sheet2.nrows # 创建一个新的Excel文件 new_excel = xlwt.Workbook() new_sheet = new_excel.add_sheet('未参会人员') # 相同项 same_content = [] # sheet2中的所有人员 excel_2_content = [] # 未参会人员 diff_content = [] for i in range(grows2): excel_2_content.append(sheet2.cell_value(i, 0)) for i in range(grows1): for j in range(grows2): sheet1_value = sheet1.cell_value(i, 0) sheet2_value = sheet2.cell_value(j, 0) # sheet1的字符串包含sheet2的字符串 if str(sheet2_value) in str(sheet1_value): same_content.append(sheet2_value) # 找出excel_2_content中不在same_content中的内容 for i in excel_2_content: if i not in same_content: diff_content.append(i) print("原有内容：", excel_2_content) print("相同项：" + str(same_content)) print("不同项：" + str(diff_content)) print("总共有" + str(len(diff_content)) + "个不同项") # 将不同项写入新的Excel文件 for i in range(len(diff_content)): new_sheet.write(i, 0, diff_content[i]) result = True new_excel.save(different_name_file) return result, len(diff_content)if __name__ == '__main__': ui()

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。