自己写的工作中用于处理数据的小程序
程序代码：import xlwings as xw
import PySimpleGUI as sg
import time
import os
#打开表格赛选出需要的数据
def open_excel(file_derss,save_shuju_name):
list0=[]
list1=[]
list3=[]
app=xw.App(visible=False,add_book=False)
workbook=app.books.open(file_derss)
sheet=workbook.sheets[0]
list1=sheet.range("a1").expand().value
j=0
#获得要保留数据的列号
for y in save_shuju_name:
j=0
for x in list1[0]:
if x==y:
list0.append(j)
break
else:
j+=1
#循环原始数据
for i in list1[1:]:
list2=[]
#将对应列号的数据保存到list2列表中
for x in list0:
list2=list2+[i[x]]
list3.append(list2)
del(list2)
return list3
#将并缸缸号合并处理
def henbing_binggang(sj):
list1=[]
binggang=set()
for i in sj:
if i[1] and i[1]!=None:
binggang.add(i[1])
else:
list1.append(i)
for i in binggang:
zhongliang=0
binggang_ok=[]
for x in sj:
if x[1]==i:
if x[2] and x[2]!=None:
zhongliang+=x[3]
else:
zhongliang+=x[3]
x[3]=zhongliang
binggang_ok=x
list1.append(binggang_ok)
return list1
def chejiansaixuan(sj,chejian,banci):
list1=[]
for i in sj:
if i[8]==chejian and i[5][:2]==banci:
list1.append(i)
return list1
def jihuapachan(sj):
gangshu=0
dunwei=0
for x in sj:
gangshu+=1
dunwei+=x[3]
return [gangshu,round(dunwei/1000,1)]
def zunshidapbu(sj):
gangshu=0
dunwei=0
for x in sj:
if x[7]=="准时":
gangshu+=1
dunwei+=x[3]
return[gangshu,round(dunwei/1000,1)]
def chaoshi(sj):
gangshu=0
dunwei=0
for x in sj:
if x[7][:2]=="超时":
gangshu+=1
dunwei+=x[3]
return[gangshu,round(dunwei/1000,1)]
def chaoshi_2_hour(sj):
gangshu=0
dunwei=0
for x in sj:
if x[7][:2]=="超时" and float(x[7][3:])>=2:
gangshu+=1
dunwei+=x[3]
return[gangshu,round(dunwei/1000,1)]
def weidaobu(sj):
gangshu=0
dunwei=0
for x in sj:
if not x[4] or x[4]==None:
gangshu+=1
dunwei+=x[3]
return[gangshu,round(dunwei/1000,1)]
def tiqian(sj):
gangshu=0
dunwei=0
for x in sj:
if x[7][:2]=="提前":
gangshu+=1
dunwei+=x[3]
return[gangshu,round(dunwei/1000,1)]
def shuju_into_biaoge(sj,banci,riqi):
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:o1').api.Merge()
#车间
sheet.range('a2:a3').api.Merge()
#计划产量
sheet.range("b2:c2").api.Merge()
#准时到布
sheet.range("d2:e2").api.Merge()
#超时到布
sheet.range("f2:g2").api.Merge()
#超时2小时到布
sheet.range("h2:i2").api.Merge()
#未到布
sheet.range("j2:k2").api.Merge()
#提前到布
sheet.range("l2:m2").api.Merge()
#按时到布率
sheet.range("n2:n3").api.Merge()
#提前到布率
sheet.range("o2:o3").api.Merge()
sheet.range("A1:o7").api.Borders(7).LineStyle=1
sheet.range("A1:o7").api.Borders(8).LineStyle=1
sheet.range("A1:o7").api.Borders(9).LineStyle=1
sheet.range("A1:o7").api.Borders(10).LineStyle=1
sheet.range("A1:o7").api.Borders(11).LineStyle=1
sheet.range("A1:o7").api.Borders(12).LineStyle=1
sheet.range("a1").value="各车间大毛坯来布情况"+"("+riqi.split("-")[1]+"月"+riqi.split("-")[2]+"日"+banci+")"
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=7
sheet.range("c1").column_width=7
sheet.range("d1").column_width=7
sheet.range("e1").column_width=7
sheet.range("f1").column_width=7
sheet.range("g1").column_width=7
sheet.range("h1").column_width=7
sheet.range("i1").column_width=7
sheet.range("j1").column_width=10
sheet.range("k1").column_width=10
sheet.range("l1").column_width=7
sheet.range("m1").column_width=7
sheet.range("n1").column_width=7
sheet.range("o1").column_width=7
sheet.range("a1").row_height=38
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("a2:b2").value=["车间","计划产量"]
sheet.range("a2：B2").api.Font.Name="微软雅黑"
sheet.range("a2：B2").api.Font.Size=12
sheet.range("a2：B2").api.Font.Bold=True
sheet.range("a2:B2").api.HorizontalAlignment=-4108
sheet.range("D2").value="提前到布"
sheet.range("D2").api.Font.Name="微软雅黑"
sheet.range("D2").api.Font.Size=12
sheet.range("D2").api.Font.Bold=True
sheet.range("D2").api.HorizontalAlignment=-4108
sheet.range("F2").value="准时到布"
sheet.range("F2").api.Font.Name="微软雅黑"
sheet.range("F2").api.Font.Size=12
sheet.range("F2").api.Font.Bold=True
sheet.range("F2").api.HorizontalAlignment=-4108
sheet.range("H2").value="超时到布"
sheet.range("H2").api.Font.Name="微软雅黑"
sheet.range("H2").api.Font.Size=12
sheet.range("H2").api.Font.Bold=True
sheet.range("H2").api.HorizontalAlignment=-4108
sheet.range("H2").api.WrapText=True
sheet.range("j2").value="超时2小时以上到布"
sheet.range("J2").api.Font.Name="微软雅黑"
sheet.range("J2").api.Font.Size=12
sheet.range("J2").api.Font.Bold=True
sheet.range("J2").api.HorizontalAlignment=-4108
sheet.range("J2").api.WrapText=True
sheet.range("l2").value="未到布"
sheet.range("l2").api.Font.Name="微软雅黑"
sheet.range("l2").api.Font.Size=12
sheet.range("l2").api.Font.Bold=True
sheet.range("l2").api.HorizontalAlignment=-4108
sheet.range("l2").api.WrapText=True
sheet.range("n2").value="提前到布率"
sheet.range("n2").api.Font.Name="微软雅黑"
sheet.range("n2").api.Font.Size=12
sheet.range("n2").api.Font.Bold=True
sheet.range("n2").api.HorizontalAlignment=-4108
sheet.range("n2").api.WrapText=True
sheet.range("o2").value="准时到布率"
sheet.range("o2").api.Font.Name="微软雅黑"
sheet.range("o2").api.Font.Size=12
sheet.range("o2").api.Font.Bold=True
sheet.range("o2").api.HorizontalAlignment=-4108
sheet.range("o2").api.WrapText=True
sheet.range("b3:m3").value=["缸数","吨位","缸数","吨位","缸数","吨位","缸数","吨位","缸数","吨位","缸数","吨位"]
sheet.range("b3:m3").api.Font.Name="微软雅黑"
sheet.range("b3:m3").api.Font.Size=12
sheet.range("b3:m3").api.Font.Bold=True
sheet.range("b3:m3").api.HorizontalAlignment=-4108
sheet.range("A4:A7").options(transpose=True).value=["一车间","三车间","四车间","合计"]
sheet.range("A4:A7").api.Font.Name="微软雅黑"
sheet.range("A4:A7").api.Font.Size=12
sheet.range("A4:A7").api.Font.Bold=True
sheet.range("A4:A7").api.HorizontalAlignment=-4108
sheet.range("b4:m4").value=sj[0]
sheet.range("b4:m4").api.Font.Name="微软雅黑"
sheet.range("b4:m4").api.Font.Size=12
sheet.range("b4:m4").api.Font.Bold=True
sheet.range("b4:m4").api.HorizontalAlignment=-4108
sheet.range("b5:m5").value=sj[1]
sheet.range("b5:m5").api.Font.Name="微软雅黑"
sheet.range("b5:m5").api.Font.Size=12
sheet.range("b5:m5").api.Font.Bold=True
sheet.range("b5:m5").api.HorizontalAlignment=-4108
sheet.range("b6:m6").value=sj[2]
sheet.range("b6:m6").api.Font.Name="微软雅黑"
sheet.range("b6:m6").api.Font.Size=12
sheet.range("b6:m6").api.Font.Bold=True
sheet.range("b6:m6").api.HorizontalAlignment=-4108
sheet.range("b7:m7").value=["=SUM(B4:B6)","=SUM(C4:C6)","=SUM(D4:D6)","=SUM(E4:E6)","=SUM(F4:F6)","=SUM(G4:G6)","=SUM(H4:H6)","=SUM(I4:I6)","=SUM(J4:J6)","=SUM(K4:K6)","=SUM(L4:L6)","=SUM(M4:M6)"]
sheet.range("b7:m7").api.Font.Name="微软雅黑"
sheet.range("b7:m7").api.Font.Size=12
sheet.range("b7:m7").api.Font.Bold=True
sheet.range("b7:m7").api.HorizontalAlignment=-4108
sheet.range("n4:n7").options(transpose=True).value=["=e4/c4","=e5/c5","=e6/c6","=e7/c7"]
sheet.range("n4:n7").api.NumberFormat="0.0"
sheet.range("n4:n7").api.Font.Name="微软雅黑"
sheet.range("n4:n7").api.Font.Size=12
sheet.range("n4:n7").api.Font.Bold=True
sheet.range("n4:n7").api.HorizontalAlignment=-4108
sheet.range("n4:n7").api.NumberFormat="0%"
sheet.range("o4:o7").options(transpose=True).value=["=g4/C4","=g5/C5","=g6/C6","=g7/C7"]
sheet.range("o4:o7").api.NumberFormat="0.0"
sheet.range("o4:o7").api.Font.Name="微软雅黑"
sheet.range("o4:o7").api.Font.Size=12
sheet.range("o4:o7").api.Font.Bold=True
sheet.range("o4:o7").api.HorizontalAlignment=-4108
sheet.range("o4:o7").api.NumberFormat="0%"
sheet.range('K8:o8').api.Merge()
sheet.range("K8").value="数据生成时间："+time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
sheet.range("K8").api.HorizontalAlignment=-4152
sheet.range("K8").api.Font.Bold=True
sheet.range('A8:G8').api.Merge()
sheet.range("A8").value="备注：以上数据中包含外加工布"
sheet.range("A8").api.HorizontalAlignment=-4131
sheet.range("A8").api.Font.Bold=True
sheet.range("A7:o7").color=(203,253,251)
def zhu_windows():
font_=("微软雅黑",20)
week=["周日","周一","周二","周三","周四","周五","周六"]
month=["一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"]
layout=[
[sg.FileBrowse(button_text="打开生产数据",font=font_,enable_events=True,file_types=(("ALL Files","*.xls"),("ALL Files","*.xlsx")))],
[sg.Radio("白班","banci"),sg.Radio("夜班","banci")],
[sg.CalendarButton(button_text="日期选择",format="%Y-%m-%d",day_abbreviations=week,month_names=month),sg.I("",size=(11,1),disabled=True)],
[sg.T("数据处理进度:",font=font_),sg.ProgressBar(7,orientation="h",size=(51,20),key="-bar-")],
[sg.B("开始计算",font=("微软雅黑",15))]
]
window=sg.Window("大毛坯按需到布率计算器1.2",layout)
while True:
event,values=window.read()
if event==None:
break
if event=="开始计算":
riqi=values[2]
if not riqi:
print(riqi)
sg.popup_ok("请先选择日期！")
continue
banci=""
if not values[0] and not values[1]:
sg.popup_ok("请先选择班次！")
continue
if values[0]:
banci="白班"
if values[1]:
banci="夜班"
yichejian=[]
sanchejian=[]
sichejian=[]
list2=[]
jishu=0
window["-bar-"].UpdateBar(jishu)
#要处理的文件
file_derss=values["打开生产数据"]
if not file_derss:
sg.popup_ok("请先选择数据！")
#要保留的数据
save_shuju_name=["缸号","拼缸缸号","实际重量","重量","到布日期","到布时间","预计到布时间","到布情况","车间"]
jishu+=1
window["-bar-"].UpdateBar(jishu)
#返回的数值
try:
biaogeshuj=open_excel(file_derss,save_shuju_name)
jishu+=1
window["-bar-"].UpdateBar(jishu)
binggangshuju=henbing_binggang(biaogeshuj)
jishu+=1
window["-bar-"].UpdateBar(jishu)
chejianshuju_1=chejiansaixuan(binggangshuju,"染色车间1",banci)
jishu+=1
window["-bar-"].UpdateBar(jishu)
chejianshuju_3=chejiansaixuan(binggangshuju,"染色车间3",banci)
jishu+=1
window["-bar-"].UpdateBar(jishu)
chejianshuju_4=chejiansaixuan(binggangshuju,"染色车间4",banci)
jishu+=1
window["-bar-"].UpdateBar(jishu)
yichejian=jihuapachan(chejianshuju_1)+tiqian(chejianshuju_1)+zunshidapbu(chejianshuju_1)+chaoshi(chejianshuju_1)+chaoshi_2_hour(chejianshuju_1)+weidaobu(chejianshuju_1)
sanchejian=jihuapachan(chejianshuju_3)+tiqian(chejianshuju_3)+zunshidapbu(chejianshuju_3)+chaoshi(chejianshuju_3)+chaoshi_2_hour(chejianshuju_3)+weidaobu(chejianshuju_3)
sichejian=jihuapachan(chejianshuju_4)+tiqian(chejianshuju_4)+zunshidapbu(chejianshuju_4)+chaoshi(chejianshuju_4)+chaoshi_2_hour(chejianshuju_4)+weidaobu(chejianshuju_4)
jishu+=1
window["-bar-"].UpdateBar(jishu)
cmmand="taskkill /F /IM wps.exe"
os.system(cmmand)
cmmand="taskkill /F /IM et.exe"
os.system(cmmand)
except:
sg.popup_ok("您选择的数据有误，请重新选择！")
continue
xuanzhejieguo=sg.popup_yes_no("是否生成数据报表？")
if xuanzhejieguo=="Yes":
list2=[yichejian,sanchejian,sichejian]
shuju_into_biaoge(list2,banci,riqi)
if xuanzhejieguo=="No":
sg.popup_ok("如果要生成数据报表，需要重新点击“开始计算”",title="",line_width=10)
zhu_windows()
