Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #! python3
- # producing new blank plan for next calendar week according to the forecast.
- from openpyxl import load_workbook, Workbook
- import datetime as dt
- import os, shutil, copy, itertools
- import pprint as pp
- import logging
- logging.basicConfig(level=logging.DEBUG, format=' %(asctime)s - %(levelname)s - %(message)s')
- def get_KW():
- today = dt.date.today()
- Year, WeekNum, DOW = today.isocalendar()
- next_KW = int(WeekNum) +1
- return next_KW
- def get_dates(kw):
- mon = 1
- today_num = dt.date.isoweekday(dt.date.today())
- difference = int(7 - (today_num - 1))
- next_monday = dt.date.today() + dt.timedelta(days=difference)
- dates = []
- dates.append(next_monday.strftime('%d.%m.%Y'))
- for i in range(1, 6):
- data = (next_monday + dt.timedelta(i)).strftime('%d.%m.%Y')
- dates.append(data)
- pp.pprint(dates)
- return dates
- def headcount(rr):
- rr_ws = rr.active
- monday_list = [rr_ws['D12'].value,rr_ws['D14'].value,rr_ws['D16'].value,rr_ws['D18'].value,rr_ws['D20'].value]
- tuesday_list = [rr_ws['E12'].value,rr_ws['E14'].value,rr_ws['E16'].value,rr_ws['E18'].value,rr_ws['E20'].value]
- wednesday_list = [rr_ws['F12'].value,rr_ws['F14'].value,rr_ws['F16'].value,rr_ws['F18'].value,rr_ws['F20'].value]
- thursday_list = [rr_ws['G12'].value,rr_ws['G14'].value,rr_ws['G16'].value,rr_ws['G18'].value,rr_ws['G20'].value]
- friday_list = [rr_ws['H12'].value,rr_ws['H14'].value,rr_ws['H16'].value,rr_ws['H18'].value,rr_ws['H20'].value]
- saturday_list = [rr_ws['I12'].value,rr_ws['I14'].value,rr_ws['I16'].value,rr_ws['I18'].value,rr_ws['I20'].value]
- headcountlist = [monday_list, tuesday_list, wednesday_list, thursday_list, friday_list, saturday_list]
- return headcountlist
- next_KW = (get_KW())
- kw_num = copy.copy(next_KW)
- dates_in_kw = get_dates(kw_num)
- # loading the blank plan as base
- plan_for_coming_week = 'Planungsmuster.xlsx'
- wb = load_workbook(plan_for_coming_week)
- # iterating over sheets and renaming them (names taken from the list dates_in_kw
- for (sheet, date) in itertools.zip_longest(wb, dates_in_kw):
- sheet.title = str(date)
- # renaming the C1 Cell where the date is stored
- list_of_days = [ x for x in wb.sheetnames[:6]]
- for i in range(len(list_of_days)):
- asheet = wb[list_of_days[i]]
- asheet['C1'] = dates_in_kw[i]
- #getting the workbook where the request for coming week is stored
- rr = load_workbook('RouteRequest.xlsx')
- dPD = headcount(rr) # list of lists of days and values in them
- x = 0
- for sheet in wb:
- for i in range(len(dPD[0])):
- if x <= 5:
- coordinates ={0:'6',1:'7',2:'8',3:'9',4:'10'}
- destination_cell = 'C'+ str(coordinates[i])
- sheet[destination_cell] = dPD[x][i]
- else:
- pass
- x += 1
- numbers = copy.deepcopy(dPD) # creating a copy of the driversPerDay list
- wb.save(f'2019 KW_{next_KW}_Planung.xlsx')
- column_letter = {4:'D', 5:'E', 6:'F', 7:'G', 8:'H'}
- colnum_to_danum = { 4:0, 5: 1, 6:2, 7: 3, 8:4}
- wb = load_workbook(f'2019 KW_{next_KW}_Planung.xlsx')
- for day in range(0,6):
- ws = wb.worksheets[day]
- for col in range(4, 9):
- column = ws[column_letter[col]]
- da_count = (18 + int(numbers[day][colnum_to_danum[col]]))
- for i in range(18, da_count):
- ws[((column_letter[col])+ str(i))] = ('x')
- wb.save(f'2019 KW_{next_KW}_Planung.xlsx')
Advertisement
Add Comment
Please, Sign In to add comment