Spaces:
Runtime error
Runtime error
| from datetime import datetime, timedelta | |
| from openpyxl import load_workbook | |
| import openpyxl as pyxl | |
| class Brw(): | |
| def __init__(self,bNo,brnd,fltr,frm,times): | |
| self.brewNo=bNo | |
| self.brand=brnd | |
| # self.mixer | |
| self.fltr=fltr | |
| self.cookStart=t[0] | |
| self.mixStart=t[1] | |
| self.filtStart=t[2] | |
| self.ktlStart=t[3] | |
| self.ktlFull=t[4] | |
| self.wrtclStart=t[5] | |
| self.wrtclEnd=t[6] | |
| def Gantiffy(brwschd): | |
| wb = load_workbook(filename = brwSchd,data_only=True) | |
| #Generate objects | |
| ws=wb['Schedule'] | |
| brews={} | |
| i=6 | |
| while ws['E'+str(i)].value!=None: | |
| t=[] #to grab times | |
| bNo=ws['C'+str(i)].value | |
| brnd=ws['E'+str(i)].value | |
| fltr=ws['F'+str(i)].value | |
| frm=ws['G'+str(i)].value | |
| for col in ('P','Q','R','S','T','U','V'): | |
| t.append(ws[col+str(i)].value) | |
| brews[bNo]=Brw(bNo,brnd,fltr,frm,t) | |
| i+=1 #increment so no inf loop | |
| #------------------- | |
| #Column widths | |
| #Generate a big list of all column names AAA-ZZZ in excel and set the column width to 1 pt for each | |
| master=[] | |
| alph=[chr(65+i) for i in range(26)] | |
| master.extend(alph) | |
| alph1=[[chr(65+i)+l for l in alph] for i in range(26)] | |
| ls=[] | |
| for l in alph1: | |
| ls.extend(l) | |
| master.extend(l) | |
| alph2=[[chr(65+i)+l for l in ls] for i in range(26)] | |
| alph2 | |
| ls=[] | |
| for l in alph2: | |
| ls.extend(l) | |
| master.extend(l) | |
| for c in master[1:1500]: | |
| ws.column_dimensions[c].width =1.5 | |
| #------------------ | |
| #Set time step, in minutes, and print timeline | |
| ts=10 | |
| i=0 #Pull first brew with below statement to get the first time needs to be on schedule | |
| st=min(brews[list(brews.keys())[i]].cookStart,brews[list(brews.keys())[i]].mixStart) | |
| # sheet["A3"].alignment = Alignment(text_rotation=90) | |
| #Print starting value, starting from top of the hour | |
| #------------------- | |
| #Generate dictionary containing column id (number or letters?) keyed by datetime for that column at same time as printout | |
| timeCol={} | |
| for i in range(1420): #720 10 min chunks in 5 days | |
| tVal=datetime(st.year,st.month,st.day,st.hour)+timedelta(minutes=10*i) | |
| timeCol[tVal]=i+2 #Column Number 2 for iteration 0, etc | |
| #Print the half hours and hours | |
| if tVal.minute in (20,30):ws.cell(column=i+2,row=2).value="-" #Print '--' on the middle of the hour | |
| elif tVal.minute==0:ws.cell(column=i+2,row=2).value="'"+str(tVal.hour) #Print the hour | |
| if tVal.hour==0 and tVal.minute==0: #Print the weekday name | |
| v=tVal.weekday() | |
| if v==0: ws.cell(column=i+2,row=1).value="Monday" | |
| elif v==1: ws.cell(column=i+2,row=1).value="Tuesday" | |
| elif v==2: ws.cell(column=i+2,row=1).value="Wednesday" | |
| elif v==3: ws.cell(column=i+2,row=1).value="Thursday" | |
| elif v==4: ws.cell(column=i+2,row=1).value="Friday" | |
| elif v==5: ws.cell(column=i+2,row=1).value="Saturday" | |
| elif v==6: ws.cell(column=i+2,row=1).value="Sunday" | |
| def styleCell(cl,val): | |
| if val==0: | |
| cl.font=pyxl.styles.Font(bold=True,size=14) | |
| cl.alignment=pyxl.styles.Alignment(horizontal='center') | |
| cl.fill=pyxl.styles.PatternFill(fill_type="solid",start_color='0092D050',end_color='0092D050') | |
| cl.border=pyxl.styles.Border(left=pyxl.styles.Side(border_style='thin'), | |
| right=pyxl.styles.Side(border_style='thin'), | |
| top=pyxl.styles.Side(border_style='thin'), | |
| bottom=pyxl.styles.Side(border_style='thin')) | |
| else: | |
| cl.fill=pyxl.styles.PatternFill(fill_type="solid",start_color='00B0F0',end_color='00B0F0') | |
| cl.font=pyxl.styles.Font(bold=True,size=14,color="00FFFFFF") | |
| cl.alignment=pyxl.styles.Alignment(horizontal='center') | |
| cl.border=pyxl.styles.Border(left=pyxl.styles.Side(border_style='thin'), | |
| right=pyxl.styles.Side(border_style='thin'), | |
| top=pyxl.styles.Side(border_style='thin'), | |
| bottom=pyxl.styles.Side(border_style='thin')) | |
| #------------------- | |
| #Print brew info to the cell on each row where it starts, | |
| #and merge that cell appropriately to the right. | |
| #Round the previous process down to nearest 10 min and next process up to ensure no conflcit when the schedule makes them very tight | |
| def d_key(tm):#d for round DOWN | |
| #Given time from a brew object, returns that time rounded DOWN to the nearest 10 minute, | |
| #corresponding to key for finding which column to print to | |
| tm = tm - timedelta(minutes=tm.minute % 10,seconds=tm.second,microseconds=tm.microsecond) | |
| return tm | |
| def u_key(tm):#u for round UP | |
| #returns key for slot rounded up. | |
| discard = timedelta(minutes=tm.minute % 10,seconds=tm.second,microseconds=tm.microsecond) | |
| tm -= discard | |
| # if discard >= timedelta(minutes=5): #Include this statement and indent next to make it classic rounding | |
| tm += timedelta(minutes=10) | |
| return tm | |
| class mm1(): | |
| def __init__(self): | |
| self.log=[] | |
| def sched(self,st,en): | |
| self.log.append((st,en)) | |
| def busy(self,st,en): | |
| for r in self.log: | |
| if r[0]<st<r[1]: | |
| return True | |
| if r[0]<en<r[1]: | |
| return True | |
| if st<r[0] and r[1]<en: | |
| return True | |
| return False | |
| mm=mm1() | |
| for brN in brews.keys(): | |
| b=brews[brN] | |
| #Cereal Cooker - assume only used if cook time is diff from mix time | |
| r=3 | |
| if d_key(b.cookStart)!=d_key(b.mixStart): | |
| ws.cell(column=timeCol[u_key(b.cookStart)],row=r).value=str(b.brewNo) +b.brand[:5] | |
| ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.cookStart)], end_row=r, end_column=timeCol[d_key(b.mixStart)]) | |
| styleCell(ws.cell(column=timeCol[u_key(b.cookStart)],row=r),brN%2) | |
| #Mixer | |
| if mm.busy(b.mixStart,b.filtStart):r=5 | |
| else: | |
| r=4 | |
| mm.sched(b.mixStart,b.filtStart) | |
| ws.cell(column=timeCol[u_key(b.mixStart)],row=r).value=str(b.brewNo) +b.brand[:5] | |
| ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.mixStart)], end_row=r, end_column=timeCol[d_key(b.filtStart)]) | |
| styleCell(ws.cell(column=timeCol[u_key(b.mixStart)],row=r),brN%2) | |
| #Filter - choose row based on | |
| if fltr=='1-MF':r=6 | |
| elif fltr=='2-MF':r=7 | |
| else:r=8 | |
| ws.cell(column=timeCol[u_key(b.filtStart)],row=r).value=str(b.brewNo) +b.brand[:5] | |
| ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.filtStart)], end_row=r, end_column=timeCol[d_key(b.ktlFull)]) | |
| styleCell(ws.cell(column=timeCol[u_key(b.filtStart)],row=r),brN%2) | |
| #Kettle | |
| r=9 | |
| ws.cell(column=timeCol[u_key(b.ktlStart)],row=r).value=str(b.brewNo) +b.brand[:5] | |
| ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.ktlStart)], end_row=r, end_column=timeCol[d_key(b.wrtclStart)]) | |
| styleCell(ws.cell(column=timeCol[u_key(b.ktlStart)],row=r),brN%2) | |
| #Cool | |
| r=10 | |
| ws.cell(column=timeCol[u_key(b.wrtclStart)],row=r).value=str(b.brewNo) +b.brand[:5] | |
| ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.wrtclStart)], end_row=r, end_column=timeCol[d_key(b.wrtclEnd)]) | |
| styleCell(ws.cell(column=timeCol[u_key(b.wrtclStart)],row=r),brN%2) | |
| wb.save(filename = 'gantt.xlsx') |