用于计算班组人均产量的程序
程序代码:
import xlwings as xw import PySimpleGUI as sg import os import time banben="人均产量计算器2.5.0" #打开表格获取数据,将数据返回到列表中 def open_excle(file_derss): list1=[] app=xw.App(visible=False,add_book=False) workbook=app.books.open(file_derss) sheet=workbook.sheets[0] list1=sheet.range("a1").expand().value return list1 #清洗数据将无用数据删除,并返回新的数据 def delete_notshuju(sj): yaobaoliu_shuju=["开卡车间","生产车间","缸号","匹数","重量","工序"] j=0 list0=[] list1=[] #获得要保留数据的列号 for y in yaobaoliu_shuju: j=0 for x in sj[0]: if x==y: list0.append(j) break else: j+=1 #循环原始数据 for i in sj[1:]: list2=[] #将对应列号的数据保存到list2列表中 for x in list0: list2=list2+[i[x]] list1.append(list2) del(list2) return list1 #将6车间和7车间数据删除 def sanchu6chejianshuju(sj): list2=[] for i in sj: if "6" not in i[0] and "7" not in i[0]: list2.append(i) return list2 #计算工序产量 def jishuangongxuchanliang(sj,gx): zhongliang=0 pishu=0 for i in sj: if i[5]==gx: zhongliang+=float(i[4]) return zhongliang #计算合并后的工段产量 def gongxuhebing(sj,sg): zongzhongliang=0 for i in sj: zongzhongliang+=jishuangongxuchanliang(sg,i) return zongzhongliang #计算总重量 def jishuanzongchanliang(sg): libu=["理布","机器理拷","退卷","翻布","双匹理布"] yaoli=["毛坯连续式摇粒","吹布","平幅汽蒸"] kaifu=["毛坯剖幅","毛坯机器理布"] kaoke=["拷克"] pinfeng=["机器拼缝","机器缝点"] shaomao=["开幅烧毛","开幅双面烧毛"] #理布产量 libuzhongliang=round(gongxuhebing(libu,sg)/1000,1) #拷克产量 kaokezhongliang=round(gongxuhebing(kaoke,sg)/1000,1) #开幅产量 kaifuzhongliang=round(gongxuhebing(kaifu,sg)/1000,1) #拼缝产量 pinfengzhongliang=round(gongxuhebing(pinfeng,sg)/1000,1) #摇粒产量 yaolizhongliang=round(gongxuhebing(yaoli,sg)/1000,1) #烧毛产量 shaomaozhongliang=round(gongxuhebing(shaomao,sg)/1000,1) return [libuzhongliang,kaokezhongliang,kaifuzhongliang,shaomaozhongliang,pinfengzhongliang,yaolizhongliang] #打开表格设置格式填入生产数据 def new_open_exle(sj,list2): app=xw.App(visible=True,add_book=False) if os.path.isfile("生产数据.xls"): workbook=app.books.open("生产数据.xls") else: workbook=app.books.add() workbook.save("生产数据.xls") sheet=workbook.sheets[0] sheet.range('A1:g1').api.Merge() sheet.range("B13:g13").api.Merge() sheet.range("B14:g14").api.Merge() sheet.range("B15:g15").api.Merge() sheet.range("A1:g15").api.Borders(7).LineStyle=1 sheet.range("A1:g15").api.Borders(8).LineStyle=1 sheet.range("A1:g15").api.Borders(9).LineStyle=1 sheet.range("A1:g15").api.Borders(10).LineStyle=1 sheet.range("A1:g15").api.Borders(11).LineStyle=1 sheet.range("A1:g15").api.Borders(12).LineStyle=1 sheet.range("a1").value="生产数据汇报表" sheet.range("a1").api.Font.Name="宋体" sheet.range("a1").api.Font.Size=25 sheet.range("a1").api.Font.Bold=True sheet.range("a1").api.HorizontalAlignment=-4108 sheet.range("a1").column_width=9 sheet.range("b1").column_width=9 sheet.range("c1").column_width=9 sheet.range("d1").column_width=13 sheet.range("e1").column_width=13 sheet.range("f1").column_width=13 sheet.range("g1").column_width=16 sheet.range("a1").row_height=32 sheet.range("a2").row_height=32 sheet.range("a3").row_height=32 sheet.range("a4").row_height=32 sheet.range("a5").row_height=32 sheet.range("a6").row_height=32 sheet.range("a7").row_height=32 sheet.range("a8").row_height=32 sheet.range("a9").row_height=32 sheet.range("a10").row_height=32 sheet.range("a11").row_height=32 sheet.range("a12").row_height=32 sheet.range("a13").row_height=95 sheet.range("a14").row_height=95 sheet.range("a15").row_height=95 sheet.range("a2:g2").value=["工序","出勤人数","当班产量","实际人均产量","目标人均产量","目标完成比例","备注"] sheet.range("a2:g2").api.Font.Name="宋体" sheet.range("a2:g2").api.Font.Size=12 sheet.range("a2:g2").api.Font.Bold=True sheet.range("a2:g2").api.HorizontalAlignment=-4108 sheet.range("A3:A15").options(transpose=True).value=["理布","拷克","剖幅","烧毛","拼缝","摇粒","平均人均效率","试样","总人数","班会迟到人数","设备情况","质量情况","劳动纪律"] sheet.range("A3:A15").api.Font.Name="宋体" sheet.range("A3:A15").api.Font.Size=12 sheet.range("A3:A15").api.Font.Bold=True sheet.range("A3:A15").api.HorizontalAlignment=-4108 #自动换行 sheet.range("a3:a15").api.WrapText=True sheet.range("b3:b12").options(transpose=True).value=list2 sheet.range("b3:b12").api.Font.Name="宋体" sheet.range("b3:b12").api.Font.Size=12 sheet.range("b3:b12").api.Font.Bold=True sheet.range("b3:b12").api.HorizontalAlignment=-4108 sheet.range("c3:c8").options(transpose=True).value=sj sheet.range("c3:c8").api.Font.Name="宋体" sheet.range("c3:c8").api.Font.Size=12 sheet.range("c3:c8").api.Font.Bold=True sheet.range("c3:c8").api.HorizontalAlignment=-4108 sheet.range("e3:e8").options(transpose=True).value=[10,25,12,5,3,8]#理布、拷克、剖幅、烧毛、拼缝、摇粒 sheet.range("e3:e8").api.Font.Name="宋体" sheet.range("e3:e8").api.Font.Size=12 sheet.range("e3:e8").api.Font.Bold=True sheet.range("e3:e8").api.HorizontalAlignment=-4108 for i in range(3,9): sheet.range("d"+str(i)).options(transpose=True).value="=c"+str(i)+"/b"+str(i) sheet.range("d3:d8").api.NumberFormat="0.0" sheet.range("d3:d8").api.Font.Name="宋体" sheet.range("d3:d8").api.Font.Size=12 sheet.range("d3:d8").api.Font.Bold=True sheet.range("d3:d8").api.HorizontalAlignment=-4108 for x in range(3,9): sheet.range("f"+str(x)).options(transpose=True).value="=d"+str(x)+"/e"+str(x) sheet.range("f3:f8").api.NumberFormat="0%" sheet.range("f3:f8").api.Font.Name="宋体" sheet.range("f3:f8").api.Font.Size=12 sheet.range("f3:f8").api.Font.Bold=True sheet.range("f3:f8").api.HorizontalAlignment=-4108 sheet.range('e16:g16').api.Merge() sheet.range("e16").value="数据生成时间:"+time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()) sheet.range("e16").api.HorizontalAlignment=-4152 sheet.range("e16").api.Font.Bold=True sheet.range("g3:g11").options(transpose=True).value=["理布、退卷、机器理拷、翻布","拷克","剖幅、机器理布","烧毛、双面烧毛","拼缝、缝点","摇粒、汽蒸、吹布","超过100%按照100%计算","包括其他工段帮忙人员","包括当班所有人员"]#理布、拷克、剖幅、烧毛、拼缝、摇粒 #设置字体 sheet.range("g3:g11").api.Font.Name="宋体" #设置字体的大小 sheet.range("g3:g11").api.Font.Size=10 #字体加粗 sheet.range("g3:g11").api.Font.Bold=True #居中 sheet.range("g3:g11").api.HorizontalAlignment=-4108 #自动换行 sheet.range("g3:g11").api.WrapText=True sheet.range("F9").value=["=(IF(F3>=100%,100%,F3)+IF(F4>=100%,100%,F4)+IF(F5>=100%,100%,F5)+IF(F6>=100%,100%,F6)+IF(F7>=100%,100%,F7)+IF(F8>=100%,100%,F8))/6"] sheet.range("f9").api.NumberFormat="0.0%" sheet.range("f9").api.Font.Name="宋体" sheet.range("f9").api.Font.Size=12 sheet.range("f9").api.Font.Bold=True sheet.range("f9").api.HorizontalAlignment=-4108 sheet.range("A9:G9").color=(203,253,251) #图形界面布局 def denglu_windows(): font_=("黑体",20) layout=[ [sg.FileBrowse(button_text="打开生产数据",size=(12,1),font=font_,enable_events=True,file_types=(("ALL Files","*.xls"),("ALL Files","*.xlsx")))], [sg.T("数据处理进度:",font=font_),sg.ProgressBar(5,orientation="h",size=(51,20),key="-bar-")], [sg.T("理布人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-libu_renshu-",size=(3,1)), sg.T(" 拷克人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-kaoke_renshu-",size=(3,1)), sg.T(" 剖幅人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-paofu_renshu-",size=(3,1))], [sg.T("烧毛人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-shaomao_renshu-",size=(3,1)), sg.T(" 拼缝人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-pinfeng_renshu-",size=(3,1)), sg.T(" 摇粒人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-yaoli_renshu-",size=(3,1))], [ sg.T("试样人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-shiyang_renshu-",size=(3,1)), sg.T(" 总人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-zong_renshu-",size=(3,1)), sg.T(" 迟到人数:",font=font_),sg.In("1",enable_events=True,font=font_,key="-chidao_renshu-",size=(3,1))] ] window=sg.Window(banben,layout) list1=[] while True: event,values=window.read() if event=="打开生产数据": list1.clear() if values["打开生产数据"]: try: i=0 window["-bar-"].UpdateBar(i) kaiguan=True 原始数据=open_excle(values["打开生产数据"]) i+=1 window["-bar-"].UpdateBar(i) 要保留的数据=delete_notshuju(原始数据) i+=1 window["-bar-"].UpdateBar(i) 二级数据=sanchu6chejianshuju(要保留的数据) i+=1 window["-bar-"].UpdateBar(i) #【理布,拷克,开幅,烧毛,拼缝,摇粒】产量 list1=jishuanzongchanliang(二级数据) i+=1 window["-bar-"].UpdateBar(i) cmmand="taskkill /F /IM wps.exe" os.system(cmmand) cmmand="taskkill /F /IM et.exe" os.system(cmmand) i+=1 window["-bar-"].UpdateBar(i) xuanzhejieguo=sg.popup_yes_no("是否生成数据报表?") if xuanzhejieguo=="Yes": list2=[values["-libu_renshu-"],values["-kaoke_renshu-"],values["-paofu_renshu-"],values["-shaomao_renshu-"],values["-pinfeng_renshu-"],values["-yaoli_renshu-"],"",values["-shiyang_renshu-"],values["-zong_renshu-"],values["-chidao_renshu-"]] new_open_exle(list1,list2) if xuanzhejieguo=="No": sg.popup_ok("如果要生成数据报表,需要重新点击“打开生产数据”",title="",line_width=10) except: sg.popup_ok("您选择的数据有误,请重新选择!") if event==None: break denglu_windows()