ObsiGate/tests/frontend/xlsx-formula.test.mjs

#!/usr/bin/env node
/**
 * ObsiGate — unit tests for the spreadsheet formula engine
 * (frontend/js/xlsx/formula.js). Pure logic, no DOM needed.
 *
 * Usage: node tests/frontend/xlsx-formula.test.mjs
 */

import { strict as assert } from "node:assert";
import { fileURLToPath, pathToFileURL } from "node:url";
import path from "node:path";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const REPO_ROOT = path.resolve(__dirname, "..", "..");

const modUrl = pathToFileURL(
  path.join(REPO_ROOT, "frontend", "js", "xlsx", "formula.js")
).href;
const {
  isFormulaText, parseFormula, evaluate, createScope, displayValue,
  colToIndex, indexToCol, parseA1,
} = await import(modUrl);

let testCount = 0;
let passCount = 0;
async function test(name, fn) {
  testCount++;
  try {
    await fn();
    passCount++;
    console.log(`  ✓ ${name}`);
  } catch (e) {
    console.error(`  ✗ ${name}`);
    console.error(e);
    process.exitCode = 1;
  }
}

/** Evaluate `text` against a fake sheet {ref: raw}. */
function run(text, cells = {}, locale = "en") {
  const scope = createScope({
    readRaw: (_sheet, ref) => {
      const hit = Object.entries(cells).find(
        ([k]) => k.toUpperCase() === String(ref).toUpperCase()
      );
      return hit ? hit[1] : null;
    },
    sheet: "F",
    locale,
  });
  return displayValue(evaluate(parseFormula(text), scope), locale);
}

await test("detects formula text", () => {
  assert.ok(isFormulaText("=1+1"));
  assert.ok(isFormulaText("  @A1  "));
  assert.ok(!isFormulaText("hello"));
  assert.ok(!isFormulaText("="));
});

await test("refs helpers", () => {
  assert.equal(colToIndex("A"), 1);
  assert.equal(colToIndex("Z"), 26);
  assert.equal(colToIndex("AA"), 27);
  assert.equal(indexToCol(28), "AB");
  assert.deepEqual(parseA1("$B$12"), { col: 2, row: 12 });
  assert.equal(parseA1("ZZZ"), null);
});

await test("arithmetic and Excel precedence (-2^2 = 4)", () => {
  assert.equal(run("=1+2*3"), "7");
  assert.equal(run("=(1+2)*3"), "9");
  assert.equal(run("=2^3"), "8");
  assert.equal(run("=-2^2"), "4");
  assert.equal(run("=10/4"), "2.5");
  assert.equal(run("=50%"), "0.5");
  assert.equal(run("=5*10%"), "0.5");
});

await test("comparisons and concatenation", () => {
  assert.equal(run("=1+1=2"), "TRUE");
  assert.equal(run('="a"&"b"'), "ab");
  assert.equal(run("=3>2"), "TRUE");
  assert.equal(run("=1<>1"), "FALSE");
});

await test("cell refs and ranges", () => {
  const cells = { A1: 2, A2: 4, B1: "x" };
  assert.equal(run("=A1*3", cells), "6");
  assert.equal(run("=SOMME(A1:A2)", cells), "6");
  assert.equal(run("=SUM(A1:A2)", cells), "6");
  assert.equal(run("=MOYENNE(A1:A2)", cells), "3");
  assert.equal(run("=A1&B1", cells), "2x");
});

await test("French decimal comma with ; separator", () => {
  assert.equal(run("=1,5+1", {}, "fr"), "2.5");
  assert.equal(run("=SOMME(1;2;3)"), "6");
  assert.equal(run("=SI(1>2;10;20)"), "20");
});

await test("functions FR/EN", () => {
  assert.equal(run("=MAX(1;5;3)"), "5");
  assert.equal(run("=MIN(1;5;3)"), "1");
  assert.equal(run("=ABS(-4)"), "4");
  assert.equal(run("=ARRONDI(2.456;2)"), "2.46");
  assert.equal(run("=RACINE(9)"), "3");
  assert.equal(run("=PUISSANCE(2;10)"), "1024");
  assert.equal(run("=NB(1;\"x\";3)"), "2");
  assert.equal(run("=NBVAL(1;\"x\";3)"), "3");
  assert.equal(run("=NBCAR(\"héllo\")"), "5");
  assert.equal(run("=MAJUSCULES(\"abc\")"), "ABC");
  assert.equal(run("=GAUCHE(\"hello\";2)"), "he");
  assert.equal(run("=DROITE(\"hello\";2)"), "lo");
  assert.equal(run("=STXT(\"hello\";2;3)"), "ell");
  assert.equal(run("=ET(1;1)"), "TRUE");
  assert.equal(run("=OU(0;1)"), "TRUE");
  assert.equal(run("=NON(1)"), "FALSE");
  assert.equal(run('=SIERREUR(1/0;"raté")'), "raté");
  assert.equal(run("=MEDIANE(3;1;2)"), "2");
  assert.equal(run("=PRODUIT(2;3;4)"), "24");
  assert.equal(run("=MOD(7;3)"), "1");
  assert.equal(run("=ENT(2.9)"), "2");
});

await test("nested formulas and chained refs", () => {
  const cells = { A1: "=B1*2", B1: 21 };
  assert.equal(run("=A1+1", cells), "43");
});

await test("errors surface as localized codes", () => {
  assert.equal(run("=1/0"), "#DIV/0!");
  assert.equal(run("=1/0", {}, "fr"), "#DIV/0!");
  assert.equal(run("=NOSUCHFN(1)"), "#NAME?");
  assert.equal(run("=NOSUCHFN(1)", {}, "fr"), "#NOM?");
  assert.equal(run('="a"+1'), "#VALUE!");
  assert.equal(run('="a"+1', {}, "fr"), "#VALEUR!");
  assert.equal(run("=A99999999"), "#REF!");
});

await test("cycles are detected", () => {
  const cells = { A1: "=B1", B1: "=A1" };
  assert.equal(run("=A1", cells), "#CIRC!");
  assert.equal(run("=A1", cells, "fr"), "#CYCLE!");
});

await test("booleans localize", () => {
  assert.equal(run("=VRAI"), "TRUE");
  assert.equal(run("=VRAI", {}, "fr"), "VRAI");
  assert.equal(run("=TRUE"), "TRUE");
  assert.equal(run("=1=1", {}, "fr"), "VRAI");
});

await test("HYPERLINK yields a link value", async () => {
  const scope = createScope({ readRaw: () => null, sheet: "F", locale: "en" });
  const v = evaluate(parseFormula('=LIEN("https://x.test";"ici")'), scope);
  assert.equal(v.t, "link");
  assert.equal(v.url, "https://x.test");
  assert.equal(v.label, "ici");
  assert.equal(displayValue(v), "ici");
});

await test("garbage is rejected by the parser", () => {
  assert.throws(() => parseFormula("=1+"), Error);
  assert.throws(() => parseFormula("=SUM("), Error);
  assert.throws(() => parseFormula("=(1"), Error);
});

await test("quoted and absolute refs", () => {
  const cells = { A1: 5 };
  assert.equal(run("=$A$1+1", cells), "6");
});

console.log(`\n${passCount}/${testCount} passed`);
process.exit(process.exitCode || 0);