Python 64.6%
TypeScript 33.7%
CSS 0.8%
1"""Open, inspect and modify existing workbooks (uploads or generated artifacts)."""23from __future__ import annotations45import io6import re7from typing import Any89from openpyxl import load_workbook10from openpyxl.chart import BarChart, LineChart, PieChart, Reference, ScatterChart, Series11from openpyxl.comments import Comment12from openpyxl.styles import Alignment, Border, Font, PatternFill, Side13from openpyxl.utils import get_column_letter14from openpyxl.utils.cell import column_index_from_string, coordinate_from_string, range_boundaries15from openpyxl.workbook.defined_name import DefinedName1617from app.tools.coerce import cell as coerce_cell18from app.tools.create_excel import (19 FORMATS,20 HEADER_FILL,21 HEADER_FONT,22 INPUT_FILL,23 INPUT_FONT,24 _readme,25)2627UQO_BLUE_DARK = "0A4A63"28TOTAL_BORDER = Border(top=Side(style="medium", color=UQO_BLUE_DARK))293031class ExcelOpError(ValueError):32 pass333435# ------------------------------------------------------------------ helpers36def _ws(wb: Any, name: str | None) -> Any:37 if not name:38 # first non "Lisez-moi" sheet39 for ws in wb.worksheets:40 if ws.title != "Lisez-moi":41 return ws42 return wb.worksheets[0]43 if name in wb.sheetnames:44 return wb[name]45 low = {s.lower(): s for s in wb.sheetnames}46 if name.lower() in low:47 return wb[low[name.lower()]]48 if name.isdigit() and 0 < int(name) <= len(wb.sheetnames):49 return wb.worksheets[int(name) - 1]50 raise ExcelOpError(f"Feuille introuvable : « {name} ». Feuilles : {', '.join(wb.sheetnames)}.")515253def _apply_format(c: Any, fmt: str | None) -> None:54 if fmt and fmt in FORMATS and fmt != "text":55 c.number_format = FORMATS[fmt]565758def _header_row(ws: Any) -> int:59 best, best_n = 1, -160 for r in range(1, min(ws.max_row, 20) + 1):61 n = sum(1 for c in ws[r] if isinstance(c.value, str) and c.value.strip() and not c.value.startswith("="))62 if n > best_n:63 best, best_n = r, n64 return best656667def _value(v: Any) -> Any:68 if isinstance(v, str) and v.startswith("="):69 return v70 return coerce_cell(v)717273# ------------------------------------------------------------------ inspect74def inspect(data: bytes, sheet: str | None = None, max_rows: int = 60, max_cols: int = 16) -> dict[str, Any]:75 wb = load_workbook(io.BytesIO(data))76 sheets = [_ws(wb, sheet)] if sheet else wb.worksheets77 out: dict[str, Any] = {"sheets": [], "defined_names": sorted(wb.defined_names.keys())[:40]}78 for ws in sheets:79 lines = []80 for row in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_rows), max_col=min(ws.max_column, max_cols)):81 cells = [f"{c.coordinate}={c.value!r}" for c in row if c.value is not None]82 if cells:83 lines.append(" ".join(cells))84 out["sheets"].append({"name": ws.title, "dims": ws.dimensions, "max_row": ws.max_row,85 "max_col": ws.max_column, "cells": lines,86 "charts": len(getattr(ws, "_charts", []))})87 return out888990def preview(data: bytes, max_rows: int = 16, max_cols: int = 10) -> dict[str, Any]:91 wb = load_workbook(io.BytesIO(data))92 sheets = []93 for ws in wb.worksheets[:8]:94 rows = []95 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):96 rows.append([("" if v is None else v) for v in r])97 sheets.append({"name": ws.title, "rows": rows, "max_row": ws.max_row, "max_col": ws.max_column})98 first = next((s for s in sheets if s["name"] != "Lisez-moi"), sheets[0] if sheets else {"name": "", "rows": []})99 return {"sheet": first["name"], "rows": first["rows"], "sheets": [s["name"] for s in sheets], "all": sheets}100101102# ------------------------------------------------------------------ operations103def apply_operations(data: bytes, operations: list[dict[str, Any]]) -> tuple[bytes, list[str]]:104 wb = load_workbook(io.BytesIO(data))105 log: list[str] = []106 for i, op in enumerate(operations, 1):107 kind = str(op.get("op") or op.get("type") or "").lower()108 try:109 msg = _apply_one(wb, kind, op)110 except ExcelOpError:111 raise112 except Exception as exc: # noqa: BLE001113 raise ExcelOpError(f"Opération {i} ({kind}) : {type(exc).__name__}: {exc}") from exc114 log.append(f"{i}. {msg}")115 if "Lisez-moi" not in wb.sheetnames:116 _readme(wb, {"objective": "Classeur modifié avec UQO-Chat", "sheets": []},117 [{"name": s} for s in wb.sheetnames])118 buf = io.BytesIO()119 wb.save(buf)120 return buf.getvalue(), log121122123def _apply_one(wb: Any, kind: str, op: dict[str, Any]) -> str: # noqa: PLR0911, PLR0912, PLR0915124 if kind in {"set_cell", "set"}:125 ws = _ws(wb, op.get("sheet"))126 cell = str(op["cell"]).upper()127 c = ws[cell]128 c.value = _value(op.get("value"))129 _apply_format(c, op.get("format"))130 if op.get("bold"):131 c.font = Font(bold=True)132 if op.get("input"):133 c.fill, c.font = INPUT_FILL, INPUT_FONT134 if op.get("name"):135 dn = DefinedName(re.sub(r"[^A-Za-z0-9_]", "_", str(op["name"]))[:60],136 attr_text=f"'{ws.title}'!${''.join(ch for ch in cell if ch.isalpha())}${''.join(ch for ch in cell if ch.isdigit())}")137 wb.defined_names[dn.name] = dn138 return f"{ws.title}!{cell} ← {op.get('value')!r}"139140 if kind in {"set_cells", "set_many"}:141 ws = _ws(wb, op.get("sheet"))142 n = 0143 for cell, v in (op.get("cells") or {}).items():144 ws[str(cell).upper()].value = _value(v)145 n += 1146 return f"{ws.title}: {n} cellule(s) modifiée(s)"147148 if kind in {"set_range", "write_rows", "rows"}:149 ws = _ws(wb, op.get("sheet"))150 col0, row0 = coordinate_from_string(str(op.get("anchor", "A1")).upper())151 c0 = column_index_from_string(col0)152 rows = op.get("rows") or []153 formats = op.get("formats") or []154 for i, row in enumerate(rows):155 for j, v in enumerate(row if isinstance(row, (list, tuple)) else [row]):156 c = ws.cell(row=row0 + i, column=c0 + j, value=_value(v))157 if j < len(formats):158 _apply_format(c, formats[j])159 if op.get("header"):160 for j, h in enumerate(op["header"]):161 c = ws.cell(row=row0 - 1 if row0 > 1 else row0, column=c0 + j, value=str(h))162 c.fill, c.font = HEADER_FILL, HEADER_FONT163 return f"{ws.title}: plage écrite depuis {op.get('anchor', 'A1')} ({len(rows)} ligne(s))"164165 if kind in {"add_column", "append_column"}:166 ws = _ws(wb, op.get("sheet"))167 hr = int(op.get("header_row") or _header_row(ws))168 col = op.get("column")169 if col:170 cidx = column_index_from_string(str(col).upper())171 ws.insert_cols(cidx)172 else:173 cidx = 1174 for c in ws[hr]:175 if c.value is not None:176 cidx = max(cidx, c.column + 1)177 head = ws.cell(row=hr, column=cidx, value=str(op.get("header", "Nouvelle colonne")))178 head.fill, head.font = HEADER_FILL, HEADER_FONT179 head.alignment = Alignment(horizontal="center", wrap_text=True)180 last = int(op.get("last_row") or _last_data_row(ws, hr))181 values = op.get("values")182 formula = op.get("formula")183 n = 0184 for r in range(hr + 1, last + 1):185 if values is not None:186 idx = r - hr - 1187 if idx >= len(values):188 break189 v = _value(values[idx])190 elif formula:191 v = str(formula).replace("{r}", str(r)).replace("{row}", str(r))192 else:193 v = None194 c = ws.cell(row=r, column=cidx, value=v)195 _apply_format(c, op.get("format"))196 n += 1197 ws.column_dimensions[get_column_letter(cidx)].width = 17198 return f"{ws.title}: colonne « {op.get('header')} » ajoutée en {get_column_letter(cidx)} ({n} lignes)"199200 if kind in {"add_row", "append_row"}:201 ws = _ws(wb, op.get("sheet"))202 hr = int(op.get("header_row") or _header_row(ws))203 r = int(op.get("row") or _last_data_row(ws, hr) + 1)204 if op.get("row"):205 ws.insert_rows(r)206 for j, v in enumerate(op.get("values") or []):207 vv = _value(v)208 if isinstance(vv, str) and vv.startswith("="):209 vv = vv.replace("{r}", str(r)).replace("{row}", str(r))210 ws.cell(row=r, column=1 + j, value=vv)211 return f"{ws.title}: ligne ajoutée en {r}"212213 if kind in {"insert_rows", "delete_rows", "insert_cols", "delete_cols"}:214 ws = _ws(wb, op.get("sheet"))215 at = int(op.get("at", 1))216 count = int(op.get("count", 1))217 getattr(ws, kind)(at, count)218 return f"{ws.title}: {kind} {at} ×{count}"219220 if kind in {"format", "style"}:221 ws = _ws(wb, op.get("sheet"))222 rng = str(op.get("range") or op.get("cell") or "A1").upper()223 min_col, min_row, max_col, max_row = range_boundaries(rng if ":" in rng else f"{rng}:{rng}")224 for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):225 for c in row:226 _apply_format(c, op.get("format"))227 if op.get("bold") is not None or op.get("color"):228 c.font = Font(bold=bool(op.get("bold")), color=str(op.get("color", "1A2B3C")).lstrip("#"))229 if op.get("fill") == "input":230 c.fill, c.font = INPUT_FILL, INPUT_FONT231 elif op.get("fill") == "header":232 c.fill, c.font = HEADER_FILL, HEADER_FONT233 elif op.get("fill"):234 c.fill = PatternFill("solid", fgColor=str(op["fill"]).lstrip("#"))235 if op.get("total"):236 c.border = TOTAL_BORDER237 c.font = Font(bold=True, color=UQO_BLUE_DARK)238 if op.get("wrap"):239 c.alignment = Alignment(wrap_text=True, vertical="top")240 return f"{ws.title}!{rng} formaté"241242 if kind in {"add_sheet", "new_sheet"}:243 name = re.sub(r"[\[\]\*\?/\\:]", " ", str(op.get("name", "Feuille")))[:31]244 if name in wb.sheetnames:245 name = (name[:27] + " (2)")246 ws = wb.create_sheet(name)247 ws.sheet_view.showGridLines = False248 ws.column_dimensions["A"].width = 40249 if op.get("title"):250 ws["A1"] = str(op["title"])251 ws["A1"].font = Font(bold=True, size=14, color=UQO_BLUE_DARK)252 table = op.get("table") or {}253 if table.get("rows") or table.get("columns"):254 from app.tools.create_excel import _render_sheet255 from app.tools.excel_spec import normalise_spec256257 spec = normalise_spec({"sheets": [{"name": name, "title": op.get("title") or name, "tables": [table],258 "notes": op.get("notes") or []}]})259 _render_sheet(wb, ws, spec["sheets"][0])260 return f"feuille « {name} » ajoutée"261262 if kind == "rename_sheet":263 ws = _ws(wb, op.get("sheet"))264 old = ws.title265 ws.title = re.sub(r"[\[\]\*\?/\\:]", " ", str(op.get("name", old)))[:31]266 return f"feuille « {old} » renommée « {ws.title} »"267268 if kind == "delete_sheet":269 ws = _ws(wb, op.get("sheet"))270 if len(wb.worksheets) <= 1:271 raise ExcelOpError("Impossible de supprimer la dernière feuille.")272 wb.remove(ws)273 return "feuille supprimée"274275 if kind == "add_chart":276 ws = _ws(wb, op.get("sheet"))277 ctype = str(op.get("type", "bar")).lower()278 chart: Any = {"line": LineChart, "pie": PieChart, "scatter": ScatterChart}.get(ctype, BarChart)()279 if ctype == "bar":280 chart.type = "col"281 chart.title = op.get("title", "")282 chart.height, chart.width = 8, 14283 vr = str(op.get("values_range") or op.get("data_range"))284 vmin_col, vmin_row, vmax_col, vmax_row = range_boundaries(vr)285 values = Reference(ws, min_col=vmin_col, min_row=vmin_row, max_col=vmax_col, max_row=vmax_row)286 if ctype == "scatter" and op.get("categories_range"):287 cmin_col, cmin_row, cmax_col, cmax_row = range_boundaries(str(op["categories_range"]))288 xs = Reference(ws, min_col=cmin_col, min_row=cmin_row, max_col=cmax_col, max_row=cmax_row)289 chart.series.append(Series(values, xs, title=op.get("series_title", "")))290 else:291 chart.add_data(values, titles_from_data=bool(op.get("titles_from_data", False)))292 if op.get("categories_range"):293 cmin_col, cmin_row, cmax_col, cmax_row = range_boundaries(str(op["categories_range"]))294 chart.set_categories(Reference(ws, min_col=cmin_col, min_row=cmin_row, max_col=cmax_col, max_row=cmax_row))295 if ctype != "pie":296 chart.legend = None297 ws.add_chart(chart, str(op.get("anchor", "H2")))298 return f"{ws.title}: graphique {ctype} ajouté"299300 if kind in {"add_note", "comment"}:301 ws = _ws(wb, op.get("sheet"))302 c = ws[str(op.get("cell", "A1")).upper()]303 c.comment = Comment(str(op.get("text", ""))[:1000], "UQO-Chat")304 return f"{ws.title}!{c.coordinate}: commentaire ajouté"305306 if kind in {"add_text", "write_text", "note"}:307 ws = _ws(wb, op.get("sheet"))308 c = ws[str(op.get("cell", f"A{ws.max_row + 2}")).upper()]309 c.value = str(op.get("text", ""))310 c.alignment = Alignment(wrap_text=True, vertical="top")311 if op.get("bold"):312 c.font = Font(bold=True, color=UQO_BLUE_DARK)313 return f"{ws.title}!{c.coordinate}: texte écrit"314315 if kind in {"set_column_width", "column_width"}:316 ws = _ws(wb, op.get("sheet"))317 ws.column_dimensions[str(op.get("column", "A")).upper()].width = float(op.get("width", 18))318 return f"{ws.title}: largeur {op.get('column')} = {op.get('width')}"319320 if kind in {"clear", "clear_range"}:321 ws = _ws(wb, op.get("sheet"))322 rng = str(op.get("range") or op.get("cell")).upper()323 min_col, min_row, max_col, max_row = range_boundaries(rng if ":" in rng else f"{rng}:{rng}")324 for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):325 for c in row:326 c.value = None327 return f"{ws.title}!{rng} effacé"328329 if kind in {"freeze", "freeze_panes"}:330 ws = _ws(wb, op.get("sheet"))331 ws.freeze_panes = str(op.get("cell", "A2")).upper()332 return f"{ws.title}: volets figés à {op.get('cell', 'A2')}"333334 raise ExcelOpError(335 f"Opération inconnue « {kind} ». Opérations : set_cell, set_cells, set_range, add_column, add_row, "336 "insert_rows, delete_rows, insert_cols, delete_cols, format, add_sheet, rename_sheet, delete_sheet, "337 "add_chart, add_note, add_text, set_column_width, clear, freeze.")338339340def _last_data_row(ws: Any, header_row: int) -> int:341 last = header_row342 for r in range(header_row + 1, ws.max_row + 1):343 if any(c.value is not None for c in ws[r][: max(1, min(ws.max_column, 30))]):344 last = r345 else:346 break347 return last348