Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #! python3
- #! iWaldman GUI Endlistmaker
- import PySimpleGUI as sg
- import os, copy, logging
- import datetime as dt
- from openpyxl import Workbook, load_workbook
- logging.basicConfig(level=logging.WARNING, format=" %(asctime)s - %(levelname)s - %(message)s")
- def cellFinder(column, targetSheet):
- for cell in targetSheet[column]:
- if cell.value == None:
- return cell.row
- layout = [ [sg.Text("Bitte den Tag wählen"), sg.CalendarButton("Pick a day")],
- [sg.Text("Bitte Endlist Ordner wählen"), sg.FolderBrowse()],
- [sg.OK(), sg.Cancel()]
- ]
- window = sg.Window("iWaldman Endlistersteller").Layout(layout)
- event, values = window.Read()
- if event in (None, "Cancel"):
- sg.Popup("Thank you for using our software\n©2019 HuskySoft")
- os._exit(0)
- #sg.Popup(values)
- kw = dt.date.strftime(values['Pick a day'], "%V")
- source_day = dt.date.strftime(values['Pick a day'], "%d.%m.%Y")
- source_day2 = dt.date.strftime(values['Pick a day'], "%d-%m-%Y")
- target_month = dt.date.strftime(values['Pick a day'], "%m")
- source_week = copy.copy(kw)
- try:
- sourceWB = load_workbook(f"C:\\Users\\CarolaNiemeier\\Dropbox\\Shared_PrimeNow\\Planung\\KW Planung 2019\\2019 KW_{source_week}_Planung.xlsx")
- try:
- sourceWS = sourceWB[source_day]
- except:
- sourceWS = sourceWB[source_day2]
- except FileNotFoundError:
- print('File not found. Please restart and try again')
- workday = sourceWS["D18:I35"]
- os.chdir(values['Browse'])
- targetWB = load_workbook(f"Endlist_{target_month}_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
- last_row_used = ''
- 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()
- sg.Popup("All Done, exiting without saving\nThank you for using ©2019 HuskySoft ©iWaldman")
- '''
- try:
- targetWB.save(f"Endlist_{target_month}_2019.xlsx")
- sg.Popup("Saving the Endlist")
- except PermissionError:
- print("File not closed - permission denied - close the file and try again")
- sg.Popup("Access Denied\nFile not closed\nclose the file and try again")
- '''
Advertisement
Add Comment
Please, Sign In to add comment