因为有统计成员到会情况的任务，每次汇总时都很麻烦，需要一个个对应腾讯会议导出名单的成员，然后在总表上进行标记，所以就写了本程序来减少统计的复杂度。
使用xlrd和xlwt包
首先安装两个包
pip install xlrd == 1.2.0
pip 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 xlrd
import xlwt
"""
pip3 install xlrd == 1.2.0
pip3 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 tk
import tkinter.filedialog
from 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 xlrd
import xlwt
import tkinter as tk
import tkinter.filedialog
from tkinter import *
from tkinter import messagebox
"""
pip3 install xlrd == 1.2.0
pip3 install xlwt == 0.7.5
pip3 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()