from __future__ import annotations import json import shutil import subprocess import tempfile import uuid from pathlib import Path from typing import Any from openpyxl import load_workbook DEFAULT_TEMPLATE = Path(__file__).resolve().parent.parent / "data" / "templates" / "Auswertung_Evaluation.xls" INPUT_ROWS = [3, 4, 5, 9, 10, 15, 16, 17, 18, 21, 22, 23, 27, 28, 29] SUMMARY_ROWS = [7, 8, 9, 13, 14, 19, 20, 21, 22, 25, 26, 27, 31, 32, 33] START_COL = 3 # C MAX_PARTICIPANTS = 18 # C:T in template def _run(cmd: list[str], cwd: Path | None = None) -> str: proc = subprocess.run(cmd, cwd=cwd, check=False, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT) if proc.returncode != 0: raise RuntimeError(f"Command failed ({proc.returncode}): {' '.join(cmd)}\n{proc.stdout}") return proc.stdout def _convert_with_libreoffice(src: Path, outdir: Path, target: str) -> Path: outdir.mkdir(parents=True, exist_ok=True) _run(["libreoffice", "--headless", "--convert-to", target, "--outdir", str(outdir), str(src)]) expected = outdir / f"{src.stem}.{target.split(':', 1)[0]}" if not expected.exists(): matches = sorted(outdir.glob(f"{src.stem}.*")) if not matches: raise FileNotFoundError(f"LibreOffice hat keine Ausgabedatei für {src} erzeugt") return matches[-1] return expected def normalize_values(values: list[list[Any]]) -> list[list[int | None]]: if not values: raise ValueError("Keine Werte übergeben.") normalized: list[list[int | None]] = [] for ridx, row in enumerate(values, start=1): if len(row) != 15: raise ValueError(f"Fragebogen {ridx}: erwartet 15 Werte, erhalten {len(row)}") out_row: list[int | None] = [] for cidx, value in enumerate(row, start=1): if value in (None, "", "null"): out_row.append(None) continue try: intval = int(value) except Exception as exc: raise ValueError(f"Fragebogen {ridx}, Item {cidx}: ungültiger Wert {value!r}") from exc if intval < 1 or intval > 5: raise ValueError(f"Fragebogen {ridx}, Item {cidx}: Wert muss 1–5 oder leer sein, nicht {intval}") out_row.append(intval) normalized.append(out_row) if len(normalized) > MAX_PARTICIPANTS: raise ValueError(f"Template unterstützt maximal {MAX_PARTICIPANTS} Fragebögen, erhalten {len(normalized)}") return normalized def fill_template(values: list[list[Any]], output_dir: str | Path, template_path: str | Path | None = None, output_format: str = "xls") -> Path: """Fill the bfz XLS template with wide 15-item values and return the output file.""" output_format = output_format.lower().strip(".") if output_format not in {"xls", "xlsx"}: raise ValueError("output_format muss 'xls' oder 'xlsx' sein") values_norm = normalize_values(values) template = Path(template_path) if template_path else DEFAULT_TEMPLATE if not template.exists(): raise FileNotFoundError(f"Template nicht gefunden: {template}") outbase = Path(output_dir) outbase.mkdir(parents=True, exist_ok=True) job_tmp = outbase / f"work_{uuid.uuid4().hex}" job_tmp.mkdir(parents=True, exist_ok=True) # Always work in xlsx internally. Old .xls is converted first; LibreOffice also recalculates later. if template.suffix.lower() == ".xlsx": xlsx_template = job_tmp / "template.xlsx" shutil.copy2(template, xlsx_template) else: copied = job_tmp / template.name shutil.copy2(template, copied) xlsx_template = _convert_with_libreoffice(copied, job_tmp, "xlsx") wb = load_workbook(xlsx_template) if "Kurs Auswertung einzeln" not in wb.sheetnames or "SEMINAR" not in wb.sheetnames: raise ValueError("Template muss die Sheets 'Kurs Auswertung einzeln' und 'SEMINAR' enthalten.") ws = wb["Kurs Auswertung einzeln"] sem = wb["SEMINAR"] for item_idx, rownum in enumerate(INPUT_ROWS): for participant_idx in range(MAX_PARTICIPANTS): cell = ws.cell(row=rownum, column=START_COL + participant_idx) if participant_idx < len(values_norm): cell.value = values_norm[participant_idx][item_idx] else: cell.value = None # Header labels: only show actually filled questionnaire columns. for participant_idx in range(MAX_PARTICIPANTS): ws.cell(row=1, column=START_COL + participant_idx).value = participant_idx + 1 if participant_idx < len(values_norm) else None # Summary sheet: write count cells directly. Keep formulas for totals/averages so the template still behaves like itself. for item_idx, sem_row in enumerate(SUMMARY_ROWS): vals = [row[item_idx] for row in values_norm if row[item_idx] is not None] counts = {k: vals.count(k) for k in range(1, 6)} for k in range(1, 6): sem.cell(row=sem_row, column=2 + k).value = counts[k] if counts[k] else None # C:G sem.cell(row=sem_row, column=8).value = None # H was legacy value 6, unused here sem.cell(row=sem_row, column=9).value = f"=SUM(C{sem_row}:H{sem_row})" sem.cell(row=sem_row, column=10).value = f"=SUM(C{sem_row}*1,D{sem_row}*2,E{sem_row}*3,F{sem_row}*4,G{sem_row}*5,H{sem_row}*6)/I{sem_row}" sem["I3"] = len(values_norm) try: wb.calculation.fullCalcOnLoad = True wb.calculation.forceFullCalc = True wb.calculation.calcMode = "auto" except Exception: pass edited = job_tmp / "Auswertung_Evaluation_mit_smileys.xlsx" wb.save(edited) # Force formula recalculation by round-tripping through LibreOffice. recalc_dir = job_tmp / "recalc" recalc_xlsx = _convert_with_libreoffice(edited, recalc_dir, "xlsx") if output_format == "xlsx": final = outbase / "Auswertung_Evaluation_mit_smileys.xlsx" shutil.copy2(recalc_xlsx, final) else: xls_file = _convert_with_libreoffice(recalc_xlsx, job_tmp / "xls", "xls") final = outbase / "Auswertung_Evaluation_mit_smileys.xls" shutil.copy2(xls_file, final) return final def values_to_csv(values: list[list[Any]], out_path: str | Path) -> Path: import csv values_norm = normalize_values(values) out = Path(out_path) with out.open("w", encoding="utf-8-sig", newline="") as f: writer = csv.writer(f, delimiter=";") writer.writerow(["fragebogen_id"] + [f"i{i:02d}" for i in range(1, 16)]) for idx, row in enumerate(values_norm, start=1): writer.writerow([idx] + ["" if v is None else v for v in row]) return out def parse_values_json(text: str) -> list[list[int | None]]: payload = json.loads(text) if isinstance(payload, dict) and "values" in payload: payload = payload["values"] if not isinstance(payload, list): raise ValueError("values muss eine Liste von Fragebogen-Zeilen sein") return normalize_values(payload)