SPB Git

Repository Public Research preview

offline.test.mjs 37.7 KB 542 lines javascript

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; &amp; &#65;&#x42;</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
Files main
Menu

Theme