Advertisement
larsmrx

Cannot produce a workbook

Dec 9th, 2019
325
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 8.09 KB | None | 0 0
  1. import PySimpleGUI as sg
  2. import os, isoweek, locale, copy, shelve, shutil
  3. from openpyxl import Workbook, load_workbook
  4. import datetime as dt
  5.  
  6. def dateConverter(date):
  7.     if isinstance(date, dt.datetime):
  8.         return dt.datetime.strftime(date, "%d.%m.%Y")
  9.     elif isinstance(date, dt.date):
  10.         dejt = str(date)
  11.         neudejt = dt.datetime.strptime(dejt, "%Y-%m-%d")
  12.         return dt.datetime.strftime(date, "%d.%m.%Y")
  13.     elif isinstance(date, str):
  14.         fds = date.strip()
  15.         try:
  16.             data = dt.datetime.strptime(fds, "%d.%m.%Y")
  17.             datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  18.             return datastring
  19.         except:
  20.             try:
  21.                 data = dt.datetime.strptime(fds, "%d-%m-%Y")
  22.                 datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  23.                 return datastring
  24.             except:
  25.                 try:
  26.                     data = dt.datetime.strptime(fds, "%Y%m%d")
  27.                     datastring = dt.datetime.strftime(data, "%d.%m.%Y")
  28.                     return datastring
  29.                 except:
  30.                     return 0
  31.  
  32. def weeknum(datestr):
  33.     if datestr in (0, None):
  34.         return 0
  35.     else:
  36.         newDateObj = dt.datetime.strptime(datestr, "%d.%m.%Y")
  37.         wk = isoweek.Week.withdate(newDateObj)
  38.         return wk[1]
  39. def czasownik(czas):
  40.     if isinstance(czas, dt.datetime):
  41.         neuczas = dt.datetime.strftime(czas, "%H")
  42.         return int(neuczas)
  43.     elif isinstance(czas, dt.time):
  44.         neuczas = dt.time.strftime(czas, "%H")
  45.         return int(neuczas)
  46.  
  47. sg.change_look_and_feel("Topanga")
  48.  
  49. layout = [  [sg.Text("Ordner mit EoS Berichten"), sg.FolderBrowse()],
  50.             [sg.Text("Bitte die KW eingeben"), sg.InputText("KW?")],
  51.             [sg.Text("Bitte das Jahr eingeben"), sg.InputText(f"{dt.datetime.today().year}")],
  52.             [sg.Text("Bitte Speicherordner wählen."), sg.FolderBrowse()],
  53.             [sg.Text("Dailycheck Mappe ->"), sg.FileBrowse()],
  54.             [sg.Text("Endlist Ordner"), sg.FolderBrowse()],
  55.             [sg.OK(),sg.Cancel()]
  56.          ]
  57.  
  58.  
  59. window = sg.Window("DogSled").Layout(layout)
  60.  
  61. event, values = window.Read()
  62. if event in (None, 'Cancel'):
  63.     sg.Popup("Thank you for using DogSled\n© 2019 HuskySoft")
  64.     os._exit(0)
  65. window.close()
  66.  
  67.  
  68. try:
  69.     # setting all the needed variables like week number, year, daterange
  70.     kw_num = int(values[0])
  71.     jahr = int(values[1])
  72.     kw = isoweek.Week(jahr, kw_num) # getting the week object from isoweek
  73.     daterange = [x for x in kw.days()] # generating days of given week
  74.     daterange.pop() #removing sunday
  75.     strdate = []
  76.  
  77.     #creating date range for give week
  78.     for dat in daterange:
  79.         strdate.append(dt.date.strftime(dat, "%Y%m%d"))
  80.  
  81.     # setting german locale for Month-folder name
  82.     locale.setlocale(locale.LC_ALL, 'de_DE')
  83.  
  84.     # finding the month by finding given thursday of given week first to extract datetime data from it
  85.     mons = kw.thursday()
  86.  
  87.  
  88.     #from datetime object 'mons' we format a string reprsentation as full local german name of the month (for example 'November')
  89.     monat = dt.datetime.strftime(mons, "%B")
  90.  
  91.     #extracting a integer representation of the month needed for calculations
  92.     monat1 = mons.month
  93.  
  94.     # creating an empty list to append the hrs from EoS reports to (these will be shifts, not actual hours, it will be multiplied later)
  95.     hours_from_eos = []
  96.  
  97.     #changing dirs to where the EoS' are, chosen by the first folder dialog window
  98.     os.chdir(values['Browse'])
  99.  
  100.     # building a list of daily workhours, these are saved in the Y16 cell of each Workbook in the SITE DSP EOS tab
  101.     for a,b in enumerate(strdate):
  102.         eos_name = f'{b} UDE3 SystemLogistik EoS Hours.xlsm'
  103.         try:
  104.             wb = load_workbook(eos_name, data_only=True) # opening as data-only to get the results, not the formulas
  105.             ws = wb['SITE-DSP-EOS']
  106.             hours_from_eos.append(ws['Y16'].value)
  107.             wb.close()
  108.         except:
  109.             print("Error encountered. File not found")
  110.  
  111.     #building a list of actual hours, not shifts
  112.     hours_from_eos2 = [x*2 for x in hours_from_eos]
  113.  
  114.     #changing folder to the Browse2 path, where the Endlists are stored
  115.  
  116.     os.chdir(values['Browse2'])
  117.     endlist = load_workbook(f"Endlist_{monat1}_2019.xlsx", data_only=True)
  118.     endlistws = endlist.active
  119.    
  120.  
  121.     #creating an empty dict, to store the dates and hours from endlist
  122.     endlist_hrs = {}
  123.  
  124.     #creating a dict with dates as keys and empty lists as values
  125.     for cell in endlistws['A']:
  126.         if cell.value != None:
  127.             if weeknum(dateConverter(cell.value)) == kw_num:
  128.                 if dateConverter(endlistws[f'A{cell.row}'].value) in endlist_hrs.keys(): #is the date already in the dict?
  129.                     pass # it is, so pass
  130.                 else:
  131.                     endlist_hrs[dateConverter((endlistws[f'A{cell.row}'].value))] = [] #its not, so add it
  132.         else:
  133.             pass #does not match
  134.  
  135.     # iterating over keys in the endlist_hrs dict, checking the dates in A column - not the best solution, iterating every time over whole A column - to be upgraded
  136.     for key in endlist_hrs.keys():
  137.         for cell in endlistws['A']:
  138.             if cell.value != None:
  139.                 if dateConverter(cell.value) == key:
  140.                     endlist_hrs[key].append(czasownik(endlistws[f'J{cell.row}'].value))
  141.  
  142.    
  143.     endlist.close() #closing the endlist workbook
  144.  
  145.     #creating a dict with dates as keys and sum of hours as values - ready to be inserted into cells in the Check workbook
  146.     full_endlist_data = {k:sum(v) for (k,v) in endlist_hrs.items()}
  147.  
  148.     #copying the dailycheck workbook and producing the final output
  149.  
  150.     faylneym = f"DC{kw_num}.xlsx"
  151.     paf = os.path.join(values['Browse0'], faylneym)
  152.  
  153.     shutil.copy2(values['Browse1'], paf)
  154.  
  155.     dcwb = load_workbook(paf, write_only=True)
  156.     dcws = dcwb['KW_XX']
  157.     dcws.title = str(kw)
  158.     dcwb.save(paf)
  159.     dcwb = load_workbook(paf)
  160.     dcws = dcwb.active
  161.  
  162.     for x,y in enumerate(strdate, start=2):
  163.         dcws[f'A{x}'].value = y
  164.     for x,y in enumerate(strdate, start=12):
  165.         dcws[f'A{x}'].value = y
  166.     for x,y in enumerate(hours_from_eos2, start=2):
  167.         dcws[f'E{x}'].value = y
  168.     for x,y in enumerate(full_endlist_data.values(), start=2):
  169.         dcws[f'D{x}'].value = y
  170.  
  171. ##    prefA = 'A'
  172. ##    prefD = 'D'
  173. ##    prefE = 'E'
  174. ##    a2 = []
  175. ##    a12 = []
  176. ##    d2 = []
  177. ##    e2 = []
  178. ##    for i in range(2, 8):
  179. ##        a2.append(prefA + str(i))
  180. ##        a12.append(prefA + str(i+10))
  181. ##        d2.append(prefD + str(i))
  182. ##        e2.append(prefE + str(i))
  183. ##
  184. ##    for cell, value in zip(a2, strdate):
  185. ##        dcws[cell] = value
  186. ##
  187. ##    for cell, value in zip(a12, strdate):
  188. ##        dcws[cell] = value
  189. ##
  190. ##    for cell, value in zip(d2, full_endlist_data.values()):
  191. ##        dcws[cell] = value
  192. ##
  193. ##    for cell, value in zip(e2, hours_from_eos2):
  194. ##        dcws[cell] = value
  195.  
  196. ##    dcws['A2'] = strdate[0]
  197. ##    dcws['A3'] = strdate[1]
  198. ##    dcws['A4'] = strdate[2]
  199. ##    dcws['A5'] = strdate[3]
  200. ##    dcws['A6'] = strdate[4]
  201. ##    dcws['A7'] = strdate[5]
  202. ##    dcws['A12'] = strdate[0]
  203. ##    dcws['A13'] = strdate[1]
  204. ##    dcws['A14'] = strdate[2]
  205. ##    dcws['A15'] = strdate[3]
  206. ##    dcws['A16'] = strdate[4]
  207. ##    dcws['A17'] = strdate[5]
  208. ##
  209. ##    dcws['D2'] = full_endlist_data[dateConverter(strdate[0])]
  210. ##    dcws['D3'] = full_endlist_data[dateConverter(strdate[1])]
  211. ##    dcws['D4'] = full_endlist_data[dateConverter(strdate[2])]
  212. ##    dcws['D5'] = full_endlist_data[dateConverter(strdate[3])]
  213. ##    dcws['D6'] = full_endlist_data[dateConverter(strdate[4])]
  214. ##    dcws['D7'] = full_endlist_data[dateConverter(strdate[5])]
  215. ##
  216. ##    dcws['E2'] = hours_from_eos2[0]
  217. ##    dcws['E3'] = hours_from_eos2[1]
  218. ##    dcws['E4'] = hours_from_eos2[2]
  219. ##    dcws['E5'] = hours_from_eos2[3]
  220. ##    dcws['E6'] = hours_from_eos2[4]
  221. ##    dcws['E7'] = hours_from_eos2[5]
  222.  
  223.     wb.save(paf)
  224.     sg.Popup("All Done.")
  225.  
  226. except:
  227.     sg.Popup("Something went wrong.")
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement