larsmrx

Robowaldman

Oct 25th, 2019
210
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 3.87 KB | None | 0 0
  1. #! python3
  2. # robowaldman shift sorting endlist writing algorithm
  3.  
  4. import openpyxl as pxl
  5. import logging, os
  6. import datetime as dt
  7.  
  8. logging.basicConfig(level=logging.DEBUG, format=" %(asctime)s - %(levelname)s - %(message)s")
  9.  
  10. def cellFinder(column, targetSheet):
  11.     for cell in targetSheet[column]:
  12.         if cell.value == None:
  13.             return cell.row
  14.  
  15. source_week = input("Please enter source day WK number ")
  16. source_day = input("Please enter source day date in DD.MM.YYYY format ")
  17.  
  18. try:
  19.     sourceWB = pxl.load_workbook(f"2019 KW_{source_week}_Planung.xlsx")
  20.     sourceWS = sourceWB[source_day]
  21. except FileNotFoundError:
  22.     print('File not found. Please restart and try again')
  23. workday = sourceWS["D18:I26"]
  24.  
  25. targetWB = pxl.load_workbook("Endlist_10_2019.xlsx")
  26. targetSheet = targetWB.active
  27.  
  28.  
  29. start_times = {0:"12:00", 1:"14:00", 2:"16:00",3:"18:00", 4:"20:00"}
  30. finish_times = {0:"14:00", 1:"16:00", 2:"18:00",3:"20:00", 4:"22:00"}
  31.  
  32.  
  33. for enum, tenHourBlock in enumerate(workday):
  34.     new_row = cellFinder('C', targetSheet)
  35.     dataCell = 'A' + str(new_row)
  36.     nameCell = 'C' + str(new_row)
  37.     startCell =  'E' + str(new_row)
  38.     finishCell = 'F' + str(new_row)
  39.     szofer_name = ''
  40.     shift_length = 0
  41.     for st, twoHBlock in enumerate(tenHourBlock):#          iterating over two hour blocks in ten hour block
  42.         if twoHBlock.value == None:#                    if there is no name given to the block
  43.             if shift_length != 0:#                      and length of the shift summed up until now is NOT 0
  44.                 logging.debug(f"None value cell with shift length of {shift_length}. Szofers name is {szofer_name}")
  45.                 targetSheet[dataCell] = source_day#         write date cell with worksheets name (ws are named with DD.MM.YYYY format dates)
  46.                 logging.debug(f"written {source_day} to {dataCell}")
  47.                 targetSheet[nameCell] = szofer_name#            write name of the DA to the name cell (there as to be a name if shift length != 0
  48.                 logging.debug(f"written {szofer_name} to {nameCell}")
  49.                 startHour = dt.datetime.strptime(targetSheet[startCell].value, "%H:%M")
  50.                 shift_duration = dt.timedelta(hours=shift_length)
  51.                 finishHour = startHour + shift_duration
  52.                 targetSheet[finishCell] = dt.datetime.strftime(finishHour, "%H:%M")
  53.                 logging.debug(f"written {finishHour} to {finishCell}")
  54.                 shift_length = 0
  55.                 logging.debug(f'row number is {new_row}')
  56.                 new_row = new_row+1
  57.                 logging.debug(f'row number was incremented by 1, new row is {new_row}')
  58.                 dataCell = 'A'+ str(cellFinder('A', targetSheet))
  59.                 logging.debug(f"new dataCell is {dataCell}")
  60.                 startCell = 'E' + str(new_row)
  61.                 logging.debug(f"new startCell is {startCell}")
  62.                 finishCell = 'F' + str(new_row)
  63.                 logging.debug(f"new finishCell is {finishCell}")
  64.                 nameCell = 'C' + str(new_row)
  65.             elif shift_length == 0:
  66.                 szofer_name = ''
  67.                 logging.debug('shift length = 0 and block is empty - pass.')
  68.                 pass
  69.         elif twoHBlock.value != None:
  70.             shift_length += 2
  71.             szofer_name = twoHBlock.value
  72.             logging.debug(f"found nonzero cell, incrementing shift length. Current date Cell and row is {dataCell}")
  73.             if targetSheet[startCell].value == None:#                 if start time cell value is empty write it
  74.                 logging.debug(f"Writing the start time {start_times[st]} to {startCell} cell")
  75.                 targetSheet[startCell] = start_times[st]#
  76. sourceWB.close()
  77. try:
  78.     targetWB.save("Endlist_10_2019.xlsx")
  79.  
  80. except PermissionError:
  81.     print("File not closed - permission denied - close the file and try again")
Advertisement
Add Comment
Please, Sign In to add comment