larsmrx

Create New Blank Plan with WK Sheet Names

Oct 22nd, 2019
223
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 3.39 KB | None | 0 0
  1. #! python3
  2. # producing new blank plan for next calendar week according to the forecast.
  3.  
  4. from openpyxl import load_workbook, Workbook
  5. import datetime as dt
  6. import os, shutil, copy, itertools
  7. import pprint as pp
  8. import logging
  9.  
  10. logging.basicConfig(level=logging.DEBUG, format=' %(asctime)s - %(levelname)s - %(message)s')
  11.  
  12. def get_KW():
  13.     today = dt.date.today()
  14.     Year, WeekNum, DOW = today.isocalendar()
  15.     next_KW = int(WeekNum) +1
  16.     return next_KW
  17.  
  18. def get_dates(kw):
  19.     mon = 1
  20.     today_num = dt.date.isoweekday(dt.date.today())
  21.     difference = int(7 - (today_num - 1))
  22.     next_monday = dt.date.today() + dt.timedelta(days=difference)
  23.     dates = []
  24.     dates.append(next_monday.strftime('%d.%m.%Y'))
  25.     for i in range(1, 6):
  26.         data = (next_monday + dt.timedelta(i)).strftime('%d.%m.%Y')
  27.         dates.append(data)
  28.     pp.pprint(dates)
  29.     return dates
  30.    
  31. def headcount(rr):
  32.     rr_ws = rr.active
  33.     monday_list = [rr_ws['D12'].value,rr_ws['D14'].value,rr_ws['D16'].value,rr_ws['D18'].value,rr_ws['D20'].value]
  34.     tuesday_list = [rr_ws['E12'].value,rr_ws['E14'].value,rr_ws['E16'].value,rr_ws['E18'].value,rr_ws['E20'].value]
  35.     wednesday_list = [rr_ws['F12'].value,rr_ws['F14'].value,rr_ws['F16'].value,rr_ws['F18'].value,rr_ws['F20'].value]
  36.     thursday_list = [rr_ws['G12'].value,rr_ws['G14'].value,rr_ws['G16'].value,rr_ws['G18'].value,rr_ws['G20'].value]
  37.     friday_list = [rr_ws['H12'].value,rr_ws['H14'].value,rr_ws['H16'].value,rr_ws['H18'].value,rr_ws['H20'].value]
  38.     saturday_list = [rr_ws['I12'].value,rr_ws['I14'].value,rr_ws['I16'].value,rr_ws['I18'].value,rr_ws['I20'].value]
  39.     headcountlist = [monday_list, tuesday_list, wednesday_list, thursday_list, friday_list, saturday_list]
  40.     return headcountlist
  41.  
  42.  
  43. next_KW = (get_KW())
  44. kw_num = copy.copy(next_KW)
  45. dates_in_kw = get_dates(kw_num)
  46.  
  47. # loading the blank plan as base
  48. plan_for_coming_week = 'Planungsmuster.xlsx'
  49. wb = load_workbook(plan_for_coming_week)
  50.  
  51. # iterating over sheets and renaming them (names taken from the list dates_in_kw
  52.  
  53. for (sheet, date) in itertools.zip_longest(wb, dates_in_kw):
  54.     sheet.title = str(date)
  55.  
  56. # renaming the C1 Cell where the date is stored
  57.  
  58. list_of_days = [ x for x in wb.sheetnames[:6]]
  59. for i in range(len(list_of_days)):
  60.     asheet = wb[list_of_days[i]]
  61.     asheet['C1'] = dates_in_kw[i]
  62.  
  63. #getting the workbook where the request for coming week is stored
  64.  
  65. rr = load_workbook('RouteRequest.xlsx')
  66. dPD = headcount(rr) # list of lists of days and values in them
  67.  
  68. x = 0
  69. for sheet in wb:
  70.     for i in range(len(dPD[0])):
  71.         if x <= 5:
  72.             coordinates ={0:'6',1:'7',2:'8',3:'9',4:'10'}
  73.             destination_cell = 'C'+ str(coordinates[i])
  74.             sheet[destination_cell] = dPD[x][i]
  75.         else:
  76.             pass
  77.     x += 1
  78.  
  79. numbers = copy.deepcopy(dPD) # creating a copy of the driversPerDay list
  80.  
  81. wb.save(f'2019 KW_{next_KW}_Planung.xlsx')
  82.  
  83.  
  84. column_letter = {4:'D', 5:'E', 6:'F', 7:'G', 8:'H'}
  85. colnum_to_danum = { 4:0, 5: 1, 6:2, 7: 3, 8:4}
  86. wb = load_workbook(f'2019 KW_{next_KW}_Planung.xlsx')
  87. for day in range(0,6):
  88.     ws = wb.worksheets[day]
  89.     for col in range(4, 9):
  90.         column = ws[column_letter[col]]
  91.         da_count = (18 + int(numbers[day][colnum_to_danum[col]]))
  92.         for i in range(18, da_count):
  93.             ws[((column_letter[col])+ str(i))] = ('x')
  94.  
  95. wb.save(f'2019 KW_{next_KW}_Planung.xlsx')
Advertisement
Add Comment
Please, Sign In to add comment