Repository Public Research preview
1/**2 * =============================================================================3 * SP BOUCHER | SOFTWARE & RESEARCH4 * ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5 * =============================================================================6 *7 * Project : LLM API toolkits8 * File : xlsx/mcp/test/offline.test.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 */4142// Offline tests of the engine: no Excel, no network, no Python. Run with `npm test`.43import assert from "node:assert/strict";44import { existsSync, mkdirSync, mkdtempSync, readFileSync, readdirSync, symlinkSync, writeFileSync } from "node:fs";45import { tmpdir } from "node:os";46import { dirname, join } from "node:path";47import { test } from "node:test";48import { fileURLToPath } from "node:url";49import { createApi } from "../lib/api.mjs";50import * as A from "../lib/audit.mjs";51import { Unsupported, evaluate, numericLiterals, parse, roundHalfAway, shiftFormula, toR1C1, tokenize } from "../lib/formula.mjs";52import { parseRange } from "../lib/refs.mjs";53import { limitsFromEnv, loadWorkbook, openWorkbook } from "../lib/workbook.mjs";54import { readZip, unsafeEntryName } from "../lib/zip.mjs";55import { FIXTURES } from "./fixtures/make-fixtures.mjs";56import { HEAD, NS, workbook, zip } from "./fixtures/xlsx-writer.mjs";5758const here = dirname(fileURLToPath(import.meta.url)), fx = join(here, "fixtures");59const tmp = mkdtempSync(join(tmpdir(), "xlsx-audit-"));60const code = (fn) => { try { fn(); return null; } catch (e) { return e.code ?? `uncoded:${e.message}`; } };61const open = (name) => openWorkbook(null, join(fx, name));62const refs = (list) => list.map((x) => x.ref);6364/* ───────── formulas ───────── */6566test("tokenizer tells cells, ranges, functions, names and sheet prefixes apart", () => {67 const kinds = (f) => tokenize(f).map((t) => t.t).join(" ");68 assert.equal(kinds("=A1+$B$2*Sheet2!C3"), "ref op ref op ref");69 assert.equal(kinds("'My Sheet'!A1:B2"), "ref");70 assert.equal(tokenize("'It''s'!A1")[0].sheet, "It's");71 assert.equal(kinds("LOG10(X1)"), "func lp ref rp"); // LOG10 is a function, not cell LOG1072 assert.equal(kinds("TaxRate*A1"), "name op ref");73 assert.equal(kinds("TRUE=false"), "bool op bool");74 assert.equal(kinds("SUM(A:A)+SUM(2:3)"), "func lp ref rp op func lp ref rp");75 assert.equal(tokenize("A:C")[0].a.whole, "col");76 assert.equal(kinds("A1<>B1"), "ref op ref");77 assert.equal(kinds("IF(A1>=1;2;3)"), "func lp ref op num comma num comma num rp"); // semicolon separators accepted78 assert.equal(kinds('"a ""quoted"" A1"'), "str");79 assert.equal(kinds("Calc!#REF!+#DIV/0!"), "err op err");80 assert.equal(kinds("_xlfn.XLOOKUP(A1,B:B,C:C)"), "func lp ref comma ref comma ref rp");81 assert.equal(tokenize("_xlfn.XLOOKUP(1)")[0].v, "XLOOKUP");82 assert.equal(kinds("1.5E+3%"), "num op");83 assert.throws(() => tokenize("[Other.xlsx]Sheet1!A1"), Unsupported);84 assert.throws(() => tokenize("{1,2,3}"), Unsupported);85 assert.throws(() => tokenize('"open'), /unterminated/);86});8788test("R1C1 normalisation makes copied formulas identical and shifted ones different", () => {89 assert.equal(toR1C1("B2*$C$1", 2, 4), "R[0]C[-2]*R1C3");90 assert.equal(toR1C1("C2*$C$1", 2, 5), toR1C1("B2*$C$1", 2, 4));91 assert.equal(toR1C1("B3*$C$1", 3, 4), toR1C1("B2*$C$1", 2, 4));92 assert.notEqual(toR1C1("B2*$C$2", 2, 4), toR1C1("B2*$C$1", 2, 4));93 assert.equal(toR1C1("SUM(B2:B10)", 11, 2), "SUM(R[-9]C[0]:R[-1]C[0])");94 assert.equal(toR1C1("Data!$A2+B$1", 2, 2), "Data!R[0]C1+R1C[0]");95 assert.equal(toR1C1("SUM(A:A)", 5, 3), "SUM(C[-2]:C[-2])");96});9798test("shiftFormula moves relative references only, as a shared formula does", () => {99 assert.equal(shiftFormula("A2*B2+$E$1", 1, 0), "A3*B3+$E$1");100 assert.equal(shiftFormula("B2*$C$1+SUM(A$1:A2)", 3, 1), "C5*$C$1+SUM(B$1:B5)");101 assert.equal(shiftFormula('A1&"A1"', 1, 1), 'B2&"A1"'); // text is left alone102 assert.equal(shiftFormula("'A1 data'!A1+LOG10(A1)", 1, 0), "'A1 data'!A2+LOG10(A2)");103 assert.equal(shiftFormula("A1", -1, 0), "#REF!");104 assert.equal(shiftFormula("TaxRate*A1", 0, 1), "TaxRate*B1");105});106107test("evaluator: arithmetic, precedence, rounding, blanks, errors, lazy IF", () => {108 const cells = { "1,1": 10, "2,1": 4, "3,1": 0, "4,1": "text", "5,1": true, "6,1": "12" };109 const ctx = { cell: (s, c, r) => cells[`${c},${r}`] ?? null, range: (s, c1, r1, c2, r2) => Object.entries(cells).filter(([k]) => { const [c, r] = k.split(",").map(Number); return c >= c1 && c <= c2 && r >= r1 && r <= r2; }).map(([, v]) => v), name: (n) => (n === "Rate" ? { sheet: null, a: { col: 2, row: 1 }, b: null } : null) };110 const ev = (f) => { const v = evaluate(parse(f), ctx); return v && typeof v === "object" ? v.error : v; };111 assert.equal(ev("A1+B1*2"), 18);112 assert.equal(ev("(A1+B1)*2"), 28);113 assert.equal(ev("-A1^2"), 100); // Excel: unary minus binds tighter than ^114 assert.equal(ev("2^3^2"), 64); // and ^ is left-associative115 assert.equal(ev("50%*A1"), 5);116 assert.equal(ev("A1/C1"), "#DIV/0!");117 assert.equal(ev("IF(C1=0,0,A1/C1)"), 0); // the untaken branch is not evaluated118 assert.equal(ev("IF(A1>5,\"big\",\"small\")"), "big");119 assert.equal(ev("IF(A1<5,1)"), false);120 assert.equal(ev("Z9+1"), 1); // blank counts as 0121 assert.equal(ev("D1+1"), "#VALUE!");122 assert.equal(ev("F1+1"), 13); // numeric text is coerced in arithmetic123 assert.equal(ev("SUM(A1:F1)"), 14); // text and booleans inside a range are ignored124 assert.equal(ev("AVERAGE(A1:C1)"), 14 / 3);125 assert.equal(ev("MIN(A1:C1)"), 0);126 assert.equal(ev("MAX(A1:C1,25)"), 25);127 assert.equal(ev("AVERAGE(X1:X9)"), "#DIV/0!");128 assert.equal(ev("Rate*2"), 8);129 assert.equal(ev("ROUND(2.5,0)"), 3);130 assert.equal(ev("ROUND(-2.5,0)"), -3); // half away from zero, unlike Math.round131 assert.equal(ev("ROUND(1.005,2)"), 1.01); // and decimal, unlike binary rounding132 assert.equal(ev("ROUND(1234.5,-2)"), 1200);133 assert.equal(ev('"a"="A"'), true);134 assert.equal(ev("1=1"), true);135 assert.equal(ev("A1<>B1"), true);136 assert.equal(roundHalfAway(0.285, 2), 0.29);137 for (const f of ["VLOOKUP(1,A1:B2,2)", 'A1&"x"', "Unknown*2", "SUM(A1:B1)*A1:B1"]) assert.throws(() => evaluate(parse(f), ctx), Unsupported, f);138});139140test("numeric constants: ROUND digits are not reported, the rest is", () => {141 assert.deepEqual(numericLiterals(parse("ROUND(A1*1.15,2)+0.5")), [1.15, 0.5]);142 assert.deepEqual(numericLiterals(parse("A1*(1+B1)")), [1]);143 assert.deepEqual(numericLiterals(parse("SUM(A1:A9)/12")), [12]);144 assert.deepEqual(numericLiterals(parse("A1+B1")), []);145});146147test("ranges are parsed with or without sheet and quotes", () => {148 assert.deepEqual(parseRange("Calc!B2:D5"), { sheet: "Calc", c1: 2, r1: 2, c2: 4, r2: 5 });149 assert.deepEqual(parseRange("'My Data'!$A$1"), { sheet: "My Data", c1: 1, r1: 1, c2: 1, r2: 1 });150 assert.equal(parseRange("D5:B2").c1, 2);151 assert.equal(parseRange("A1:B2:C3"), null);152 assert.equal(parseRange("hello"), null);153});154155/* ───────── fixtures ───────── */156157test("committed fixtures are exactly what the generator produces", () => {158 for (const [name, make] of Object.entries(FIXTURES)) assert.ok(readFileSync(join(fx, name)).equals(make()), `${name} is out of date: run npm run fixtures`);159});160161test("reader: shared strings, shared formulas, formats, hidden sheets, names", () => {162 const wb = open("features.xlsx"), s = A.summary(wb);163 assert.deepEqual(s.sheets.map((x) => [x.name, x.state, x.used_range]), [["My Data", "visible", "A1:D5"], ["Report", "visible", "A1:B4"], ["Hidden", "hidden", "A1:A1"]]);164 const cells = Object.fromEntries(A.readRange(wb, { sheet: "My Data" }).cells.map((c) => [c.ref, c]));165 assert.equal(cells.D1.value, "Rich text & more"); // rich-text runs joined, entity decoded166 assert.equal(cells.B1.value, "Qty"); // phonetic run ignored167 assert.equal(cells.C3.formula, "=A3*B3+$E$1"); // shared formula expanded168 assert.equal(cells.C4.formula, "=A4*B4+$E$1");169 assert.equal(cells.A4.value, 30); // cell without an r attribute takes the next column170 assert.equal(cells.D2.number_format, "yyyy\\-mm\\-dd");171 assert.equal(cells.D3.number_format, "0.00%");172 assert.equal(cells.A2.number_format, "#,##0.00");173 assert.equal(cells.D4.value, true);174 assert.equal(cells.A5, undefined); // styled but empty175 assert.deepEqual(s.defined_names.map((n) => [n.name, n.scope, n.plain_reference]), [["LocalRate", "Report", true], ["Block", "workbook", true], ["Const", "workbook", false]]);176 const r = A.recalcCheck(wb);177 assert.equal(r.mismatch, 0);178 assert.equal(r.match, 7); // includes SUM over a whole column, a whole row, and a sheet-scoped name179 assert.deepEqual(r.outside_subset_reasons.map((x) => x.reason).sort(), ["function VLOOKUP", "text concatenation (&)"]);180});181182test("paging through a range returns every cell once", () => {183 const wb = open("clean.xlsx"), seen = [];184 let offset = 0, pages = 0;185 while (offset !== null) { const p = A.readRange(wb, { range: "Calc!A1:G7", offset, limit: 10 }); seen.push(...refs(p.cells)); offset = p.next_offset; pages++; assert.equal(p.non_empty_cells, 48); }186 assert.equal(pages, 5);187 assert.equal(new Set(seen).size, 48);188 assert.equal(code(() => A.readRange(wb, { range: "Nope!A1" })), "sheet_not_found");189 assert.equal(code(() => A.readRange(wb, { range: "A1:B2" })), "bad_range");190 assert.equal(A.readRange(wb, { range: "calc!b3" }).cells[0].formula, "=B2*Inputs!$B$2"); // sheet names are case-insensitive191});192193test("clean model: no finding at all", () => {194 const wb = open("clean.xlsx"), s = A.summary(wb);195 assert.deepEqual(s.totals, { sheets: 5, cells: 114, formulas: 57, error_cells: 0, formulas_without_cached_value: 0, circular_references: 0 });196 assert.equal(A.findErrors(wb).error_cells, 0);197 assert.equal(A.findCircular(wb).circular_references, 0);198 const h = A.findHardcoded(wb);199 assert.equal(h.constants_inside_formulas.total, 0);200 assert.equal(h.constants_where_a_formula_is_expected.total, 0);201 const c = A.formulaConsistency(wb);202 assert.equal(c.inconsistent_cells, 0); // B2 opens a recurrence and G3:G7 are totals: neither is reported203 assert.ok(c.runs_checked >= 10);204 const k = A.checkControls(wb);205 assert.deepEqual([k.controls, k.passing, k.failing, k.unknown, k.all_pass, k.overall_cell.ref, k.overall_cell_consistent], [4, 4, 0, 0, true, "Controls!E7", true]);206 assert.deepEqual(k.warnings, []);207 const r = A.recalcCheck(wb);208 assert.deepEqual([r.formulas, r.recomputed, r.match, r.mismatch, r.outside_subset, r.verdict], [57, 52, 52, 0, 5, "consistent_where_checked"]);209 assert.deepEqual(r.outside_subset_reasons, [{ reason: "function ABS", count: 4 }, { reason: "function AND", count: 1 }]);210 assert.equal(s.evaluator.share_outside_subset, 0.0877);211});212213test("an independent calculator (LibreOffice) agrees with the engine and with the fixtures", () => {214 // clean-libreoffice.xlsx is nocache.xlsx opened and saved by LibreOffice: its stored values were computed there.215 const lo = open("clean-libreoffice.xlsx"), r = A.recalcCheck(lo);216 assert.deepEqual([r.formulas, r.match, r.mismatch, r.without_cached_value], [57, 52, 0, 0]);217 const c = A.compareWorkbooks(open("clean.xlsx"), lo, { tolerance: 1e-6 });218 assert.deepEqual(c.counts, { right_by_formula: 57, right_but_typed_as_constant: 0, wrong: 0, missing: 0, unknown: 0 });219 assert.equal(c.same_formula_pattern, 57);220 assert.equal(A.checkControls(lo).all_pass, true);221});222223test("planted defects are found at their exact cells", () => {224 const wb = open("defects.xlsx");225 const e = A.findErrors(wb);226 assert.deepEqual(e.items.map((x) => [x.ref, x.error, x.root_cause]), [["Outputs!B4", "#REF!", true], ["Outputs!B6", "#DIV/0!", true], ["Outputs!B7", "#DIV/0!", false]]);227 assert.equal(e.items[0].broken_reference, true);228 assert.deepEqual(e.items[2].propagated_from, ["Outputs!B6"]);229 assert.deepEqual([e.root_causes, e.propagated], [2, 1]);230231 const circ = A.findCircular(wb);232 assert.equal(circ.circular_references, 1);233 assert.deepEqual(circ.items[0].loop, ["Calc!B9", "Calc!B10", "Calc!B9"]);234235 const h = A.findHardcoded(wb);236 assert.deepEqual(h.constants_where_a_formula_is_expected.items, [{ ref: "Calc!D3", value: 110000, direction: "row", neighbours: ["C3", "E3"], neighbour_pattern: "R[-1]C[0]*Inputs!R2C2", expected_formula: "=D2*Inputs!$B$2" }]);237 assert.deepEqual(h.constants_inside_formulas.items.map((x) => [x.ref, x.constants]), [["Calc!E4", [0.65]], ["Calc!B9", [0.1]], ["Calc!B10", [1000]], ["Outputs!B7", [2]]]);238 assert.deepEqual(refs(A.findHardcoded(wb, { ignore: [0, 1, 2, 1000] }).constants_inside_formulas.items), ["Calc!E4", "Calc!B9"]);239240 const c = A.formulaConsistency(wb);241 assert.deepEqual(c.items, [{ ref: "Calc!E4", direction: "row", formula: "=E3*0.65", expected_formula: "=E3*Inputs!$B$5", run: "B4:G4", majority: "4 of 6", confidence: "high" }]);242243 const k = A.checkControls(wb);244 assert.deepEqual([k.controls, k.passing, k.failing, k.all_pass], [4, 3, 1, false]);245 assert.deepEqual(k.failing_controls.map((x) => [x.ref, x.label, x.value]), [["Controls!E5", "Revenue = units x price", false]]);246 assert.deepEqual([k.overall_cell.ref, k.overall_cell.pass, k.overall_cell_consistent], ["Controls!E7", false, true]);247248 const r = A.recalcCheck(wb);249 assert.equal(r.mismatch, 0); // the stored values of this file follow its (defective) formulas250 assert.deepEqual(r.outside_subset_reasons.find((x) => x.reason === "circular reference"), { reason: "circular reference", count: 2 });251});252253test("a stale stored value is pinned to its cell", () => {254 const r = A.recalcCheck(open("stale.xlsx"));255 assert.equal(r.verdict, "stale_or_altered_values");256 assert.deepEqual(r.mismatches.items.map((m) => [m.ref, m.cached]), [["Outputs!B5", 0.5]]);257 assert.ok(Math.abs(r.mismatches.items[0].recomputed - 0.3) < 1e-6);258});259260test("a workbook without stored values is evaluated where possible and unknown elsewhere", () => {261 const wb = open("nocache.xlsx"), r = A.recalcCheck(wb);262 assert.deepEqual([r.verdict, r.recomputed, r.without_cached_value, r.outside_subset], ["no_cached_values", 0, 52, 5]);263 const cells = Object.fromEntries(A.readRange(wb, { range: "Outputs!B1:B5" }).cells.map((c) => [c.ref, c]));264 assert.deepEqual([cells.B1.value, cells.B1.source], [552563.125, "evaluated"]);265 assert.equal(cells.B2.value, 165768.94);266 const e7 = A.readRange(wb, { range: "Controls!E7" }).cells[0];267 assert.deepEqual([e7.value, e7.source, e7.reason], [null, "unknown", "function AND"]); // never guessed268 const k = A.checkControls(wb);269 assert.equal(k.overall_cell.pass, null);270 assert.equal(k.overall_cell_consistent, null);271 assert.match(k.warnings.join(" "), /no stored value/);272 assert.deepEqual([k.controls, k.failing], [4, 0]); // the four differences are evaluable and within tolerance273 assert.ok(k.items.every((c) => c.kind === "difference" && c.pass === true));274});275276test("tracing follows references across sheets, both ways", () => {277 const wb = open("defects.xlsx");278 const p = A.trace(wb, { cell: "Outputs!B2", depth: 2 });279 assert.deepEqual(p.references_as_written, ["Calc!G7"]);280 assert.deepEqual(p.nodes.map((n) => [n.ref, n.level]), [["Calc!G7", 1], ["Calc!B7", 2], ["Calc!C7", 2], ["Calc!D7", 2], ["Calc!E7", 2], ["Calc!F7", 2]]);281 assert.equal(p.deeper_levels_exist, true);282 const deep = A.trace(wb, { cell: "Outputs!B1", depth: 10 });283 assert.ok(refs(deep.nodes).includes("Inputs!B2") && deep.nodes.find((n) => n.ref === "Inputs!B2").input);284 assert.ok(refs(deep.nodes).includes("Calc!D3")); // the pasted constant shows up as an input feeding the total285 const d = A.trace(wb, { cell: "Inputs!B8", direction: "dependents", depth: 3 });286 assert.deepEqual(d.nodes.map((n) => [n.ref, n.level]), [["Outputs!B6", 1], ["Outputs!B7", 2]]);287 assert.equal(A.trace(wb, { cell: "Calc!B9" }).in_circular_reference, true);288 assert.equal(A.trace(wb, { cell: "Calc!B3" }).in_circular_reference, false);289 const name = A.trace(wb, { cell: "Calc!B6" });290 assert.ok(name.references_as_written.some((x) => x.startsWith("TaxRate (name → Inputs!$B$6")) && refs(name.nodes).includes("Inputs!B6"));291 const empty = A.trace(wb, { cell: "Calc!C9", direction: "dependents" });292 assert.equal(empty.count, 0);293 assert.equal(A.trace(open("clean.xlsx"), { cell: "Inputs!B3", direction: "dependents", depth: 1 }).count, 4);294 assert.equal(code(() => A.trace(wb, { cell: "Calc!A1:B2" })), "bad_range");295});296297test("formulas grouped by pattern describe the model in a few lines", () => {298 const g = A.listFormulas(open("clean.xlsx"), { sheet: "Calc", group: true });299 assert.equal(g.formulas, 35);300 assert.deepEqual(g.groups[0], { sheet: "Calc", pattern: "R[-2]C[0]-R[-1]C[0]", count: 10, first: "B5", example: "=B3-B4", cells: ["B5", "C5", "D5", "E5", "F5", "B7", "C7", "D7", "E7", "F7"] });301 const l = A.listFormulas(open("clean.xlsx"), { range: "Calc!B3:B3" });302 assert.deepEqual([l.items[0].ref, l.items[0].dependents, l.items[0].precedents, l.items[0].evaluated_by_this_tool], ["Calc!B3", 4, 2, true]); // B4, B5, G3, Controls!C4303});304305/* ───────── comparison and grading ───────── */306307const RUBRIC = { items: [308 { id: "units", label: "Units", range: "Calc!C2:F2", points: 4, formula_required: true },309 { id: "revenue", range: "Calc!B3:F3", points: 5 },310 { id: "costs", range: "Calc!B4:F4", points: 5 },311 { id: "margin", range: "Calc!B5:F5", points: 4 },312 { id: "tax_net", range: "Calc!B6:F7", points: 4 },313 { id: "outputs", range: "Outputs!B1:B5", points: 5 },314 { id: "strict", range: "Outputs!B1:B2", points: 2, partial: "all_or_nothing", carried_forward_credit: 0.5 },315] };316317test("comparison classifies every reference formula cell", () => {318 const c = A.compareWorkbooks(open("reference.xlsx"), open("submission.xlsx"));319 assert.equal(c.cells_compared, 57);320 assert.deepEqual(c.counts, { right_by_formula: 21, right_but_typed_as_constant: 4, wrong: 31, missing: 1, unknown: 0 });321 assert.equal(c.different_formula_same_value, 1); // C3 written as Inputs!$B$2*C2322 const by = Object.fromEntries(c.differences.items.map((x) => [x.ref, x.verdict]));323 assert.deepEqual([by["Calc!C2"], by["Calc!B4"], by["Outputs!B4"], by["Calc!B3"]], ["match_constant", "wrong", "missing", undefined]);324 assert.deepEqual(c.structure.sheets_missing, []);325 assert.deepEqual(c.structure.used_ranges.map((u) => [u.sheet, u.formulas_reference, u.formulas_submission]), [["Calc", 35, 31], ["Outputs", 5, 4]]);326 assert.equal(c.inputs_changed.total, 0);327 const scoped = A.compareWorkbooks(open("reference.xlsx"), open("submission.xlsx"), { ranges: ["Calc!B3:F3", "Inputs!B2:B7"] });328 assert.deepEqual([scoped.cells_compared, scoped.counts.right_by_formula, scoped.counts.right_but_typed_as_constant], [11, 5, 6]);329 const self = A.compareWorkbooks(open("reference.xlsx"), open("clean.xlsx"));330 assert.deepEqual([self.counts.right_by_formula, self.differences.total], [57, 0]);331});332333test("structural differences and changed inputs are reported", () => {334 const ref = loadWorkbook(workbook({ sheets: [{ name: "In", cells: { A1: 10, A2: 20 } }, { name: "Out", cells: { A1: { f: "In!A1+In!A2", v: 30 } } }], names: [{ name: "Base", ref: "In!$A$1" }] }));335 const sub = loadWorkbook(workbook({ sheets: [{ name: "Extra", cells: { A1: 1 } }, { name: "In", cells: { A1: 11, A2: { f: "A1*2", v: 22 }, A9: { f: "A1", v: 11 } } }] }));336 const c = A.compareWorkbooks(ref, sub);337 assert.deepEqual([c.structure.sheets_missing, c.structure.sheets_added, c.structure.names_missing], [["Out"], ["Extra"], ["Base"]]);338 assert.deepEqual(c.inputs_changed.items.map((x) => [x.ref, x.change]), [["In!A1", "value_changed"], ["In!A2", "replaced_by_formula"]]);339 assert.equal(c.counts.missing, 1);340 assert.deepEqual(refs(c.extra_formulas_in_submission.items), ["In!A9"]);341});342343test("grading: formula, typed constant, wrong, carried-forward, missing", () => {344 const g = A.gradeSubmission(open("reference.xlsx"), open("submission.xlsx"), RUBRIC);345 assert.deepEqual(g.items.map((i) => [i.id, i.awarded]), [["units", 2], ["revenue", 5], ["costs", 0], ["margin", 4], ["tax_net", 4], ["outputs", 3], ["strict", 0]]);346 assert.deepEqual([g.suggested_total, g.max_points, g.percent], [18, 29, 62.07]);347 assert.deepEqual(g.verdict_counts, { correct_value_typed_as_constant: 4, correct: 7, wrong: 6, carried_forward_error: 18, missing: 1 });348 const ev = (id) => Object.fromEntries(g.items.find((i) => i.id === id).evidence.map((e) => [e.ref, e]));349 assert.deepEqual([ev("units")["Calc!C2"].verdict, ev("units")["Calc!C2"].credit], ["correct_value_typed_as_constant", 0.5]);350 assert.equal(ev("revenue")["Calc!C3"].verdict, "correct"); // an equivalent formula is right351 assert.equal(ev("revenue")["Calc!C3"].same_formula, false);352 assert.deepEqual([ev("costs")["Calc!B4"].verdict, ev("costs")["Calc!B4"].expected, ev("costs")["Calc!B4"].got, ev("costs")["Calc!B4"].got_formula], ["wrong", 60000, 25000, "=B3*Inputs!$B$6"]);353 assert.equal(ev("margin")["Calc!B5"].verdict, "carried_forward_error"); // same formula on a wrong earlier value354 assert.match(ev("margin")["Calc!B5"].note, /same formula as the reference/);355 const o = ev("outputs");356 assert.deepEqual(Object.values(o).map((e) => [e.ref, e.verdict, e.credit]), [["Outputs!B1", "correct", 1], ["Outputs!B2", "carried_forward_error", 1], ["Outputs!B3", "carried_forward_error", 1], ["Outputs!B4", "missing", 0], ["Outputs!B5", "wrong", 0]]);357 assert.match(o["Outputs!B3"].note, /reference formula applied to the submission's own values/); // a different but equivalent formula358 assert.equal(ev("strict")["Outputs!B2"].credit, 0.5);359 assert.match(g.disclaimer, /human grader/);360 assert.match(g.warnings.join(" "), /Carried-forward credit was granted/);361 assert.match(A.renderGradeMarkdown(g), /Suggested total: \*\*18 \/ 29\*\*/);362363 const self = A.gradeSubmission(open("reference.xlsx"), open("clean.xlsx"), RUBRIC);364 assert.deepEqual([self.suggested_total, self.max_points], [29, 29]);365 const harsh = A.gradeSubmission(open("reference.xlsx"), open("submission.xlsx"), { ...RUBRIC, carried_forward_credit: 0, constant_credit: 0 });366 assert.deepEqual(harsh.items.map((i) => i.awarded), [0, 5, 0, 0, 0, 1, 0]);367});368369test("grading: tolerance, sign and scale notes, unknown values need a human", () => {370 const ref = loadWorkbook(workbook({ sheets: [{ name: "S", cells: { A1: 200, A2: { f: "A1*0.15", v: 30 }, A3: { f: "A2-A1", v: -170 }, A4: { f: "A2/A1", v: 0.15 }, A5: { f: "VLOOKUP(1,A1:A2,1)", v: 7 } } }] }));371 const sub = loadWorkbook(workbook({ sheets: [{ name: "S", cells: { A1: 200, A2: { f: "A1*0.15", v: 30.004 }, A3: { f: "A1-A2", v: 170 }, A4: { f: "A2/A1*100", v: 15 }, A5: { f: "VLOOKUP(1,A1:A2,1)" } } }] }));372 const g = A.gradeSubmission(ref, sub, { items: [{ id: "a", range: "S!A2", points: 1 }, { id: "b", range: "S!A3", points: 1 }, { id: "c", range: "S!A4", points: 1 }, { id: "d", range: "S!A5", points: 1 }, { id: "tight", range: "S!A2", points: 1, tolerance: 0.001 }] });373 assert.deepEqual(g.items.map((i) => i.awarded), [1, 0, 0, 0, 0]);374 assert.match(g.items[1].evidence[0].note, /Sign error/);375 assert.match(g.items[2].evidence[0].note, /Scale error.*times 100/);376 assert.match(g.items[4].evidence[0].note, /stored value does not follow/); // same formula, same inputs, different value: not a carried-forward error377 assert.deepEqual(g.needs_human_review, ["S!A5"]);378 assert.match(g.warnings.join(" "), /could not be valued without Excel/);379});380381test("a malformed rubric is refused with a precise message", () => {382 const R = open("reference.xlsx"), S = open("submission.xlsx");383 const bad = (rubric) => code(() => A.gradeSubmission(R, S, rubric));384 assert.equal(bad(null), "rubric_invalid");385 assert.equal(bad({ items: [] }), "rubric_invalid");386 assert.equal(bad({ items: [{ id: "x", range: "B3:F3", points: 1 }] }), "rubric_invalid"); // no sheet387 assert.equal(bad({ items: [{ id: "x", range: "Calc!B3", points: -1 }] }), "rubric_invalid");388 assert.equal(bad({ items: [{ id: "x", range: "Calc!B3", points: 1 }, { id: "x", range: "Calc!B4", points: 1 }] }), "rubric_invalid");389 assert.equal(bad({ items: [{ id: "x", range: "Calc!B3", points: 1, partial: "half" }] }), "rubric_invalid");390 assert.equal(bad({ items: [{ id: "x", range: "Calc!B3", points: 1, constant_credit: 2 }] }), "rubric_invalid");391 assert.equal(bad({ items: [{ id: "x", range: "Calc!Z99", points: 1 }] }), "rubric_invalid"); // empty in the reference392 assert.equal(bad({ items: [{ id: "x", range: "Nope!A1", points: 1 }] }), "sheet_not_found");393});394395test("controls: typed constants, status text, named ranges, explicit ranges, inconsistent overall cell", () => {396 const wb = loadWorkbook(workbook({397 sheets: [398 { name: "Checks", cells: { A1: "Check", B1: "Diff", C1: "Status", A2: "Balance sheet balances", B2: { f: "Data!A1-Data!A2", v: 0 }, C2: { f: 'IF(B2=0,"OK","FAIL")', v: "OK" }, A3: "Cash ties", B3: { f: "Data!A3-Data!A4", v: 12.5 }, C3: { f: 'IF(B3=0,"OK","FAIL")', v: "FAIL" }, A4: "Typed", C4: true, A5: "Difference only", B5: { f: "Data!A1-Data!A2", v: 0.004 }, A6: "Broken", C6: { f: "1/0", v: "#DIV/0!" }, A8: "Overall", C8: { f: "AND(C2:C6)", v: true } } },399 { name: "Data", cells: { A1: 100, A2: 100, A3: 50, A4: 37.5, D1: { f: "A1=A2", v: true }, D2: { f: "A3-A4", v: 12.5 } } },400 ],401 names: [{ name: "chk_balance", ref: "Data!$D$1" }],402 }));403 const k = A.checkControls(wb, { ranges: ["Data!D2"] });404 assert.deepEqual(k.items.map((c) => [c.ref, c.kind, c.pass]), [["Checks!C2", "status", true], ["Checks!C3", "status", false], ["Checks!C4", "boolean", true], ["Checks!B5", "difference", true], ["Checks!C6", "error", false], ["Data!D1", "boolean", true], ["Data!D2", "difference", false]]);405 assert.deepEqual([k.failing, k.all_pass, k.overall_cell.ref, k.overall_cell_consistent], [3, false, "Checks!C8", false]);406 assert.match(k.warnings.join(" "), /typed value instead of a formula/);407 assert.match(k.warnings.join(" "), /overall cell disagrees/);408 assert.equal(A.checkControls(wb, { tolerance: 20 }).failing, 2); // the difference column is judged against the tolerance; status text is not409 const none = A.checkControls(open("features.xlsx"));410 assert.deepEqual([none.found, none.all_pass], [false, null]);411});412413test("consistency: column runs, low confidence when the layout runs the other way, long circular chains", () => {414 const cells = { A1: 1, A2: 2, A3: 3, A4: 4, A5: 5 };415 for (let r = 1; r <= 5; r++) cells[`B${r}`] = { f: r === 4 ? `A${r}*3` : `A${r}*2`, v: 0 };416 const wb = loadWorkbook(workbook({ sheets: [{ name: "S", cells }] }));417 assert.deepEqual(A.formulaConsistency(wb).items.map((x) => [x.ref, x.direction, x.expected_formula, x.confidence]), [["S!B4", "column", "=A4*2", "high"]]);418 assert.equal(A.formulaConsistency(wb, { min_run: 6 }).inconsistent_cells, 0);419 const loop = {};420 for (let r = 1; r <= 300; r++) loop[`A${r}`] = { f: r === 300 ? "A1+1" : `A${r + 1}+1`, v: 0 };421 const c = A.findCircular(loadWorkbook(workbook({ sheets: [{ name: "L", cells: { ...loop, B1: { f: "B1+1", v: 0 } } }] })));422 assert.deepEqual(c.items.map((x) => x.cells).sort((a, b) => a - b), [1, 300]);423 assert.deepEqual(c.items.find((x) => x.cells === 1).loop, ["L!B1", "L!B1"]);424 assert.equal(c.items.find((x) => x.cells === 300).loop.length, 301);425});426427test("reports: Markdown and JSON, never overwriting", () => {428 const api = createApi({ root: tmp, env: {} });429 writeFileSync(join(tmp, "defects.xlsx"), readFileSync(join(fx, "defects.xlsx")));430 const inline = api.export_report({ path: "defects.xlsx" });431 assert.equal(inline.format, "markdown");432 assert.deepEqual(inline.findings, { errors: 3, error_root_causes: 2, circular_references: 1, constants_inside_formulas: 4, typed_constants_where_formula_expected: 1, broken_patterns: 1, controls_failing: 1, controls_found: true, recalc_mismatches: 0, share_outside_subset: 0.1167 });433 for (const s of ["# Workbook audit: defects.xlsx", "| Outputs!B4 | #REF! | yes |", "Calc!B9 → Calc!B10 → Calc!B9", "| Calc!D3 | 110000 | row | `=D2*Inputs!$B$2` |", "| Calc!E4 | row | `=E3*0.65` | `=E3*Inputs!$B$5` |", "Revenue = units x price | boolean | false | NO |", "## Limits of this audit"]) assert.ok(inline.content.includes(s), s);434 assert.ok(!inline.content.includes(tmp)); // no absolute path leaks into a report435 const w = api.export_report({ path: "defects.xlsx", output: "audit.md" });436 assert.equal(readFileSync(join(tmp, "audit.md"), "utf8"), inline.content);437 assert.equal(w.written.bytes, Buffer.byteLength(inline.content));438 assert.equal(code(() => api.export_report({ path: "defects.xlsx", output: "audit.md" })), "exists");439 assert.equal(readFileSync(join(tmp, "audit.md"), "utf8"), inline.content);440 const j = api.export_report({ path: "defects.xlsx", output: "audit.json" });441 assert.equal(j.format, "json");442 assert.equal(JSON.parse(readFileSync(join(tmp, "audit.json"), "utf8")).errors.error_cells, 3);443 assert.equal(code(() => api.export_report({ path: "defects.xlsx", output: "audit.xlsx" })), "bad_output");444 assert.equal(code(() => api.export_report({ path: "defects.xlsx", output: "../escape.md" })), "outside_root");445 assert.equal(code(() => api.export_report({ path: "defects.xlsx", output: "nodir/a.md" })), "not_found");446 assert.ok(!existsSync(join(dirname(tmp), "escape.md")));447});448449/* ───────── hostile and broken files ───────── */450451test("paths are confined to the root, symlinks included", () => {452 const root = join(tmp, "root"), outside = join(tmp, "outside");453 mkdirSync(root); mkdirSync(outside);454 writeFileSync(join(root, "ok.xlsx"), readFileSync(join(fx, "clean.xlsx")));455 writeFileSync(join(outside, "secret.xlsx"), readFileSync(join(fx, "clean.xlsx")));456 symlinkSync(join(outside, "secret.xlsx"), join(root, "link.xlsx"));457 symlinkSync(outside, join(root, "dir"));458 const api = createApi({ root, env: {} });459 assert.equal(api.workbook_summary({ path: "ok.xlsx" }).totals.formulas, 57);460 assert.equal(api.workbook_summary({ path: join(root, "ok.xlsx") }).totals.formulas, 57);461 for (const p of ["../outside/secret.xlsx", join(outside, "secret.xlsx"), "link.xlsx", "dir/secret.xlsx", "/etc/hosts"]) assert.equal(code(() => api.workbook_summary({ path: p })), "outside_root", p);462 assert.equal(code(() => api.workbook_summary({ path: "missing.xlsx" })), "not_found");463 assert.equal(code(() => api.workbook_summary({ path: "" })), "bad_path");464 assert.equal(code(() => api.workbook_summary({ path: "a\0.xlsx" })), "bad_path");465 assert.equal(code(() => api.compare_workbooks({ reference: "ok.xlsx", submission: "link.xlsx" })), "outside_root");466 assert.equal(code(() => api.grade_submission({ reference: "ok.xlsx", submission: "ok.xlsx", rubric_path: "../outside/r.json" })), "outside_root");467 assert.equal(code(() => api.export_report({ path: "ok.xlsx", output: "dir/report.md" })), "outside_root"); // through a symlinked directory468 assert.deepEqual(readdirSync(outside), ["secret.xlsx"]);469 writeFileSync(join(root, "old.xls"), "x"); writeFileSync(join(root, "data.csv"), "a,b");470 assert.equal(code(() => api.workbook_summary({ path: "old.xls" })), "unsupported_type");471 assert.equal(code(() => api.workbook_summary({ path: "data.csv" })), "unsupported_type");472});473474test("malformed archives are refused with a reason", () => {475 const good = readFileSync(join(fx, "clean.xlsx"));476 assert.equal(code(() => loadWorkbook(Buffer.from("this is not a zip file, just text pretending"))), "not_a_zip");477 assert.equal(code(() => loadWorkbook(Buffer.alloc(5))), "not_a_zip");478 assert.equal(code(() => loadWorkbook(good.subarray(0, 300))), "zip_corrupt"); // truncated479 const broken = Buffer.from(good); broken.fill(0xff, 60, 400); // damage compressed data, directory intact480 assert.ok(["zip_corrupt", "not_a_workbook"].includes(code(() => loadWorkbook(broken))));481 assert.equal(code(() => loadWorkbook(zip([{ name: "hello.txt", data: "hi" }]))), "not_a_workbook");482 assert.equal(code(() => loadWorkbook(zip([{ name: "xl/workbook.xml", data: `${HEAD}<workbook xmlns="${NS}"><sheets/></workbook>` }]))), "not_a_workbook");483 assert.equal(code(() => loadWorkbook(zip([{ name: "xl/workbook.xml", data: "x", flags: 1 }]))), "zip_encrypted");484 const z64 = zip([{ name: "a", data: "x" }]); z64.writeUInt16LE(0xffff, z64.length - 22 + 10);485 assert.equal(code(() => readZip(z64)), "zip_unsupported");486 const method = zip([{ name: "a", data: "x", store: true }]); method.writeUInt16LE(12, method.length - 22 - 47 + 10);487 assert.equal(code(() => readZip(method)), "zip_unsupported");488 // a sheet declared in the workbook but absent from the package: reported, not fatal489 const wb = loadWorkbook(zip([{ name: "xl/workbook.xml", data: `${HEAD}<workbook xmlns="${NS}" xmlns:r="r"><sheets><sheet name="Ghost" sheetId="1" r:id="rId1"/></sheets></workbook>` }, { name: "xl/_rels/workbook.xml.rels", data: `${HEAD}<Relationships><Relationship Id="rId1" Type="http://x/worksheet" Target="worksheets/sheet1.xml"/></Relationships>` }]));490 assert.match(wb.warnings.join(" "), /Ghost: part xl\/worksheets\/sheet1.xml is missing/);491});492493test("entry names that try to leave the archive are refused", () => {494 for (const n of ["../evil.xml", "xl/../../evil.xml", "/etc/passwd", "C:/x", "xl\\..\\evil", "", "a\0b"]) assert.equal(unsafeEntryName(n), true, JSON.stringify(n));495 for (const n of ["xl/workbook.xml", "[Content_Types].xml", "xl/worksheets/sheet..1.xml"]) assert.equal(unsafeEntryName(n), false, n);496 const before = readdirSync(tmp).sort();497 for (const name of ["../evil.xml", "xl/../../../evil.xml", "/abs.xml"]) {498 const bad = workbook({ sheets: [{ name: "S", cells: { A1: 1 } }], extra: [{ name, data: "<x/>" }] });499 assert.equal(code(() => loadWorkbook(bad)), "zip_unsafe_name", name);500 }501 assert.deepEqual(readdirSync(tmp).sort(), before); // and nothing is ever extracted to disk502});503504test("decompression bombs stop at the limit, whatever the header declares", () => {505 const big = Buffer.alloc(24 * 1024 * 1024, 0x20), small = limitsFromEnv({ XLSX_AUDIT_MAX_UNZIPPED_MB: "8" });506 const honest = workbook({ sheets: [{ name: "S", xml: big }] });507 assert.ok(honest.length < 100_000); // 24 MB of blanks in a file of a few kilobytes508 assert.equal(code(() => loadWorkbook(honest, small)), "zip_limit");509 const lying = zip([{ name: "xl/workbook.xml", data: big, declaredSize: 100 }]);510 assert.equal(code(() => loadWorkbook(lying, small)), "zip_limit"); // inflation is capped, the declared size is not trusted511 const total = workbook({ sheets: Array.from({ length: 4 }, (_, i) => ({ name: `S${i}`, xml: Buffer.alloc(3 * 1024 * 1024, 0x20) })) });512 assert.equal(code(() => loadWorkbook(total, { ...small, maxEntryBytes: 4 * 1024 * 1024, maxTotalBytes: 8 * 1024 * 1024 })), "zip_limit"); // many medium entries513 const huge = workbook({ sheets: [{ name: "S", xml: Buffer.alloc(140 * 1024 * 1024, 0x20) }] });514 assert.equal(code(() => loadWorkbook(huge)), "zip_limit"); // default limits515 assert.equal(code(() => readZip(zip(Array.from({ length: 6 }, (_, i) => ({ name: `f${i}`, data: "x" }))), { limits: { ...small, maxEntries: 5 } })), "zip_limit");516 assert.equal(code(() => loadWorkbook(readFileSync(join(fx, "clean.xlsx")), { ...small, maxFileBytes: 1000 })), "file_too_large");517 assert.equal(code(() => loadWorkbook(readFileSync(join(fx, "clean.xlsx")), { ...small, maxCells: 20 })), "workbook_too_large");518});519520test("XML entity tricks and formula-like text stay inert", () => {521 const sst = `${HEAD}<!DOCTYPE lolz [<!ENTITY lol "lol"><!ENTITY lol2 "&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;">]><sst xmlns="${NS}"><si><t>&lol2; & AB</t></si></sst>`;522 const sheet = `${HEAD}<worksheet xmlns="${NS}"><sheetData><row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="inlineStr"><is><t>=HYPERLINK("http://x")</t></is></c><c r="C1" t="s"><v>99</v></c></row></sheetData></worksheet>`;523 const wb = loadWorkbook(workbook({ sheets: [{ name: "S", xml: sheet }], sharedStrings: sst }));524 const cells = A.readRange(wb, { sheet: "S" }).cells;525 assert.equal(cells[0].value, "&lol2; & AB"); // custom entities are not expanded526 assert.equal(cells[1].value, '=HYPERLINK("http://x")'); // text that looks like a formula is text527 assert.equal(cells[1].formula, undefined);528 assert.equal(cells[2].value, ""); // a shared-string index out of range does not crash529 assert.equal(A.summary(wb).totals.formulas, 0);530});531532test("macros and external links are flagged, never opened", () => {533 const wb = loadWorkbook(workbook({ sheets: [{ name: "S", cells: { A1: { f: "[1]Other!A1*2", v: 4 }, A2: { f: "SUM(Sheet1:Sheet3!A1)", v: 1 }, A3: { f: "A1+", v: 1 } } }], extra: [{ name: "xl/vbaProject.bin", data: "MZ" }, { name: "xl/externalLinks/externalLink1.xml", data: "<x/>" }] }));534 const s = A.summary(wb);535 assert.deepEqual([s.has_macros, s.has_external_links], [true, true]);536 assert.match(s.warnings.join(" "), /neither read nor run/);537 const r = A.recalcCheck(wb);538 assert.deepEqual([r.recomputed, r.outside_subset], [0, 3]);539 assert.deepEqual(r.outside_subset_reasons.map((x) => x.reason).sort(), ["3-D reference", "external workbook or structured table reference", "unparsable formula (unexpected end of formula)"]);540 assert.equal(A.readRange(wb, { range: "S!A1" }).cells[0].value, 4); // the stored value is still readable541});542