Repository Public Research preview
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