SheetJS
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 "
", 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 "
", line 1, in ModuleNotFoundError: No module named 'openpyxl' from openpyxl import load_workbook Traceback (most recent call last): File " ", line 1, in ModuleNotFoundError: No module named 'openpyxl' from openpyxl.styles import Font, PatternFill, Alignment Traceback (most recent call last): File " ", line 1, in ModuleNotFoundError: No module named 'openpyxl' from openpyxl.utils import get_column_letter Traceback (most recent call last): File " ", line 1, in ModuleNotFoundError: No module named 'openpyxl' from openpyxl.formatting.rule import FormulaRule, CellIsRule, IconSetRule, ColorScaleRule Traceback (most recent call last): File " ", line 1, in ModuleNotFoundError: No module named 'openpyxl' from openpyxl.worksheet.datavalidation import DataValidation Traceback (most recent call last): File " ", line 1, in ModuleNotFoundError: No module named 'openpyxl' from openpyxl.styles.differential import DifferentialStyle Traceback (most recent call last): File " ", line 1, in 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 "
", line 1, in 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 "
", line 1, in NameError: name 'wb' is not defined sheet_kunden = wb["Kunden"] Traceback (most recent call last): File " ", line 1, in NameError: name 'wb' is not defined sheet_auftraege = wb["Aufträge"] Traceback (most recent call last): File " ", line 1, in NameError: name 'wb' is not defined sheet_zoll = wb["Zoll_ATLAS"] Traceback (most recent call last): File " ", line 1, in NameError: name 'wb' is not defined sheet_doc = wb["Dokumentation"] Traceback (most recent call last): File " ", line 1, in NameError: name 'wb' is not defined sheet_spedition = wb["Spedition"] Traceback (most recent call last): File " ", line 1, in NameError: name 'wb' is not defined sheet_stat = wb["Statistik"] Traceback (most recent call last): File " ", line 1, in 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 "
", line 1 dims = {} IndentationError: unexpected indent for row in ws.rows: File " ", line 1 for row in ws.rows: IndentationError: unexpected indent for cell in row: File " ", line 1 for cell in row: IndentationError: unexpected indent if cell.value is not None: File " ", 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 " ", 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 " ", line 1 for col, value in dims.items(): IndentationError: unexpected indent ws.column_dimensions[col].width = min(max(value + 2, 10), 60) File " ", 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 "
", line 1, in 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 "
", line 1, in NameError: name 'DataValidation' is not defined dv_prod.error = "Ungültige Prod_ID - bitte aus Liste wählen" Traceback (most recent call last): File " ", line 1, in NameError: name 'dv_prod' is not defined dv_prod.errorTitle = "Ungültige Eingabe" Traceback (most recent call last): File " ", line 1, in 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 "
", line 1, in NameError: name 'DataValidation' is not defined dv_kunde.error = "Ungültige Kundennr - bitte aus Liste wählen" Traceback (most recent call last): File " ", line 1, in 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 "
", line 1, in 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 "
", line 1, in 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 "
", line 1, in NameError: name 'sheet_auftraege' is not defined sheet.add_data_validation(dv_prod) Traceback (most recent call last): File " ", line 1, in NameError: name 'sheet' is not defined sheet.add_data_validation(dv_kunde) Traceback (most recent call last): File " ", line 1, in NameError: name 'sheet' is not defined sheet.add_data_validation(dv_status) Traceback (most recent call last): File " ", line 1, in NameError: name 'sheet' is not defined sheet.add_data_validation(dv_kat) # for other sheets if needed Traceback (most recent call last): File " ", line 1, in NameError: name 'sheet' is not defined dv_prod.add(f"C2:C{max_rows}") Traceback (most recent call last): File "
", line 1, in NameError: name 'dv_prod' is not defined dv_kunde.add(f"B2:B{max_rows}") Traceback (most recent call last): File " ", line 1, in NameError: name 'dv_kunde' is not defined dv_status.add(f"H2:H{max_rows}") Traceback (most recent call last): File " ", line 1, in 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 "
", line 1, in 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 "
", line 1, in 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 " ", line 1, in 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 "
", line 1, in NameError: name 'sheet_kunden' is not defined headers = {cell.value:cell.column for cell in sheet[1]} Traceback (most recent call last): File " ", line 1, in 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 " ", line 1, in 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 "
", line 2, in 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 "
", line 1, in NameError: name 'sheet_auftraege' is not defined hdrs = {cell.value:cell.column for cell in sheet[1]} Traceback (most recent call last): File " ", line 1, in NameError: name 'sheet' is not defined status_col = get_column_letter(hdrs.get("Status", 8)) Traceback (most recent call last): File " ", line 1, in 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 " ", line 1, in 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 " ", line 1, in 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 " ", line 1, in 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 " ", line 1, in 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 " ", line 1, in 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 " ", line 1, in 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 "
", line 1, in NameError: name 'sheet_prod' is not defined hdrs = {cell.value:cell.column for cell in sheet[1]} Traceback (most recent call last): File " ", line 1, in 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 " ", line 1, in 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 "
", line 1, in 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 "
", line 1, in NameError: name 'sheet_auftraege' is not defined col_prod = get_column_letter(headers_a.get("Prod_ID", 3)) Traceback (most recent call last): File " ", line 1, in NameError: name 'get_column_letter' is not defined col_einzel = get_column_letter(headers_a.get("Einzelpreis", 5)) Traceback (most recent call last): File " ", line 1, in NameError: name 'get_column_letter' is not defined col_menge = get_column_letter(headers_a.get("Menge", 4)) Traceback (most recent call last): File " ", line 1, in NameError: name 'get_column_letter' is not defined col_gesamt = get_column_letter(headers_a.get("Gesamtpreis", 6)) Traceback (most recent call last): File " ", line 1, in 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 "
", line 2, in 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 "
", line 1, in 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 "
", line 1, in 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 " ", line 1, in 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 " ", line 1, in 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 "
", line 4, in 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 "
", line 1, in 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 "
", line 1, in 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 " ", line 1, in 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 "
", line 1, in NameError: name 'sheet_prod' is not defined banding(sheet_auftraege, 2, sheet_auftraege.max_row) Traceback (most recent call last): File " ", line 1, in NameError: name 'sheet_auftraege' is not defined banding(sheet_kunden, 2, sheet_kunden.max_row)
