Spaces:
Running on Zero
Running on Zero
| """ | |
| report.py — Exportación del informe a Excel (.xlsx) y CSV (separador «;», listo para Excel en español). | |
| """ | |
| from __future__ import annotations | |
| import csv | |
| import math | |
| from pathlib import Path | |
| from openpyxl import Workbook | |
| from openpyxl.styles import Alignment, Font, PatternFill | |
| from openpyxl.utils import get_column_letter | |
| COLUMNS = [ | |
| ("archivo", "Archivo"), | |
| ("duracion", "Duración"), | |
| ("estado_inicial", "Estado inicial"), | |
| ("estado_final", "Estado final"), | |
| ("fallos", "Qué fallaba"), | |
| ("nivel", "Nivel de procesado usado"), | |
| ("cambios", "Qué se ha cambiado"), | |
| ("notas", "Qué se ha hecho (detalle)"), | |
| ("formato", "Formato original"), | |
| ("pico_antes", "Pico antes (dBFS)"), ("pico_despues", "Pico después (dBFS)"), | |
| ("rms_antes", "RMS voz antes (dBFS)"), ("rms_despues", "RMS voz después (dBFS)"), | |
| ("ruido_rel_antes", "Ruido relativo antes (dB)"), ("ruido_rel_despues", "Ruido relativo después (dB)"), | |
| ("snr_antes", "SNR antes (dB)"), ("snr_despues", "SNR después (dB)"), | |
| ("sil_ini_antes", "Silencio inicial antes (s)"), ("sil_ini_despues", "Silencio inicial después (s)"), | |
| ("sil_fin_antes", "Silencio final antes (s)"), ("sil_fin_despues", "Silencio final después (s)"), | |
| ("dnsmos_antes", "Nota de calidad antes"), ("dnsmos_despues", "Nota de calidad después"), | |
| ("d_pico", "Δ Pico (dB)"), ("d_rms", "Δ RMS voz (dB)"), ("d_ruido", "Δ Ruido de fondo (dB)"), | |
| ("d_snr", "Δ SNR (dB)"), ("d_dnsmos", "Δ Nota de calidad"), | |
| ("ruidos_quitados", "Ruidos sueltos quitados"), | |
| ] | |
| def _num(v, nd=2): | |
| if v is None or (isinstance(v, float) and (math.isnan(v) or math.isinf(v))): | |
| return None | |
| return round(float(v), nd) | |
| def _delta(a, b): | |
| a, b = _num(a), _num(b) | |
| return None if a is None or b is None else round(b - a, 2) | |
| def build_rows(items: list[dict]) -> list[dict]: | |
| """items: [{'nombre','duracion_s','estado_inicial','estado_final','formato','antes':m,'despues':m,'fallos','notas'}]""" | |
| rows = [] | |
| for it in items: | |
| a, d = it.get("antes") or {}, it.get("despues") or {} | |
| row = { | |
| "archivo": it["nombre"], | |
| "duracion": (it.get("duracion_s") or 0) / 86400, # fracción de día = formato hora de Excel | |
| "duracion_s": it.get("duracion_s") or 0, | |
| "estado_inicial": it.get("estado_inicial", ""), | |
| "estado_final": it.get("estado_final", ""), | |
| "formato": it.get("formato", ""), | |
| "fallos": ", ".join(it.get("fallos") or []), | |
| "notas": " · ".join(it.get("notas") or []), | |
| } | |
| for key, mk in (("pico", "pico_dbfs"), ("rms", "rms_voz_dbfs"), ("ruido_rel", "ruido_relativo_db"), | |
| ("snr", "snr_db"), ("sil_ini", "silencio_ini_s"), ("sil_fin", "silencio_fin_s")): | |
| row[f"{key}_antes"] = _num(a.get(mk)) | |
| row[f"{key}_despues"] = _num(d.get(mk)) | |
| row["dnsmos_antes"] = _num((a.get("dnsmos") or {}).get("OVRL")) | |
| row["dnsmos_despues"] = _num((d.get("dnsmos") or {}).get("OVRL")) | |
| for key, mk in (("d_pico", "pico_dbfs"), ("d_rms", "rms_voz_dbfs"), ("d_ruido", "ruido_fondo_dbfs"), | |
| ("d_snr", "snr_db")): | |
| row[key] = _delta(a.get(mk), d.get(mk)) | |
| row["d_dnsmos"] = _delta(row["dnsmos_antes"], row["dnsmos_despues"]) | |
| row["nivel"] = it.get("nivel", "") | |
| row["cambios"] = ", ".join(it.get("cambios") or []) | |
| row["ruidos_quitados"] = next((n.split("—", 1)[-1].strip() for n in it.get("notas") or [] | |
| if n.startswith("Ruidos sueltos en las pausas sustituidos")), "") | |
| rows.append(row) | |
| return rows | |
| def write_xlsx(path: Path, rows: list[dict], perfil: str, resumen: list[tuple[str, str]]) -> Path: | |
| wb = Workbook() | |
| ws = wb.active | |
| ws.title = "Ficheros" | |
| head_fill = PatternFill("solid", fgColor="01696F") | |
| ws.append([label for _, label in COLUMNS]) | |
| for c in ws[1]: | |
| c.font = Font(bold=True, color="FFFFFF") | |
| c.fill = head_fill | |
| c.alignment = Alignment(wrap_text=True, vertical="center") | |
| ok_fill, bad_fill = PatternFill("solid", fgColor="E3F1D9"), PatternFill("solid", fgColor="F6DCEB") | |
| for r in rows: | |
| ws.append([r.get(k) for k, _ in COLUMNS]) | |
| i = ws.max_row | |
| ws.cell(i, 2).number_format = "[h]:mm:ss" | |
| for col in (3, 4): | |
| cell = ws.cell(i, col) | |
| if cell.value: | |
| cell.fill = ok_fill if str(cell.value).startswith(("PASA", "CORREGIDO", "YA CUMPL", "CUMPL")) else bad_fill | |
| n = ws.max_row | |
| ws.append(["TOTAL", f"=SUM(B2:B{n})"]) | |
| ws.cell(ws.max_row, 1).font = Font(bold=True) | |
| ws.cell(ws.max_row, 2).number_format = "[h]:mm:ss" | |
| ws.cell(ws.max_row, 2).font = Font(bold=True) | |
| widths = {1: 34, 2: 11, 3: 16, 4: 24, 5: 40, 6: 14, 7: 40, 8: 90, 9: 34} | |
| for i in range(1, len(COLUMNS) + 1): | |
| ws.column_dimensions[get_column_letter(i)].width = widths.get(i, 13) | |
| ws.freeze_panes = "B2" | |
| ws2 = wb.create_sheet("Resumen") | |
| ws2.append(["Perfil", perfil]) | |
| for k, v in resumen: | |
| ws2.append([k, v]) | |
| ws2.column_dimensions["A"].width = 30 | |
| ws2.column_dimensions["B"].width = 60 | |
| for row in ws2.iter_rows(min_col=1, max_col=1): | |
| row[0].font = Font(bold=True) | |
| wb.save(path) | |
| return path | |
| def write_csv(path: Path, rows: list[dict]) -> Path: | |
| def fmt(v): | |
| if isinstance(v, float): | |
| return f"{v:.2f}".replace(".", ",") | |
| return "" if v is None else str(v) | |
| keys = [k for k, _ in COLUMNS] | |
| with open(path, "w", newline="", encoding="utf-8-sig") as f: | |
| w = csv.writer(f, delimiter=";") | |
| w.writerow([label for _, label in COLUMNS] + ["Duración (s)"]) | |
| for r in rows: | |
| vals = [fmt(r.get(k)) for k in keys] | |
| s = int(round(r["duracion_s"])) | |
| vals[1] = f"{s // 3600}:{s % 3600 // 60:02d}:{s % 60:02d}" | |
| w.writerow(vals + [fmt(r["duracion_s"])]) | |
| return path | |