"""Erzeugt Excel-Beispieldateien für Tests und den manuellen Probelauf.""" from __future__ import annotations from datetime import datetime from pathlib import Path from openpyxl import Workbook from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter FIXTURE_DIR = Path(__file__).resolve().parent / "fixtures" B_HEADERS = [ "AN = Adressnr.", "A = Anrede", "Z1 = Titel", "Z2 = Vorname", "Z3 = Nachname", "Z4 = Namenszusatz", "Z5 = Namenszusatz2", "Z6 = Straße + Hausnummer", "Z7 = PLZ", "Z8 = Ort", "Z9 = Land", "P1 = Briefanrede", "KEP = Kundenmailadresse. Privat", "KEB = Kundenmailadresse. Business", "MV = Marke Volvo", "MJ = Modelljahr", "M = Modell", "V=Variante", "S = letztes Servicedatum", "FGN = Fahrgestellnr.", "AKZ = amtl. KZ", "EZL = Erstzulassungsdatum", ] A_HEADERS = [ "Adressnr.", "Anrede", "Titel", "Haltervorname", "Nachname", "Straße", "Hausnummer", "PLZ", "Ort", "Land", "Briefanrede", "E-Mail", "Marke", "Modelljahr", "Modell", "Variante", "Servicedatum", "Fahrgestellnr.", "Kennzeichen", "Erstzulassung", "Kundennummer", "Firma", ] def _success_rows() -> list[list[object]]: return [ [ 1001, "Herr", "", "Max", "Mustermann", "Holtenauer Straße", "12", "24103", "Kiel", "Deutschland", "", "max@test.de", "Volvo", 2020, "XC60", "Momentum", "15.01.2024", "YV1XYZ123 ABC", "ki-sd221", "01.03.2020", "K1001", "", ], [ 1002, "Frau", "", "Anna", "Schmidt", "Düppelstraße", "5", "24105", "Kiel", "DE", "", "anna@test.de", "Volvo", 2018, "V40", "", "10.10.2023", "YV1ANNASCHMIDT1", "KI-AS 88", "01.08.2018", "K1002", "", ], [ 1003, "Herr", "Dr.", "Peter", "Weber", "Preetzer Straße", "80", "24143", "Kiel", "Deutschland", "", "peter@test.de", "Volvo", 2022, "XC90", "Inscription", "", "YV1PETERWEBER01", "KI-PW 7", "12.12.2022", "K1003", "", ], [ 1004, "Firma", "", "", "Meier AG", "Industriegebiet", "4", "24145", "Kiel", "Deutschland", "", "info@meier.de", "Volvo", 2019, "S90", "", "", "YV1MEIERAG00001", "KI-MA 12", "05.05.2019", "K1004", "Meier AG", ], [ 1005, "Herr", "", "Thomas", "Blank", "-", "", "-", "-", "Deutschland", "", "thomas@test.de", "Volvo", 2020, "XC60", "", "", "YV1THOMASBLANK1", "KI TB 221", "01.03.2020", "K1005", "", ], [ 2001, "Firma", "", "", "", "", "", "", "", "Deutschland", "", "info@condok.de", "Volvo", 2021, "XC40", "", "", "YV1CONDOK11111", "KI-CD 1", "01.06.2021", "K2001", "Condok GmbH", ], [ 2002, "Firma", "", "", "", "Wellseedamm", "12-14", "24145", "Kiel", "Deutschland", "", "info@condok.de", "Volvo", 2021, "XC90", "", "", "YV1CONDOK22222", "KI-CD 2", "01.06.2021", "K2002", "Condok GmbH", ], [ 1007, "Frau", "", "Lisa", "Hahn", "Fördestraße", "21", "24944", "Flensburg", "Deutschland", "", "lisa@test.de", "Volvo", 2021, "C40", "", "", "YV1LISAHAN00001", "FL-LH 21", "03.03.2021", "K1007", "", ], [ 1008, "Herr", "", "Otto", "Lang", "Sophienblatt", "1", "24103", "Kiel", "Deutschland", "", "N/A", "Volvo", 2017, "V90", "", "", "YV1OTTOLANG0001", "KI-OL 3", "07.07.2017", "K1008", "", ], [ 1009, "Frau", "", "Eva", "Klein", "Dänische Straße", "3", "24103", "Kiel", "Deutschland", "Sehr geehrte Frau Klein", "eva@test.de", "Volvo", 2023, "EX30", "", "", "YV1EVAKLEIN0001", "KI-EK 9", "01.01.2023", "K1009", "", ], ] def write_table_a(path: Path, rows: list[list[object]]) -> None: wb = Workbook() ws = wb.active ws.title = "Kunden" ws.append(A_HEADERS) for row in rows: ws.append(row) wb.save(path) wb.close() def write_template_b(path: Path) -> None: wb = Workbook() ws = wb.active ws.title = "Import" header_font = Font(name="Calibri", bold=True, size=11, color="FFFFFF") header_fill = PatternFill("solid", fgColor="1F4E79") data_font = Font(name="Calibri", size=11) data_align = Alignment(vertical="center") thin = Border( left=Side(style="thin", color="D0D7DE"), right=Side(style="thin", color="D0D7DE"), top=Side(style="thin", color="D0D7DE"), bottom=Side(style="thin", color="D0D7DE"), ) ws.append(B_HEADERS) for col in range(1, len(B_HEADERS) + 1): cell = ws.cell(1, col) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(vertical="center", wrap_text=True) examples = [ [ 1505, "Herr", "", "Thomas", "Blank", "", "", "Russeer Weg 9", "24111", "Kiel", "Deutschland", "", "thomas@test.de", "thomas@test.de", "Volvo", 2020, "XC60", "", datetime(2024, 1, 15), "YV1THOMASBLANK1", "KI-TB 221", datetime(2020, 3, 1), ], [ 9999, "Frau", "", "Beispiel", "Kundin", "", "", "Musterstraße 1", "20095", "Hamburg", "Deutschland", "Sehr geehrte Frau Kundin", "beispiel@test.de", "beispiel@test.de", "Volvo", 2019, "V60", "", datetime(2023, 5, 1), "YV1EXAMPLE999XX", "HH-EX 999", datetime(2019, 1, 1), ], ] for row in examples: ws.append(row) date_cols = {19, 22} for row in range(2, 4): for col in range(1, len(B_HEADERS) + 1): cell = ws.cell(row, col) cell.font = data_font cell.alignment = data_align cell.border = thin if col in date_cols: cell.number_format = "DD.MM.YYYY" if col == 9: cell.number_format = "@" widths = [18, 12, 10, 14, 16, 14, 14, 28, 10, 16, 14, 32, 28, 28, 14, 12, 12, 14, 20, 22, 14, 20] for idx, width in enumerate(widths, start=1): ws.column_dimensions[get_column_letter(idx)].width = width ws.freeze_panes = "A2" ws.auto_filter.ref = f"A1:{get_column_letter(len(B_HEADERS))}3" ws.row_dimensions[1].height = 22 ws.row_dimensions[2].height = 18 wb.save(path) wb.close() def build_all(target: Path | None = None) -> dict[str, Path]: directory = target or FIXTURE_DIR directory.mkdir(parents=True, exist_ok=True) template = directory / "template_b.xlsx" success_a = directory / "table_a_success.xlsx" missing_a = directory / "table_a_missing_address.xlsx" conflict_a = directory / "table_a_conflict.xlsx" write_template_b(template) rows = _success_rows() write_table_a(success_a, rows) missing_rows = [list(r) for r in rows] missing_rows.append( [ 3001, "Herr", "", "Ohne", "Adresse", "", "", "", "", "Deutschland", "", "ohne@test.de", "Volvo", 2020, "XC60", "", "", "YV1OHNEADRESSE1", "KI-OA 1", "01.01.2020", "K3001", "Unbekannte Firma XYZ", ] ) write_table_a(missing_a, missing_rows) conflict_rows = [list(r) for r in rows[:2]] conflict_rows[0][18] = "KI-XX 1" conflict_rows[1][18] = "KI-XX 1" conflict_rows[0][17] = "YV1CONFLICTAAA1" conflict_rows[1][17] = "YV1CONFLICTBBB1" write_table_a(conflict_a, conflict_rows) return { "template_b": template, "table_a_success": success_a, "table_a_missing_address": missing_a, "table_a_conflict": conflict_a, } if __name__ == "__main__": created = build_all() for name, path in created.items(): print(f"{name}: {path}")