Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #! python3
- # robowaldman shift sorting endlist writing algorithm
- import openpyxl as pxl
- import logging, os
- import datetime as dt
- logging.basicConfig(level=logging.DEBUG, format=" %(asctime)s - %(levelname)s - %(message)s")
- def cellFinder(column, targetSheet):
- for cell in targetSheet[column]:
- if cell.value == None:
- return cell.row
- source_week = input("Please enter source day WK number ")
- source_day = input("Please enter source day date in DD.MM.YYYY format ")
- try:
- sourceWB = pxl.load_workbook(f"2019 KW_{source_week}_Planung.xlsx")
- sourceWS = sourceWB[source_day]
- except FileNotFoundError:
- print('File not found. Please restart and try again')
- workday = sourceWS["D18:I26"]
- targetWB = pxl.load_workbook("Endlist_10_2019.xlsx")
- targetSheet = targetWB.active
- start_times = {0:"12:00", 1:"14:00", 2:"16:00",3:"18:00", 4:"20:00"}
- finish_times = {0:"14:00", 1:"16:00", 2:"18:00",3:"20:00", 4:"22:00"}
- for enum, tenHourBlock in enumerate(workday):
- new_row = cellFinder('C', targetSheet)
- dataCell = 'A' + str(new_row)
- nameCell = 'C' + str(new_row)
- startCell = 'E' + str(new_row)
- finishCell = 'F' + str(new_row)
- szofer_name = ''
- shift_length = 0
- for st, twoHBlock in enumerate(tenHourBlock):# iterating over two hour blocks in ten hour block
- if twoHBlock.value == None:# if there is no name given to the block
- if shift_length != 0:# and length of the shift summed up until now is NOT 0
- logging.debug(f"None value cell with shift length of {shift_length}. Szofers name is {szofer_name}")
- targetSheet[dataCell] = source_day# write date cell with worksheets name (ws are named with DD.MM.YYYY format dates)
- logging.debug(f"written {source_day} to {dataCell}")
- targetSheet[nameCell] = szofer_name# write name of the DA to the name cell (there as to be a name if shift length != 0
- logging.debug(f"written {szofer_name} to {nameCell}")
- startHour = dt.datetime.strptime(targetSheet[startCell].value, "%H:%M")
- shift_duration = dt.timedelta(hours=shift_length)
- finishHour = startHour + shift_duration
- targetSheet[finishCell] = dt.datetime.strftime(finishHour, "%H:%M")
- logging.debug(f"written {finishHour} to {finishCell}")
- shift_length = 0
- logging.debug(f'row number is {new_row}')
- new_row = new_row+1
- logging.debug(f'row number was incremented by 1, new row is {new_row}')
- dataCell = 'A'+ str(cellFinder('A', targetSheet))
- logging.debug(f"new dataCell is {dataCell}")
- startCell = 'E' + str(new_row)
- logging.debug(f"new startCell is {startCell}")
- finishCell = 'F' + str(new_row)
- logging.debug(f"new finishCell is {finishCell}")
- nameCell = 'C' + str(new_row)
- elif shift_length == 0:
- szofer_name = ''
- logging.debug('shift length = 0 and block is empty - pass.')
- pass
- elif twoHBlock.value != None:
- shift_length += 2
- szofer_name = twoHBlock.value
- logging.debug(f"found nonzero cell, incrementing shift length. Current date Cell and row is {dataCell}")
- if targetSheet[startCell].value == None:# if start time cell value is empty write it
- logging.debug(f"Writing the start time {start_times[st]} to {startCell} cell")
- targetSheet[startCell] = start_times[st]#
- sourceWB.close()
- try:
- targetWB.save("Endlist_10_2019.xlsx")
- except PermissionError:
- print("File not closed - permission denied - close the file and try again")
Advertisement
Add Comment
Please, Sign In to add comment