import os from datetime import datetime, timedelta, date from collections import defaultdict import xlsxwriter # ============================================================ # SZINEK # ============================================================ EV_SZINEK_HEX = [ "#4472C4", # kek "#ED7D31", # narancs "#70AD47", # zold "#9B59B6", # lila "#E74C3C", # piros "#1ABC9C", # tirkiz "#F39C12", # sarga ] EV_SZINEK_XL = [c.replace("#","") for c in EV_SZINEK_HEX] def ev_szin(ev, evek_lista): idx = evek_lista.index(ev) if ev in evek_lista else 0 return EV_SZINEK_XL[idx % len(EV_SZINEK_XL)] def halvany_szin(hex6, arany=0.5): """Szin halvanyitasa feher fele keverve (arany=0..1)""" r = int(hex6[0:2], 16) g = int(hex6[2:4], 16) b = int(hex6[4:6], 16) r = int(r + (255 - r) * arany) g = int(g + (255 - g) * arany) b = int(b + (255 - b) * arany) return f"{r:02X}{g:02X}{b:02X}" def vilagos_szin(hex6): r = min(255, int(hex6[0:2],16) + 140) g = min(255, int(hex6[2:4],16) + 140) b = min(255, int(hex6[4:6],16) + 140) return f"{r:02X}{g:02X}{b:02X}" HU_HONAPOK = ["Januar","Februar","Marcius","Aprilis","Majus","Junius", "Julius","Augusztus","Szeptember","Oktober","November","December"] NAPOK = ["Hetfo","Kedd","Szerda","Csutortok","Pentek","Szombat","Vasarnap"] # ============================================================ # ADATOK # ============================================================ def het_kulcs(d): iso = d.isocalendar() return (iso[0], iso[1]) def het_datum_range(iso_ev, het_szam): jan4 = date(iso_ev, 1, 4) eleje = jan4 + timedelta(weeks=het_szam-1) - timedelta(days=jan4.weekday()) return eleje, eleje + timedelta(days=6) def aktualis_het(): return het_kulcs(date.today()) def aktualis_ho(): return date.today().month def adatok_evekre(unas_data, shop_data, date_from, date_to): result = {} d = datetime.strptime(date_from, "%Y-%m-%d").date() veg = datetime.strptime(date_to, "%Y-%m-%d").date() while d <= veg: ds = d.strftime("%Y-%m-%d") ev = d.year ho = d.month hk = het_kulcs(d) u = unas_data.get(ds, 0.0) s = shop_data.get(ds, 0.0) t = u + s if ev not in result: result[ev] = { 'napi': {}, 'heti': defaultdict(lambda: [0.0, 0.0, 0.0]), 'havi': defaultdict(lambda: [0.0, 0.0, 0.0]), 'eves': [0.0, 0.0, 0.0] } result[ev]['napi'][ds] = (u, s, t) result[ev]['heti'][hk][0] += u result[ev]['heti'][hk][1] += s result[ev]['heti'][hk][2] += t result[ev]['havi'][ho][0] += u result[ev]['havi'][ho][1] += s result[ev]['havi'][ho][2] += t result[ev]['eves'][0] += u result[ev]['eves'][1] += s result[ev]['eves'][2] += t d += timedelta(days=1) return result # ============================================================ # FORMATOK (xlsxwriter) # ============================================================ def make_formats(wb): f = {} base = {"font_name": "Arial", "font_size": 9, "border": 1, "border_color": "#BDD7EE"} f["normal"] = wb.add_format({**base}) f["normal_r"] = wb.add_format({**base, "align": "right"}) f["normal_c"] = wb.add_format({**base, "align": "center"}) f["penz"] = wb.add_format({**base, "align": "right", "num_format": '#,##0 "Ft"'}) f["penz_even"]= wb.add_format({**base, "align": "right", "num_format": '#,##0 "Ft"', "bg_color": "#EBF3FB"}) f["header"] = wb.add_format({**base, "bold": True, "font_color": "white", "bg_color": "#1F3864", "align": "center", "valign": "vcenter"}) f["subheader"]= wb.add_format({**base, "bold": True, "font_color": "white", "bg_color": "#2E75B6", "align": "center"}) f["total_label"] = wb.add_format({**base, "bold": True, "bg_color": "#BDD7EE", "align": "right"}) f["total_penz"] = wb.add_format({**base, "bold": True, "bg_color": "#BDD7EE", "align": "right", "num_format": '#,##0 "Ft"'}) f["total_dim"] = wb.add_format({**base, "bg_color": "#EEF4FB", "align": "right", "num_format": '#,##0 "Ft"'}) f["hl_label"] = wb.add_format({**base, "bold": True, "bg_color": "#FFEB9C", "align": "right"}) f["hl_penz"] = wb.add_format({**base, "bold": True, "bg_color": "#FFEB9C", "align": "right", "num_format": '#,##0 "Ft"'}) f["hl_dim"] = wb.add_format({**base, "bg_color": "#FFFAED", "align": "right", "num_format": '#,##0 "Ft"'}) f["even"] = wb.add_format({**base, "bg_color": "#EBF3FB"}) f["even_c"] = wb.add_format({**base, "bg_color": "#EBF3FB", "align": "center"}) f["even_r"] = wb.add_format({**base, "bg_color": "#EBF3FB", "align": "right"}) f["left"] = wb.add_format({**base, "align": "left"}) f["normal_l"] = wb.add_format({**base, "align": "left"}) f["left_even"]= wb.add_format({**base, "align": "left", "bg_color": "#EBF3FB"}) return f def ev_fmt(wb, hex6, f): """Ev fejlec format""" return wb.add_format({"font_name":"Arial","font_size":9,"bold":True, "font_color":"white","bg_color":f"#{hex6}", "align":"center","border":1,"border_color":"#BDD7EE"}) def ev_light_fmt(wb, hex6): """Vilagos ev szin format""" light = vilagos_szin(hex6) return wb.add_format({"font_name":"Arial","font_size":9, "bg_color":f"#{light}","border":1,"border_color":"#BDD7EE"}) def ev_light_penz_fmt(wb, hex6): light = vilagos_szin(hex6) return wb.add_format({"font_name":"Arial","font_size":9, "bg_color":f"#{light}","border":1,"border_color":"#BDD7EE", "align":"right","num_format":'#,##0 "Ft"'}) def ev_sub_fmt(wb, hex6): """Ev aloszlop fejlec (vilagos)""" light = vilagos_szin(hex6) return wb.add_format({"font_name":"Arial","font_size":9,"bold":True, "bg_color":f"#{light}","border":1,"border_color":"#BDD7EE", "align":"center"}) # ============================================================ # FUL 1: NAPI ADATOK (forditott sorrend) # ============================================================ def create_napi_sheet(wb, ev_adatok, evek, date_from, date_to, f): ws = wb.add_worksheet("Napi adatok") ws.hide_gridlines(2) ws.freeze_panes(1, 0) evek_str = [str(e) for e in evek] ws.set_row(0, 18) ws.set_column(0, 0, 13) ws.set_column(1, 1, 13) ws.set_column(2, 2, 16) ws.set_column(3, 3, 18) ws.set_column(4, 4, 16) ws.write(0, 0, "Datum", f["header"]) ws.write(0, 1, "Nap", f["header"]) ws.write(0, 2, "Unas (Ft)", f["header"]) ws.write(0, 3, "Shoprenter (Ft)",f["header"]) ws.write(0, 4, "Osszesen (Ft)", f["header"]) # Forditott sorrend - legujabb elol d_from = datetime.strptime(date_from, "%Y-%m-%d").date() d_to = datetime.strptime(date_to, "%Y-%m-%d").date() # Ev formatumok cachebe ev_light_c = {} ev_light_p = {} for ev in evek: hex6 = ev_szin(str(ev), evek_str) ev_light_c[ev] = wb.add_format({"font_name":"Arial","font_size":9,"bg_color":f"#{vilagos_szin(hex6)}","border":1,"border_color":"#BDD7EE","align":"left"}) ev_light_p[ev] = ev_light_penz_fmt(wb, hex6) row = 1 i = 0 d = d_to while d >= d_from: ev = d.year ds = d.strftime("%Y-%m-%d") u, s, t = ev_adatok.get(ev, {}).get('napi', {}).get(ds, (0.0, 0.0, 0.0)) if i % 2 == 0: fc = ev_light_c[ev] fl = ev_light_c[ev] fp = ev_light_p[ev] else: fc = f["normal_c"] fl = f["normal_l"] fp = f["penz"] ws.write(row, 0, ds, fl) ws.write(row, 1, NAPOK[d.weekday()], fl) ws.write(row, 2, u, fp) ws.write(row, 3, s, fp) ws.write(row, 4, t, fp) row += 1 i += 1 d -= timedelta(days=1) # ============================================================ # FUL 2: HETI OSSZEHASONLITAS # ============================================================ def create_heti_sheet(wb, ev_adatok, evek, f): ws = wb.add_worksheet("Heti osszehasonlitas") ws.hide_gridlines(2) ws.freeze_panes(2, 0) evek_str = [str(e) for e in evek] n = len(evek) ma_het = aktualis_het() elozo_het_szam = ma_het[1] - 1 if ma_het[1] > 1 else 52 def ev_cols(i): base = 1 + i * 3 return base, base+1, base+2 # Oszlopszelessegek ws.set_column(0, 0, 12) for i in range(n): cu, cs, ct = ev_cols(i) ws.set_column(cu, ct, 15) ws.set_row(0, 18) ws.set_row(1, 16) # Fejlec 1. sor ws.write(0, 0, "Het", f["header"]) for i, ev in enumerate(evek): hex6 = ev_szin(str(ev), evek_str) ef = ev_fmt(wb, hex6, f) blank = wb.add_format({"bg_color":f"#{hex6}","border":1,"border_color":"#BDD7EE"}) cu, cs, ct = ev_cols(i) ws.write(0, cu, str(ev), ef) ws.write(0, cs, "", blank) ws.write(0, ct, "", blank) # Fejlec 2. sor ws.write(1, 0, "", f["header"]) for i, ev in enumerate(evek): hex6 = ev_szin(str(ev), evek_str) sf = ev_sub_fmt(wb, hex6) cu, cs, ct = ev_cols(i) ws.write(1, cu, "Unas", sf) ws.write(1, cs, "Shoprenter", sf) ws.write(1, ct, "Osszesen", sf) # Hetek 1-53 max_het = 53 for ev in evek: for (_, hs) in ev_adatok[ev]['heti'].keys(): if hs > max_het: max_het = hs data_start_row = 2 row = data_start_row aktualis_het_row = None # Ev-specifikus formatumok ev_penz_fmts = {} ev_penz_even_fmts = {} for ev in evek: hex6 = ev_szin(str(ev), evek_str) light = vilagos_szin(hex6) ev_penz_fmts[ev] = wb.add_format({"font_name":"Arial","font_size":9, "border":1,"border_color":"#BDD7EE", "align":"right","num_format":'#,##0 "Ft"'}) ev_penz_even_fmts[ev] = wb.add_format({"font_name":"Arial","font_size":9, "bg_color":f"#{light}","border":1, "border_color":"#BDD7EE", "align":"right","num_format":'#,##0 "Ft"'}) for j in range(1, max_het + 1): is_aktualis = (j == ma_het[1]) if is_aktualis: aktualis_het_row = row label_fmt = wb.add_format({"font_name":"Arial","font_size":9,"bold":False, "bg_color":"#FFEB9C","border":1,"border_color":"#BDD7EE", "align":"center"}) penz_fmt_normal = wb.add_format({"font_name":"Arial","font_size":9, "bg_color":"#FFEB9C","border":1,"border_color":"#BDD7EE", "align":"right","num_format":'#,##0 "Ft"'}) elif j % 2 == 0: label_fmt = f["even_c"] penz_fmt_normal = f["penz_even"] else: label_fmt = f["normal_c"] penz_fmt_normal = f["penz"] ws.write(row, 0, f"{j}. het", label_fmt) for i, ev in enumerate(evek): vals = [0.0, 0.0, 0.0] for (_, hs), v in ev_adatok[ev]['heti'].items(): if hs == j: vals = v break cu, cs, ct = ev_cols(i) pf = penz_fmt_normal ws.write(row, cu, vals[0] or None, pf) ws.write(row, cs, vals[1] or None, pf) ws.write(row, ct, vals[2] or None, pf) row += 1 data_end_row = row - 1 # Eves osszesen sor ws.write(row, 0, "Eves osszesen:", f["total_label"]) for i in range(n): cu, cs, ct = ev_cols(i) ws.write_formula(row, cu, f"=SUM({xl_col(cu)}{data_start_row+1}:{xl_col(cu)}{data_end_row+1})", f["total_dim"]) ws.write_formula(row, cs, f"=SUM({xl_col(cs)}{data_start_row+1}:{xl_col(cs)}{data_end_row+1})", f["total_dim"]) ws.write_formula(row, ct, f"=SUM({xl_col(ct)}{data_start_row+1}:{xl_col(ct)}{data_end_row+1})", f["total_penz"]) osszesen_row = row row += 1 # Elozo hetig sor elozo_het_data_row = data_start_row + elozo_het_szam - 1 ws.write(row, 0, f"{elozo_het_szam}. hetig:", f["hl_label"]) for i in range(n): cu, cs, ct = ev_cols(i) ws.write_formula(row, cu, f"=SUM({xl_col(cu)}{data_start_row+1}:{xl_col(cu)}{elozo_het_data_row+1})", f["hl_dim"]) ws.write_formula(row, cs, f"=SUM({xl_col(cs)}{data_start_row+1}:{xl_col(cs)}{elozo_het_data_row+1})", f["hl_dim"]) ws.write_formula(row, ct, f"=SUM({xl_col(ct)}{data_start_row+1}:{xl_col(ct)}{elozo_het_data_row+1})", f["hl_penz"]) row += 2 # GRAFIKON - vonalgrafikon, csak osszesitett (ct) oszlopok chart = wb.add_chart({"type": "line"}) chart.set_title({"name": "Heti bevetel osszehasonlitas"}) chart.set_x_axis({"name": "Het"}) chart.set_y_axis({"name": "Bevetel (Ft)", "num_format": '#,##0'}) chart.set_size({"width": 1200, "height": 400}) chart.set_style(10) akt_ev = max(evek) for i, ev in enumerate(evek): cu, cs, ct = ev_cols(i) hex6 = ev_szin(str(ev), evek_str) if ev == akt_ev: line_color = f"#{hex6}" line_width = 2.5 else: line_color = f"#{halvany_szin(hex6, 0.55)}" line_width = 1.5 chart.add_series({ "name": str(ev), "categories": ["Heti osszehasonlitas", data_start_row, 0, data_end_row, 0], "values": ["Heti osszehasonlitas", data_start_row, ct, data_end_row, ct], "line": {"color": line_color, "width": line_width}, }) ws.insert_chart(row, 0, chart) # ============================================================ # FUL 3: HAVI OSSZEHASONLITAS # ============================================================ def create_havi_sheet(wb, ev_adatok, evek, f): ws = wb.add_worksheet("Havi osszehasonlitas") ws.hide_gridlines(2) ws.freeze_panes(2, 0) evek_str = [str(e) for e in evek] n = len(evek) ma_ho = aktualis_ho() elozo_ho = ma_ho - 1 if ma_ho > 1 else 12 def ev_cols(i): base = 1 + i * 3 return base, base+1, base+2 ws.set_column(0, 0, 28) for i in range(n): cu, cs, ct = ev_cols(i) ws.set_column(cu, ct, 15) ws.set_row(0, 18) ws.set_row(1, 16) # Fejlec 1. sor ws.write(0, 0, "Honap", f["header"]) for i, ev in enumerate(evek): hex6 = ev_szin(str(ev), evek_str) ef = ev_fmt(wb, hex6, f) blank = wb.add_format({"bg_color":f"#{hex6}","border":1,"border_color":"#BDD7EE"}) cu, cs, ct = ev_cols(i) ws.write(0, cu, str(ev), ef) ws.write(0, cs, "", blank) ws.write(0, ct, "", blank) # Fejlec 2. sor ws.write(1, 0, "", f["header"]) for i, ev in enumerate(evek): hex6 = ev_szin(str(ev), evek_str) sf = ev_sub_fmt(wb, hex6) cu, cs, ct = ev_cols(i) ws.write(1, cu, "Unas", sf) ws.write(1, cs, "Shoprenter", sf) ws.write(1, ct, "Osszesen", sf) data_start_row = 2 elozo_ho_data_row = None row = data_start_row for j, ho in enumerate(range(1, 13)): is_aktualis = (ho == ma_ho) if is_aktualis: label_fmt = wb.add_format({"font_name":"Arial","font_size":9, "bg_color":"#FFEB9C","border":1,"border_color":"#BDD7EE", "align":"left"}) penz_fmt_r = wb.add_format({"font_name":"Arial","font_size":9, "bg_color":"#FFEB9C","border":1,"border_color":"#BDD7EE", "align":"right","num_format":'#,##0 "Ft"'}) elif j % 2 == 0: label_fmt = f["left_even"] penz_fmt_r = f["penz_even"] else: label_fmt = f["left"] penz_fmt_r = f["penz"] ws.write(row, 0, HU_HONAPOK[ho-1], label_fmt) for i, ev in enumerate(evek): vals = ev_adatok[ev]['havi'].get(ho, [0,0,0]) cu, cs, ct = ev_cols(i) ws.write(row, cu, vals[0] or None, penz_fmt_r) ws.write(row, cs, vals[1] or None, penz_fmt_r) ws.write(row, ct, vals[2] or None, penz_fmt_r) if ho == elozo_ho: elozo_ho_data_row = row row += 1 data_end_row = row - 1 # Eves osszesen ws.write(row, 0, "Eves osszesen:", f["total_label"]) for i in range(n): cu, cs, ct = ev_cols(i) ws.write_formula(row, cu, f"=SUM({xl_col(cu)}{data_start_row+1}:{xl_col(cu)}{data_end_row+1})", f["total_dim"]) ws.write_formula(row, cs, f"=SUM({xl_col(cs)}{data_start_row+1}:{xl_col(cs)}{data_end_row+1})", f["total_dim"]) ws.write_formula(row, ct, f"=SUM({xl_col(ct)}{data_start_row+1}:{xl_col(ct)}{data_end_row+1})", f["total_penz"]) row += 1 # Elozo honapig if elozo_ho_data_row: ws.write(row, 0, f"{HU_HONAPOK[elozo_ho-1]}-ig osszesen:", f["hl_label"]) for i in range(n): cu, cs, ct = ev_cols(i) ws.write_formula(row, cu, f"=SUM({xl_col(cu)}{data_start_row+1}:{xl_col(cu)}{elozo_ho_data_row+1})", f["hl_dim"]) ws.write_formula(row, cs, f"=SUM({xl_col(cs)}{data_start_row+1}:{xl_col(cs)}{elozo_ho_data_row+1})", f["hl_dim"]) ws.write_formula(row, ct, f"=SUM({xl_col(ct)}{data_start_row+1}:{xl_col(ct)}{elozo_ho_data_row+1})", f["hl_penz"]) row += 2 # GRAFIKON - oszlopgrafikon, csak osszesitett chart = wb.add_chart({"type": "column"}) chart.set_title({"name": "Havi bevetel osszehasonlitas"}) chart.set_x_axis({"name": "Honap"}) chart.set_y_axis({"name": "Bevetel (Ft)", "num_format": '#,##0'}) chart.set_size({"width": 900, "height": 400}) chart.set_style(10) for i, ev in enumerate(evek): cu, cs, ct = ev_cols(i) hex6 = ev_szin(str(ev), evek_str) chart.add_series({ "name": str(ev), "categories": ["Havi osszehasonlitas", data_start_row, 0, data_end_row, 0], "values": ["Havi osszehasonlitas", data_start_row, ct, data_end_row, ct], "fill": {"color": f"#{hex6}"}, "border": {"color": f"#{hex6}"}, }) ws.insert_chart(row, 0, chart) # ============================================================ # FUL 4: EVES OSSZESITO # ============================================================ def create_eves_sheet(wb, ev_adatok, evek, f): ws = wb.add_worksheet("Eves osszesito") ws.hide_gridlines(2) evek_str = [str(e) for e in evek] ws.set_column(0, 0, 22) for i in range(len(evek)): ws.set_column(i+1, i+1, 18) ws.set_row(0, 18) # Fejlec ws.write(0, 0, "", f["header"]) for i, ev in enumerate(evek): hex6 = ev_szin(str(ev), evek_str) ef = ev_fmt(wb, hex6, f) ws.write(0, i+1, str(ev), ef) sorok = [("Unas bevetel", 0), ("Shoprenter bevetel", 1), ("Osszesen", 2)] for j, (label, idx) in enumerate(sorok): row = j + 1 bold = (idx == 2) lf = f["total_label"] if bold else (f["left_even"] if j%2==0 else f["left"]) pf = f["total_penz"] if bold else (f["penz_even"] if j%2==0 else f["penz"]) ws.write(row, 0, label, lf) for i, ev in enumerate(evek): val = ev_adatok[ev]['eves'][idx] ws.write(row, i+1, val or None, pf) # Grafikon - csak Osszesen (row 3, 1-alapu = index 2+1=3) chart = wb.add_chart({"type": "column"}) chart.set_title({"name": "Eves bevetel osszehasonlitas"}) chart.set_y_axis({"num_format": '#,##0'}) chart.set_size({"width": 600, "height": 350}) chart.set_style(10) for i, ev in enumerate(evek): hex6 = ev_szin(str(ev), evek_str) chart.add_series({ "name": str(ev), "categories": ["Eves osszesito", 0, 1, 0, len(evek)], "values": ["Eves osszesito", 3, i+1, 3, i+1], "fill": {"color": f"#{hex6}"}, "border": {"color": f"#{hex6}"}, }) ws.insert_chart(5, 0, chart) # ============================================================ # SEGÉDFÜGGVÉNY: oszlop betű (0-alapú -> A, B, ...) # ============================================================ def xl_col(col_idx): """0-alapu oszlop index -> Excel betu (A, B, ..., Z, AA, ...)""" result = "" col_idx += 1 while col_idx: col_idx, r = divmod(col_idx - 1, 26) result = chr(65 + r) + result return result # ============================================================ # FOPROGRAM # ============================================================ def generate_excel(unas_data, shop_data, date_from, date_to, output_path): os.makedirs(os.path.dirname(output_path), exist_ok=True) ev_from = int(date_from[:4]) ev_to = int(date_to[:4]) evek = list(range(ev_from, ev_to + 1)) ev_adatok = adatok_evekre(unas_data, shop_data, date_from, date_to) for ev in evek: if ev not in ev_adatok: ev_adatok[ev] = { 'napi': {}, 'heti': defaultdict(lambda: [0.0, 0.0, 0.0]), 'havi': defaultdict(lambda: [0.0, 0.0, 0.0]), 'eves': [0.0, 0.0, 0.0] } wb = xlsxwriter.Workbook(output_path) f = make_formats(wb) create_napi_sheet(wb, ev_adatok, evek, date_from, date_to, f) create_heti_sheet(wb, ev_adatok, evek, f) create_havi_sheet(wb, ev_adatok, evek, f) create_eves_sheet(wb, ev_adatok, evek, f) wb.close() print(f" [OK] Excel mentve: {output_path}")