SPB Git

Repository Public Research preview

audit.mjs 53.7 KB 751 lines javascript

1/**2 * =============================================================================3 *                  SP BOUCHER | SOFTWARE & RESEARCH4 *             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5 * =============================================================================6 *7 * Project          : LLM API toolkits8 * File             : xlsx/mcp/lib/audit.mjs9 * Module           : xlsx10 * Repository       : https://git.spboucher.ai/llm-api-toolkits11 * Created          : 2026-11-1012 * Last Modified    : 2026-11-1013 *14 * Author           : Simon-Pierre Boucher15 * Position         : Professor of Finance16 * Institution      : Université du Québec en Outaouais (UQO)17 * Organization     : SP Boucher18 * Website          : https://spboucher.ai19 * Git              : https://git.spboucher.ai20 *21 * Preferred Contact: dev@spboucher.ai22 * Development      : dev@spboucher.ai23 * Support          : support@spboucher.ai24 * Security/Admin   : admin@spboucher.ai25 *26 * Copyright (c) 202627 * Simon-Pierre Boucher28 * All rights reserved where applicable. This file is governed by the29 * applicable project license, copyright notices and relevant law.30 * Existing third-party rights and notices remain unaffected.31 *32 * License          : None specified (no license file in this repository)33 *34 * Do not embed credentials, private keys, access tokens,35 * certificates or confidential personal information.36 *37 * =============================================================================38 * END OF SP BOUCHER SOURCE HEADER39 * =============================================================================40 */4142import { writeFileSync } from "node:fs";43import { basename, extname } from "node:path";44import { XlsxError } from "./zip.mjs";45import { cellKey, cellsIn, getCell, resolveInside, sheetOf } from "./workbook.mjs";46import { AGGREGATES, ERR, FormulaError, SUPPORTED_FUNCTIONS, Unsupported, evaluate, isErr, numericLiterals, parse, references, shiftFormula, toR1C1, walk } from "./formula.mjs";47import { addr, parseRange, quoteSheet } from "./refs.mjs";4849/**50 * Audit engine. Every function takes loaded workbooks and returns plain JSON-serialisable data.51 *52 * Value rule used everywhere: a constant is its value; a formula is its cached value when the file53 * holds one; otherwise it is evaluated if it stays inside the supported subset; otherwise it is54 * unknown, and reported as unknown rather than guessed.55 */56export const DISCLAIMER = "This is a grading aid, not a grade. It compares cells mechanically; a human grader reviews the evidence and decides the final mark.";57export const SUBSET = `Evaluated subset: + - * / ^, unary minus, %, comparisons, ${SUPPORTED_FUNCTIONS.join(", ")}, cell / range / cross-sheet references and defined names that are plain references. Every other formula is read from its cached value.`;5859const SH = 2 ** 35;60const nodeId = (sheet, cell) => sheet.index * SH + cellKey(cell.col, cell.row);61const fullRef = (sheet, cell) => `${quoteSheet(sheet.name)}!${cell.ref}`;62const plain = (v) => (isErr(v) ? v.error : v ?? null);63const cap = (items, limit) => ({ total: items.length, returned: Math.min(items.length, limit), truncated: items.length > limit, items: items.slice(0, limit) });64const worksheets = (wb, sheet) => (sheet ? [sheetOf(wb, sheet)] : wb.sheets);65const refOfId = (wb, id) => { const s = wb.sheets[Math.floor(id / SH)]; return { sheet: s, cell: s.cells.get(id % SH) }; };6667function lookupName(wb, name, sheetName) {68  const n = name.toLowerCase();69  return wb.names.find((x) => x.name.toLowerCase() === n && x.scope && x.scope.toLowerCase() === sheetName.toLowerCase()) ?? wb.names.find((x) => x.name.toLowerCase() === n && !x.scope) ?? null;70}7172/** Parsed view of a formula cell, computed once. `unsupported` is null when the bounded evaluator can run it. */73export function info(wb, sheet, cell) {74  if (cell._i) return cell._i;75  const i = { ast: null, r1c1: cell.f, refs: [], names: [], funcs: [], unsupported: null, unparsable: false };76  try {77    i.ast = parse(cell.f);78    i.r1c1 = toR1C1(cell.f, cell.row, cell.col);79    Object.assign(i, references(i.ast));80    if (cell.fNote) i.unsupported = cell.fNote;81    else if (cell.array) i.unsupported = "array formula";82    else {83      const bad = i.funcs.find((f) => !SUPPORTED_FUNCTIONS.includes(f));84      if (bad) i.unsupported = `function ${bad}`;85      else {86        walk(i.ast, (n) => { if (!i.unsupported && n.k === "bin" && n.op === "&") i.unsupported = "text concatenation (&)"; });87        const nm = i.names.find((x) => !lookupName(wb, x.v, x.sheet ?? sheet.name)?.target);88        if (!i.unsupported && nm) i.unsupported = `name ${nm.v}`;89      }90    }91  } catch (e) {92    if (e instanceof Unsupported) i.unsupported = e.what;93    else if (e instanceof FormulaError) { i.unsupported = `unparsable formula (${e.message})`; i.unparsable = true; }94    else throw e;95  }96  Object.defineProperty(cell, "_i", { value: i, enumerable: false });97  return i;98}99100function resolver(wb) {101  if (wb._resolver) return wb._resolver;102  const memo = new Map(), busy = new Set();103  const target = (sheet, sn) => (sn === null ? sheet : wb.byName.get(sn.toLowerCase()) ?? null);104  const ctxFor = (sheet) => ({105    cell(sn, col, row) { const s = target(sheet, sn); if (!s) return ERR("#REF!"); const c = getCell(s, col, row); return c ? valueOf(s, c) : null; },106    range(sn, c1, r1, c2, r2) { const s = target(sheet, sn); if (!s) return [ERR("#REF!")]; const a = []; for (const c of cellsIn(s, c1, r1, c2, r2)) a.push(valueOf(s, c)); return a; },107    name(n, sn) { return lookupName(wb, n, sn ?? sheet.name)?.target ?? null; },108  });109  function recompute(sheet, cell) {110    const i = info(wb, sheet, cell);111    if (i.unsupported) throw new Unsupported(i.unsupported);112    return evaluate(i.ast, ctxFor(sheet));113  }114  function valueOf(sheet, cell) {115    if (cell.f === null || cell.hasCached) return cell.v;116    const id = nodeId(sheet, cell);117    const m = memo.get(id);118    if (m) { if (m.err) throw m.err; return m.v; }119    if (busy.has(id)) throw new Unsupported("circular reference");120    busy.add(id);121    try { const v = recompute(sheet, cell); memo.set(id, { v }); return v; }122    catch (e) {123      const err = e instanceof Unsupported ? e : e instanceof RangeError ? new Unsupported("dependency chain too deep to evaluate") : null;124      if (!err) throw e;125      memo.set(id, { err });126      throw err;127    } finally { busy.delete(id); }128  }129  /** → { value, source: constant | cached | evaluated | unknown, reason? } */130  function val(sheet, cell) {131    if (cell.f === null) return { value: cell.v, source: "constant" };132    if (cell.hasCached) return { value: cell.v, source: "cached" };133    try { return { value: valueOf(sheet, cell), source: "evaluated" }; }134    catch (e) { if (e instanceof Unsupported) return { value: null, source: "unknown", reason: e.what }; throw e; }135  }136  const r = { val, recompute, ctxFor };137  Object.defineProperty(wb, "_resolver", { value: r, enumerable: false });138  return r;139}140export const valueOfCell = (wb, sheet, cell) => resolver(wb).val(sheet, cell);141142export function same(a, b, abs = 1e-9, rel = 1e-9) {143  if (isErr(a) || isErr(b)) return isErr(a) && isErr(b) && a.error === b.error;144  if (a === null || a === undefined || a === "") return b === null || b === undefined || b === "";145  if (typeof a === "number" && typeof b === "number") return Math.abs(a - b) <= Math.max(abs, rel * Math.max(Math.abs(a), Math.abs(b)));146  if (typeof a === "string" && typeof b === "string") return a.trim().toLowerCase() === b.trim().toLowerCase();147  return a === b;148}149150/* ---------- dependency graph ---------- */151const EDGE_BUDGET = 3_000_000;152function graph(wb) {153  if (wb._graph) return wb._graph;154  const prec = new Map(), dep = new Map();155  let edges = 0, truncated = false;156  for (const sheet of wb.sheets) for (const cell of sheet.cells.values()) {157    if (cell.f === null) continue;158    const id = nodeId(sheet, cell), i = info(wb, sheet, cell), set = new Set();159    const targets = i.refs.map((r) => ({ sheet: r.sheet === null ? sheet : wb.byName.get(r.sheet.toLowerCase()), a: r.a, b: r.b }));160    for (const n of i.names) { const t = lookupName(wb, n.v, n.sheet ?? sheet.name)?.target; if (t) targets.push({ sheet: wb.byName.get(t.sheet.toLowerCase()), a: t.a, b: t.b }); }161    for (const t of targets) {162      if (!t.sheet) continue;163      const b = t.b ?? t.a;164      for (const c of cellsIn(t.sheet, Math.min(t.a.col, b.col), Math.min(t.a.row, b.row), Math.max(t.a.col, b.col), Math.max(t.a.row, b.row))) {165        if (edges >= EDGE_BUDGET) { truncated = true; break; }166        const pid = nodeId(t.sheet, c);167        if (!set.has(pid)) { set.add(pid); edges++; if (!dep.has(pid)) dep.set(pid, []); dep.get(pid).push(id); }168      }169    }170    prec.set(id, [...set]);171  }172  const g = { prec, dep, truncated };173  Object.defineProperty(wb, "_graph", { value: g, enumerable: false });174  return g;175}176177function cycles(wb) {178  if (wb._cycles) return wb._cycles;179  const g = graph(wb), index = new Map(), low = new Map(), on = new Set(), stack = [], comps = [];180  const fp = (id) => g.prec.get(id).filter((p) => g.prec.has(p));181  let idx = 0;182  const visit = (v) => { index.set(v, idx); low.set(v, idx); idx++; stack.push(v); on.add(v); };183  for (const root of g.prec.keys()) {184    if (index.has(root)) continue;185    visit(root);186    const work = [{ v: root, nb: fp(root), i: 0 }];187    while (work.length) {188      const fr = work[work.length - 1];189      if (fr.i < fr.nb.length) {190        const w = fr.nb[fr.i++];191        if (!index.has(w)) { visit(w); work.push({ v: w, nb: fp(w), i: 0 }); }192        else if (on.has(w)) low.set(fr.v, Math.min(low.get(fr.v), index.get(w)));193      } else {194        work.pop();195        if (work.length) { const u = work[work.length - 1].v; low.set(u, Math.min(low.get(u), low.get(fr.v))); }196        if (low.get(fr.v) === index.get(fr.v)) {197          const comp = [];198          let w;199          do { w = stack.pop(); on.delete(w); comp.push(w); } while (w !== fr.v);200          if (comp.length > 1 || fr.nb.includes(fr.v)) comps.push(comp);201        }202      }203    }204  }205  const members = new Set(comps.flat());206  const out = comps.map((comp) => {207    // a concrete loop: shortest way from the first member back to itself, inside the component208    const inside = new Set(comp), start = comp.slice().sort((a, b) => a - b)[0], prev = new Map([[start, null]]), queue = [start];209    let end = null;210    while (queue.length && end === null) { const v = queue.shift(); for (const w of fp(v)) { if (w === start) { end = v; break; } if (inside.has(w) && !prev.has(w)) { prev.set(w, v); queue.push(w); } } }211    const path = [];212    for (let v = end; v !== null && v !== undefined; v = prev.get(v)) path.unshift(v);213    const name = (id) => { const { sheet, cell } = refOfId(wb, id); return fullRef(sheet, cell); };214    return { cells: comp.length, loop: [...path.map(name), name(start)], members: comp.slice(0, 50).map(name) };215  });216  const r = { list: out, members };217  Object.defineProperty(wb, "_cycles", { value: r, enumerable: false });218  return r;219}220221export function findCircular(wb, { limit = 50 } = {}) {222  const c = cycles(wb);223  return { circular_references: c.list.length, ...cap(c.list, limit), graph_truncated: graph(wb).truncated, note: c.list.length ? "Each loop reads: the first cell depends on the next, and so on back to the first. Excel shows such cells as 0 or needs iterative calculation; their cached values are not trustworthy." : "No circular reference found." };224}225226/* ---------- summary / reading ---------- */227const CONTROL_SHEET = /control|check|contr[oô]le|v[eé]rif|reconcil|validation/i;228229export function summary(wb) {230  const funcs = new Map();231  let formulas = 0, unsupported = 0, noCached = 0, errors = 0, cellsTotal = 0;232  const sheets = wb.sheets.map((s) => {233    const n = { formulas: 0, numbers: 0, text: 0, booleans: 0, errors: 0 }, patterns = new Set();234    for (const c of s.cells.values()) {235      if (c.f !== null) {236        n.formulas++;237        const i = info(wb, s, c);238        patterns.add(i.r1c1);239        for (const f of i.funcs) funcs.set(f, (funcs.get(f) ?? 0) + 1);240        if (i.unsupported) unsupported++;241        if (!c.hasCached) noCached++;242      } else if (c.t === "n") n.numbers++; else if (c.t === "s") n.text++; else if (c.t === "b") n.booleans++;243      if (c.t === "e") n.errors++;244    }245    formulas += n.formulas; errors += n.errors; cellsTotal += s.cells.size;246    return { name: s.name, state: s.state, kind: s.kind, used_range: s.dim ? `${addr(s.dim.c1, s.dim.r1)}:${addr(s.dim.c2, s.dim.r2)}` : null, cells: s.cells.size, ...n, distinct_formula_patterns: patterns.size, merged_ranges: s.merged.length, looks_like_controls: CONTROL_SHEET.test(s.name) };247  });248  return {249    file: wb.path ? basename(wb.path) : null, bytes: wb.bytes ?? null, sheets,250    totals: { sheets: sheets.length, cells: cellsTotal, formulas, error_cells: errors, formulas_without_cached_value: noCached, circular_references: cycles(wb).list.length },251    functions_used: [...funcs].sort((a, b) => b[1] - a[1]).map(([name, count]) => ({ name, count, evaluated: SUPPORTED_FUNCTIONS.includes(name) })),252    evaluator: { formulas_in_subset: formulas - unsupported, formulas_outside_subset: unsupported, share_outside_subset: formulas ? Math.round((unsupported / formulas) * 1e4) / 1e4 : 0, note: SUBSET },253    defined_names: wb.names.map((n) => ({ name: n.name, scope: n.scope ?? "workbook", refers_to: n.refers_to, plain_reference: !!n.target })),254    has_macros: wb.hasMacros, has_external_links: wb.hasExternalLinks, date_system: wb.date1904 ? 1904 : 1900, warnings: wb.warnings,255  };256}257258function scopeOf(wb, { range, sheet }) {259  if (range) {260    const r = parseRange(range);261    if (!r) throw new XlsxError("bad_range", `Cannot read ${JSON.stringify(range)} as a range. Use Sheet!A1:D20 (quote sheet names holding spaces: 'My sheet'!A1).`);262    const name = r.sheet ?? sheet;263    if (!name) throw new XlsxError("bad_range", "Give the sheet in the range (Sheet!A1:D20) or in the sheet argument.");264    return { ...r, sheet: sheetOf(wb, name) };265  }266  if (!sheet) throw new XlsxError("bad_range", "Give a range (Sheet!A1:D20) or a sheet name.");267  const s = sheetOf(wb, sheet);268  return { sheet: s, c1: s.dim?.c1 ?? 1, r1: s.dim?.r1 ?? 1, c2: s.dim?.c2 ?? 1, r2: s.dim?.r2 ?? 1 };269}270271export function readRange(wb, { range, sheet, offset = 0, limit = 200 } = {}) {272  const sc = scopeOf(wb, { range, sheet }), R = resolver(wb), all = [...cellsIn(sc.sheet, sc.c1, sc.r1, sc.c2, sc.r2)];273  const cells = all.slice(offset, offset + limit).map((c) => {274    const v = R.val(sc.sheet, c), o = { ref: c.ref, value: plain(v.value), type: isErr(v.value) ? "error" : v.value === null ? "unknown" : typeof v.value, source: v.source };275    if (c.f !== null) o.formula = `=${c.f}`;276    if (v.reason) o.reason = v.reason;277    if (c.fmt !== "General") o.number_format = c.fmt;278    return o;279  });280  return { sheet: sc.sheet.name, range: `${addr(sc.c1, sc.r1)}:${addr(sc.c2, sc.r2)}`, non_empty_cells: all.length, offset, returned: cells.length, next_offset: offset + cells.length < all.length ? offset + cells.length : null, cells, note: "Empty cells are omitted. Dates are stored as serial numbers; read number_format to recognise them." };281}282283export function listFormulas(wb, { sheet, range, offset = 0, limit = 200, group = false } = {}) {284  const g = graph(wb), R = resolver(wb), rows = [];285  const scopes = range ? [scopeOf(wb, { range, sheet })] : worksheets(wb, sheet).map((s) => ({ sheet: s, c1: 1, r1: 1, c2: Infinity, r2: Infinity }));286  for (const sc of scopes) for (const c of cellsIn(sc.sheet, sc.c1, sc.r1, sc.c2, sc.r2)) if (c.f !== null) rows.push({ sheet: sc.sheet, cell: c });287  if (group) {288    const m = new Map();289    for (const { sheet: s, cell } of rows) { const k = `${s.name}\u0000${info(wb, s, cell).r1c1}`; if (!m.has(k)) m.set(k, { sheet: s.name, pattern: info(wb, s, cell).r1c1, count: 0, first: cell.ref, example: `=${cell.f}`, cells: [] }); const e = m.get(k); e.count++; if (e.cells.length < 20) e.cells.push(cell.ref); }290    const groups = [...m.values()].sort((a, b) => b.count - a.count);291    return { formulas: rows.length, patterns: groups.length, offset, groups: groups.slice(offset, offset + limit), next_offset: offset + limit < groups.length ? offset + limit : null };292  }293  const page = rows.slice(offset, offset + limit).map(({ sheet: s, cell }) => {294    const i = info(wb, s, cell), v = R.val(s, cell);295    const o = { ref: fullRef(s, cell), formula: `=${cell.f}`, r1c1: i.r1c1, value: plain(v.value), source: v.source, dependents: g.dep.get(nodeId(s, cell))?.length ?? 0, precedents: g.prec.get(nodeId(s, cell))?.length ?? 0, evaluated_by_this_tool: !i.unsupported };296    if (i.unsupported) o.outside_subset = i.unsupported;297    return o;298  });299  return { formulas: rows.length, offset, returned: page.length, next_offset: offset + page.length < rows.length ? offset + page.length : null, items: page, graph_truncated: g.truncated };300}301302/* ---------- defects ---------- */303export function findErrors(wb, { sheet, limit = 200 } = {}) {304  const g = graph(wb), R = resolver(wb), out = [];305  for (const s of worksheets(wb, sheet)) for (const c of s.cells.values()) {306    const broken = c.f !== null && c.f.includes("#REF!");307    let error = null, origin = null;308    if (c.t === "e") { error = c.v.error; origin = c.f === null ? "constant" : "cached"; }309    else if (c.f !== null && !c.hasCached) { const v = R.val(s, c); if (isErr(v.value)) { error = v.value.error; origin = "evaluated"; } }310    if (!error && broken) { error = "#REF!"; origin = "formula_text"; }311    if (!error) continue;312    const from = [];313    if (c.f !== null) for (const pid of g.prec.get(nodeId(s, c)) ?? []) { const p = refOfId(wb, pid); const pv = R.val(p.sheet, p.cell); if (isErr(pv.value) || (p.cell.f !== null && p.cell.f.includes("#REF!"))) { from.push(fullRef(p.sheet, p.cell)); if (from.length >= 5) break; } }314    const o = { ref: fullRef(s, c), error, origin, root_cause: from.length === 0 };315    if (c.f !== null) o.formula = `=${c.f}`;316    if (broken) o.broken_reference = true;317    if (from.length) o.propagated_from = from;318    out.push(o);319  }320  out.sort((a, b) => Number(b.root_cause) - Number(a.root_cause));321  const roots = out.filter((x) => x.root_cause).length;322  return { error_cells: out.length, root_causes: roots, propagated: out.length - roots, ...cap(out, limit), note: "Fix the root causes first: the propagated errors disappear with them. Errors hidden by IFERROR are not visible here." };323}324325export function findHardcoded(wb, { sheet, ignore = [0, 1], limit = 200 } = {}) {326  const skip = new Set(ignore), inFormula = [], pasted = [];327  for (const s of worksheets(wb, sheet)) for (const c of s.cells.values()) {328    if (c.f !== null) {329      const i = info(wb, s, c);330      if (!i.ast) continue;331      const k = numericLiterals(i.ast).filter((x) => !skip.has(x));332      if (k.length) inFormula.push({ ref: fullRef(s, c), formula: `=${c.f}`, constants: [...new Set(k)], only_constants: i.refs.length === 0 && i.names.length === 0 });333      continue;334    }335    if (c.t !== "n") continue;336    const pair = (a, b) => (a && b && a.f !== null && b.f !== null && info(wb, s, a).r1c1 === info(wb, s, b).r1c1 ? a : null);337    const l = pair(getCell(s, c.col - 1, c.row), getCell(s, c.col + 1, c.row)), u = l ? null : pair(getCell(s, c.col, c.row - 1), getCell(s, c.col, c.row + 1));338    if (l) pasted.push({ ref: fullRef(s, c), value: c.v, direction: "row", neighbours: [addr(c.col - 1, c.row), addr(c.col + 1, c.row)], neighbour_pattern: info(wb, s, l).r1c1, expected_formula: `=${shiftFormula(l.f, 0, 1)}` });339    else if (u) pasted.push({ ref: fullRef(s, c), value: c.v, direction: "column", neighbours: [addr(c.col, c.row - 1), addr(c.col, c.row + 1)], neighbour_pattern: info(wb, s, u).r1c1, expected_formula: `=${shiftFormula(u.f, 1, 0)}` });340  }341  return {342    constants_inside_formulas: cap(inFormula, limit), constants_where_a_formula_is_expected: cap(pasted, limit), ignored_constants: [...skip],343    note: "A constant inside a formula is not always a defect (12 months, 100 for a percentage). Judge each one: if it is an assumption, it belongs in a labelled input cell. A typed number between two cells holding the same formula pattern is almost always an overwritten formula. The digits argument of ROUND is never reported.",344  };345}346347const root = (ast) => { let n = ast; while (n && (n.k === "paren" || n.k === "un")) n = n.x; return n; };348const isAggregate = (ast) => { const n = root(ast); return n?.k === "func" && AGGREGATES.has(n.name); };349350export function formulaConsistency(wb, { sheet, min_run = 3, limit = 200 } = {}) {351  const found = [];352  let runsChecked = 0;353  for (const s of worksheets(wb, sheet)) {354    const lines = [];355    const cols = new Map();356    for (const r of s.rowNumbers) {357      const list = s.rows.get(r).filter((c) => c.f !== null);358      lines.push({ dir: "row", cells: list, pos: (c) => c.col });359      for (const c of list) { if (!cols.has(c.col)) cols.set(c.col, []); cols.get(c.col).push(c); }360    }361    for (const list of cols.values()) lines.push({ dir: "column", cells: list, pos: (c) => c.row });362    for (const line of lines) {363      let run = [];364      const flush = () => {365        if (run.length >= min_run) {366          runsChecked++;367          const counts = new Map();368          for (const c of run) { const p = info(wb, s, c).r1c1; counts.set(p, (counts.get(p) ?? 0) + 1); }369          const [top, n] = [...counts].sort((a, b) => b[1] - a[1])[0];370          if (n >= 2 && n > run.length / 2 && n < run.length) {371            const majority = run.filter((c) => info(wb, s, c).r1c1 === top), majAgg = isAggregate(info(wb, s, majority[0]).ast);372            for (const c of run) {373              const i = info(wb, s, c);374              if (i.r1c1 === top) continue;375              if (!majAgg && i.ast && isAggregate(i.ast)) continue; // a total closing the run376              if (c === run[0] && top.includes(line.dir === "row" ? "R[0]C[-1]" : "R[-1]C[0]")) continue; // opening cell of a recurrence (first period)377              const near = majority.reduce((b, m) => (Math.abs(line.pos(m) - line.pos(c)) < Math.abs(line.pos(b) - line.pos(c)) ? m : b));378              const perp = line.dir === "row" ? [getCell(s, c.col, c.row - 1), getCell(s, c.col, c.row + 1)] : [getCell(s, c.col - 1, c.row), getCell(s, c.col + 1, c.row)];379              const fitsOtherWay = perp.some((p) => p && p.f !== null && info(wb, s, p).r1c1 === i.r1c1);380              found.push({ ref: fullRef(s, c), direction: line.dir, formula: `=${c.f}`, expected_formula: `=${shiftFormula(near.f, c.row - near.row, c.col - near.col)}`, run: `${run[0].ref}:${run[run.length - 1].ref}`, majority: `${n} of ${run.length}`, confidence: fitsOtherWay ? "low" : "high" });381            }382          }383        }384        run = [];385      };386      for (const c of line.cells) { if (run.length && line.pos(c) !== line.pos(run[run.length - 1]) + 1) flush(); run.push(c); }387      flush();388    }389  }390  found.sort((a, b) => (a.confidence === b.confidence ? 0 : a.confidence === "high" ? -1 : 1));391  return { runs_checked: runsChecked, inconsistent_cells: found.length, ...cap(found, limit), note: `Formulas are compared in R1C1 form along rows and columns, in runs of at least ${min_run} adjacent formula cells with a clear majority pattern. "low" confidence means the cell matches its neighbours in the other direction (the layout probably runs that way). A total closing a run is not reported.` };392}393394/* ---------- tracing ---------- */395export function trace(wb, { cell, sheet, direction = "precedents", depth = 2, limit = 200 } = {}) {396  const sc = scopeOf(wb, { range: cell, sheet });397  if (sc.c1 !== sc.c2 || sc.r1 !== sc.r2) throw new XlsxError("bad_range", "Give a single cell, for example Summary!C12.");398  const s = sc.sheet, g = graph(wb), R = resolver(wb), start = getCell(s, sc.c1, sc.r1);399  const out = { cell: `${quoteSheet(s.name)}!${addr(sc.c1, sc.r1)}`, direction, depth };400  if (!start) {401    if (direction === "precedents") return { ...out, empty: true, nodes: [], note: "The cell is empty." };402  } else {403    const v = R.val(s, start);404    out.value = plain(v.value); out.source = v.source;405    if (start.f !== null) out.formula = `=${start.f}`;406  }407  const startId = s.index * SH + cellKey(sc.c1, sc.r1), seen = new Set([startId]), nodes = [];408  let frontier = [startId], truncated = false;409  if (direction === "precedents" && start?.f != null) {410    const i = info(wb, s, start);411    out.references_as_written = [412      ...i.refs.map((r) => `${r.sheet !== null ? quoteSheet(r.sheet) + "!" : ""}${r.a.whole === "col" ? `column ${addr(r.a.col, 1).replace(/\d+$/, "")}` : r.a.whole === "row" ? `row ${r.a.row}` : addr(r.a.col, r.a.row)}${r.b ? ":" + (r.b.whole === "col" ? addr(r.b.col, 1).replace(/\d+$/, "") : r.b.whole === "row" ? r.b.row : addr(r.b.col, r.b.row)) : ""}`),413      ...i.names.map((n) => `${n.v} (name${lookupName(wb, n.v, n.sheet ?? s.name) ? ` → ${lookupName(wb, n.v, n.sheet ?? s.name).refers_to}` : ", undefined"})`),414    ];415  }416  if (direction === "dependents" && !start) {417    // an empty cell can still be referenced: find the formulas whose ranges cover it418    frontier = [];419    for (const sh of wb.sheets) for (const c of sh.cells.values()) {420      if (c.f === null) continue;421      const i = info(wb, sh, c);422      if (i.refs.some((r) => { const t = r.sheet === null ? sh : wb.byName.get(r.sheet.toLowerCase()); const b = r.b ?? r.a; return t === s && sc.c1 >= Math.min(r.a.col, b.col) && sc.c1 <= Math.max(r.a.col, b.col) && sc.r1 >= Math.min(r.a.row, b.row) && sc.r1 <= Math.max(r.a.row, b.row); })) { const id = nodeId(sh, c); seen.add(id); frontier.push(id); nodes.push({ ref: fullRef(sh, c), level: 1, formula: `=${c.f}`, value: plain(R.val(sh, c).value) }); }423    }424  }425  const edges = direction === "precedents" ? g.prec : g.dep;426  for (let level = start || direction === "precedents" ? 1 : 2; level <= depth && frontier.length && !truncated; level++) {427    const next = [];428    for (const id of frontier) for (const n of edges.get(id) ?? []) {429      if (seen.has(n)) continue;430      seen.add(n);431      if (nodes.length >= limit) { truncated = true; break; }432      const { sheet: ns, cell: nc } = refOfId(wb, n), nv = R.val(ns, nc), o = { ref: fullRef(ns, nc), level, value: plain(nv.value) };433      if (nc.f !== null) o.formula = `=${nc.f}`; else o.input = true;434      nodes.push(o); next.push(n);435    }436    frontier = next;437  }438  const c = cycles(wb);439  return { ...out, in_circular_reference: c.members.has(startId), nodes, count: nodes.length, truncated, deeper_levels_exist: !truncated && frontier.some((id) => (edges.get(id) ?? []).some((n) => !seen.has(n))), graph_truncated: g.truncated, note: direction === "precedents" ? "Level 1 = cells read directly by the formula; ranges are expanded to their non-empty cells. Cells marked input are constants." : "Level 1 = formulas that read this cell directly." };440}441442/* ---------- controls ---------- */443const PASS_TXT = /^(ok|pass|passed|true|vrai|yes|oui|✓)$/i, FAIL_TXT = /^(fail|failed|error|erreur|ko|false|faux|no|non|✗)$/i;444const DIFF_HEAD = /diff|[ée]cart|delta|variance|gap/i, OVERALL = /\b(all|overall|global|tous|toutes)\b/i;445446function judgeControlValue(v, tolerance) {447  if (v.source === "unknown") return { pass: null, kind: "unknown", why: v.reason };448  const x = v.value;449  if (isErr(x)) return { pass: false, kind: "error" };450  if (typeof x === "boolean") return { pass: x, kind: "boolean" };451  if (typeof x === "string") return PASS_TXT.test(x.trim()) ? { pass: true, kind: "status" } : FAIL_TXT.test(x.trim()) ? { pass: false, kind: "status" } : null;452  if (typeof x === "number") return { pass: Math.abs(x) <= tolerance, kind: "difference" };453  return null;454}455456export function checkControls(wb, { sheet, ranges = [], tolerance = 0.005, limit = 200 } = {}) {457  const R = resolver(wb), controls = [], sources = [];458  let overall = null;459  const sheets = sheet ? [sheetOf(wb, sheet)] : wb.sheets.filter((s) => CONTROL_SHEET.test(s.name));460  for (const s of sheets) {461    sources.push(`sheet ${s.name}`);462    const diffCols = new Set();463    let headerRow = null;464    for (const r of s.rowNumbers) { const texts = s.rows.get(r).filter((c) => c.t === "s" && c.f === null); if (texts.length >= 2) { headerRow = r; for (const c of texts) if (DIFF_HEAD.test(c.v)) diffCols.add(c.col); break; } }465    for (const r of s.rowNumbers) {466      if (r === headerRow) continue;467      const row = s.rows.get(r), labelCell = row.find((c) => c.t === "s" && c.f === null && !PASS_TXT.test(c.v.trim()) && !FAIL_TXT.test(c.v.trim()));468      const label = labelCell ? labelCell.v : null;469      let verdicts = [];470      for (const c of row) {471        if (c === labelCell) continue;472        const v = R.val(s, c), j = judgeControlValue(v, tolerance);473        if (!j) continue;474        if (j.kind === "difference" && !diffCols.has(c.col)) continue; // plain amounts are not controls475        if (j.kind === "unknown" && c.f === null) continue;476        verdicts.push({ cell: c, v, j });477      }478      if (label && OVERALL.test(label) && !overall) {479        const x = verdicts.find((y) => y.j.kind === "boolean" || y.j.kind === "status" || y.j.kind === "unknown");480        if (x) { overall = { ref: fullRef(s, x.cell), label, kind: x.j.kind, value: plain(x.v.value), pass: x.j.pass }; if (x.cell.f !== null) overall.formula = `=${x.cell.f}`; else overall.typed_constant = true; if (x.j.why) overall.reason = x.j.why; continue; }481      }482      const strong = verdicts.filter((x) => x.j.kind === "boolean" || x.j.kind === "status" || x.j.kind === "error");483      if (strong.length) verdicts = strong; else verdicts = verdicts.filter((x) => x.j.kind === "difference" || (x.j.kind === "unknown" && diffCols.has(x.cell.col)));484      for (const x of verdicts) {485        const o = { ref: fullRef(s, x.cell), label, kind: x.j.kind, value: plain(x.v.value), pass: x.j.pass };486        if (x.cell.f !== null) o.formula = `=${x.cell.f}`; else o.typed_constant = true;487        if (x.j.why) o.reason = x.j.why;488        controls.push(o);489      }490    }491  }492  const perCell = (s, c, label) => {493    const v = R.val(s, c), j = judgeControlValue(v, tolerance);494    if (!j) return;495    const o = { ref: fullRef(s, c), label, kind: j.kind, value: plain(v.value), pass: j.pass };496    if (c.f !== null) o.formula = `=${c.f}`; else o.typed_constant = true;497    controls.push(o);498  };499  if (!sheet) for (const n of wb.names) {500    if (!n.target || !/^(chk|check|ctrl|control|test|ok)[_.A-Za-z0-9]*$/i.test(n.name)) continue;501    const s = wb.byName.get(n.target.sheet.toLowerCase()), b = n.target.b ?? n.target.a;502    if (sheets.includes(s)) continue;503    sources.push(`name ${n.name}`);504    for (const c of cellsIn(s, Math.min(n.target.a.col, b.col), Math.min(n.target.a.row, b.row), Math.max(n.target.a.col, b.col), Math.max(n.target.a.row, b.row))) perCell(s, c, n.name);505  }506  for (const rg of ranges) { const sc = scopeOf(wb, { range: rg }); sources.push(`range ${rg}`); for (const c of cellsIn(sc.sheet, sc.c1, sc.r1, sc.c2, sc.r2)) perCell(sc.sheet, c, rg); }507  const failing = controls.filter((c) => c.pass === false), unknown = controls.filter((c) => c.pass === null), typed = controls.filter((c) => c.typed_constant);508  const allPass = controls.length > 0 && failing.length === 0 && unknown.length === 0;509  const out = { found: controls.length > 0 || !!overall, sources, tolerance, controls: controls.length, passing: controls.length - failing.length - unknown.length, failing: failing.length, unknown: unknown.length, all_pass: controls.length ? allPass : null, failing_controls: failing.slice(0, limit), unknown_controls: unknown.slice(0, limit), ...cap(controls, limit) };510  if (overall) { out.overall_cell = overall; out.overall_cell_consistent = overall.pass === null ? null : overall.pass === allPass; }511  const warn = [];512  if (!out.found) warn.push("No control was found. Looked for a sheet whose name contains control / check / reconciliation / validation, and for defined names starting with chk, check, ctrl, control, test or ok. Pass sheet or ranges to point at the controls.");513  if (typed.length) warn.push(`${typed.length} control cell(s) hold a typed value instead of a formula: a typed TRUE or 0 checks nothing.`);514  if (overall && overall.pass === null) warn.push(`The overall cell ${overall.ref} has no stored value and its formula is outside the evaluated subset: open the workbook in Excel to read it.`);515  if (overall && out.overall_cell_consistent === false) warn.push("The overall cell disagrees with the individual controls: it does not cover all of them, or its cached value is stale.");516  if (controls.length && !overall) warn.push("No single 'all checks pass' cell was recognised (a boolean or OK/FAIL cell on a row labelled all / overall / global).");517  out.warnings = warn;518  return out;519}520521/* ---------- recalculation ---------- */522export function recalcCheck(wb, { sheet, abs_tolerance = 1e-9, rel_tolerance = 1e-9, limit = 200 } = {}) {523  const R = resolver(wb), mismatches = [], reasons = new Map();524  const loops = cycles(wb).members;525  let formulas = 0, match = 0, outside = 0, noCached = 0;526  for (const s of worksheets(wb, sheet)) for (const c of s.cells.values()) {527    if (c.f === null) continue;528    formulas++;529    let v;530    try { if (loops.has(nodeId(s, c))) throw new Unsupported("circular reference"); v = R.recompute(s, c); }531    catch (e) {532      const why = e instanceof Unsupported ? e.what : e instanceof RangeError ? "dependency chain too deep to evaluate" : null;533      if (why === null) throw e;534      outside++; reasons.set(why, (reasons.get(why) ?? 0) + 1); continue;535    }536    if (!c.hasCached) { noCached++; continue; }537    if (same(v, c.v, abs_tolerance, rel_tolerance)) match++;538    else mismatches.push({ ref: fullRef(s, c), formula: `=${c.f}`, cached: plain(c.v), recomputed: plain(v), difference: typeof v === "number" && typeof c.v === "number" ? v - c.v : null });539  }540  const checked = match + mismatches.length;541  return {542    formulas, recomputed: checked, match, mismatch: mismatches.length, without_cached_value: noCached, outside_subset: outside,543    share_outside_subset: formulas ? Math.round((outside / formulas) * 1e4) / 1e4 : 0,544    outside_subset_reasons: [...reasons].sort((a, b) => b[1] - a[1]).slice(0, 20).map(([reason, count]) => ({ reason, count })),545    mismatches: cap(mismatches, limit),546    verdict: formulas === 0 ? "no_formulas" : checked === 0 ? (noCached ? "no_cached_values" : "nothing_recomputable") : mismatches.length ? "stale_or_altered_values" : outside ? "consistent_where_checked" : "consistent",547    note: `Each formula is recomputed once from the stored values of the cells it reads, then compared with its own stored value. A mismatch means the stored value is stale (file saved without recalculation, or written by a library) or was altered. ${SUBSET} Nothing is said about formulas outside the subset.`,548  };549}550551/* ---------- comparison and grading ---------- */552function judgeCell(R, S, sheetName, col, row, tol, rel) {553  const rs = R.byName.get(sheetName.toLowerCase()), ss = S.byName.get(sheetName.toLowerCase());554  const rc = rs ? getCell(rs, col, row) : null, sc = ss ? getCell(ss, col, row) : null;555  const o = { ref: `${quoteSheet(rs?.name ?? sheetName)}!${addr(col, row)}`, rc, sc, rs, ss };556  const exp = rc ? resolver(R).val(rs, rc) : { value: null, source: "empty" };557  o.expected = plain(exp.value);558  if (rc?.f != null) o.expected_formula = `=${rc.f}`;559  if (!sc) { o.verdict = !ss ? "missing_sheet" : "missing"; o.got = null; return o; }560  const got = resolver(S).val(ss, sc);561  o.got = plain(got.value);562  if (sc.f !== null) o.got_formula = `=${sc.f}`;563  o.expVal = exp.value; o.gotVal = got.value;564  if (exp.source === "unknown" || got.source === "unknown") { o.verdict = "unknown"; o.reason = `value not available without Excel (${exp.reason ?? got.reason})`; return o; }565  if (rc?.f != null && sc.f !== null) o.same_formula = info(R, rs, rc).r1c1 === info(S, ss, sc).r1c1;566  o.verdict = same(exp.value, got.value, tol, rel) ? (sc.f !== null ? "match_formula" : "match_constant") : "wrong";567  return o;568}569const publicCell = (o) => { const { rc, sc, rs, ss, expVal, gotVal, ...rest } = o; return rest; };570571function structure(R, S) {572  const rn = R.sheets.map((s) => s.name), sn = S.sheets.map((s) => s.name), low = (a) => a.map((x) => x.toLowerCase());573  const names = (wb) => wb.names.map((n) => n.name);574  return {575    sheets_missing: rn.filter((n) => !low(sn).includes(n.toLowerCase())), sheets_added: sn.filter((n) => !low(rn).includes(n.toLowerCase())),576    sheet_order_changed: JSON.stringify(low(rn).filter((n) => low(sn).includes(n))) !== JSON.stringify(low(sn).filter((n) => low(rn).includes(n))),577    names_missing: names(R).filter((n) => !low(names(S)).includes(n.toLowerCase())), names_added: names(S).filter((n) => !low(names(R)).includes(n.toLowerCase())),578    used_ranges: R.sheets.filter((s) => S.byName.has(s.name.toLowerCase())).map((s) => { const t = S.byName.get(s.name.toLowerCase()), d = (x) => (x.dim ? `${addr(x.dim.c1, x.dim.r1)}:${addr(x.dim.c2, x.dim.r2)}` : null); return { sheet: s.name, reference: d(s), submission: d(t), formulas_reference: [...s.cells.values()].filter((c) => c.f !== null).length, formulas_submission: [...t.cells.values()].filter((c) => c.f !== null).length }; }).filter((x) => x.reference !== x.submission || x.formulas_reference !== x.formulas_submission),579  };580}581582function changedInputs(R, S, tol, rel, limit = 100) {583  const list = [];584  for (const rs of R.sheets) { const ss = S.byName.get(rs.name.toLowerCase()); if (!ss) continue; for (const rc of rs.cells.values()) { if (rc.f !== null || (rc.t !== "n" && rc.t !== "b")) continue; const sc = getCell(ss, rc.col, rc.row); if (sc && sc.f === null && same(rc.v, sc.v, tol, rel)) continue; list.push({ ref: fullRef(rs, rc), reference: plain(rc.v), submission: sc ? (sc.f !== null ? `=${sc.f}` : plain(sc.v)) : null, change: !sc ? "removed" : sc.f !== null ? "replaced_by_formula" : "value_changed" }); } }585  return cap(list, limit);586}587588export function compareWorkbooks(R, S, { ranges, tolerance = 0.005, rel_tolerance = 0, limit = 200 } = {}) {589  const targets = [];590  if (ranges?.length) for (const rg of ranges) { const sc = scopeOf(R, { range: rg }); for (const c of cellsIn(sc.sheet, sc.c1, sc.r1, sc.c2, sc.r2)) targets.push([sc.sheet.name, c.col, c.row]); }591  else for (const s of R.sheets) for (const c of s.cells.values()) if (c.f !== null) targets.push([s.name, c.col, c.row]);592  const counts = { match_formula: 0, match_constant: 0, wrong: 0, missing: 0, missing_sheet: 0, unknown: 0 }, issues = [];593  let sameFormula = 0, differentFormulaSameValue = 0;594  for (const [sn, col, row] of targets) {595    const j = judgeCell(R, S, sn, col, row, tolerance, rel_tolerance);596    counts[j.verdict]++;597    if (j.same_formula) sameFormula++;598    if (j.verdict === "match_formula" && j.same_formula === false) differentFormulaSameValue++;599    if (j.verdict !== "match_formula") issues.push(publicCell(j));600  }601  const extra = [];602  for (const ss of S.sheets) { const rs = R.byName.get(ss.name.toLowerCase()); for (const c of ss.cells.values()) if (c.f !== null && !(rs && getCell(rs, c.col, c.row))) extra.push({ ref: fullRef(ss, c), formula: `=${c.f}` }); }603  return {604    reference: R.path ? basename(R.path) : null, submission: S.path ? basename(S.path) : null,605    compared: ranges?.length ? "the non-empty reference cells of the given ranges" : "every formula cell of the reference", cells_compared: targets.length, tolerance, rel_tolerance,606    counts: { right_by_formula: counts.match_formula, right_but_typed_as_constant: counts.match_constant, wrong: counts.wrong, missing: counts.missing + counts.missing_sheet, unknown: counts.unknown },607    same_formula_pattern: sameFormula, different_formula_same_value: differentFormulaSameValue,608    differences: cap(issues, limit), structure: structure(R, S), inputs_changed: changedInputs(R, S, tolerance, rel_tolerance), extra_formulas_in_submission: cap(extra, 50),609    note: "Values follow the rule: stored value, else evaluated inside the supported subset, else unknown. A different formula giving the same value is counted right: check it is not a coincidence of this data set.",610  };611}612613const num = (x, d) => (typeof x === "number" && Number.isFinite(x) ? x : d);614export function validateRubric(rubric) {615  const bad = (m) => { throw new XlsxError("rubric_invalid", m); };616  if (!rubric || typeof rubric !== "object" || !Array.isArray(rubric.items) || rubric.items.length === 0) bad("The rubric needs a non-empty items array.");617  if (rubric.items.length > 500) bad("The rubric has more than 500 items.");618  const ids = new Set();619  return rubric.items.map((it, k) => {620    const id = String(it?.id ?? `item-${k + 1}`);621    if (ids.has(id)) bad(`Duplicate item id ${id}.`);622    ids.add(id);623    const ranges = Array.isArray(it.ranges) ? it.ranges : it.range ? [it.range] : [];624    if (!ranges.length) bad(`Item ${id}: give range (for example "Model!C10:G10") or ranges.`);625    for (const r of ranges) if (!parseRange(String(r))?.sheet) bad(`Item ${id}: ${JSON.stringify(r)} is not a range with its sheet name.`);626    if (typeof it.points !== "number" || !(it.points >= 0)) bad(`Item ${id}: points must be a number ≥ 0.`);627    const partial = it.partial ?? rubric.partial ?? "proportional";628    if (partial !== "proportional" && partial !== "all_or_nothing") bad(`Item ${id}: partial must be "proportional" or "all_or_nothing".`);629    const unit = (v, d, n) => { const x = num(v, d); if (x < 0 || x > 1) bad(`Item ${id}: ${n} must be between 0 and 1.`); return x; };630    return {631      id, label: it.label ? String(it.label) : null, ranges: ranges.map(String), points: it.points, partial,632      tolerance: Math.max(0, num(it.tolerance, num(rubric.tolerance, 0.005))), rel_tolerance: Math.max(0, num(it.rel_tolerance, num(rubric.rel_tolerance, 0))),633      formula_required: Boolean(it.formula_required ?? rubric.formula_required ?? false),634      constant_credit: unit(it.constant_credit ?? rubric.constant_credit, 0.5, "constant_credit"),635      carried_forward_credit: unit(it.carried_forward_credit ?? rubric.carried_forward_credit, 1, "carried_forward_credit"),636    };637  });638}639640export function gradeSubmission(R, S, rubric, { max_cells_per_item = 5000 } = {}) {641  const items = validateRubric(rubric), SR = resolver(S), review = [], tally = {};642  let total = 0, max = 0;643  const graded = items.map((it) => {644    const cells = [];645    for (const rg of it.ranges) { const sc = scopeOf(R, { range: rg }); for (const c of cellsIn(sc.sheet, sc.c1, sc.r1, sc.c2, sc.r2)) cells.push([sc.sheet.name, c.col, c.row]); }646    if (!cells.length) throw new XlsxError("rubric_invalid", `Item ${it.id}: the range holds no value in the reference workbook.`);647    if (cells.length > max_cells_per_item) throw new XlsxError("rubric_invalid", `Item ${it.id}: more than ${max_cells_per_item} cells.`);648    const evidence = cells.map(([sn, col, row]) => {649      const j = judgeCell(R, S, sn, col, row, it.tolerance, it.rel_tolerance);650      let credit = 0, verdict = j.verdict, note = null;651      if (verdict === "match_formula") { credit = 1; verdict = "correct"; }652      else if (verdict === "match_constant") {653        if (!it.formula_required || j.rc.f === null) { credit = 1; verdict = "correct"; }654        else { credit = it.constant_credit; verdict = "correct_value_typed_as_constant"; note = "The value is right but the cell holds a typed number where a formula is required."; }655      } else if (verdict === "wrong") {656        if (j.sc.f !== null && j.rc?.f != null) {657          // Carried forward = the reference method, applied to the submission's own earlier values, gives the submitted value.658          const ri = info(R, j.rs, j.rc);659          let how = null, recomputed = false;660          if (ri.ast && !ri.unsupported) {661            try {662              const v = evaluate(ri.ast, SR.ctxFor(j.ss));663              recomputed = true;664              if (same(v, j.gotVal, it.tolerance, it.rel_tolerance)) how = j.same_formula ? "same formula as the reference" : "the reference formula applied to the submission's own values gives the submitted value";665              else if (j.same_formula) note = "Same formula as the reference, but the stored value does not follow from it: stale or overwritten value. Open the file in Excel and recalculate before deciding.";666            } catch (e) { if (!(e instanceof Unsupported) && !(e instanceof RangeError)) throw e; }667          }668          if (!how && !recomputed && j.same_formula) how = "same formula as the reference (not recomputed: outside the evaluated subset)";669          if (how) { credit = it.carried_forward_credit; verdict = "carried_forward_error"; note = `Method right, input wrong: ${how}. The error comes from an earlier cell and is penalised there, not again here.`; }670        }671        if (verdict === "wrong" && !note && typeof j.expVal === "number" && typeof j.gotVal === "number") {672          if (same(-j.expVal, j.gotVal, it.tolerance, it.rel_tolerance)) note = "Sign error: the absolute value is right.";673          else if (j.expVal !== 0 && [100, 0.01, 1000, 0.001, 12, 1 / 12].some((k) => same(j.expVal * k, j.gotVal, it.tolerance * Math.max(1, k), Math.max(it.rel_tolerance, 1e-9)))) note = `Scale error: the submitted value is the expected value times ${Math.round((j.gotVal / j.expVal) * 1e6) / 1e6}.`;674        }675      } else if (verdict === "unknown") { review.push(j.ref); note = j.reason; }676      else note = verdict === "missing_sheet" ? "The sheet is missing from the submission." : "The cell is empty in the submission.";677      tally[verdict] = (tally[verdict] ?? 0) + 1;678      const o = { ...publicCell(j), verdict, credit };679      delete o.reason;680      if (note) o.note = note;681      return o;682    });683    const mean = evidence.reduce((a, e) => a + e.credit, 0) / evidence.length;684    const awarded = Math.round(it.points * (it.partial === "all_or_nothing" ? (evidence.every((e) => e.credit === 1) ? 1 : 0) : mean) * 100) / 100;685    total += awarded; max += it.points;686    const o = { id: it.id, label: it.label, points: it.points, awarded, partial: it.partial, tolerance: it.tolerance, formula_required: it.formula_required, cells: evidence.length, fully_correct: evidence.filter((e) => e.credit === 1 && e.verdict === "correct").length, evidence };687    if (it.rel_tolerance) o.rel_tolerance = it.rel_tolerance;688    return o;689  });690  total = Math.round(total * 100) / 100;691  const inputs = changedInputs(R, S, 0, 0);692  const warnings = [];693  if (review.length) warnings.push(`${review.length} cell(s) could not be valued without Excel and received 0: open the submission and grade them by hand.`);694  if (inputs.total) warnings.push(`${inputs.total} input cell(s) differ from the reference. If the inputs were given, the student altered the data: carried-forward credit then follows from a changed input, which no rubric item may be penalising.`);695  if (tally.carried_forward_error) warnings.push("Carried-forward credit was granted: check that the originating error is itself covered by a rubric item, otherwise it is never penalised.");696  return {697    disclaimer: DISCLAIMER, reference: R.path ? basename(R.path) : null, submission: S.path ? basename(S.path) : null,698    suggested_total: total, max_points: max, percent: max ? Math.round((total / max) * 1e4) / 100 : null, verdict_counts: tally, items: graded,699    needs_human_review: review, inputs_changed: inputs, structure: structure(R, S), warnings,700    policy: { tolerance: "absolute, per item (default 0.005); rel_tolerance adds a relative band", formula_required: "a right value typed as a constant earns constant_credit (default 0.5)", carried_forward: "a wrong value produced by the right method on a wrong earlier value earns carried_forward_credit (default 1)", partial: "proportional = mean cell credit × points; all_or_nothing = points only if every cell is fully correct" },701  };702}703704/* ---------- report ---------- */705export function buildReport(wb, { tolerance = 0.005, limit = 100 } = {}) {706  return { generated_by: "xlsx-audit", summary: summary(wb), errors: findErrors(wb, { limit }), circular: findCircular(wb, { limit }), hardcoded: findHardcoded(wb, { limit }), consistency: formulaConsistency(wb, { limit }), controls: checkControls(wb, { tolerance, limit }), recalc: recalcCheck(wb, { limit }) };707}708709const md = (s) => (typeof s === "number" ? String(Number(s.toPrecision(12))) : String(s ?? "").replace(/\|/g, "\\|").replace(/\n/g, " "));710const code = (s) => `\`${String(s).replace(/`/g, "'")}\``;711function table(head, rows) { return rows.length ? `| ${head.join(" | ")} |\n|${head.map(() => "---").join("|")}|\n${rows.map((r) => `| ${r.map(md).join(" | ")} |`).join("\n")}\n` : ""; }712713export function renderMarkdown(r) {714  const s = r.summary, L = [];715  const more = (c) => (c.truncated ? `\n_${c.total - c.returned} more not shown._\n` : "");716  L.push(`# Workbook audit: ${s.file ?? "workbook"}\n`);717  L.push(`${s.totals.sheets} sheets, ${s.totals.cells} non-empty cells, ${s.totals.formulas} formulas. ${r.recalc.outside_subset} formulas (${(r.recalc.share_outside_subset * 100).toFixed(1)} %) could not be recomputed by this tool and were read from their stored values.\n`);718  const findings = [["Error cells (root causes)", `${r.errors.error_cells} (${r.errors.root_causes})`], ["Circular references", r.circular.circular_references], ["Constants inside formulas", r.hardcoded.constants_inside_formulas.total], ["Typed constants where a formula is expected", r.hardcoded.constants_where_a_formula_is_expected.total], ["Cells breaking their row / column pattern", r.consistency.inconsistent_cells], ["Controls failing / unknown / total", r.controls.found ? `${r.controls.failing} / ${r.controls.unknown} / ${r.controls.controls}` : "no control found"], ["Stored values that differ from a recomputation", `${r.recalc.mismatch} of ${r.recalc.recomputed} recomputed`]];719  L.push(`## Findings at a glance\n\n${table(["Check", "Result"], findings)}`);720  L.push(`## Sheets\n\n${table(["Sheet", "State", "Used range", "Formulas", "Numbers", "Errors", "Formula patterns"], s.sheets.map((x) => [x.name, x.state, x.used_range ?? "empty", x.formulas, x.numbers, x.errors, x.distinct_formula_patterns]))}`);721  if (s.warnings.length) L.push(`## Warnings\n\n${s.warnings.map((w) => `- ${w}`).join("\n")}\n`);722  if (r.errors.error_cells) L.push(`## Errors\n\n${table(["Cell", "Error", "Root cause", "Formula", "Comes from"], r.errors.items.map((e) => [e.ref, e.error, e.root_cause ? "yes" : "no", e.formula ? code(e.formula) : "", (e.propagated_from ?? []).join(", ")]))}${more(r.errors)}`);723  if (r.circular.circular_references) L.push(`## Circular references\n\n${r.circular.items.map((c) => `- ${c.loop.join(" → ")}`).join("\n")}\n`);724  const p = r.hardcoded.constants_where_a_formula_is_expected;725  if (p.total) L.push(`## Typed constants where a formula is expected\n\n${table(["Cell", "Value", "Direction", "Expected formula"], p.items.map((e) => [e.ref, e.value, e.direction, code(e.expected_formula)]))}${more(p)}`);726  const h = r.hardcoded.constants_inside_formulas;727  if (h.total) L.push(`## Constants inside formulas\n\n${table(["Cell", "Formula", "Constants"], h.items.map((e) => [e.ref, code(e.formula), e.constants.join(", ")]))}${more(h)}\n${r.hardcoded.note}\n`);728  if (r.consistency.inconsistent_cells) L.push(`## Broken formula patterns\n\n${table(["Cell", "Direction", "Formula", "Expected", "Run", "Confidence"], r.consistency.items.map((e) => [e.ref, e.direction, code(e.formula), code(e.expected_formula), e.run, e.confidence]))}${more(r.consistency)}`);729  L.push(`## Controls\n\n${r.controls.found ? `${r.controls.passing} passing, ${r.controls.failing} failing, ${r.controls.unknown} unknown (tolerance ${r.controls.tolerance}).${r.controls.overall_cell ? ` Overall cell ${r.controls.overall_cell.ref} = ${r.controls.overall_cell.value}.` : ""}\n\n${table(["Cell", "Control", "Kind", "Value", "Pass"], r.controls.items.map((c) => [c.ref, c.label ?? "", c.kind, c.value, c.pass === null ? "unknown" : c.pass ? "yes" : "NO"]))}` : ""}${r.controls.warnings.map((w) => `- ${w}`).join("\n")}\n`);730  L.push(`## Recalculation check\n\nVerdict: ${r.recalc.verdict}. ${r.recalc.match} of ${r.recalc.recomputed} recomputed formulas agree with their stored value; ${r.recalc.outside_subset} formulas could not be recomputed; ${r.recalc.without_cached_value} have no stored value.\n\n${table(["Cell", "Formula", "Stored", "Recomputed"], r.recalc.mismatches.items.map((m) => [m.ref, code(m.formula), m.cached, m.recomputed]))}${r.recalc.outside_subset_reasons.length ? `\nNot recomputed: ${r.recalc.outside_subset_reasons.map((x) => `${x.reason} (${x.count})`).join(", ")}.\n` : ""}`);731  L.push(`## Limits of this audit\n\n- ${SUBSET}\n- Macros, external links, pivot tables, charts, conditional formats and data validation are not read.\n- A clean report does not prove the model is right: it proves these mechanical defects are absent.\n`);732  return L.join("\n");733}734735export function renderGradeMarkdown(g) {736  const L = [`# Grading aid: ${g.submission ?? "submission"}\n`, `> ${g.disclaimer}\n`, `Suggested total: **${g.suggested_total} / ${g.max_points}**${g.percent === null ? "" : ` (${g.percent} %)`}\n`];737  L.push(table(["Item", "Label", "Awarded", "Points", "Cells fully correct"], g.items.map((i) => [i.id, i.label ?? "", i.awarded, i.points, `${i.fully_correct} / ${i.cells}`])));738  for (const i of g.items) { const bad = i.evidence.filter((e) => e.verdict !== "correct"); if (bad.length) L.push(`## ${i.id}${i.label ? ` — ${i.label}` : ""}\n\n${table(["Cell", "Verdict", "Credit", "Expected", "Submitted", "Submitted formula", "Note"], bad.map((e) => [e.ref, e.verdict, e.credit, e.expected, e.got, e.got_formula ? code(e.got_formula) : "", e.note ?? ""]))}`); }739  if (g.warnings.length) L.push(`## To check by hand\n\n${g.warnings.map((w) => `- ${w}`).join("\n")}\n`);740  return L.join("\n");741}742743/** Writes a new file inside root. Never overwrites: an existing destination is an error. */744export function writeNew(root, p, content, extensions = [".md", ".json"]) {745  const dest = root === null ? p : resolveInside(root, p, { mustExist: false });746  if (!extensions.includes(extname(dest).toLowerCase())) throw new XlsxError("bad_output", `The output file must end with ${extensions.join(" or ")}.`);747  try { writeFileSync(dest, content, { flag: "wx" }); }748  catch (e) { if (e.code === "EEXIST") throw new XlsxError("exists", `${basename(dest)} already exists. Reports never overwrite a file: choose another name.`); throw new XlsxError("write_failed", `Cannot write ${basename(dest)}: ${e.code ?? e.message}`); }749  return { path: dest, bytes: Buffer.byteLength(content) };750}751
Files main
Menu

Theme