1"""create_excel — build a professional .xlsx from a structured spec (or a named template)."""23from __future__ import annotations45import io6import re7from datetime import date8from typing import Any910from openpyxl import Workbook, load_workbook11from openpyxl.chart import BarChart, LineChart, PieChart, Reference12from openpyxl.styles import Alignment, Border, Font, PatternFill, Side13from openpyxl.utils import get_column_letter14from openpyxl.utils.cell import column_index_from_string, coordinate_from_string15from openpyxl.workbook.defined_name import DefinedName16from pydantic import BaseModel, Field1718from app.llm.schemas import Artifact, ToolResult19from app.services import files as file_service20from app.tools.excel_templates import TEMPLATES21from app.tools.registry import ToolContext, registry2223UQO_BLUE = "00467F"24UQO_BLUE_DARK = "003057"25UQO_BLUE_LIGHT = "E6EEF5"26UQO_GREEN = "78BE20"27INPUT_FILL = PatternFill("solid", fgColor="DDEBF7")28INPUT_FONT = Font(color="1F4E79", bold=False)29HEADER_FILL = PatternFill("solid", fgColor=UQO_BLUE)30HEADER_FONT = Font(color="FFFFFF", bold=True)31TITLE_FONT = Font(color=UQO_BLUE_DARK, bold=True, size=14)32THIN = Side(style="thin", color="B7C4D1")33TOTAL_BORDER = Border(top=Side(style="medium", color=UQO_BLUE_DARK))3435FORMATS = {36 # Excel renders separators per user locale (fr-CA → « 185 000,00 $ »).37 "currency": '#,##0.00 "$";[Red]-#,##0.00 "$"',38 "percent": "0.00%",39 "number": "#,##0.00",40 "integer": "0",41 "area": '#,##0.00 "m²"',42 "factor": "0.000000",43 "text": "@",44}454647class ExcelArgs(BaseModel):48 spec: dict[str, Any] | None = None49 template: str | None = Field(None, description=f"One of {', '.join(TEMPLATES)}")50 params: dict[str, Any] = Field(default_factory=dict)51 filename: str | None = None525354def _coord(anchor: str) -> tuple[int, int]:55 col_letter, row = coordinate_from_string(anchor)56 return column_index_from_string(col_letter), row575859def _safe_defined_name(name: str) -> str:60 n = re.sub(r"[^A-Za-z0-9_]", "_", name)61 if not n or n[0].isdigit():62 n = "_" + n63 return n[:60]646566def _apply_format(cell: Any, kind: str) -> None:67 fmt = FORMATS.get(kind)68 if fmt and kind != "text":69 cell.number_format = fmt707172def _render_sheet(wb: Workbook, ws: Any, sheet: dict[str, Any]) -> dict[str, Any]:73 ws.sheet_view.showGridLines = False74 ws.column_dimensions["A"].width = 4475 for c in "BCDEFGHIJK":76 ws.column_dimensions[c].width = 1877 title = sheet.get("title") or sheet.get("name", "Feuille")78 ws["A1"] = title79 ws["A1"].font = TITLE_FONT80 ws["A2"] = "UQO-Chat · outil pédagogique · ne constitue pas une évaluation professionnelle"81 ws["A2"].font = Font(color="5B6B7B", italic=True, size=9)8283 # Inputs84 inputs = sheet.get("inputs") or []85 if inputs:86 first_row = min(_coord(i["cell"])[1] for i in inputs)87 if sheet.get("inputs_title") and first_row > 3:88 ws.cell(row=first_row - 1, column=1, value=sheet["inputs_title"]).font = Font(89 bold=True, color=UQO_BLUE)90 for inp in inputs:91 col, row = _coord(inp["cell"])92 label_cell = ws.cell(row=row, column=max(1, col - 1), value=inp.get("label", ""))93 label_cell.font = Font(color="1A2B3C")94 cell = ws.cell(row=row, column=col, value=inp.get("value"))95 is_formula = isinstance(inp.get("value"), str) and str(inp["value"]).startswith("=")96 if not is_formula:97 cell.fill = INPUT_FILL98 cell.font = INPUT_FONT99 else:100 cell.font = Font(bold=True)101 _apply_format(cell, inp.get("format", "number"))102 if inp.get("name"):103 dn = DefinedName(_safe_defined_name(inp["name"]),104 attr_text=f"'{ws.title}'!${get_column_letter(col)}${row}")105 wb.defined_names[dn.name] = dn106107 # Tables108 max_row_used = 3109 for table in sheet.get("tables") or []:110 col0, row0 = _coord(table.get("anchor", "A4"))111 columns = table.get("columns") or []112 for j, coldef in enumerate(columns):113 c = ws.cell(row=row0, column=col0 + j, value=coldef.get("header", ""))114 c.fill = HEADER_FILL115 c.font = HEADER_FONT116 c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)117 c.border = Border(bottom=THIN)118 ws.row_dimensions[row0].height = 30119 row_formats = {int(k): v for k, v in (table.get("row_formats") or {}).items()}120 bold_rows = set(table.get("bold_rows") or [])121 for i, row in enumerate(table.get("rows") or []):122 r = row0 + 1 + i123 for j, value in enumerate(row):124 c = ws.cell(row=r, column=col0 + j, value=value)125 kind = columns[j].get("type", "text") if j < len(columns) else "text"126 if j == 0 and table.get("first_col_format"):127 kind = table["first_col_format"]128 if j > 0 and i in row_formats:129 kind = row_formats[i]130 _apply_format(c, kind)131 c.border = Border(bottom=Side(style="hair", color="D5DDE5"))132 if i in bold_rows:133 c.font = Font(bold=True)134 if i % 2 == 1:135 c.fill = PatternFill("solid", fgColor="F5F7FA")136 max_row_used = max(max_row_used, r)137 totals = table.get("totals")138 if totals:139 r = row0 + 1 + len(table.get("rows") or [])140 lc = ws.cell(row=r, column=col0, value=totals.get("label", "Total"))141 lc.font = Font(bold=True, color=UQO_BLUE_DARK)142 lc.border = TOTAL_BORDER143 vcol = col0 + (len(columns) - 1 if len(columns) == 2 else 1)144 vc = ws.cell(row=r, column=vcol, value=totals.get("formula"))145 vc.font = Font(bold=True, color=UQO_BLUE_DARK)146 vc.border = TOTAL_BORDER147 _apply_format(vc, totals.get("format", columns[min(1, len(columns) - 1)].get(148 "type", "currency") if columns else "currency"))149 for j in range(len(columns)):150 ws.cell(row=r, column=col0 + j).border = TOTAL_BORDER151 if totals.get("name"):152 dn = DefinedName(_safe_defined_name(totals["name"]),153 attr_text=f"'{ws.title}'!${get_column_letter(vcol)}${r}")154 wb.defined_names[dn.name] = dn155 max_row_used = max(max_row_used, r)156157 # Charts158 for ch in sheet.get("charts") or []:159 try:160 _add_chart(ws, ch)161 except Exception: # noqa: BLE001 — a bad chart must not break the workbook162 continue163164 # Notes165 notes = sheet.get("notes") or []166 if notes:167 r = max_row_used + 2168 ws.cell(row=r, column=1, value="Notes").font = Font(bold=True, color=UQO_BLUE)169 for k, note in enumerate(notes, 1):170 c = ws.cell(row=r + k, column=1, value=f"• {note}")171 c.alignment = Alignment(wrap_text=True, vertical="top")172 ws.merge_cells(start_row=r + k, start_column=1, end_row=r + k, end_column=6)173 ws.freeze_panes = "A3"174 return {"name": ws.title, "rows": ws.max_row, "tables": len(sheet.get("tables") or []),175 "inputs": len(inputs)}176177178def _ref(ws: Any, rng: str) -> Reference:179 a, b = rng.split(":")180 c1, r1 = _coord(a)181 c2, r2 = _coord(b)182 return Reference(ws, min_col=c1, min_row=r1, max_col=c2, max_row=r2)183184185def _add_chart(ws: Any, ch: dict[str, Any]) -> None:186 kind = ch.get("type", "bar")187 chart: Any188 if kind == "line":189 chart = LineChart()190 elif kind == "pie":191 chart = PieChart()192 else:193 chart = BarChart()194 chart.type = "col"195 chart.title = ch.get("title", "")196 chart.height, chart.width = 8, 14197 values_range = ch.get("values_range") or ch.get("data_range")198 if not values_range:199 return200 chart.add_data(_ref(ws, values_range), titles_from_data=False)201 if ch.get("categories_range"):202 chart.set_categories(_ref(ws, ch["categories_range"]))203 if kind != "pie":204 chart.legend = None205 ws.add_chart(chart, ch.get("anchor", "E4"))206207208def _readme(wb: Workbook, spec: dict[str, Any], summaries: list[dict[str, Any]]) -> None:209 ws = wb.create_sheet("Lisez-moi", 0)210 ws.sheet_view.showGridLines = False211 ws.column_dimensions["A"].width = 28212 ws.column_dimensions["B"].width = 90213 ws["A1"] = "UQO-Chat — classeur pédagogique"214 ws["A1"].font = TITLE_FONT215 rows = [216 ("Objectif", spec.get("objective") or (spec["sheets"][0].get("title") if spec.get("sheets") else "")),217 ("Cours", spec.get("course", "IMM1003 / IMM1033 — UQO")),218 ("Date", date.today().isoformat()),219 ("Feuilles", ", ".join(s["name"] for s in summaries)),220 ("Convention", "Cellules bleu pâle = hypothèses modifiables ; les autres cellules sont calculées par formule."),221 ("Avertissement", "Outil pédagogique — ne constitue pas une évaluation professionnelle. "222 "Seul un évaluateur agréé (É.A.) membre de l'OEAQ peut signer un rapport d'évaluation."),223 ]224 for i, (k, v) in enumerate(rows, 3):225 ws.cell(row=i, column=1, value=k).font = Font(bold=True, color=UQO_BLUE)226 c = ws.cell(row=i, column=2, value=v)227 c.alignment = Alignment(wrap_text=True, vertical="top")228 for i, note in enumerate(spec.get("hypotheses") or [], 3 + len(rows) + 1):229 ws.cell(row=i, column=1, value="Hypothèse").font = Font(color="5B6B7B")230 ws.cell(row=i, column=2, value=note)231232233def build_workbook(spec: dict[str, Any]) -> tuple[bytes, list[dict[str, Any]]]:234 wb = Workbook()235 wb.remove(wb.active)236 summaries: list[dict[str, Any]] = []237 for sheet in spec.get("sheets") or [{"name": "Feuille 1"}]:238 name = re.sub(r"[\[\]\*\?/\\:]", " ", str(sheet.get("name", "Feuille")))[:31] or "Feuille"239 ws = wb.create_sheet(name)240 summaries.append(_render_sheet(wb, ws, sheet))241 _readme(wb, spec, summaries)242 wb.active = 1 if len(wb.sheetnames) > 1 else 0243 buf = io.BytesIO()244 wb.save(buf)245 data = buf.getvalue()246 # Re-read to verify integrity (no empty sheets, formulas parse).247 check = load_workbook(io.BytesIO(data))248 for ws in check.worksheets[1:]:249 if ws.max_row < 2:250 raise ValueError(f"Feuille vide : {ws.title}")251 return data, summaries252253254def _preview(data: bytes, max_rows: int = 14, max_cols: int = 8) -> dict[str, Any]:255 wb = load_workbook(io.BytesIO(data))256 ws = wb.worksheets[1] if len(wb.worksheets) > 1 else wb.worksheets[0]257 rows: list[list[Any]] = []258 for r in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_rows), max_col=max_cols,259 values_only=True):260 rows.append([("" if v is None else v) for v in r])261 return {"sheet": ws.title, "rows": rows, "sheets": wb.sheetnames}262263264async def run(args: dict[str, Any], ctx: ToolContext) -> ToolResult:265 await ctx.report("running", "Création du classeur Excel…")266 spec = args.get("spec")267 if args.get("template"):268 tpl = TEMPLATES.get(args["template"])269 if not tpl:270 return ToolResult(content=f"Gabarit inconnu. Gabarits : {', '.join(TEMPLATES)}.",271 error=True)272 spec = tpl(args.get("params") or {})273 if not spec or not spec.get("sheets"):274 return ToolResult(content="Fournis `spec.sheets` (liste de feuilles) ou `template`.",275 error=True)276 filename = args.get("filename") or spec.get("filename") or "classeur.xlsx"277 if not filename.lower().endswith(".xlsx"):278 filename += ".xlsx"279 data, summaries = build_workbook(spec)280 rec = await file_service.store_artifact(ctx.user_id, ctx.conversation_id, filename, data,281 ftype="xlsx")282 preview = _preview(data)283 art = Artifact(type="xlsx", file_id=rec.id, filename=rec.filename,284 url=f"/api/v1/files/{rec.id}", preview=preview)285 desc = "; ".join(f"« {s['name']} » ({s['tables']} tableau(x), {s['inputs']} hypothèse(s))"286 for s in summaries)287 return ToolResult(288 content=f"Classeur « {rec.filename} » créé ({len(data) // 1024} Ko). Feuilles : {desc}. "289 "Formules vivantes ; feuille Lisez-moi ajoutée. Le fichier est affiché à l'étudiant "290 "avec un bouton Télécharger.",291 artifacts=[art],292 payload={"filename": rec.filename, "file_id": rec.id, "sheets": summaries,293 "preview": preview},294 meta={"summary": f"Excel : {rec.filename}"},295 )296297298registry.register("create_excel", run, ExcelArgs, heavy=True)299