SPB Git

Repository Public Research preview

formula.mjs 18.5 KB 367 lines javascript

1/**2 * =============================================================================3 *                  SP BOUCHER | SOFTWARE & RESEARCH4 *             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5 * =============================================================================6 *7 * Project          : LLM API toolkits8 * File             : xlsx/mcp/lib/formula.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 { MAX_COL, MAX_ROW, addr, colToNum, numToCol, quoteSheet } from "./refs.mjs";4344/**45 * Formula tokenizer, parser, R1C1 normaliser and a deliberately small evaluator.46 *47 * Evaluated subset (everything else is reported as "unsupported" and never guessed):48 *   numbers, strings, TRUE/FALSE, + - * / ^, unary + and -, postfix %, comparisons (= <> < > <= >=),49 *   cell and range references (same sheet and cross-sheet), defined names that are plain references,50 *   SUM, AVERAGE, MIN, MAX, ROUND, IF.51 */52export class FormulaError extends Error {}53export class Unsupported extends Error {54  constructor(what) { super(what); this.what = what; }55}56export const SUPPORTED_FUNCTIONS = ["SUM", "AVERAGE", "MIN", "MAX", "ROUND", "IF"];57export const AGGREGATES = new Set(["SUM", "AVERAGE", "MIN", "MAX", "SUBTOTAL", "COUNT", "COUNTA", "PRODUCT", "SUMPRODUCT", "AGGREGATE", "AND", "OR", "SUMIF", "SUMIFS", "COUNTIF", "COUNTIFS", "AVERAGEIF", "AVERAGEIFS", "MEDIAN", "NPV", "IRR"]);58export const ERR = (code) => ({ error: code });59export const isErr = (v) => v !== null && typeof v === "object" && "error" in v;6061const CELL = /^(\$?)([A-Za-z]{1,3})(\$?)(\d{1,7})/;62const COLRANGE = /^(\$?)([A-Za-z]{1,3}):(\$?)([A-Za-z]{1,3})(?![A-Za-z0-9_(])/;63const ROWRANGE = /^(\$?)(\d{1,7}):(\$?)(\d{1,7})(?![\w.])/;6465function cellAt(src, i) {66  const m = CELL.exec(src.slice(i));67  if (!m) return null;68  const col = colToNum(m[2]), row = Number(m[4]);69  if (col > MAX_COL || row < 1 || row > MAX_ROW) return null;70  const next = src[i + m[0].length];71  if (next !== undefined && /[A-Za-z0-9_.(]/.test(next)) return null; // part of a longer name, or a function such as LOG10(72  return { len: m[0].length, pt: { col, row, absCol: m[1] === "$", absRow: m[3] === "$" } };73}7475export function tokenize(formula) {76  const src = String(formula).replace(/^=/, "");77  const out = [];78  let i = 0;79  while (i < src.length) {80    const ch = src[i];81    if (/\s/.test(ch)) { i++; continue; }82    if (ch === '"') {83      let j = i + 1, s = "";84      for (;;) { if (j >= src.length) throw new FormulaError("unterminated string"); if (src[j] === '"') { if (src[j + 1] === '"') { s += '"'; j += 2; continue; } break; } s += src[j++]; }85      out.push({ t: "str", v: s }); i = j + 1; continue;86    }87    if (ch === "#") { const m = /^#(REF!|DIV\/0!|N\/A|VALUE!|NAME\?|NUM!|NULL!|SPILL!|CALC!|GETTING_DATA)/.exec(src.slice(i)); if (!m) throw new FormulaError(`unexpected # at ${i}`); out.push({ t: "err", v: m[0] }); i += m[0].length; continue; }88    if (ch === "[") throw new Unsupported("external workbook or structured table reference");89    if (ch === "{") throw new Unsupported("array constant");90    // sheet prefix91    let sheet = null, k = i;92    const q = /^'((?:[^']|'')+)'!/.exec(src.slice(i));93    const u = q ? null : /^([A-Za-z_][\w.]*)!/.exec(src.slice(i));94    if (q) { sheet = q[1].replace(/''/g, "'"); k = i + q[0].length; } else if (u) { sheet = u[1]; k = i + u[0].length; }95    if (sheet !== null && /[:]/.test(sheet)) throw new Unsupported("3-D reference");96    if (sheet === null && /^[A-Za-z_][\w.]*:[A-Za-z_][\w.]*!/.test(src.slice(i))) throw new Unsupported("3-D reference");97    if (sheet !== null && src.startsWith("#REF!", k)) { out.push({ t: "err", v: "#REF!" }); i = k + 5; continue; }98    const cr = COLRANGE.exec(src.slice(k));99    if (cr && colToNum(cr[2]) <= MAX_COL && colToNum(cr[4]) <= MAX_COL) {100      out.push({ t: "ref", sheet, a: { col: colToNum(cr[2]), row: 1, absCol: cr[1] === "$", absRow: true, whole: "col" }, b: { col: colToNum(cr[4]), row: MAX_ROW, absCol: cr[3] === "$", absRow: true, whole: "col" } });101      i = k + cr[0].length; continue;102    }103    const rr = /\d|\$/.test(src[k] ?? "") ? ROWRANGE.exec(src.slice(k)) : null;104    if (rr && (sheet !== null || !/^\d+(\.\d+)?$/.test(src.slice(k).split(/[^\d.:$]/)[0]) || rr[0].includes(":"))) {105      const prev = out[out.length - 1];106      const operand = prev && (prev.t === "num" || prev.t === "ref" || prev.t === "rp");107      if (!operand) { out.push({ t: "ref", sheet, a: { col: 1, row: Number(rr[2]), absCol: true, absRow: rr[1] === "$", whole: "row" }, b: { col: MAX_COL, row: Number(rr[4]), absCol: true, absRow: rr[3] === "$", whole: "row" } }); i = k + rr[0].length; continue; }108    }109    const c1 = cellAt(src, k);110    if (c1) {111      let end = k + c1.len, b = null;112      if (src[end] === ":") { const c2 = cellAt(src, end + 1); if (c2) { b = c2.pt; end = end + 1 + c2.len; } }113      out.push({ t: "ref", sheet, a: c1.pt, b }); i = end; continue;114    }115    if (sheet !== null) { const nm = /^[A-Za-z_\\][\w.\\]*/.exec(src.slice(k)); if (!nm) throw new FormulaError(`bad reference after sheet name at ${k}`); out.push({ t: "name", v: nm[0], sheet }); i = k + nm[0].length; continue; }116    const num = /^(?:\d+\.?\d*|\.\d+)(?:[eE][+-]?\d+)?/.exec(src.slice(i));117    if (num) { out.push({ t: "num", v: Number(num[0]), raw: num[0] }); i += num[0].length; continue; }118    const id = /^[A-Za-z_\\][\w.\\]*/.exec(src.slice(i));119    if (id) {120      const word = id[0], after = src.slice(i + word.length).match(/^\s*\(/);121      if (after) out.push({ t: "func", v: word.toUpperCase().replace(/^_XLFN\.|^_XLWS\./, "") });122      else if (/^(TRUE|FALSE)$/i.test(word)) out.push({ t: "bool", v: /^true$/i.test(word) });123      else out.push({ t: "name", v: word, sheet: null });124      i += word.length; continue;125    }126    const two = src.slice(i, i + 2);127    if (two === "<>" || two === "<=" || two === ">=") { out.push({ t: "op", v: two }); i += 2; continue; }128    if ("+-*/^&=<>%".includes(ch)) { out.push({ t: "op", v: ch }); i++; continue; }129    if (ch === "(") { out.push({ t: "lp" }); i++; continue; }130    if (ch === ")") { out.push({ t: "rp" }); i++; continue; }131    if (ch === "," || ch === ";") { out.push({ t: "comma" }); i++; continue; }132    if (ch === "@") { i++; continue; } // implicit-intersection marker133    throw new FormulaError(`unexpected character ${JSON.stringify(ch)} at ${i}`);134  }135  return out;136}137138/** Recursive-descent parser. Precedence (low → high): comparison, &, + -, * /, ^, unary, %. */139export function parse(formula) {140  const toks = Array.isArray(formula) ? formula : tokenize(formula);141  let p = 0;142  const peek = () => toks[p], isOp = (...v) => toks[p]?.t === "op" && v.includes(toks[p].v);143  function comparison() { let l = concat(); while (isOp("=", "<>", "<", ">", "<=", ">=")) { const op = toks[p++].v; l = { k: "bin", op, l, r: concat() }; } return l; }144  function concat() { let l = additive(); while (isOp("&")) { p++; l = { k: "bin", op: "&", l, r: additive() }; } return l; }145  function additive() { let l = term(); while (isOp("+", "-")) { const op = toks[p++].v; l = { k: "bin", op, l, r: term() }; } return l; }146  function term() { let l = power(); while (isOp("*", "/")) { const op = toks[p++].v; l = { k: "bin", op, l, r: power() }; } return l; }147  function power() { let l = unary(); while (isOp("^")) { p++; l = { k: "bin", op: "^", l, r: unary() }; } return l; }148  function unary() { if (isOp("+", "-")) { const op = toks[p++].v; return { k: "un", op, x: unary() }; } return postfix(); }149  function postfix() { let x = primary(); while (isOp("%")) { p++; x = { k: "pct", x }; } return x; }150  function primary() {151    const t = toks[p++];152    if (!t) throw new FormulaError("unexpected end of formula");153    if (t.t === "num") return { k: "num", v: t.v };154    if (t.t === "str") return { k: "str", v: t.v };155    if (t.t === "bool") return { k: "bool", v: t.v };156    if (t.t === "err") return { k: "err", v: t.v };157    if (t.t === "ref") return { k: "ref", sheet: t.sheet, a: t.a, b: t.b };158    if (t.t === "name") return { k: "name", v: t.v, sheet: t.sheet };159    if (t.t === "lp") { const e = comparison(); if (toks[p++]?.t !== "rp") throw new FormulaError("missing )"); return { k: "paren", x: e }; }160    if (t.t === "func") {161      if (toks[p++]?.t !== "lp") throw new FormulaError("missing ( after function name");162      const args = [];163      if (peek()?.t === "rp") { p++; return { k: "func", name: t.v, args }; }164      for (;;) {165        if (peek()?.t === "comma" || peek()?.t === "rp") args.push({ k: "missing" }); else args.push(comparison());166        const n = toks[p++];167        if (n?.t === "rp") break;168        if (n?.t !== "comma") throw new FormulaError("expected , or ) in function call");169      }170      return { k: "func", name: t.v, args };171    }172    throw new FormulaError(`unexpected token ${t.t}`);173  }174  const ast = comparison();175  if (p < toks.length) throw new FormulaError("unexpected content after the end of the formula");176  return ast;177}178179export function walk(node, fn, parent = null, slot = null) {180  if (!node) return;181  fn(node, parent, slot);182  if (node.k === "bin") { walk(node.l, fn, node, "l"); walk(node.r, fn, node, "r"); }183  else if (node.k === "un" || node.k === "pct" || node.k === "paren") walk(node.x, fn, node, "x");184  else if (node.k === "func") node.args.forEach((a, i) => walk(a, fn, node, i));185}186187/* ---------- R1C1 normalisation: two cells with the same pattern hold "the same formula" ---------- */188function r1c1Point(pt, row, col) {189  if (pt.whole === "col") return pt.absCol ? `C${pt.col}` : `C[${pt.col - col}]`;190  if (pt.whole === "row") return pt.absRow ? `R${pt.row}` : `R[${pt.row - row}]`;191  return `${pt.absRow ? `R${pt.row}` : `R[${pt.row - row}]`}${pt.absCol ? `C${pt.col}` : `C[${pt.col - col}]`}`;192}193export function toR1C1(formula, row, col) {194  const toks = tokenize(formula);195  let s = "";196  for (const t of toks) {197    if (t.t === "ref") s += `${t.sheet !== null ? quoteSheet(t.sheet) + "!" : ""}${r1c1Point(t.a, row, col)}${t.b ? ":" + r1c1Point(t.b, row, col) : ""}`;198    else if (t.t === "num") s += t.raw;199    else if (t.t === "str") s += `"${t.v.replace(/"/g, '""')}"`;200    else if (t.t === "bool") s += t.v ? "TRUE" : "FALSE";201    else if (t.t === "func") s += t.v;202    else if (t.t === "name") s += `${t.sheet !== null ? quoteSheet(t.sheet) + "!" : ""}${t.v}`;203    else if (t.t === "lp") s += "(";204    else if (t.t === "rp") s += ")";205    else if (t.t === "comma") s += ",";206    else s += t.v;207  }208  return s;209}210211/** Translate a formula by (dRow, dCol), moving relative references only — how shared formulas are expanded. */212export function shiftFormula(formula, dRow, dCol) {213  const src = String(formula).replace(/^=/, "");214  let out = "", i = 0, inStr = false;215  while (i < src.length) {216    const ch = src[i];217    if (ch === '"') { inStr = !inStr; out += ch; i++; continue; }218    if (inStr) { out += ch; i++; continue; }219    if (ch === "'") { const m = /^'(?:[^']|'')+'!?/.exec(src.slice(i)); if (m) { out += m[0]; i += m[0].length; continue; } }220    const prev = src[i - 1];221    if ((prev === undefined || !/[A-Za-z0-9_.$]/.test(prev)) && /[$A-Za-z]/.test(ch)) {222      const c = cellAt(src, i);223      if (c) {224        const { pt } = c, col = pt.absCol ? pt.col : pt.col + dCol, row = pt.absRow ? pt.row : pt.row + dRow;225        out += col < 1 || col > MAX_COL || row < 1 || row > MAX_ROW ? "#REF!" : `${pt.absCol ? "$" : ""}${numToCol(col)}${pt.absRow ? "$" : ""}${row}`;226        i += c.len; continue;227      }228      const word = /^[$A-Za-z_][\w.$]*/.exec(src.slice(i));229      if (word) { out += word[0]; i += word[0].length; continue; }230    }231    out += ch; i++;232  }233  return out;234}235236/* ---------- references used by a formula ---------- */237export function references(ast) {238  const refs = [], names = [], funcs = new Set();239  walk(ast, (n) => { if (n.k === "ref") refs.push(n); else if (n.k === "name") names.push(n); else if (n.k === "func") funcs.add(n.name); });240  return { refs, names, funcs: [...funcs] };241}242243/** Numeric literals written inside a formula, ignoring the "digits" argument of ROUND-like functions. */244export function numericLiterals(ast) {245  const out = [];246  walk(ast, (n, parent, slot) => {247    if (n.k !== "num") return;248    if (parent?.k === "func" && /^ROUND(UP|DOWN)?$|^TRUNC$|^FIXED$/.test(parent.name) && slot === 1) return;249    if (parent?.k === "un" || parent?.k === "paren") return out.push(n.v);250    out.push(n.v);251  });252  return out;253}254255/* ---------- evaluator (bounded subset) ---------- */256const num = (v) => {257  if (isErr(v)) return v;258  if (v === null || v === undefined || v === "") return 0;259  if (typeof v === "number") return v;260  if (typeof v === "boolean") return v ? 1 : 0;261  if (typeof v === "string") { const t = v.trim(); if (t !== "" && /^[+-]?(?:\d+\.?\d*|\.\d+)(?:[eE][+-]?\d+)?$/.test(t)) return Number(t); return ERR("#VALUE!"); }262  return ERR("#VALUE!");263};264const rank = (v) => (typeof v === "number" ? 0 : typeof v === "string" ? 1 : 2);265function compare(a, b) {266  if (a === null || a === undefined) a = typeof b === "string" ? "" : typeof b === "boolean" ? false : 0;267  if (b === null || b === undefined) b = typeof a === "string" ? "" : typeof a === "boolean" ? false : 0;268  if (rank(a) !== rank(b)) return rank(a) - rank(b);269  if (typeof a === "string") { const x = a.toLowerCase(), y = b.toLowerCase(); return x < y ? -1 : x > y ? 1 : 0; }270  return a === b ? 0 : a < b ? -1 : 1;271}272export function roundHalfAway(x, digits) {273  if (!Number.isFinite(x)) return x;274  const d = Math.trunc(digits), s = x < 0 ? -1 : 1, a = Math.abs(x);275  const r = Number(`${Math.round(Number(`${a}e${d}`))}e${-d}`);276  return Number.isFinite(r) ? s * r : x;277}278279/**280 * ctx: { cell(sheet|null, col, row) → value, range(sheet|null, c1, r1, c2, r2) → value[] (existing cells only),281 *        name(name, sheet|null) → { sheet, a, b } | null }282 */283export function evaluate(ast, ctx) {284  const scalar = (n) => {285    const v = ev(n);286    if (Array.isArray(v)) { if (v.length === 1) return v[0]; throw new Unsupported("a range used where a single value is expected"); }287    return v;288  };289  const flat = (args) => {290    const vals = [];291    for (const a of args) {292      if (a.k === "missing") continue;293      const v = ev(a);294      if (Array.isArray(v)) { for (const x of v) { if (isErr(x)) return x; if (typeof x === "number") vals.push(x); } } // text, booleans and blanks inside ranges are ignored295      else { const n = num(v); if (isErr(n)) return n; vals.push(n); }296    }297    return vals;298  };299  function ev(n) {300    switch (n.k) {301      case "num": case "str": case "bool": return n.v;302      case "err": return ERR(n.v);303      case "paren": return ev(n.x);304      case "missing": return null;305      case "ref": {306        if (!n.b) return ctx.cell(n.sheet, n.a.col, n.a.row);307        return ctx.range(n.sheet, Math.min(n.a.col, n.b.col), Math.min(n.a.row, n.b.row), Math.max(n.a.col, n.b.col), Math.max(n.a.row, n.b.row));308      }309      case "name": {310        const r = ctx.name(n.v, n.sheet);311        if (!r) throw new Unsupported(`name ${n.v}`);312        return ev({ k: "ref", sheet: r.sheet, a: r.a, b: r.b });313      }314      case "un": { const v = num(scalar(n.x)); return isErr(v) ? v : n.op === "-" ? -v : v; }315      case "pct": { const v = num(scalar(n.x)); return isErr(v) ? v : v / 100; }316      case "bin": {317        if (n.op === "&") throw new Unsupported("text concatenation (&)");318        const l = scalar(n.l), r = scalar(n.r);319        if (isErr(l)) return l;320        if (isErr(r)) return r;321        if (["=", "<>", "<", ">", "<=", ">="].includes(n.op)) { const c = compare(l, r); return n.op === "=" ? c === 0 : n.op === "<>" ? c !== 0 : n.op === "<" ? c < 0 : n.op === ">" ? c > 0 : n.op === "<=" ? c <= 0 : c >= 0; }322        const a = num(l), b = num(r);323        if (isErr(a)) return a;324        if (isErr(b)) return b;325        if (n.op === "+") return a + b;326        if (n.op === "-") return a - b;327        if (n.op === "*") return a * b;328        if (n.op === "/") return b === 0 ? ERR("#DIV/0!") : a / b;329        if (n.op === "^") { const v = Math.pow(a, b); return Number.isFinite(v) ? v : ERR("#NUM!"); }330        throw new Unsupported(`operator ${n.op}`);331      }332      case "func": {333        const f = n.name;334        if (f === "IF") {335          if (n.args.length < 2 || n.args.length > 3) throw new FormulaError("IF takes 2 or 3 arguments");336          const c = scalar(n.args[0]);337          if (isErr(c)) return c;338          const t = typeof c === "string" ? (/^true$/i.test(c) ? true : /^false$/i.test(c) ? false : ERR("#VALUE!")) : num(c) !== 0;339          if (isErr(t)) return t;340          if (t) return n.args[1].k === "missing" ? 0 : scalar(n.args[1]);341          return n.args.length < 3 ? false : n.args[2].k === "missing" ? 0 : scalar(n.args[2]);342        }343        if (f === "ROUND") {344          if (n.args.length !== 2) throw new FormulaError("ROUND takes 2 arguments");345          const x = num(scalar(n.args[0])), d = num(scalar(n.args[1]));346          if (isErr(x)) return x;347          if (isErr(d)) return d;348          return roundHalfAway(x, d);349        }350        if (f === "SUM" || f === "AVERAGE" || f === "MIN" || f === "MAX") {351          const v = flat(n.args);352          if (isErr(v)) return v;353          if (f === "SUM") return v.reduce((a, b) => a + b, 0);354          if (f === "AVERAGE") return v.length ? v.reduce((a, b) => a + b, 0) / v.length : ERR("#DIV/0!");355          if (!v.length) return 0;356          return f === "MIN" ? Math.min(...v) : Math.max(...v);357        }358        throw new Unsupported(`function ${f}`);359      }360      default: throw new Unsupported(`node ${n.k}`);361    }362  }363  return scalar(ast);364}365366export { addr };367
Files main
Menu

Theme