Python 64.6%
TypeScript 33.7%
CSS 0.8%
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_spec import normalise_spec21from app.tools.excel_templates import TEMPLATES22from app.tools.registry import ToolContext, registry2324UQO_BLUE = "0F6180"25UQO_BLUE_DARK = "0A4A63"26UQO_BLUE_LIGHT = "E6F0F4"27UQO_GREEN = "78BE20"28INPUT_FILL = PatternFill("solid", fgColor="DDEBF7")29INPUT_FONT = Font(color="1F4E79", bold=False)30HEADER_FILL = PatternFill("solid", fgColor=UQO_BLUE)31HEADER_FONT = Font(color="FFFFFF", bold=True)32TITLE_FONT = Font(color=UQO_BLUE_DARK, bold=True, size=14)33THIN = Side(style="thin", color="B7C4D1")34TOTAL_BORDER = Border(top=Side(style="medium", color=UQO_BLUE_DARK))3536FORMATS = {37 # Excel renders separators per user locale (fr-CA → « 185 000,00 $ »).38 "currency": '#,##0.00 "$";[Red]-#,##0.00 "$"',39 "percent": "0.00%",40 "number": "#,##0.00",41 "integer": "0",42 "area": '#,##0.00 "m²"',43 "factor": "0.000000",44 "text": "@",45}464748class ExcelArgs(BaseModel):49 spec: dict[str, Any] | None = None50 template: str | None = Field(None, description=f"One of {', '.join(TEMPLATES)}")51 params: dict[str, Any] = Field(default_factory=dict)52 filename: str | None = None535455def _coord(anchor: str) -> tuple[int, int]:56 col_letter, row = coordinate_from_string(anchor)57 return column_index_from_string(col_letter), row585960def _safe_defined_name(name: str) -> str:61 n = re.sub(r"[^A-Za-z0-9_]", "_", name)62 if not n or n[0].isdigit():63 n = "_" + n64 return n[:60]656667def _apply_format(cell: Any, kind: str) -> None:68 fmt = FORMATS.get(kind)69 if fmt and kind != "text":70 cell.number_format = fmt717273def _render_sheet(wb: Workbook, ws: Any, sheet: dict[str, Any]) -> dict[str, Any]:74 ws.sheet_view.showGridLines = False75 ws.column_dimensions["A"].width = 4076 for i in range(2, 30):77 ws.column_dimensions[get_column_letter(i)].width = 1778 title = sheet.get("title") or sheet.get("name", "Feuille")79 ws["A1"] = title80 ws["A1"].font = TITLE_FONT81 ws["A2"] = "UQO-Chat · outil pédagogique · ne constitue pas une évaluation professionnelle"82 ws["A2"].font = Font(color="5B6B7B", italic=True, size=9)8384 # Inputs85 inputs = sheet.get("inputs") or []86 if inputs:87 first_row = min(_coord(i["cell"])[1] for i in inputs)88 if sheet.get("inputs_title") and first_row > 3:89 ws.cell(row=first_row - 1, column=1, value=sheet["inputs_title"]).font = Font(90 bold=True, color=UQO_BLUE)91 for inp in inputs:92 col, row = _coord(inp["cell"])93 label_cell = ws.cell(row=row, column=max(1, col - 1), value=inp.get("label", ""))94 label_cell.font = Font(color="1A2B3C")95 cell = ws.cell(row=row, column=col, value=inp.get("value"))96 is_formula = isinstance(inp.get("value"), str) and str(inp["value"]).startswith("=")97 if not is_formula:98 cell.fill = INPUT_FILL99 cell.font = INPUT_FONT100 else:101 cell.font = Font(bold=True)102 _apply_format(cell, inp.get("format", "number"))103 if inp.get("name"):104 dn = DefinedName(_safe_defined_name(inp["name"]),105 attr_text=f"'{ws.title}'!${get_column_letter(col)}${row}")106 wb.defined_names[dn.name] = dn107108 # Tables109 max_row_used = 3110 for table in sheet.get("tables") or []:111 col0, row0 = _coord(table.get("anchor", "A4"))112 columns = table.get("columns") or []113 for j, coldef in enumerate(columns):114 c = ws.cell(row=row0, column=col0 + j, value=coldef.get("header", ""))115 c.fill = HEADER_FILL116 c.font = HEADER_FONT117 c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)118 c.border = Border(bottom=THIN)119 ws.row_dimensions[row0].height = 30120 row_formats = {int(k): v for k, v in (table.get("row_formats") or {}).items()}121 bold_rows = set(table.get("bold_rows") or [])122 for i, row in enumerate(table.get("rows") or []):123 r = row0 + 1 + i124 for j, value in enumerate(row):125 if isinstance(value, (dict, list, tuple)):126 value = str(value)[:250]127 c = ws.cell(row=r, column=col0 + j, value=value)128 kind = columns[j].get("type", "text") if j < len(columns) else "text"129 if j == 0 and table.get("first_col_format"):130 kind = table["first_col_format"]131 if j > 0 and i in row_formats:132 kind = row_formats[i]133 _apply_format(c, kind)134 c.border = Border(bottom=Side(style="hair", color="D5DDE5"))135 if i in bold_rows:136 c.font = Font(bold=True)137 if i % 2 == 1:138 c.fill = PatternFill("solid", fgColor="F5F7FA")139 max_row_used = max(max_row_used, r)140 totals = table.get("totals")141 if totals:142 r = row0 + 1 + len(table.get("rows") or [])143 lc = ws.cell(row=r, column=col0, value=totals.get("label", "Total"))144 lc.font = Font(bold=True, color=UQO_BLUE_DARK)145 lc.border = TOTAL_BORDER146 vcol = col0 + (len(columns) - 1 if len(columns) == 2 else 1)147 vc = ws.cell(row=r, column=vcol, value=totals.get("formula"))148 vc.font = Font(bold=True, color=UQO_BLUE_DARK)149 vc.border = TOTAL_BORDER150 _apply_format(vc, totals.get("format", columns[min(1, len(columns) - 1)].get(151 "type", "currency") if columns else "currency"))152 for j in range(len(columns)):153 ws.cell(row=r, column=col0 + j).border = TOTAL_BORDER154 if totals.get("name"):155 dn = DefinedName(_safe_defined_name(totals["name"]),156 attr_text=f"'{ws.title}'!${get_column_letter(vcol)}${r}")157 wb.defined_names[dn.name] = dn158 max_row_used = max(max_row_used, r)159160 # Charts161 for ch in sheet.get("charts") or []:162 try:163 _add_chart(ws, ch)164 except Exception: # noqa: BLE001 — a bad chart must not break the workbook165 continue166167 # Notes168 notes = sheet.get("notes") or []169 if notes:170 r = max_row_used + 2171 ws.cell(row=r, column=1, value="Notes").font = Font(bold=True, color=UQO_BLUE)172 for k, note in enumerate(notes, 1):173 c = ws.cell(row=r + k, column=1, value=f"• {note}")174 c.alignment = Alignment(wrap_text=True, vertical="top")175 ws.merge_cells(start_row=r + k, start_column=1, end_row=r + k, end_column=6)176 ws.freeze_panes = "A3"177 return {"name": ws.title, "rows": ws.max_row, "tables": len(sheet.get("tables") or []),178 "inputs": len(inputs)}179180181def _ref(ws: Any, rng: str) -> Reference:182 a, b = rng.split(":")183 c1, r1 = _coord(a)184 c2, r2 = _coord(b)185 return Reference(ws, min_col=c1, min_row=r1, max_col=c2, max_row=r2)186187188def _add_chart(ws: Any, ch: dict[str, Any]) -> None:189 kind = ch.get("type", "bar")190 chart: Any191 if kind == "line":192 chart = LineChart()193 elif kind == "pie":194 chart = PieChart()195 else:196 chart = BarChart()197 chart.type = "col"198 chart.title = ch.get("title", "")199 chart.height, chart.width = 8, 14200 values_range = ch.get("values_range") or ch.get("data_range")201 if not values_range:202 return203 chart.add_data(_ref(ws, values_range), titles_from_data=False)204 if ch.get("categories_range"):205 chart.set_categories(_ref(ws, ch["categories_range"]))206 if kind != "pie":207 chart.legend = None208 ws.add_chart(chart, ch.get("anchor", "E4"))209210211def _readme(wb: Workbook, spec: dict[str, Any], summaries: list[dict[str, Any]]) -> None:212 ws = wb.create_sheet("Lisez-moi", 0)213 ws.sheet_view.showGridLines = False214 ws.column_dimensions["A"].width = 28215 ws.column_dimensions["B"].width = 90216 ws["A1"] = "UQO-Chat — classeur pédagogique"217 ws["A1"].font = TITLE_FONT218 rows = [219 ("Objectif", spec.get("objective") or (spec["sheets"][0].get("title") if spec.get("sheets") else "")),220 ("Cours", spec.get("course", "IMM1003 / IMM1033 — UQO")),221 ("Date", date.today().isoformat()),222 ("Feuilles", ", ".join(s["name"] for s in summaries)),223 ("Convention", "Cellules bleu pâle = hypothèses modifiables ; les autres cellules sont calculées par formule."),224 ("Avertissement", "Outil pédagogique — ne constitue pas une évaluation professionnelle. "225 "Seul un évaluateur agréé (É.A.) membre de l'OEAQ peut signer un rapport d'évaluation."),226 ]227 for i, (k, v) in enumerate(rows, 3):228 ws.cell(row=i, column=1, value=k).font = Font(bold=True, color=UQO_BLUE)229 c = ws.cell(row=i, column=2, value=v)230 c.alignment = Alignment(wrap_text=True, vertical="top")231 for i, note in enumerate(spec.get("hypotheses") or [], 3 + len(rows) + 1):232 ws.cell(row=i, column=1, value="Hypothèse").font = Font(color="5B6B7B")233 ws.cell(row=i, column=2, value=note)234235236def build_workbook(spec: dict[str, Any]) -> tuple[bytes, list[dict[str, Any]]]:237 wb = Workbook()238 wb.remove(wb.active)239 summaries: list[dict[str, Any]] = []240 for sheet in spec.get("sheets") or [{"name": "Feuille 1"}]:241 name = re.sub(r"[\[\]\*\?/\\:]", " ", str(sheet.get("name", "Feuille")))[:31] or "Feuille"242 ws = wb.create_sheet(name)243 summaries.append(_render_sheet(wb, ws, sheet))244 _readme(wb, spec, summaries)245 wb.active = 1 if len(wb.sheetnames) > 1 else 0246 buf = io.BytesIO()247 wb.save(buf)248 data = buf.getvalue()249 # Re-read to verify integrity (no empty sheets, formulas parse).250 check = load_workbook(io.BytesIO(data))251 for ws in check.worksheets[1:]:252 if ws.max_row < 2:253 raise ValueError(f"Feuille vide : {ws.title}")254 return data, summaries255256257def _preview(data: bytes, max_rows: int = 16, max_cols: int = 10) -> dict[str, Any]:258 wb = load_workbook(io.BytesIO(data))259 sheets = []260 for ws in wb.worksheets[:8]:261 rows: list[list[Any]] = []262 for r in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_rows),263 max_col=min(max(ws.max_column, 1), max_cols), values_only=True):264 rows.append([("" if v is None else v) for v in r])265 sheets.append({"name": ws.title, "rows": rows, "max_row": ws.max_row, "max_col": ws.max_column})266 first = next((s for s in sheets if s["name"] != "Lisez-moi"), sheets[0])267 return {"sheet": first["name"], "rows": first["rows"], "sheets": [s["name"] for s in sheets], "all": sheets}268269270async def run(args: dict[str, Any], ctx: ToolContext) -> ToolResult:271 await ctx.report("running", "Création du classeur Excel…")272 spec = args.get("spec")273 if args.get("template"):274 tpl = TEMPLATES.get(args["template"])275 if not tpl:276 return ToolResult(content=f"Gabarit inconnu. Gabarits : {', '.join(TEMPLATES)}.",277 error=True)278 spec = tpl(args.get("params") or {})279 if spec and not args.get("template"):280 spec = normalise_spec(spec)281 if not spec or not spec.get("sheets"):282 return ToolResult(content="Fournis `spec.sheets` (liste de feuilles avec tables/rows) ou "283 f"`template` parmi : {', '.join(TEMPLATES)}.", error=True)284 total_rows = sum(len(t.get("rows") or []) for sh in spec["sheets"] for t in sh.get("tables") or [])285 if total_rows > 2000:286 return ToolResult(content="Classeur trop volumineux (> 2000 lignes) : découpe en plusieurs "287 "appels ou génère les lignes avec execute_python (openpyxl).", error=True)288 filename = args.get("filename") or spec.get("filename") or "classeur.xlsx"289 if not filename.lower().endswith(".xlsx"):290 filename += ".xlsx"291 data, summaries = build_workbook(spec)292 rec = await file_service.store_artifact(ctx.user_id, ctx.conversation_id, filename, data,293 ftype="xlsx")294 preview = _preview(data)295 art = Artifact(type="xlsx", file_id=rec.id, filename=rec.filename,296 url=f"/api/v1/files/{rec.id}", preview=preview)297 desc = "; ".join(f"« {s['name']} » ({s['tables']} tableau(x), {s['inputs']} hypothèse(s))"298 for s in summaries)299 return ToolResult(300 content=f"Classeur « {rec.filename} » créé ({len(data) // 1024} Ko). Feuilles : {desc}. "301 "Formules vivantes ; feuille Lisez-moi ajoutée. Le fichier est affiché à l'étudiant "302 "avec un bouton Télécharger.",303 artifacts=[art],304 payload={"filename": rec.filename, "file_id": rec.id, "sheets": summaries,305 "preview": preview},306 meta={"summary": f"Excel : {rec.filename}"},307 )308309310registry.register("create_excel", run, ExcelArgs, heavy=True)311