larsmrx

Endlistersteller with GUI

Nov 20th, 2019
190
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 4.93 KB | None | 0 0
  1. #! python3
  2. #! iWaldman GUI Endlistmaker
  3.  
  4. import PySimpleGUI as sg
  5. import os, copy, logging
  6. import datetime as dt
  7. from openpyxl import Workbook, load_workbook
  8. logging.basicConfig(level=logging.WARNING, 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. layout = [  [sg.Text("Bitte den Tag wählen"), sg.CalendarButton("Pick a day")],
  16.             [sg.Text("Bitte Endlist Ordner wählen"),                sg.FolderBrowse()],
  17.             [sg.OK(), sg.Cancel()]
  18.            
  19.          ]
  20.          
  21. window = sg.Window("iWaldman Endlistersteller").Layout(layout)
  22.  
  23. event, values = window.Read()
  24. if event in (None, "Cancel"):
  25.     sg.Popup("Thank you for using our software\n©2019 HuskySoft")
  26.     os._exit(0)
  27.  
  28. #sg.Popup(values)
  29. kw = dt.date.strftime(values['Pick a day'], "%V")
  30. source_day = dt.date.strftime(values['Pick a day'], "%d.%m.%Y")
  31. source_day2 = dt.date.strftime(values['Pick a day'], "%d-%m-%Y")
  32. target_month = dt.date.strftime(values['Pick a day'], "%m")
  33. source_week = copy.copy(kw)
  34.  
  35. try:
  36.     sourceWB = load_workbook(f"C:\\Users\\CarolaNiemeier\\Dropbox\\Shared_PrimeNow\\Planung\\KW Planung 2019\\2019 KW_{source_week}_Planung.xlsx")
  37.     try:
  38.         sourceWS = sourceWB[source_day]
  39.     except:
  40.         sourceWS = sourceWB[source_day2]
  41. except FileNotFoundError:
  42.     print('File not found. Please restart and try again')
  43.  
  44. workday = sourceWS["D18:I35"]
  45.  
  46. os.chdir(values['Browse'])
  47. targetWB = load_workbook(f"Endlist_{target_month}_2019.xlsx")
  48. targetSheet = targetWB.active
  49.  
  50.  
  51.  
  52. start_times = {0:"12:00", 1:"14:00", 2:"16:00",3:"18:00", 4:"20:00"}
  53. finish_times = {0:"14:00", 1:"16:00", 2:"18:00",3:"20:00", 4:"22:00"}
  54.  
  55.  
  56. for enum, tenHourBlock in enumerate(workday):
  57.     new_row = cellFinder('C', targetSheet)
  58.     dataCell = 'A' + str(new_row)
  59.     nameCell = 'C' + str(new_row)
  60.     startCell =  'E' + str(new_row)
  61.     finishCell = 'F' + str(new_row)
  62.     szofer_name = ''
  63.     shift_length = 0
  64.     last_row_used = ''
  65.     for st, twoHBlock in enumerate(tenHourBlock):#          iterating over two hour blocks in ten hour block
  66.         if twoHBlock.value == None:#                    if there is no name given to the block
  67.             if shift_length != 0:#                      and length of the shift summed up until now is NOT 0
  68.                 logging.debug(f"None value cell with shift length of {shift_length}. Szofers name is {szofer_name}")
  69.                 targetSheet[dataCell] = source_day#         write date cell with worksheets name (ws are named with DD.MM.YYYY format dates)
  70.                 logging.debug(f"written {source_day} to {dataCell}")
  71.                 targetSheet[nameCell] = szofer_name#            write name of the DA to the name cell (there as to be a name if shift length != 0
  72.                 logging.debug(f"written {szofer_name} to {nameCell}")
  73.                 startHour = dt.datetime.strptime(targetSheet[startCell].value, "%H:%M")
  74.                 shift_duration = dt.timedelta(hours=shift_length)
  75.                 finishHour = startHour + shift_duration
  76.                 targetSheet[finishCell] = dt.datetime.strftime(finishHour, "%H:%M")
  77.                 logging.debug(f"written {finishHour} to {finishCell}")
  78.                 shift_length = 0
  79.                 logging.debug(f'row number is {new_row}')
  80.                 new_row = new_row+1
  81.                 logging.debug(f'row number was incremented by 1, new row is {new_row}')
  82.                 dataCell = 'A'+ str(cellFinder('A', targetSheet))
  83.                 logging.debug(f"new dataCell is {dataCell}")
  84.                 startCell = 'E' + str(new_row)
  85.                 logging.debug(f"new startCell is {startCell}")
  86.                 finishCell = 'F' + str(new_row)
  87.                 logging.debug(f"new finishCell is {finishCell}")
  88.                 nameCell = 'C' + str(new_row)
  89.             elif shift_length == 0:
  90.                 szofer_name = ''
  91.                 logging.debug('shift length = 0 and block is empty - pass.')
  92.                 pass
  93.         elif twoHBlock.value != None:
  94.             shift_length += 2
  95.             szofer_name = twoHBlock.value
  96.             logging.debug(f"found nonzero cell, incrementing shift length. Current date Cell and row is {dataCell}")
  97.             if targetSheet[startCell].value == None:#                 if start time cell value is empty write it
  98.                 logging.debug(f"Writing the start time {start_times[st]} to {startCell} cell")
  99.                 targetSheet[startCell] = start_times[st]#
  100. sourceWB.close()
  101. sg.Popup("All Done, exiting without saving\nThank you for using ©2019 HuskySoft ©iWaldman")
  102. '''
  103. try:
  104.    targetWB.save(f"Endlist_{target_month}_2019.xlsx")
  105.    sg.Popup("Saving the Endlist")
  106. except PermissionError:
  107.    print("File not closed - permission denied - close the file and try again")
  108.    sg.Popup("Access Denied\nFile not closed\nclose the file and try again")
  109. '''
Advertisement
Add Comment
Please, Sign In to add comment