|
#2
sdg1232021-11-25 12:06
|
程序代码：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()
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()
