"""create_excel — build a professional .xlsx from a structured spec (or a named template).""" from __future__ import annotations import io import re from datetime import date from typing import Any from openpyxl import Workbook, load_workbook from openpyxl.chart import BarChart, LineChart, PieChart, Reference from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter from openpyxl.utils.cell import column_index_from_string, coordinate_from_string from openpyxl.workbook.defined_name import DefinedName from pydantic import BaseModel, Field from app.llm.schemas import Artifact, ToolResult from app.services import files as file_service from app.tools.excel_spec import normalise_spec from app.tools.excel_templates import TEMPLATES from app.tools.registry import ToolContext, registry UQO_BLUE = "0F6180" UQO_BLUE_DARK = "0A4A63" UQO_BLUE_LIGHT = "E6F0F4" UQO_GREEN = "78BE20" INPUT_FILL = PatternFill("solid", fgColor="DDEBF7") INPUT_FONT = Font(color="1F4E79", bold=False) HEADER_FILL = PatternFill("solid", fgColor=UQO_BLUE) HEADER_FONT = Font(color="FFFFFF", bold=True) TITLE_FONT = Font(color=UQO_BLUE_DARK, bold=True, size=14) THIN = Side(style="thin", color="B7C4D1") TOTAL_BORDER = Border(top=Side(style="medium", color=UQO_BLUE_DARK)) FORMATS = { # Excel renders separators per user locale (fr-CA → « 185 000,00 $ »). "currency": '#,##0.00 "$";[Red]-#,##0.00 "$"', "percent": "0.00%", "number": "#,##0.00", "integer": "0", "area": '#,##0.00 "m²"', "factor": "0.000000", "text": "@", } class ExcelArgs(BaseModel): spec: dict[str, Any] | None = None template: str | None = Field(None, description=f"One of {', '.join(TEMPLATES)}") params: dict[str, Any] = Field(default_factory=dict) filename: str | None = None def _coord(anchor: str) -> tuple[int, int]: col_letter, row = coordinate_from_string(anchor) return column_index_from_string(col_letter), row def _safe_defined_name(name: str) -> str: n = re.sub(r"[^A-Za-z0-9_]", "_", name) if not n or n[0].isdigit(): n = "_" + n return n[:60] def _apply_format(cell: Any, kind: str) -> None: fmt = FORMATS.get(kind) if fmt and kind != "text": cell.number_format = fmt def _render_sheet(wb: Workbook, ws: Any, sheet: dict[str, Any]) -> dict[str, Any]: ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 40 for i in range(2, 30): ws.column_dimensions[get_column_letter(i)].width = 17 title = sheet.get("title") or sheet.get("name", "Feuille") ws["A1"] = title ws["A1"].font = TITLE_FONT ws["A2"] = "UQO-Chat · outil pédagogique · ne constitue pas une évaluation professionnelle" ws["A2"].font = Font(color="5B6B7B", italic=True, size=9) # Inputs inputs = sheet.get("inputs") or [] if inputs: first_row = min(_coord(i["cell"])[1] for i in inputs) if sheet.get("inputs_title") and first_row > 3: ws.cell(row=first_row - 1, column=1, value=sheet["inputs_title"]).font = Font( bold=True, color=UQO_BLUE) for inp in inputs: col, row = _coord(inp["cell"]) label_cell = ws.cell(row=row, column=max(1, col - 1), value=inp.get("label", "")) label_cell.font = Font(color="1A2B3C") cell = ws.cell(row=row, column=col, value=inp.get("value")) is_formula = isinstance(inp.get("value"), str) and str(inp["value"]).startswith("=") if not is_formula: cell.fill = INPUT_FILL cell.font = INPUT_FONT else: cell.font = Font(bold=True) _apply_format(cell, inp.get("format", "number")) if inp.get("name"): dn = DefinedName(_safe_defined_name(inp["name"]), attr_text=f"'{ws.title}'!${get_column_letter(col)}${row}") wb.defined_names[dn.name] = dn # Tables max_row_used = 3 for table in sheet.get("tables") or []: col0, row0 = _coord(table.get("anchor", "A4")) columns = table.get("columns") or [] for j, coldef in enumerate(columns): c = ws.cell(row=row0, column=col0 + j, value=coldef.get("header", "")) c.fill = HEADER_FILL c.font = HEADER_FONT c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) c.border = Border(bottom=THIN) ws.row_dimensions[row0].height = 30 row_formats = {int(k): v for k, v in (table.get("row_formats") or {}).items()} bold_rows = set(table.get("bold_rows") or []) for i, row in enumerate(table.get("rows") or []): r = row0 + 1 + i for j, value in enumerate(row): if isinstance(value, (dict, list, tuple)): value = str(value)[:250] c = ws.cell(row=r, column=col0 + j, value=value) kind = columns[j].get("type", "text") if j < len(columns) else "text" if j == 0 and table.get("first_col_format"): kind = table["first_col_format"] if j > 0 and i in row_formats: kind = row_formats[i] _apply_format(c, kind) c.border = Border(bottom=Side(style="hair", color="D5DDE5")) if i in bold_rows: c.font = Font(bold=True) if i % 2 == 1: c.fill = PatternFill("solid", fgColor="F5F7FA") max_row_used = max(max_row_used, r) totals = table.get("totals") if totals: r = row0 + 1 + len(table.get("rows") or []) lc = ws.cell(row=r, column=col0, value=totals.get("label", "Total")) lc.font = Font(bold=True, color=UQO_BLUE_DARK) lc.border = TOTAL_BORDER vcol = col0 + (len(columns) - 1 if len(columns) == 2 else 1) vc = ws.cell(row=r, column=vcol, value=totals.get("formula")) vc.font = Font(bold=True, color=UQO_BLUE_DARK) vc.border = TOTAL_BORDER _apply_format(vc, totals.get("format", columns[min(1, len(columns) - 1)].get( "type", "currency") if columns else "currency")) for j in range(len(columns)): ws.cell(row=r, column=col0 + j).border = TOTAL_BORDER if totals.get("name"): dn = DefinedName(_safe_defined_name(totals["name"]), attr_text=f"'{ws.title}'!${get_column_letter(vcol)}${r}") wb.defined_names[dn.name] = dn max_row_used = max(max_row_used, r) # Charts for ch in sheet.get("charts") or []: try: _add_chart(ws, ch) except Exception: # noqa: BLE001 — a bad chart must not break the workbook continue # Notes notes = sheet.get("notes") or [] if notes: r = max_row_used + 2 ws.cell(row=r, column=1, value="Notes").font = Font(bold=True, color=UQO_BLUE) for k, note in enumerate(notes, 1): c = ws.cell(row=r + k, column=1, value=f"• {note}") c.alignment = Alignment(wrap_text=True, vertical="top") ws.merge_cells(start_row=r + k, start_column=1, end_row=r + k, end_column=6) ws.freeze_panes = "A3" return {"name": ws.title, "rows": ws.max_row, "tables": len(sheet.get("tables") or []), "inputs": len(inputs)} def _ref(ws: Any, rng: str) -> Reference: a, b = rng.split(":") c1, r1 = _coord(a) c2, r2 = _coord(b) return Reference(ws, min_col=c1, min_row=r1, max_col=c2, max_row=r2) def _add_chart(ws: Any, ch: dict[str, Any]) -> None: kind = ch.get("type", "bar") chart: Any if kind == "line": chart = LineChart() elif kind == "pie": chart = PieChart() else: chart = BarChart() chart.type = "col" chart.title = ch.get("title", "") chart.height, chart.width = 8, 14 values_range = ch.get("values_range") or ch.get("data_range") if not values_range: return chart.add_data(_ref(ws, values_range), titles_from_data=False) if ch.get("categories_range"): chart.set_categories(_ref(ws, ch["categories_range"])) if kind != "pie": chart.legend = None ws.add_chart(chart, ch.get("anchor", "E4")) def _readme(wb: Workbook, spec: dict[str, Any], summaries: list[dict[str, Any]]) -> None: ws = wb.create_sheet("Lisez-moi", 0) ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 28 ws.column_dimensions["B"].width = 90 ws["A1"] = "UQO-Chat — classeur pédagogique" ws["A1"].font = TITLE_FONT rows = [ ("Objectif", spec.get("objective") or (spec["sheets"][0].get("title") if spec.get("sheets") else "")), ("Cours", spec.get("course", "IMM1003 / IMM1033 — UQO")), ("Date", date.today().isoformat()), ("Feuilles", ", ".join(s["name"] for s in summaries)), ("Convention", "Cellules bleu pâle = hypothèses modifiables ; les autres cellules sont calculées par formule."), ("Avertissement", "Outil pédagogique — ne constitue pas une évaluation professionnelle. " "Seul un évaluateur agréé (É.A.) membre de l'OEAQ peut signer un rapport d'évaluation."), ] for i, (k, v) in enumerate(rows, 3): ws.cell(row=i, column=1, value=k).font = Font(bold=True, color=UQO_BLUE) c = ws.cell(row=i, column=2, value=v) c.alignment = Alignment(wrap_text=True, vertical="top") for i, note in enumerate(spec.get("hypotheses") or [], 3 + len(rows) + 1): ws.cell(row=i, column=1, value="Hypothèse").font = Font(color="5B6B7B") ws.cell(row=i, column=2, value=note) def build_workbook(spec: dict[str, Any]) -> tuple[bytes, list[dict[str, Any]]]: wb = Workbook() wb.remove(wb.active) summaries: list[dict[str, Any]] = [] for sheet in spec.get("sheets") or [{"name": "Feuille 1"}]: name = re.sub(r"[\[\]\*\?/\\:]", " ", str(sheet.get("name", "Feuille")))[:31] or "Feuille" ws = wb.create_sheet(name) summaries.append(_render_sheet(wb, ws, sheet)) _readme(wb, spec, summaries) wb.active = 1 if len(wb.sheetnames) > 1 else 0 buf = io.BytesIO() wb.save(buf) data = buf.getvalue() # Re-read to verify integrity (no empty sheets, formulas parse). check = load_workbook(io.BytesIO(data)) for ws in check.worksheets[1:]: if ws.max_row < 2: raise ValueError(f"Feuille vide : {ws.title}") return data, summaries def _preview(data: bytes, max_rows: int = 16, max_cols: int = 10) -> dict[str, Any]: wb = load_workbook(io.BytesIO(data)) sheets = [] for ws in wb.worksheets[:8]: rows: list[list[Any]] = [] for r in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_rows), max_col=min(max(ws.max_column, 1), max_cols), values_only=True): rows.append([("" if v is None else v) for v in r]) sheets.append({"name": ws.title, "rows": rows, "max_row": ws.max_row, "max_col": ws.max_column}) first = next((s for s in sheets if s["name"] != "Lisez-moi"), sheets[0]) return {"sheet": first["name"], "rows": first["rows"], "sheets": [s["name"] for s in sheets], "all": sheets} async def run(args: dict[str, Any], ctx: ToolContext) -> ToolResult: await ctx.report("running", "Création du classeur Excel…") spec = args.get("spec") if args.get("template"): tpl = TEMPLATES.get(args["template"]) if not tpl: return ToolResult(content=f"Gabarit inconnu. Gabarits : {', '.join(TEMPLATES)}.", error=True) spec = tpl(args.get("params") or {}) if spec and not args.get("template"): spec = normalise_spec(spec) if not spec or not spec.get("sheets"): return ToolResult(content="Fournis `spec.sheets` (liste de feuilles avec tables/rows) ou " f"`template` parmi : {', '.join(TEMPLATES)}.", error=True) total_rows = sum(len(t.get("rows") or []) for sh in spec["sheets"] for t in sh.get("tables") or []) if total_rows > 2000: return ToolResult(content="Classeur trop volumineux (> 2000 lignes) : découpe en plusieurs " "appels ou génère les lignes avec execute_python (openpyxl).", error=True) filename = args.get("filename") or spec.get("filename") or "classeur.xlsx" if not filename.lower().endswith(".xlsx"): filename += ".xlsx" data, summaries = build_workbook(spec) rec = await file_service.store_artifact(ctx.user_id, ctx.conversation_id, filename, data, ftype="xlsx") preview = _preview(data) art = Artifact(type="xlsx", file_id=rec.id, filename=rec.filename, url=f"/api/v1/files/{rec.id}", preview=preview) desc = "; ".join(f"« {s['name']} » ({s['tables']} tableau(x), {s['inputs']} hypothèse(s))" for s in summaries) return ToolResult( content=f"Classeur « {rec.filename} » créé ({len(data) // 1024} Ko). Feuilles : {desc}. " "Formules vivantes ; feuille Lisez-moi ajoutée. Le fichier est affiché à l'étudiant " "avec un bouton Télécharger.", artifacts=[art], payload={"filename": rec.filename, "file_id": rec.id, "sheets": summaries, "preview": preview}, meta={"summary": f"Excel : {rec.filename}"}, ) registry.register("create_excel", run, ExcelArgs, heavy=True)