【开发杂记】基于Python和TK的exe应用程序开发
0 前言
一开始也没想到要做这个，人力的领导嫌用Excel太烦了想把一些机械重复劳动以程序替换，所以产生了这个需求。由于是面向个人需求开发的产品，所以做成单机的，一套做下来感觉事倍功半，以后还是B/S架构做成网页好点，这里避免工作浪费在这里总结一下全过程。
目录
1 数据
数据是员工技能考试数据，进行了简单的脱敏处理，大致如下：
2 需求
其实需求没有太复杂，实现简单只是效率和前端上经不起考验。
- 实现表格的查询功能包括单条件查询和多条件查询。
- 实现简单的业务计算并产生报表。
- 所有功能要交互完成。
3 GUI工具TK（tkinter）
用python做GUI的情况应该比较少，我也就随大流选了TK工具包，其难点还是在于网格式布局的理解以及功能套件封装没有那么易用，比如滚轮滑动都费了较大力气才弄好,对这方面感兴趣的可以看Python的GUI图形界面工具大全。
4 功能实现
4.1 整体框架
TK的布局分为层级式和网格式，当然网格式比较灵活，但比较难分清楚每个部件构成。本文采取的是网格式，就像画画一样，所有功能按钮都是一层层铺上去的，这里先建个底层，也就是主界面。
4.11主界面
主界面采用全屏展示，再把横竖的滚动条也加上，注意此处的滚动条仅有其形，功能还没加上。
root = tk.Tk()
root.title("乐川考试系统") # #窗口标题
w = root.winfo_screenwidth()
h = root.winfo_screenheight()
root.geometry("%dx%d" %(w,h))
canvas= tk.Canvas(root, width=w, height=h)
xscrollbar = tk.Scrollbar(root,orient=tk.HORIZONTAL)
yscrollbar = tk.Scrollbar(root)
yscrollbar.config(command=canvas.yview)
yscrollbar.pack(side=tk.RIGHT, fill=tk.Y)
xscrollbar.config(command=canvas.xview)
xscrollbar.pack(side=tk.BOTTOM, fill=tk.X)
canvas.config(xscrollcommand=xscrollbar.set,yscrollcommand=yscrollbar.set)
canvas.pack(side="left", fill="both", expand=True) #side=LEFT,expand=True,fill=BOTH
main_frm = tk.Frame(canvas)
canvas.create_window(0,0,window=main_frm, anchor='nw')
4.12 主界面配件
初始界面很简单，就文件上传(数据加载)和功能按钮栏。
# In[]
path = tk.StringVar() # #创建变量，便于取值
path.set('')
upload_frm = tk.Frame(master=main_frm) #上传组件
upload_frm.grid(row=0,column=0)
entry1 = tk.Entry(upload_frm,textvariable=path,width='40')
entry1.grid(row=0, column=0)
btn = tk.Button(upload_frm, text='上传文件', command=lambda:upload_file(upload_frm,path))
btn.grid(row=0, column=1,padx=20)
func_frm = tk.Frame(main_frm) #功能索引
# func_frm.pack()
func_frm.grid(row=1, column=0,padx=20, pady=20)
select_button =tk.Button(func_frm,bg='skyblue',text='进入查询界面',width=15,height=1,
command=lambda :select(main_frm, path))
select_button.grid(row=2, column=0)
# select_button.pack()
func_button_1 =tk.Button(func_frm,bg='skyblue',text='功能按钮2',width=15,height=1,
command=lambda :func_cal_pass(main_frm, path))
func_button_1.grid(row=2, column=1)
func_button_2 =tk.Button(func_frm,bg='skyblue',text='功能按钮3',width=15,height=1,
command=lambda :func3())
func_button_2.grid(row=2, column=2)
cur_row = 2
4.2 上传功能实现
def upload_file(root,path):
selectFile = filedialog.askopenfilename() # askopenfilename 1次上传1个；askopenfilenames1次上传多个
entry1.insert(0, selectFile)
path.set(selectFile)
4.3 查询功能实现
查询主要由查询条件、查询算法、结果展示三部分构成。
4.31 查询条件交互功能实现
这里展示出数据所有的列，再根据每一列的需求进行筛选。这里只做了“==”的条件查询。
def select(root,path):
data_path = path.get()
data = pd.read_excel(data_path)
data = data.round(2)
data = data.applymap(str)
colnms = list(data.columns)
values = [sorted(data[nm].drop_duplicates()) for nm in colnms]
for i in range (len(values)):
values[i].insert(0, '--请选择')
values[i] = tuple(values[i])
# print(values)
start_row = 3
start_col = 0
QCONDI_ROW_CNTS = 5 #查询条件多少行一列
textvs = ['']*len(colnms)
global cur_row
condi_frm = tk.Frame(master=root)
# condi_frm.pack()
condi_frm.grid(row=cur_row+1)
for i in range(len(colnms)):
textvs[i] = tk.StringVar()
frm = tk.Frame(master=condi_frm)
frm.grid(row = start_row + i%QCONDI_ROW_CNTS,column = start_col+i//QCONDI_ROW_CNTS,sticky = E)
com = ttk.Combobox(frm, textvariable=textvs[i]) # #创建下拉菜单
tk.Label(frm, text = colnms[i]).grid(row= start_row + i,column=0,sticky = W,padx=5,pady=10)
com.grid(row=start_row + i, column=1,sticky = E,padx=20) # #将下拉菜单绑定到窗体
com["value"] = values[i]
com.current(0)
cur_row = start_row + i
query_button =tk.Button(root,bg='skyblue',text='查询',width=12,height=1,command=lambda:query(root,data,textvs))
query_button.grid(row=cur_row+1, columnspan=3)
root.update()
4.32 查询算法实现
其实就是数据库的单表查询，直接用最笨的方法，筛完一个再继续，直到为空或者条件筛完。
def query(root,data,textvs):
global cur_row
ipts = [v.get() for v in textvs]
DEFALUT = '--请选择'
colnms = list(data.columns)
sub_data = data.copy()
for i in range(len(ipts)):
if ipts[i] == DEFALUT:
pass
else:
sub_data = sub_data[sub_data[colnms[i]]==ipts[i]]
show_data(root,sub_data,pd.DataFrame([]))
4.33 查询结果展示功能实现
最多展示只展示10条。
def show_data(root,sub_data,detail_data):
global cur_row
cur_row = 10
table_frm = tk.Frame(root)
table_frm.grid(row=cur_row+1)
cur_row = cur_row+1
tree = ttk.Treeview(table_frm) # #创建表格对象
tree.delete(*tree.get_children())
tree["columns"] = list(sub_data.columns) # #定义列
for coln in tree["columns"]:
tree.column(coln, width=100, anchor = "center") # #设置列
tree.heading(coln, text=coln) # #设置显示的表头名
for i in range(min(10,len(sub_data))): #最多展示
# print(sub_data.iloc[i])
tree.insert("", i, text="第"+str(i+1)+"行", values=tuple(sub_data.iloc[i].values)) # #给第0行添加数据，索引值可重复
tree.grid(padx=10, pady=20,row = cur_row+1,columnspan=3)
cur_row = cur_row + 10
dlpath = tk.StringVar()
dlpath.set('')
dl_frm = tk.Frame(root)
dl_frm.grid(row=cur_row+1, columnspan=3)
cur_row = cur_row + 1
entry1 = tk.Entry(dl_frm,textvariable=dlpath,width='40')
entry1.grid(row=cur_row+1, column=0,padx=20)
cur_row = cur_row + 1
btn = tk.Button(dl_frm, text='保存结果', command=lambda:download_file(sub_data,detail_data,root,dlpath))
btn.grid(row=cur_row, column=1)
cur_row = cur_row + 1
# cur_row = 0
4.4 业务计算功能实现
这里的业务也就是常规的统计计算。
def func_cal_pass(root, path):
data_path = path.get()
data = pd.read_excel(data_path)
colnms = list(data.columns)
# print(colnms)
dep = 0
etype = 0
for i in range(len(colnms)):
if '所在机构和部门' in colnms[i]:
dep = i
if '报考科目' in colnms[i]:
etype = i
dep_data = sorted(data[colnms[dep]].drop_duplicates())
# print(dep_data)
func_data = pd.DataFrame(columns = ["所在机构和部门", "报考人数", "合格人数", "不合格人数","缺考人数","合格率","不合格率","缺考率"])
for i in range(len(dep_data)):
sub_data = data[data[colnms[dep]]==dep_data[i]]
pass_num = len(sub_data[sub_data['合格情况']=='合格']) #通过
abs_num = len(sub_data[sub_data['成绩']==0]) #缺考
un_pass_num = len(sub_data)-pass_num-abs_num #未通过
row = [dep_data[i],len(sub_data),pass_num,un_pass_num,abs_num,round(1.0*pass_num/len(sub_data),2),
round(1.0*un_pass_num/len(sub_data),2),round(1.0*abs_num/len(sub_data),2)]
# print(row)
func_data.loc[i]=row
detail_list = []
etype_data = sorted(data[colnms[etype]].drop_duplicates())
for i in range(len(dep_data)):
sub_data_1 = data[data[colnms[dep]]==dep_data[i]] #先找部门
for j in range(len(etype_data)):
sub_data_2 = sub_data_1[sub_data_1[colnms[etype]]==etype_data[j]] #再找科目
# print(len(sub_data_2))
if not sub_data_2.empty:
pass_num = len(sub_data_2[sub_data_2['合格情况']=='合格']) #通过
abs_num = len(sub_data_2[sub_data_2['成绩']==0]) #缺考
un_pass_num = len(sub_data_2)-pass_num-abs_num #未通过
row = [str(dep_data[i]),etype_data[j],len(sub_data_2),pass_num,un_pass_num,abs_num,round(1.0*pass_num/len(sub_data_2),2),
round(1.0*un_pass_num/len(sub_data_2),2),round(1.0*abs_num/len(sub_data_2),2)]
detail_list.append(row)
print(row)
detail_data = pd.DataFrame(detail_list,columns = ["所在机构和部门", "报考科目","报考人数", "合格人数", "不合格人数","缺考人数","合格率","不合格率","缺考率"])
# print(detail_data)
# print(func_data)
show_data(root,func_data,detail_data)
4.5 结果保存功能实现
def download_file(data,detail_data,root,path):
selectFile = filedialog.asksaveasfilename() # askopenfilename 1次上传1个；askopenfilenames1次上传多个
entry1.insert(0, selectFile)
path.set(selectFile)
writer = pd.ExcelWriter(selectFile+'.xlsx')
if selectFile:
data.to_excel(writer,index=False,sheet_name='汇总')
if not detail_data.empty:
detail_data.to_excel(writer,index=False,sheet_name='明细')
messagebox.showinfo(title = '提示',message='保存成功！')
writer.save()
4.6 全局滚动条实现
root.update()
canvas.config(scrollregion=[0,0,w,h])
def processWheel(event):
a= int(-(event.delta)/60)
canvas.yview_scroll(a,'units')
# canvas.xview_scroll(a,'units')
main_frm.bind_all("<MouseWheel>", processWheel)
root.mainloop() # #窗口持久化
5 打包成.exe文件
主要用pyinstaller打包，详见Python PyInstaller安装和使用教程（详解版）。
6 完整代码
# -*- coding: utf-8 -*-
"""
Created on Tue Sep 14 22:37:56 2021
@author: Administrator
"""
import pandas as pd
import tkinter as tk
from tkinter import ttk
from tkinter import filedialog
from tkinter import messagebox
from tkinter import W,N,E,S,HORIZONTAL,BOTTOM,X,VERTICAL,Y,RIGHT,LEFT,BOTH
# import Pmw
def upload_file(root,path):
selectFile = filedialog.askopenfilename() # askopenfilename 1次上传1个；askopenfilenames1次上传多个
entry1.insert(0, selectFile)
path.set(selectFile)
def func_cal_pass(root, path):
data_path = path.get()
data = pd.read_excel(data_path)
colnms = list(data.columns)
# print(colnms)
dep = 0
etype = 0
for i in range(len(colnms)):
if '所在机构和部门' in colnms[i]:
dep = i
if '报考科目' in colnms[i]:
etype = i
dep_data = sorted(data[colnms[dep]].drop_duplicates())
# print(dep_data)
func_data = pd.DataFrame(columns = ["所在机构和部门", "报考人数", "合格人数", "不合格人数","缺考人数","合格率","不合格率","缺考率"])
for i in range(len(dep_data)):
sub_data = data[data[colnms[dep]]==dep_data[i]]
pass_num = len(sub_data[sub_data['合格情况']=='合格']) #通过
abs_num = len(sub_data[sub_data['成绩']==0]) #缺考
un_pass_num = len(sub_data)-pass_num-abs_num #未通过
row = [dep_data[i],len(sub_data),pass_num,un_pass_num,abs_num,round(1.0*pass_num/len(sub_data),2),
round(1.0*un_pass_num/len(sub_data),2),round(1.0*abs_num/len(sub_data),2)]
# print(row)
func_data.loc[i]=row
detail_list = []
etype_data = sorted(data[colnms[etype]].drop_duplicates())
for i in range(len(dep_data)):
sub_data_1 = data[data[colnms[dep]]==dep_data[i]] #先找部门
for j in range(len(etype_data)):
sub_data_2 = sub_data_1[sub_data_1[colnms[etype]]==etype_data[j]] #再找科目
# print(len(sub_data_2))
if not sub_data_2.empty:
pass_num = len(sub_data_2[sub_data_2['合格情况']=='合格']) #通过
abs_num = len(sub_data_2[sub_data_2['成绩']==0]) #缺考
un_pass_num = len(sub_data_2)-pass_num-abs_num #未通过
row = [str(dep_data[i]),etype_data[j],len(sub_data_2),pass_num,un_pass_num,abs_num,round(1.0*pass_num/len(sub_data_2),2),
round(1.0*un_pass_num/len(sub_data_2),2),round(1.0*abs_num/len(sub_data_2),2)]
detail_list.append(row)
print(row)
detail_data = pd.DataFrame(detail_list,columns = ["所在机构和部门", "报考科目","报考人数", "合格人数", "不合格人数","缺考人数","合格率","不合格率","缺考率"])
# print(detail_data)
# print(func_data)
show_data(root,func_data,detail_data)
def func3():
return
def select(root,path):
data_path = path.get()
data = pd.read_excel(data_path)
data = data.round(2)
data = data.applymap(str)
colnms = list(data.columns)
values = [sorted(data[nm].drop_duplicates()) for nm in colnms]
for i in range (len(values)):
values[i].insert(0, '--请选择')
values[i] = tuple(values[i])
# print(values)
start_row = 3
start_col = 0
QCONDI_ROW_CNTS = 5 #查询条件多少行一列
textvs = ['']*len(colnms)
global cur_row
condi_frm = tk.Frame(master=root)
# condi_frm.pack()
condi_frm.grid(row=cur_row+1)
for i in range(len(colnms)):
textvs[i] = tk.StringVar()
frm = tk.Frame(master=condi_frm)
frm.grid(row = start_row + i%QCONDI_ROW_CNTS,column = start_col+i//QCONDI_ROW_CNTS,sticky = E)
com = ttk.Combobox(frm, textvariable=textvs[i]) # #创建下拉菜单
tk.Label(frm, text = colnms[i]).grid(row= start_row + i,column=0,sticky = W,padx=5,pady=10)
com.grid(row=start_row + i, column=1,sticky = E,padx=20) # #将下拉菜单绑定到窗体
com["value"] = values[i]
com.current(0)
cur_row = start_row + i
query_button =tk.Button(root,bg='skyblue',text='查询',width=12,height=1,command=lambda:query(root,data,textvs))
query_button.grid(row=cur_row+1, columnspan=3)
root.update()
def query(root,data,textvs):
global cur_row
ipts = [v.get() for v in textvs]
DEFALUT = '--请选择'
colnms = list(data.columns)
sub_data = data.copy()
for i in range(len(ipts)):
if ipts[i] == DEFALUT:
pass
else:
sub_data = sub_data[sub_data[colnms[i]]==ipts[i]]
show_data(root,sub_data,pd.DataFrame([]))
def download_file(data,detail_data,root,path):
selectFile = filedialog.asksaveasfilename() # askopenfilename 1次上传1个；askopenfilenames1次上传多个
entry1.insert(0, selectFile)
path.set(selectFile)
writer = pd.ExcelWriter(selectFile+'.xlsx')
if selectFile:
data.to_excel(writer,index=False,sheet_name='汇总')
if not detail_data.empty:
detail_data.to_excel(writer,index=False,sheet_name='明细')
messagebox.showinfo(title = '提示',message='保存成功！')
writer.save()
def show_data(root,sub_data,detail_data):
global cur_row
cur_row = 10
table_frm = tk.Frame(root)
table_frm.grid(row=cur_row+1)
cur_row = cur_row+1
tree = ttk.Treeview(table_frm) # #创建表格对象
tree.delete(*tree.get_children())
tree["columns"] = list(sub_data.columns) # #定义列
for coln in tree["columns"]:
tree.column(coln, width=100, anchor = "center") # #设置列
tree.heading(coln, text=coln) # #设置显示的表头名
for i in range(min(10,len(sub_data))): #最多展示
# print(sub_data.iloc[i])
tree.insert("", i, text="第"+str(i+1)+"行", values=tuple(sub_data.iloc[i].values)) # #给第0行添加数据，索引值可重复
tree.grid(padx=10, pady=20,row = cur_row+1,columnspan=3)
cur_row = cur_row + 10
dlpath = tk.StringVar()
dlpath.set('')
dl_frm = tk.Frame(root)
dl_frm.grid(row=cur_row+1, columnspan=3)
cur_row = cur_row + 1
entry1 = tk.Entry(dl_frm,textvariable=dlpath,width='40')
entry1.grid(row=cur_row+1, column=0,padx=20)
cur_row = cur_row + 1
btn = tk.Button(dl_frm, text='保存结果', command=lambda:download_file(sub_data,detail_data,root,dlpath))
btn.grid(row=cur_row, column=1)
cur_row = cur_row + 1
# cur_row = 0
root = tk.Tk()
root.title("乐川考试系统") # #窗口标题
w = root.winfo_screenwidth()
h = root.winfo_screenheight()
root.geometry("%dx%d" %(w,h))
canvas= tk.Canvas(root, width=w, height=h)
xscrollbar = tk.Scrollbar(root,orient=tk.HORIZONTAL)
yscrollbar = tk.Scrollbar(root)
yscrollbar.config(command=canvas.yview)
yscrollbar.pack(side=tk.RIGHT, fill=tk.Y)
xscrollbar.config(command=canvas.xview)
xscrollbar.pack(side=tk.BOTTOM, fill=tk.X)
canvas.config(xscrollcommand=xscrollbar.set,yscrollcommand=yscrollbar.set)
canvas.pack(side="left", fill="both", expand=True) #side=LEFT,expand=True,fill=BOTH
main_frm = tk.Frame(canvas)
canvas.create_window(0,0,window=main_frm, anchor='nw')
# In[]
path = tk.StringVar() # #创建变量，便于取值
path.set('')
upload_frm = tk.Frame(master=main_frm) #上传组件
upload_frm.grid(row=0,column=0)
entry1 = tk.Entry(upload_frm,textvariable=path,width='40')
entry1.grid(row=0, column=0)
btn = tk.Button(upload_frm, text='上传文件', command=lambda:upload_file(upload_frm,path))
btn.grid(row=0, column=1,padx=20)
func_frm = tk.Frame(main_frm) #功能索引
# func_frm.pack()
func_frm.grid(row=1, column=0,padx=20, pady=20)
select_button =tk.Button(func_frm,bg='skyblue',text='进入查询界面',width=15,height=1,
command=lambda :select(main_frm, path))
select_button.grid(row=2, column=0)
# select_button.pack()
func_button_1 =tk.Button(func_frm,bg='skyblue',text='功能按钮2',width=15,height=1,
command=lambda :func_cal_pass(main_frm, path))
func_button_1.grid(row=2, column=1)
func_button_2 =tk.Button(func_frm,bg='skyblue',text='功能按钮3',width=15,height=1,
command=lambda :func3())
func_button_2.grid(row=2, column=2)
cur_row = 2
# In[]
root.update()
canvas.config(scrollregion=[0,0,w,h])
def processWheel(event):
a= int(-(event.delta)/60)
canvas.yview_scroll(a,'units')
# canvas.xview_scroll(a,'units')
main_frm.bind_all("<MouseWheel>", processWheel)
root.mainloop() # #窗口持久化