"""Erzeugt aus einer realen QAF-Arbeitsmappe eine anonymisierte, strukturtreue Fixture. Das Original wird ausschliesslich GELESEN. Die Ausgabe ist eine neue Datei. ── Warum openpyxl und nicht ExcelJS ──────────────────────────────────────────── ExcelJS kann die realen 8.8-Arbeitsmappen nicht einmal unveraendert zurueckschreiben (Abbruch in cf-rule-xform bei einer bedingten Formatierungs- regel ohne `formulae`). openpyxl schafft den Round-Trip auf Zellebene verlustfrei; gemessen an 220705_QAF_HICE_Vergabe.xlsx bleiben 12 Blaetter, 6 veryHidden, 43 benannte Bereiche, 208 verbundene Zellen, 41 bedingte Formate, 26 Datenpruefungen, 1707 Formeln, 1509 Zahlen und 4833 Texte unveraendert. Bekannte Einbusse, bewusst in Kauf genommen und im Manifest ausgewiesen: die ZIP-Eintraege gehen von 71 auf 35 zurueck. Verloren gehen `customXml/*` sowie die Erweiterungen zu bedingter Formatierung und Datenpruefung. Das sind Dokument-Metadaten ohne fachliche Bedeutung — und sie koennen ihrerseits Kundendaten tragen, weshalb ihr Wegfall fuer eine Fixture eher erwuenscht ist. ── Warum Werte ERSETZT und nicht skaliert werden ─────────────────────────────── Eine Skalierung aller Zahlen mit einem Faktor s bricht die Arithmetik: bei Preis x Menge = Summe stuende s^2 im Produkt, aber s in einer hartcodierten Summe. Eine Skalierung NUR der erkannten Geldzellen wiederum laesst alles stehen, was der Parser nicht zuordnet — gemessen 759 von 1513 Zahlzellen. Dort blieben echte Werte im Klartext. Deshalb: jede Zahl in einer Zelle OHNE Formel wird deterministisch ersetzt. Formelzellen bleiben unangetastet und rechnen sich aus den ersetzten Eingaben konsistent neu. Die Groessenordnung bleibt erhalten (sonst waere die Fixture fachlich unplausibel und Toleranzen/Reconciliation liefen ins Leere), die Ziffern darunter nicht. Damit gilt: KEIN Originalwert wandert in die Fixture. Die Leck-Pruefung am Ende verifiziert das gegen die vollstaendige Menge der Originalwerte, nicht nur gegen die zugeordneten. """ from __future__ import annotations import argparse import hashlib import json import re import sys from pathlib import Path # openpyxl wird BEWUSST erst in den Funktionen importiert, die es brauchen. # # Der Grund: `anonymise_workbook_xml.py` holt sich von hier `TextAnonymiser`, # `is_characteristic` und `replace_number` — keines davon braucht openpyxl. Ein # Import auf Modulebene machte die XML-Fassung trotzdem davon abhaengig, und in # CI ist openpyxl nicht installiert: die Regressionssperre fiel dort mit # `ModuleNotFoundError` in 28 von 28 Tests aus, waehrend sie lokal gruen war. # # Sachlich gehoert es auch so: die XML-Fassung existiert genau deshalb, weil # openpyxl den Container umschreibt und Formel-Caches verliert. Sie soll die # Bibliothek nicht einmal laden muessen. # ── Deterministische Ersetzung ────────────────────────────────────────────── def _digest(seed: str, *parts: str) -> int: h = hashlib.sha256((seed + "|" + "|".join(parts)).encode("utf-8")).digest() return int.from_bytes(h[:8], "big") def replace_number(value: float, seed: str, sheet: str, coord: str) -> float: """Ersetzt eine Zahl deterministisch unter Erhalt von Vorzeichen, Groessenordnung und Nachkommastellen-Charakter. Null bleibt Null: eine 0 traegt keine Information ueber die Kalkulation, und sie ist fachlich bedeutsam (Nullkosten-Zugang ist ein eigener Pflichtfall). Ganze Zahlen bleiben ganz — sonst wuerde aus einer Stueckzahl eine Kommazahl und der Parser saehe eine Struktur, die es im Original nicht gibt. """ if value == 0: return 0 rnd = _digest(seed, sheet, coord) sign = -1 if value < 0 else 1 magnitude = abs(value) # Groessenordnung erhalten: 1234.5 -> irgendetwas in [1000, 10000) exp = len(str(int(magnitude))) - 1 if magnitude >= 1 else -1 lo = 10 ** exp hi = 10 ** (exp + 1) span = hi - lo new = lo + (rnd % max(int(span * 1000), 1)) / 1000.0 if float(value).is_integer(): candidate = max(1, int(new)) # Bei kleinen ganzen Zahlen ist der Zielbereich winzig (eine 1 landet in # [1,10)), also trifft die Ersetzung den Originalwert regelmaessig wieder. # Das ist kein theoretisches Risiko: die zellgenaue Leck-Pruefung hat auf # der HICE-Mappe 13 solcher Faelle gemeldet. Deshalb hier ausweichen, # statt es der Pruefung zu ueberlassen. if candidate == int(magnitude): candidate = int(lo) + ((candidate - int(lo) + 1) % max(int(span), 1)) if candidate == int(magnitude): candidate += 1 return sign * candidate decimals = len(str(value).split(".")[-1]) if "." in str(value) else 2 result = round(new, min(decimals, 6)) if result == magnitude: result = round(result + 10 ** -min(decimals, 6), min(decimals, 6)) return sign * result class TextAnonymiser: """Pseudonymisiert Texte strukturerhaltend. Formular-Beschriftungen bleiben stehen: sie sind Vokabular der BMW-Vorlage, kein Lieferantengeheimnis — und ohne sie erkennt der Parser die Mappe nicht mehr, womit die Fixture nichts mehr pruefen wuerde. Gleiche Texte bekommen dasselbe Pseudonym (sonst waeren Gleichteile keine mehr), und Klammer-Varianten werden getrennt behandelt (sonst faellt die Umbenennungs-Erkennung weg). Pseudonyme teilen sich bewusst KEINEN Wortstamm: "Pos01"/"Pos02" statt "Teil 01"/"Teil 02", sonst gilt jeder Sammelname fuer jedes Teil als Treffer und die Zuordnungsregeln greifen anders als im Original. """ def __init__(self, labels: set[str], seed: str): self.labels = labels self.labels_normalised = {self._norm(x) for x in labels} self.seed = seed self._map: dict[str, str] = {} self._variants: dict[str, str] = {} @staticmethod def _norm(s: str) -> str: return re.sub(r"\s+", " ", s.strip().lower()) def is_label(self, text: str) -> bool: return self._norm(text) in self.labels_normalised def pseudonym(self, text: str) -> str: raw = text.strip() if raw == "": return text if self.is_label(raw): return text # Reine Zahlen-/Datumstexte unveraendert lassen: sie sind Struktur, # und ein Pseudonym daraus machte aus einem Textdatum einen Namen. # # ABER NUR KURZE. Eine Telefonnummer besteht ebenfalls nur aus Ziffern # und Trennzeichen und fiel damit unter diese Ausnahme — in der ersten # Golden-Fixture stand deshalb eine reale Rufnummer im Klartext, waehrend # Name, E-Mail und Firma daneben korrekt ersetzt waren. Ein Datum hat # hoechstens acht Ziffern (TT.MM.JJJJ); alles darueber ist keine # Datumsangabe mehr, sondern eine Kennung. if re.fullmatch(r"[\d\s.,:/-]+", raw) and len(re.sub(r"\D", "", raw)) <= 8: return text m = re.fullmatch(r"(.*?)\s*\(([^)]*)\)\s*", raw) if m: return f"{self._token(m.group(1))} ({self._variant(m.group(2))})" return self._token(raw) def pseudonym_erzwungen(self, text: str) -> str: """Wie pseudonym(), aber ohne die Beschriftungs-Ausnahme. Fuer Stellen, an denen ein Wort keine Beschriftung sein KANN, auch wenn es zufaellig wie eine aussieht — Dokument-Metadaten etwa. Siehe anonymise_workbook_xml.py, ersetze_weiteren_eintrag(). """ raw = text.strip() if raw == "": return text m = re.fullmatch(r"(.*?)\s*\(([^)]*)\)\s*", raw) if m: return f"{self._token(m.group(1))} ({self._variant(m.group(2))})" return self._token(raw) def _token(self, s: str) -> str: key = self._norm(s) if key == "": return s if key not in self._map: self._map[key] = f"Pos{len(self._map) + 1:03d}" return self._map[key] def _variant(self, s: str) -> str: key = self._norm(s) if key not in self._variants: self._variants[key] = f"var{len(self._variants) + 1}" return self._variants[key] # ── Hauptlauf ─────────────────────────────────────────────────────────────── def anonymise(src: Path, dst: Path, labels: set[str], seed: str) -> dict: from openpyxl import load_workbook from openpyxl.utils import get_column_letter wb = load_workbook(src, data_only=False, keep_links=True) original_numbers: set[float] = set() original_texts: set[str] = set() stats = {"numbers_replaced": 0, "numbers_kept_zero": 0, "texts_pseudonymised": 0, "texts_kept_label": 0, "formulas_untouched": 0} for ws in wb.worksheets: for row in ws.iter_rows(): for cell in row: v = cell.value if v is None: continue # Formelzellen bleiben unangetastet — sie rechnen sich aus den # ersetzten Eingaben konsistent neu. if isinstance(v, str) and v.startswith("="): stats["formulas_untouched"] += 1 continue coord = f"{get_column_letter(cell.column)}{cell.row}" if isinstance(v, bool): continue if isinstance(v, (int, float)): original_numbers.add(float(v)) if v == 0: stats["numbers_kept_zero"] += 1 continue cell.value = replace_number(float(v), seed, ws.title, coord) stats["numbers_replaced"] += 1 elif isinstance(v, str): original_texts.add(v) if TextAnonymiser._norm(v) == "": continue anon = ANON.pseudonym(v) if anon == v: stats["texts_kept_label"] += 1 else: cell.value = anon stats["texts_pseudonymised"] += 1 dst.parent.mkdir(parents=True, exist_ok=True) wb.save(dst) wb.close() leaks = verify_no_leak(dst, src, original_numbers, original_texts, labels) return {"stats": stats, "leaks": leaks, "sha256": hashlib.sha256(dst.read_bytes()).hexdigest()} def is_characteristic(value: float) -> bool: """Traegt dieser Zahlenwert genug Information, um ein Geheimnis zu sein? Bei 1507 ersetzten Zahlen in einem kleinen Wertebereich kollidieren triviale Werte zwangslaeufig: eine 12 im Original und eine neu erzeugte 12 an ganz anderer Stelle sind kein Leck, sondern Arithmetik. Eine Pruefung, die darauf anschlaegt, meldet 39 Fehlalarme und wird deshalb irgendwann abgeschaltet — das waere der eigentliche Schaden. Charakteristisch ist ein Wert, wenn er sich nicht zufaellig wiederholt: ab drei Nachkommastellen oder ab Betraegen >= 1000. Genau dort sitzen die Kalkulationsgroessen (397.9058, 12.9631), nicht bei Stueckzahlen und Indizes. """ if value == 0: return False if abs(value) >= 1000: return True s = repr(float(value)) return "." in s and len(s.split(".")[-1].rstrip("0")) >= 3 def verify_no_leak(dst: Path, src: Path, original_numbers: set[float], original_texts: set[str], labels: set[str]) -> list[str]: """Zwei Pruefungen, beide notwendig. 1. ZELLGENAU: Jede Zelle, die im Original einen Wert trug, muss in der Ausgabe einen anderen tragen (ausser Null, Formeln und Beschriftungen). Das ist der harte Nachweis, dass tatsaechlich ersetzt wurde — und er faellt nicht auf Kollisionen herein. 2. GLOBAL fuer charakteristische Werte: Ein Betrag mit vielen Nachkomma- stellen oder >= 1000 darf NIRGENDWO in der Ausgabe auftauchen, auch nicht an anderer Stelle. Sonst koennte ein verschobener Wert das Geheimnis weitertragen. Asymmetrisch mit Absicht: ersetzt wird nach Regel, geprueft wird gegen alle Originalwerte. Andersherum bliebe jede Zelle unbemerkt, die die Regel nicht erfasst. """ labels_norm = {re.sub(r"\s+", " ", x.strip().lower()) for x in labels} leaks: list[str] = [] def is_trivial_text(t: str) -> bool: norm = re.sub(r"\s+", " ", t.strip().lower()) return norm == "" or norm in labels_norm or bool(re.fullmatch(r"[\d\s.,:/-]+", t.strip())) from openpyxl import load_workbook from openpyxl.utils import get_column_letter wb_src = load_workbook(src, data_only=False, keep_links=True) wb_out = load_workbook(dst, data_only=False, keep_links=True) # (1) zellgenau for ws_src in wb_src.worksheets: ws_out = wb_out[ws_src.title] for row in ws_src.iter_rows(): for cell in row: v = cell.value if v is None or isinstance(v, bool): continue if isinstance(v, str) and v.startswith("="): continue coord = f"{get_column_letter(cell.column)}{cell.row}" new = ws_out[coord].value if isinstance(v, (int, float)): if v != 0 and new == v: leaks.append(f"unveraendert {ws_src.title}!{coord}: {v}") elif isinstance(v, str) and not is_trivial_text(v) and new == v: leaks.append(f"unveraendert {ws_src.title}!{coord}: {v[:32]}") # (2) global fuer charakteristische Werte out_numbers: set[float] = set() out_texts: set[str] = set() for ws in wb_out.worksheets: for row in ws.iter_rows(): for cell in row: v = cell.value if isinstance(v, bool) or v is None: continue if isinstance(v, (int, float)): out_numbers.add(float(v)) elif isinstance(v, str) and not v.startswith("="): out_texts.add(v) wb_src.close() wb_out.close() for n in original_numbers & out_numbers: if is_characteristic(n): leaks.append(f"charakteristische Zahl weiterhin vorhanden: {n}") for t in original_texts & out_texts: if not is_trivial_text(t): leaks.append(f"Originaltext weiterhin vorhanden: {t[:40]}") return leaks def main() -> int: ap = argparse.ArgumentParser(description=__doc__) ap.add_argument("source") ap.add_argument("dest") ap.add_argument("--labels", required=True, help="JSON mit Formular-Beschriftungen") ap.add_argument("--seed", default="qaf-golden-2026") args = ap.parse_args() src, dst = Path(args.source), Path(args.dest) if not src.exists(): print(f"Quelle fehlt: {src}", file=sys.stderr) return 2 labels = set(json.loads(Path(args.labels).read_text("utf-8"))["labels"]) global ANON ANON = TextAnonymiser(labels, args.seed) result = anonymise(src, dst, labels, args.seed) print(json.dumps(result["stats"], indent=2, ensure_ascii=False)) if result["leaks"]: print(f"\nABBRUCH — {len(result['leaks'])} Originalwert(e) in der Ausgabe:", file=sys.stderr) for leak in sorted(set(result["leaks"]))[:15]: print(" " + leak, file=sys.stderr) dst.unlink(missing_ok=True) return 1 print(f"\nFixture: {dst}") print(f"SHA-256: {result['sha256']}") print("Leck-Pruefung bestanden (kein Originalwert, keine Originalzeichenkette).") return 0 if __name__ == "__main__": raise SystemExit(main())