"""Exports XLSX, ODS et CSV : vraies cellules, nombres en nombres, cellule vide = non renseigné."""
from __future__ import annotations
import csv
import io
import zipfile
from datetime import datetime, timezone
from typing import Any
from xml.sax.saxutils import escape as xml_escape
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from ..core import isoweek
FORMULA_TRIGGERS = ("=", "+", "-", "@", "\t", "\r")
HEADERS = ["Thématique de contrôle", "Nombre", "Observations", "Détails (taux / vitesses)"]
NOTE = "Cellule « Nombre » vide = non renseigné ; 0 = aucune infraction relevée."
def neutralize(text: str | None) -> str:
"""Empêche l'interprétation d'un texte comme formule dans Excel / LibreOffice (OWASP CSV injection)."""
if not text:
return ""
return "'" + text if text.startswith(FORMULA_TRIGGERS) else text
def _rows(data: dict[str, Any]) -> list[tuple[str, int | None, str, str]]:
return [(neutralize(r["label"]), r["value"], neutralize(r["observation"] or ""), neutralize(r["details_text"]))
for r in data["rows"]]
def _meta(data: dict[str, Any], user_name: str, now: datetime) -> list[tuple[str, str | int]]:
s = data["settings"]
return [
("Document", neutralize(s["document_title"])),
("Année ISO", data["iso_year"]),
("Semaine ISO", data["iso_week"]),
("Du (lundi)", data["start"]),
("Au (dimanche)", data["end"]),
("Statut", neutralize(data["status_line"])),
("Version validée", data["version_no"] if data["version_no"] else "aucune"),
("Exporté le", isoweek.fr_datetime(now)),
("Exporté par", neutralize(user_name)),
("Mention de diffusion", neutralize(s["diffusion_mention"]) or "—"),
("Convention", NOTE),
("Application", "EDCF 52 — Bilan d'activité"),
]
def base_filename(data: dict[str, Any]) -> str:
suffix = "" if data["official"] else "_BROUILLON"
return f"EDCF52_bilan_{data['iso_year']}-S{data['iso_week']:02d}{suffix}"
def to_xlsx(data: dict[str, Any], user_name: str) -> bytes:
now = datetime.now(timezone.utc)
wb = Workbook()
ws = wb.active
ws.title = f"Bilan S{data['iso_week']} {data['iso_year']}"
navy = "0A2A5E"
thin = Side(style="thin", color="C9D3E3")
ws["A1"] = neutralize(data["settings"]["document_title"])
ws["A1"].font = Font(name="Calibri", size=16, bold=True, color=navy)
ws.merge_cells("A1:D1")
ws["A2"] = f"Semaine {data['iso_week']} — du {data['start']} au {data['end']}"
ws["A2"].font = Font(size=12, bold=True)
ws["A3"] = neutralize(data["status_line"])
ws["A3"].font = Font(size=10, italic=not data["official"], color="B3172D" if not data["official"] else "5B6B82")
ws["A4"] = NOTE
ws["A4"].font = Font(size=9, color="5B6B82")
header_row = 6
ws.append([])
for col, title in enumerate(HEADERS, start=1):
c = ws.cell(row=header_row, column=col, value=title)
c.font = Font(bold=True, color="FFFFFF")
c.fill = PatternFill("solid", fgColor=navy)
c.alignment = Alignment(horizontal="center" if col == 2 else "left", vertical="center")
for i, (label, value, obs, details) in enumerate(_rows(data)):
r = header_row + 1 + i
ws.cell(row=r, column=1, value=label).font = Font(bold=True)
cv = ws.cell(row=r, column=2, value=value) # None → cellule réellement vide
cv.number_format = "0"
cv.alignment = Alignment(horizontal="center", vertical="top")
ws.cell(row=r, column=3, value=obs or None).alignment = Alignment(wrap_text=True, vertical="top")
ws.cell(row=r, column=4, value=details or None).alignment = Alignment(wrap_text=True, vertical="top")
for col in range(1, 5):
ws.cell(row=r, column=col).border = Border(bottom=thin)
if i % 2:
ws.cell(row=r, column=col).fill = PatternFill("solid", fgColor="F3F6FB")
ws.column_dimensions["A"].width = 44
ws.column_dimensions["B"].width = 11
ws.column_dimensions["C"].width = 70
ws.column_dimensions["D"].width = 34
ws.freeze_panes = f"A{header_row + 1}"
ws.page_setup.orientation = "portrait"
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.sheet_properties.pageSetUpPr.fitToPage = True
meta = wb.create_sheet("Métadonnées")
for k, v in _meta(data, user_name, now):
meta.append([k, v])
meta.cell(row=meta.max_row, column=1).font = Font(bold=True)
meta.column_dimensions["A"].width = 24
meta.column_dimensions["B"].width = 80
wb.properties.title = neutralize(data["settings"]["document_title"])
wb.properties.creator = "EDCF 52 — Bilan d'activité"
out = io.BytesIO()
wb.save(out)
return out.getvalue()
# --- ODS (OpenDocument écrit directement, sans dépendance) ---------------------------------------
_NS = ('xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" '
'xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" '
'xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" '
'xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" '
'xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" '
'xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" '
'xmlns:dc="http://purl.org/dc/elements/1.1/"')
def _text_cell(text: str | int | None, style: str | None = None) -> str:
st = f' table:style-name="{style}"' if style else ""
if text is None or text == "":
return f""
paras = "".join(f"{xml_escape(line)}" for line in str(text).split("\n"))
return f'{paras}'
def _num_cell(value: int | None, style: str) -> str:
if value is None:
return f''
return (f''
f"{value}")
def to_ods(data: dict[str, Any], user_name: str) -> bytes:
now = datetime.now(timezone.utc)
styles = (
''
''
''
''
''
''
''
''
''
''
''
''
''
''
)
rows = []
rows.append(f"{_text_cell(neutralize(data['settings']['document_title']), 'ceTitle')}")
rows.append(f"{_text_cell(f'Semaine {data['iso_week']} — du {data['start']} au {data['end']}', 'ceSub')}")
rows.append(f"{_text_cell(neutralize(data['status_line']), 'ceNote')}")
rows.append(f"{_text_cell(NOTE, 'ceNote')}")
rows.append("")
rows.append("" + "".join(_text_cell(h, "ceHead") for h in HEADERS) + "")
for label, value, obs, details in _rows(data):
rows.append("" + _text_cell(label, "ceLabel") + _num_cell(value, "ceNum")
+ _text_cell(obs, "ceWrap") + _text_cell(details, "ceWrap") + "")
meta_rows = "".join(
"" + _text_cell(k, "ceKey")
+ (_num_cell(v, "ceKey") if isinstance(v, int) else _text_cell(v)) + ""
for k, v in _meta(data, user_name, now))
sheet_name = xml_escape(f"Bilan S{data['iso_week']} {data['iso_year']}")
content = (
f'{styles}'
''
f''
''
''
+ "".join(rows) + ""
''
'' + meta_rows + ""
""
)
meta = (f''
f"EDCF 52 Bilan d'activite"
f"{xml_escape(neutralize(data['settings']['document_title']))}"
f"{now.strftime('%Y-%m-%dT%H:%M:%S')}"
"")
styles_xml = (f''
"")
manifest = (
''
''
''
''
''
''
"")
out = io.BytesIO()
with zipfile.ZipFile(out, "w") as z:
z.writestr(zipfile.ZipInfo("mimetype"), "application/vnd.oasis.opendocument.spreadsheet",
compress_type=zipfile.ZIP_STORED)
for name, body in (("content.xml", content), ("styles.xml", styles_xml), ("meta.xml", meta),
("META-INF/manifest.xml", manifest)):
z.writestr(name, body, compress_type=zipfile.ZIP_DEFLATED)
return out.getvalue()
def to_csv(data: dict[str, Any]) -> bytes:
buf = io.StringIO()
w = csv.writer(buf, delimiter=";", lineterminator="\r\n")
w.writerow(["Année ISO", "Semaine ISO", "Du", "Au", *HEADERS, "Statut"])
status = "validé" if data["official"] else "non validé"
for label, value, obs, details in _rows(data):
w.writerow([data["iso_year"], data["iso_week"], data["start"], data["end"], label,
"" if value is None else value, obs, details, status])
return ("" + buf.getvalue()).encode("utf-8")