datei zoll

Python 3.11.9 (tags/v3.11.9:de54cf5, Apr  2 2024, 10:12:12) [MSC v.1938 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> C:\Users\Cathrin\Documents\Dronivo\dronivo_atlas_export_suite_v3\dronivo_merged_final_excel\dronivo_merged_final.xlsxpip install openpyxl pandas
  File "<stdin>", line 1
    C:\Users\Cathrin\Documents\Dronivo\dronivo_atlas_export_suite_v3\dronivo_merged_final_excel\dronivo_merged_final.xlsxpip install openpyxl pandas
       ^
SyntaxError: unexpected character after line continuation character
>>> #!/usr/bin/env python3
>>> # -*- coding: utf-8 -*-
>>>
>>> """
... format_excel_dronivo.py
...
... Dieses Script formatiert und ergänzt die Excel-Datei
... 'Dronivo_Produkte_Logistik_v2.xlsx' mit:
... - Header-Styling
... - Spaltenbreiten
... - Dropdowns (Datenvalidierung) für Prod_ID, Kundennr, Status, Kategorie
... - Bedingte Formatierung (Kundenstatus farbig, Status/Verfügbar)
... - Icon-Set bei Verfügbarkeit (Ja/Nein) - umgesetzt via farbige Füllung
... - Einfügen von Standard-Formeln (VLOOKUP für Einzelpreis, Gesamtpreis, SUMIFS für Statistik)
... - Hyperlink-Styling
... - Tabellenfreundliche Einstellungen
...
... Anpassungen:
... - Datei- und Sheet-Namen ggf. anpassen
... - Script ist so geschrieben, dass es viele Zeilen (bis max_rows) unterstützt
... """
"\nformat_excel_dronivo.py\n\nDieses Script formatiert und ergänzt die Excel-Datei\n'Dronivo_Produkte_Logistik_v2.xlsx' mit:\n- Header-Styling\n- Spaltenbreiten\n- Dropdowns (Datenvalidierung) für Prod_ID, Kundennr, Status, Kategorie\n- Bedingte Formatierung (Kundenstatus farbig, Status/Verfügbar)\n- Icon-Set bei Verfügbarkeit (Ja/Nein) - umgesetzt via farbige Füllung\n- Einfügen von Standard-Formeln (VLOOKUP für Einzelpreis, Gesamtpreis, SUMIFS für Statistik)\n- Hyperlink-Styling\n- Tabellenfreundliche Einstellungen\n\nAnpassungen:\n- Datei- und Sheet-Namen ggf. anpassen\n- Script ist so geschrieben, dass es viele Zeilen (bis max_rows) unterstützt\n"
>>>
>>> import openpyxl
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>> from openpyxl import load_workbook
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>> from openpyxl.styles import Font, PatternFill, Alignment
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>> from openpyxl.utils import get_column_letter
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>> from openpyxl.formatting.rule import FormulaRule, CellIsRule, IconSetRule, ColorScaleRule
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>> from openpyxl.worksheet.datavalidation import DataValidation
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>> from openpyxl.styles.differential import DifferentialStyle
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'openpyxl'
>>>
>>> # Pfad zur existierenden Excel-Datei (anpassen falls nötig)
>>> IN_FILE = "Dronivo_Produkte_Logistik_v2.xlsx"
>>> OUT_FILE = "Dronivo_Produkte_Logistik_v2_formatiert.xlsx"
>>>
>>> # Maximale Zeilen, bis wohin Formeln/Validation angewendet werden (erweiterbar)
>>> max_rows = 1000
>>>
>>> # Lade die Arbeitsmappe
>>> wb = load_workbook(IN_FILE)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'load_workbook' is not defined
>>> # Nützliche Sheet-Namen (müssen in der Datei vorhanden sein)
>>> sheet_prod = wb["Produkte"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>> sheet_kunden = wb["Kunden"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>> sheet_auftraege = wb["Aufträge"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>> sheet_zoll = wb["Zoll_ATLAS"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>> sheet_doc = wb["Dokumentation"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>> sheet_spedition = wb["Spedition"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>> sheet_stat = wb["Statistik"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'wb' is not defined
>>>
>>> # --- Funktion: Header Styling & Spaltenbreite automatisch ---
>>> def style_headers_and_columns(ws):
...     # Header: fett, zentriert, Hintergrund hellgrau
...     header_fill = PatternFill(start_color="DDDDDD", end_color="DDDDDD", fill_type="solid")
...     header_font = Font(bold=True)
...     for cell in ws[1]:
...         cell.font = header_font
...         cell.fill = header_fill
...         cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
...
>>>     # automatische Spaltenbreite basierend auf Inhalt (einfach)
>>>     dims = {}
  File "<stdin>", line 1
    dims = {}
IndentationError: unexpected indent
>>>     for row in ws.rows:
  File "<stdin>", line 1
    for row in ws.rows:
IndentationError: unexpected indent
>>>         for cell in row:
  File "<stdin>", line 1
    for cell in row:
IndentationError: unexpected indent
>>>             if cell.value is not None:
  File "<stdin>", line 1
    if cell.value is not None:
IndentationError: unexpected indent
>>>                 dims[cell.column_letter] = max(dims.get(cell.column_letter, 0), len(str(cell.value)))
  File "<stdin>", line 1
    dims[cell.column_letter] = max(dims.get(cell.column_letter, 0), len(str(cell.value)))
IndentationError: unexpected indent
>>>     for col, value in dims.items():
  File "<stdin>", line 1
    for col, value in dims.items():
IndentationError: unexpected indent
>>>         ws.column_dimensions[col].width = min(max(value + 2, 10), 60)
  File "<stdin>", line 1
    ws.column_dimensions[col].width = min(max(value + 2, 10), 60)
IndentationError: unexpected indent
>>>
>>> # Style alle relevanten Sheets
>>> for s in [sheet_prod, sheet_kunden, sheet_auftraege, sheet_zoll, sheet_doc, sheet_spedition, sheet_stat]:
...     style_headers_and_columns(s)
...
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_prod' is not defined
>>> # --- Datenvalidierung / Dropdown-Listen einrichten ---
>>> # Hilfsfunktion: liefert Bereichsstring für Validierung (z.B. 'Produkte'!$A$2:$A$200)
>>> def col_range(sheet, col_letter, start_row=2, end_row=max_rows):
...     return f"'{sheet.title}'!${col_letter}${start_row}:${col_letter}${end_row}"
...
>>> # Erstelle DataValidation-Objekte
>>> # 1) Prod_ID-List: aus "Produkte" Spalte A
>>> prod_id_col = 'A'  # in Produkte sollte Prod_ID in Spalte A sein
>>> dv_prod = DataValidation(type="list", formula1=f"={col_range(sheet_prod, prod_id_col)}", allow_blank=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'DataValidation' is not defined
>>> dv_prod.error = "Ungültige Prod_ID - bitte aus Liste wählen"
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'dv_prod' is not defined
>>> dv_prod.errorTitle = "Ungültige Eingabe"
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'dv_prod' is not defined
>>>
>>> # 2) Kundennr-List: aus "Kunden" Spalte A
>>> kund_nr_col = 'A'
>>> dv_kunde = DataValidation(type="list", formula1=f"={col_range(sheet_kunden, kund_nr_col)}", allow_blank=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'DataValidation' is not defined
>>> dv_kunde.error = "Ungültige Kundennr - bitte aus Liste wählen"
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'dv_kunde' is not defined
>>>
>>> # 3) Status-Liste für Aufträge
>>> status_list = '"offen,versandt,bezahlt,storniert"'
>>> dv_status = DataValidation(type="list", formula1=status_list, allow_blank=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'DataValidation' is not defined
>>>
>>> # 4) Kundenkategorie (Neu, Bestand, Privat, Geschäftlich)
>>> cat_list = '"Neu,Bestand,Privat,Geschäftlich"'
>>> dv_kat = DataValidation(type="list", formula1=cat_list, allow_blank=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'DataValidation' is not defined
>>>
>>> # Füge Validation zur Aufträge-Tabelle hinzu (Prod_ID in Spalte C, Kundennr in B, Status in H)
>>> # We apply to rows 2..max_rows
>>> sheet = sheet_auftraege
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_auftraege' is not defined
>>> sheet.add_data_validation(dv_prod)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> sheet.add_data_validation(dv_kunde)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> sheet.add_data_validation(dv_status)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> sheet.add_data_validation(dv_kat)  # for other sheets if needed
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>>
>>> dv_prod.add(f"C2:C{max_rows}")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'dv_prod' is not defined
>>> dv_kunde.add(f"B2:B{max_rows}")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'dv_kunde' is not defined
>>> dv_status.add(f"H2:H{max_rows}")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'dv_status' is not defined
>>>
>>> # Kunden sheet: Kategorie in Spalte C (indexing may vary); ensure DataValidation added where appropriate
>>> sheet = sheet_kunden
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_kunden' is not defined
>>> # Find column index for 'Kategorie' (fall back to C)
>>> # Here assume column D? We will try to detect header
>>> headers_k = {cell.value:cell.column for cell in sheet_kunden[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_kunden' is not defined
>>> if "Kategorie" in headers_k:
...     kat_col_letter = get_column_letter(headers_k["Kategorie"])
...     dv_kat.add(f"{kat_col_letter}2:{kat_col_letter}{max_rows}")
... else:
...     dv_kat.add(f"C2:C{max_rows}")  # conservative fallback
...
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'headers_k' is not defined
>>> # --- Bedingte Formatierungen ---
>>> # 1) Kundenstatus farbig machen (z. B. Neu=hellgrün, Bestand=Hellblau, Privat=Hellgelb, Geschäftlich=Hellcyan)
>>> # We'll use FormulaRule to check cell text equality and apply fill
>>> status_fill_map = {
...     "Neu": "C6EFCE",         # hellgrün
...     "Bestand": "DDEBF7",     # hellblau
...     "Privat": "FFF2CC",      # hellgelb
...     "Geschäftlich": "D9EAD3" # hellgrün-2 / adjust
... }
>>>
>>> # Find Kundenstatus column letter
>>> sheet = sheet_kunden
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_kunden' is not defined
>>> headers = {cell.value:cell.column for cell in sheet[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> status_col_letter = get_column_letter(headers.get("Kundenstatus", headers.get("Kategorie", 3)))  # try common names
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> # Apply FormulaRule for each category
>>> for status, color in status_fill_map.items():
...     rule = FormulaRule(formula=[f'${status_col_letter}2="{status}"'], fill=PatternFill(start_color=color, end_color=color, fill_type="solid"))
...     sheet.conditional_formatting.add(f"{status_col_letter}2:{status_col_letter}{max_rows}", rule)
...
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
NameError: name 'FormulaRule' is not defined
>>> # 2) Auftrags-Status: offen->rot, bezahlt->grün, versandt->gelb
>>> sheet = sheet_auftraege
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_auftraege' is not defined
>>> hdrs = {cell.value:cell.column for cell in sheet[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> status_col = get_column_letter(hdrs.get("Status", 8))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> rule_offen = FormulaRule(formula=[f'${status_col}2="offen"'], fill=PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid"))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'FormulaRule' is not defined
>>> rule_bez = FormulaRule(formula=[f'${status_col}2="bezahlt"'], fill=PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid"))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'FormulaRule' is not defined
>>> rule_vers = FormulaRule(formula=[f'${status_col}2="versandt"'], fill=PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid"))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'FormulaRule' is not defined
>>> sheet.conditional_formatting.add(f"{status_col}2:{status_col}{max_rows}", rule_offen)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> sheet.conditional_formatting.add(f"{status_col}2:{status_col}{max_rows}", rule_bez)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> sheet.conditional_formatting.add(f"{status_col}2:{status_col}{max_rows}", rule_vers)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>>
>>> # 3) Verfügbar-Spalte (Produkte): Zellfarbe wenn "Ja" -> hellgrün, "Auf Anfrage" -> hellorange
>>> sheet = sheet_prod
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_prod' is not defined
>>> hdrs = {cell.value:cell.column for cell in sheet[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet' is not defined
>>> if "Verfügbar" in hdrs:
...     ver_col = get_column_letter(hdrs["Verfügbar"])
...     rule_yes = FormulaRule(formula=[f'${ver_col}2="Ja"'], fill=PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid"))
...     rule_req = FormulaRule(formula=[f'${ver_col}2="Auf Anfrage"'], fill=PatternFill(start_color="FFD966", end_color="FFD966", fill_type="solid"))
...     sheet.conditional_formatting.add(f"{ver_col}2:{ver_col}{max_rows}", rule_yes)
...     sheet.conditional_formatting.add(f"{ver_col}2:{ver_col}{max_rows}", rule_req)
...
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'hdrs' is not defined
>>> # --- Icon-like marking for Produkte Einsatzklassifizierung (set font color + bold for High-Tech/Dual-Use) ---
>>> if "Einsatzklassifizierung" in headers:
...     ek_col = get_column_letter(headers["Einsatzklassifizierung"])
...     # Bold + red tint for Dual-Use/High-Tech to highlight
...     rule_dual = FormulaRule(formula=[f'${ek_col}2="High-Tech / Dual-Use"'], fill=PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid"))
...     sheet_prod.conditional_formatting.add(f"{ek_col}2:{ek_col}{max_rows}", rule_dual)
...
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'headers' is not defined
>>> # --- Standard-Formeln eintragen (Aufträge: Einzelpreis + Gesamtpreis) ---
>>> # Determine column letters in Aufträge sheet
>>> headers_a = {cell.value:cell.column for cell in sheet_auftraege[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_auftraege' is not defined
>>> col_prod = get_column_letter(headers_a.get("Prod_ID", 3))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> col_einzel = get_column_letter(headers_a.get("Einzelpreis", 5))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> col_menge = get_column_letter(headers_a.get("Menge", 4))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> col_gesamt = get_column_letter(headers_a.get("Gesamtpreis", 6))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>>
>>> # Einfüge-VLOOKUP-Formel und Gesamtpreis für Zeilen 2..max_rows
>>> # Formel: =WENN(C2="";"";VLOOKUP(C2;Produkte!$A:$P;5;FALSE))
>>> for row in range(2, max_rows+1):
...     cell_v = sheet_auftraege[f"{col_einzel}{row}"]
...     cell_g = sheet_auftraege[f"{col_gesamt}{row}"]
...     # Wenn Prod_ID leer -> leer lassen, sonst VLOOKUP
...     cell_v.value = f'=WENN(${col_prod}{row}="";"";WENNFEHLER(VLOOKUP(${col_prod}{row};Produkte!$A:$P;5;FALSCH); "n/a"))'
...     # Gesamtpreis = Menge * Einzelpreis (wenn beide Daten vorhanden)
...     cell_g.value = f'=WENN(ODER(${col_menge}{row}="";${col_einzel}{row}="");"";${col_menge}{row}*${col_einzel}{row})'
...
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
NameError: name 'sheet_auftraege' is not defined
>>> # --- Statistik: Monats-Formeln einsetzen (SUMIFS) ---
>>> # Erwartet: Statistik sheet hat Spalten Jahr, Monat, Umsatz_EUR ...
>>> # Wir schreiben SUMIFS-Formeln in Statistik entsprechend 2025 Monate.
>>> stat_ws = sheet_stat
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_stat' is not defined
>>> # Find column indices for 'Umsatz_EUR' and 'Anz_Aufträge' if existing
>>> stat_hdrs = {cell.value:cell.column for cell in stat_ws[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'stat_ws' is not defined. Did you mean: 'status'?
>>> col_umsatz = get_column_letter(stat_hdrs.get("Umsatz_EUR", 3))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> col_anz = get_column_letter(stat_hdrs.get("Anz_Aufträge", 4))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'get_column_letter' is not defined
>>> months_map = {
...     1: "Jan",2:"Feb",3:"Mar",4:"Apr",5:"Mai",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Okt",11:"Nov",12:"Dez"
... }
>>> # Fill formulas for rows 2..13 (Monate)
>>> for i in range(1,13):
...     row = i+1
...     if i < 12:
...         sumifs = f'=SUMMEWENNS(Aufträge!{col_gesamt}:{col_gesamt};Aufträge!G:G;">="&DATUM(2025,{i},1);Aufträge!G:G;"<"&DATUM(2025,{i+1},1))'
...         countifs = f'=ZÄHLENWENNS(Aufträge!G:G;">="&DATUM(2025,{i},1);Aufträge!G:G;"<"&DATUM(2025,{i+1},1))'
...     else:
...         sumifs = f'=SUMMEWENNS(Aufträge!{col_gesamt}:{col_gesamt};Aufträge!G:G;">="&DATUM(2025,{i},1);Aufträge!G:G;"<"&DATUM(2026,1,1))'
...         countifs = f'=ZÄHLENWENNS(Aufträge!G:G;">="&DATUM(2025,{i},1);Aufträge!G:G;"<"&DATUM(2026,1,1))'
...     stat_ws[f"A{row}"].value = 2025
...     stat_ws[f"B{row}"].value = months_map[i]
...     stat_ws[f"{col_umsatz}{row}"].value = sumifs
...     stat_ws[f"{col_anz}{row}"].value = countifs
...
Traceback (most recent call last):
  File "<stdin>", line 4, in <module>
NameError: name 'col_gesamt' is not defined
>>> # --- Hyperlink Styling: make hyperlinks blue and underlined in Dokumentation sheet 'Doku_Link' column ---
>>> doc_ws = sheet_doc
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_doc' is not defined
>>> # find column for 'Doku_Link'
>>> doc_hdrs = {cell.value:cell.column for cell in doc_ws[1]}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'doc_ws' is not defined
>>> if "Doku_Link" in doc_hdrs:
...     link_col = get_column_letter(doc_hdrs["Doku_Link"])
...     for r in range(2, doc_ws.max_row+1):
...         c = doc_ws[f"{link_col}{r}"]
...         if c.value and isinstance(c.value, str) and c.value.startswith("=HYPERLINK"):
...             c.font = Font(underline="single", color="0563C1")  # Excel-standard link-blue
...
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'doc_hdrs' is not defined
>>> # --- Tabellen-Layout: Alternierende Zeilenfarbe für Lesbarkeit (Produkte & Aufträge) ---
>>> def banding(ws, start_row=2, end_row=None, color1="FFFFFF", color2="F7FBFF"):
...     if end_row is None:
...         end_row = max_rows
...     for r in range(start_row, min(ws.max_row, end_row)+1):
...         fill = PatternFill(start_color=color1 if (r % 2 == 0) else color2, end_color=color1 if (r % 2 == 0) else color2, fill_type="solid")
...         for c in ws[r]:
...             if c.value is not None:
...                 c.fill = fill
...
>>> banding(sheet_prod, 2, sheet_prod.max_row)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_prod' is not defined
>>> banding(sheet_auftraege, 2, sheet_auftraege.max_row)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'sheet_auftraege' is not defined
>>> banding(sheet_kunden, 2, sheet_kunden.max_row)

datei zoll #2

Description

-- coding: utf-8 --

Pfad zur existierenden Excel-Datei (anpassen falls nötig)

Maximale Zeilen, bis wohin Formeln/Validation angewendet werden (erweiterbar)

Lade die Arbeitsmappe

Nützliche Sheet-Namen (müssen in der Datei vorhanden sein)

--- Funktion: Header Styling & Spaltenbreite automatisch ---

Style alle relevanten Sheets

--- Datenvalidierung / Dropdown-Listen einrichten ---

Hilfsfunktion: liefert Bereichsstring für Validierung (z.B. 'Produkte'!$A$2:$A$200)

Erstelle DataValidation-Objekte

1) Prod_ID-List: aus "Produkte" Spalte A

2) Kundennr-List: aus "Kunden" Spalte A

3) Status-Liste für Aufträge

4) Kundenkategorie (Neu, Bestand, Privat, Geschäftlich)

Füge Validation zur Aufträge-Tabelle hinzu (Prod_ID in Spalte C, Kundennr in B, Status in H)

We apply to rows 2..max_rows

Kunden sheet: Kategorie in Spalte C (indexing may vary); ensure DataValidation added where appropriate

Find column index for 'Kategorie' (fall back to C)

Here assume column D? We will try to detect header

--- Bedingte Formatierungen ---

1) Kundenstatus farbig machen (z. B. Neu=hellgrün, Bestand=Hellblau, Privat=Hellgelb, Geschäftlich=Hellcyan)

We'll use FormulaRule to check cell text equality and apply fill

Find Kundenstatus column letter

Apply FormulaRule for each category

2) Auftrags-Status: offen->rot, bezahlt->grün, versandt->gelb

3) Verfügbar-Spalte (Produkte): Zellfarbe wenn "Ja" -> hellgrün, "Auf Anfrage" -> hellorange

--- Icon-like marking for Produkte Einsatzklassifizierung (set font color + bold for High-Tech/Dual-Use) ---

--- Standard-Formeln eintragen (Aufträge: Einzelpreis + Gesamtpreis) ---

Determine column letters in Aufträge sheet

Einfüge-VLOOKUP-Formel und Gesamtpreis für Zeilen 2..max_rows

Formel: =WENN(C2="";"";VLOOKUP(C2;Produkte!$A:$P;5;FALSE))

--- Statistik: Monats-Formeln einsetzen (SUMIFS) ---

Erwartet: Statistik sheet hat Spalten Jahr, Monat, Umsatz_EUR ...

Wir schreiben SUMIFS-Formeln in Statistik entsprechend 2025 Monate.

Find column indices for 'Umsatz_EUR' and 'Anz_Aufträge' if existing

Fill formulas for rows 2..13 (Monate)

--- Hyperlink Styling: make hyperlinks blue and underlined in Dokumentation sheet 'Doku_Link' column ---

find column for 'Doku_Link'

--- Tabellen-Layout: Alternierende Zeilenfarbe für Lesbarkeit (Produkte & Aufträge) ---

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions