Repository Public Research preview
1/**2 * =============================================================================3 * SP BOUCHER | SOFTWARE & RESEARCH4 * ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5 * =============================================================================6 *7 * Project : LLM API toolkits8 * File : xlsx/skill/excel-model-workbook/scripts/lib/workbook.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 { readFileSync, realpathSync, statSync } from "node:fs";43import { extname, isAbsolute, relative, resolve, sep } from "node:path";44import { DEFAULT_LIMITS, XlsxError, readZip } from "./zip.mjs";45import { scan } from "./xml.mjs";46import { MAX_COL, addr, parseCell } from "./refs.mjs";47import { shiftFormula, tokenize } from "./formula.mjs";4849/**50 * Reads an .xlsx / .xlsm package into plain objects. Nothing is executed: macros, external links,51 * pivot caches and drawings are never opened. Dates are not converted (the number format is reported).52 */53export const KEY = MAX_COL + 1;54export const cellKey = (col, row) => row * KEY + col;5556const BUILTIN_FORMATS = {57 0: "General", 1: "0", 2: "0.00", 3: "#,##0", 4: "#,##0.00", 9: "0%", 10: "0.00%", 11: "0.00E+00", 12: "# ?/?", 13: "# ??/??",58 14: "mm-dd-yy", 15: "d-mmm-yy", 16: "d-mmm", 17: "mmm-yy", 18: "h:mm AM/PM", 19: "h:mm:ss AM/PM", 20: "h:mm", 21: "h:mm:ss", 22: "m/d/yy h:mm",59 37: "#,##0 ;(#,##0)", 38: "#,##0 ;[Red](#,##0)", 39: "#,##0.00;(#,##0.00)", 40: "#,##0.00;[Red](#,##0.00)", 45: "mm:ss", 46: "[h]:mm:ss", 47: "mmss.0", 48: "##0.0E+0", 49: "@",60};6162export function limitsFromEnv(env = process.env) {63 const mb = (name, dflt) => { const v = Number(env[name]); return Number.isFinite(v) && v > 0 ? Math.floor(v * 1024 * 1024) : dflt; };64 const cells = Number(env.XLSX_AUDIT_MAX_CELLS);65 const unz = mb("XLSX_AUDIT_MAX_UNZIPPED_MB", null);66 return {67 ...DEFAULT_LIMITS,68 maxFileBytes: mb("XLSX_AUDIT_MAX_FILE_MB", DEFAULT_LIMITS.maxFileBytes),69 maxEntryBytes: unz ?? DEFAULT_LIMITS.maxEntryBytes,70 maxTotalBytes: unz ?? DEFAULT_LIMITS.maxTotalBytes,71 maxCells: Number.isFinite(cells) && cells > 0 ? Math.floor(cells) : 2_000_000,72 };73}7475/** Resolves `p` inside `root` (symlinks followed on both sides) or throws. `mustExist=false` checks the parent directory. */76export function resolveInside(root, p, { mustExist = true } = {}) {77 if (typeof p !== "string" || p.length === 0 || p.includes("\0")) throw new XlsxError("bad_path", "A file path is required.");78 let realRoot;79 try { realRoot = realpathSync(resolve(root)); } catch { throw new XlsxError("bad_root", "The configured root directory does not exist."); }80 const abs = isAbsolute(p) ? resolve(p) : resolve(realRoot, p);81 const lexical = (base) => { const r = relative(base, abs); return r !== "" && !r.startsWith("..") && !isAbsolute(r); };82 if (!lexical(realRoot) && !lexical(resolve(root))) throw new XlsxError("outside_root", `The path is outside the allowed directory (${realRoot}). Set XLSX_AUDIT_ROOT to widen it.`);83 let real;84 try { real = mustExist ? realpathSync(abs) : resolve(realpathSync(resolve(abs, "..")), abs.split(sep).pop()); }85 catch { throw new XlsxError("not_found", mustExist ? `File not found: ${p}` : `The destination directory does not exist: ${p}`); }86 const rel = relative(realRoot, real);87 if (rel === "" || rel.startsWith("..") || isAbsolute(rel)) throw new XlsxError("outside_root", `The path is outside the allowed directory (${realRoot}). Set XLSX_AUDIT_ROOT to widen it.`);88 return real;89}9091export function openWorkbook(root, p, limits = limitsFromEnv()) {92 const real = root === null ? resolve(p) : resolveInside(root, p);93 const ext = extname(real).toLowerCase();94 if (ext !== ".xlsx" && ext !== ".xlsm") throw new XlsxError("unsupported_type", `Only .xlsx and .xlsm workbooks are read (got ${ext || "no extension"}). Save .xls, .xlsb, .ods or .csv files as .xlsx first.`);95 let st;96 try { st = statSync(real); } catch { throw new XlsxError("not_found", `File not found: ${p}`); }97 if (!st.isFile()) throw new XlsxError("not_found", `Not a regular file: ${p}`);98 if (st.size > limits.maxFileBytes) throw new XlsxError("file_too_large", `The file is ${st.size} bytes (limit ${limits.maxFileBytes}).`);99 const wb = loadWorkbook(readFileSync(real), limits);100 wb.path = real;101 wb.bytes = st.size;102 return wb;103}104105const text = (buf) => buf.toString("utf8").replace(/^/, "");106107function parseSharedStrings(xml) {108 const out = [];109 let cur = null, inT = false, skip = 0;110 scan(xml, {111 open(n) { if (n === "si") cur = ""; else if (n === "rPh") skip++; else if (n === "t" && cur !== null && !skip) inT = true; },112 text(t) { if (inT) cur += t; },113 close(n) { if (n === "t") inT = false; else if (n === "rPh") skip--; else if (n === "si") { out.push(cur ?? ""); cur = null; } },114 });115 return out;116}117118function parseStyles(xml) {119 const custom = new Map(), xfs = [];120 let inXfs = false;121 scan(xml, {122 open(n, a) {123 if (n === "numFmt" && a.numFmtId !== undefined) custom.set(Number(a.numFmtId), a.formatCode ?? "");124 else if (n === "cellXfs") inXfs = true;125 else if (n === "xf" && inXfs) xfs.push(Number(a.numFmtId ?? 0));126 },127 close(n) { if (n === "cellXfs") inXfs = false; },128 });129 return xfs.map((id) => custom.get(id) ?? BUILTIN_FORMATS[id] ?? `builtin:${id}`);130}131132function parseSheet(xml, sheet, shared, formats, budget) {133 let row = 0, col = 0, cur = null, where = null, fAttrs = null, buf = "";134 const masters = new Map();135 scan(xml, {136 open(n, a, selfClosing) {137 if (n === "row") { row = a.r ? Number(a.r) : row + 1; col = 0; }138 else if (n === "c") {139 const pos = a.r ? parseCell(a.r) : null;140 if (pos) { row = pos.row; col = pos.col; } else col += 1;141 cur = { row, col, t: a.t ?? "n", s: a.s ? Number(a.s) : 0, raw: null, f: null, shared: false, array: false, fNote: null };142 } else if (cur && n === "f") { where = "f"; buf = ""; fAttrs = a; if (selfClosing) where = "f-empty"; }143 else if (cur && n === "v") { where = "v"; buf = ""; }144 else if (cur && n === "is") { where = "is"; buf = ""; }145 else if (cur && n === "t" && where === "is") where = "is-t";146 else if (n === "mergeCell" && a.ref) sheet.merged.push(a.ref);147 },148 text(t) { if (where === "f" || where === "v" || where === "is-t") buf += t; },149 close(n) {150 if (!cur) return;151 if (n === "f") {152 const a = fAttrs ?? {};153 if (a.t === "shared" && a.si !== undefined) {154 if (buf.length) { masters.set(a.si, { formula: buf, row: cur.row, col: cur.col }); cur.f = buf; }155 else { const m = masters.get(a.si); if (m) cur.f = shiftFormula(m.formula, cur.row - m.row, cur.col - m.col); else cur.fNote = "shared formula without a master"; }156 cur.shared = true;157 } else if (a.t === "dataTable") { cur.f = buf.length ? buf : "TABLE()"; cur.fNote = "data table"; }158 else { if (buf.length) cur.f = buf; if (a.t === "array") cur.array = true; }159 where = null; fAttrs = null;160 } else if (n === "v") { cur.raw = buf; where = null; }161 else if (n === "t" && where === "is-t") where = "is";162 else if (n === "is") { cur.raw = buf; where = null; }163 else if (n === "c") {164 const c = cur; cur = null; where = null;165 let v = null, type = "blank";166 if (c.raw !== null) {167 if (c.t === "s") { v = shared[Number(c.raw)] ?? ""; type = "s"; }168 else if (c.t === "str" || c.t === "inlineStr") { v = c.raw; type = "s"; }169 else if (c.t === "b") { v = c.raw === "1" || c.raw === "true"; type = "b"; }170 else if (c.t === "e") { v = { error: c.raw }; type = "e"; }171 else if (c.t === "d") { v = c.raw; type = "s"; }172 else if (c.raw.trim() !== "") { const x = Number(c.raw); if (Number.isFinite(x)) { v = x; type = "n"; } else { v = c.raw; type = "s"; } }173 }174 if (c.f === null && type === "blank") return; // styled but empty175 if (c.row < 1 || c.col < 1 || c.col > MAX_COL) return;176 if (--budget.left < 0) throw new XlsxError("workbook_too_large", `The workbook holds more than ${budget.max} non-empty cells (limit XLSX_AUDIT_MAX_CELLS).`);177 const cell = { row: c.row, col: c.col, ref: addr(c.col, c.row), v, t: type, f: c.f, fmt: formats[c.s] ?? "General", hasCached: type !== "blank", shared: c.shared, array: c.array };178 if (c.fNote) cell.fNote = c.fNote;179 sheet.cells.set(cellKey(c.col, c.row), cell);180 }181 },182 });183 const rows = new Map();184 let minR = Infinity, maxR = 0, minC = Infinity, maxC = 0;185 for (const c of sheet.cells.values()) {186 if (!rows.has(c.row)) rows.set(c.row, []);187 rows.get(c.row).push(c);188 if (c.row < minR) minR = c.row; if (c.row > maxR) maxR = c.row;189 if (c.col < minC) minC = c.col; if (c.col > maxC) maxC = c.col;190 }191 for (const list of rows.values()) list.sort((a, b) => a.col - b.col);192 sheet.rows = rows;193 sheet.rowNumbers = [...rows.keys()].sort((a, b) => a - b);194 sheet.dim = sheet.cells.size ? { r1: minR, c1: minC, r2: maxR, c2: maxC } : null;195}196197export function loadWorkbook(buf, limits = limitsFromEnv()) {198 const need = (n) => n === "xl/workbook.xml" || n === "xl/_rels/workbook.xml.rels" || n === "xl/sharedStrings.xml" || n === "xl/styles.xml" || /^xl\/worksheets\/[^/]+\.xml$/.test(n) || n === "[Content_Types].xml";199 const names = [];200 const files = readZip(buf, { limits, want: (n) => { names.push(n); return need(n); } });201 const wbXml = files.get("xl/workbook.xml");202 if (!wbXml) throw new XlsxError("not_a_workbook", "xl/workbook.xml is missing: this ZIP file is not an Excel workbook (.xlsx).");203 const rels = new Map();204 if (files.has("xl/_rels/workbook.xml.rels")) scan(text(files.get("xl/_rels/workbook.xml.rels")), { open(n, a) { if (n === "Relationship" && a.Id) rels.set(a.Id, { target: a.Target ?? "", type: a.Type ?? "", external: a.TargetMode === "External" }); } });205 const wb = { sheets: [], names: [], warnings: [], date1904: false, hasMacros: names.some((n) => /vbaProject\.bin$/i.test(n)), hasExternalLinks: names.some((n) => n.startsWith("xl/externalLinks/")), limits };206 const decl = [], dn = [];207 let curName = null, nameBuf = "";208 scan(text(wbXml), {209 open(n, a) {210 if (n === "sheet" && a.name !== undefined) decl.push({ name: a.name, state: a.state ?? "visible", rid: a.id });211 else if (n === "definedName") { curName = a; nameBuf = ""; }212 else if (n === "workbookPr" && (a.date1904 === "1" || a.date1904 === "true")) wb.date1904 = true;213 },214 text(t) { if (curName) nameBuf += t; },215 close(n) { if (n === "definedName" && curName) { dn.push({ ...curName, text: nameBuf }); curName = null; } },216 });217 if (!decl.length) throw new XlsxError("not_a_workbook", "The workbook declares no sheets.");218 const shared = files.has("xl/sharedStrings.xml") ? parseSharedStrings(text(files.get("xl/sharedStrings.xml"))) : [];219 const formats = files.has("xl/styles.xml") ? parseStyles(text(files.get("xl/styles.xml"))) : [];220 const budget = { left: limits.maxCells ?? 2_000_000, max: limits.maxCells ?? 2_000_000 };221 for (const d of decl) {222 const rel = rels.get(d.rid);223 const sheet = { name: d.name, state: d.state, index: wb.sheets.length, cells: new Map(), rows: new Map(), rowNumbers: [], dim: null, merged: [], kind: "worksheet" };224 wb.sheets.push(sheet);225 if (!rel || rel.external) { sheet.kind = "unknown"; wb.warnings.push(`Sheet ${d.name}: no part found in the package.`); continue; }226 if (!/\/worksheet$/.test(rel.type)) { sheet.kind = rel.type.split("/").pop(); wb.warnings.push(`Sheet ${d.name} is a ${sheet.kind}, not a worksheet: it holds no cells and is not read.`); continue; }227 const path = rel.target.startsWith("/") ? rel.target.slice(1) : `xl/${rel.target}`.replace(/\/\.\//g, "/");228 const data = files.get(path);229 if (!data) { wb.warnings.push(`Sheet ${d.name}: part ${path} is missing from the package.`); continue; }230 parseSheet(text(data), sheet, shared, formats, budget);231 }232 wb.byName = new Map(wb.sheets.map((s) => [s.name.toLowerCase(), s]));233 for (const d of dn) {234 if (d.name?.startsWith("_xlnm.") || d.hidden === "1") continue; // print areas, filters, hidden names235 const scope = d.localSheetId !== undefined ? wb.sheets[Number(d.localSheetId)]?.name ?? null : null;236 let target = null;237 try {238 const toks = tokenize(d.text);239 if (toks.length === 1 && toks[0].t === "ref" && toks[0].sheet !== null && wb.byName.has(toks[0].sheet.toLowerCase())) target = { sheet: toks[0].sheet, a: toks[0].a, b: toks[0].b };240 } catch { /* a formula or a constant: reported as text only */ }241 wb.names.push({ name: d.name, scope, refers_to: d.text, target });242 }243 if (wb.hasMacros) wb.warnings.push("The workbook contains VBA macros. They are neither read nor run.");244 if (wb.hasExternalLinks) wb.warnings.push("The workbook links to other workbooks. External references are never followed; formulas using them rely on cached values.");245 if (wb.date1904) wb.warnings.push("The workbook uses the 1904 date system.");246 return wb;247}248249export function sheetOf(wb, name) {250 const s = wb.byName.get(String(name).toLowerCase());251 if (!s) throw new XlsxError("sheet_not_found", `No sheet named ${JSON.stringify(name)}. Sheets: ${wb.sheets.map((x) => x.name).join(", ")}`);252 return s;253}254export const getCell = (sheet, col, row) => sheet.cells.get(cellKey(col, row)) ?? null;255256/** Existing cells inside a rectangle, row-major. Whole-column and whole-row ranges are clipped to the used area. */257export function* cellsIn(sheet, c1, r1, c2, r2) {258 if (!sheet.dim) return;259 const a = Math.max(r1, sheet.dim.r1), b = Math.min(r2, sheet.dim.r2);260 if (b - a + 1 <= sheet.rowNumbers.length) {261 for (let r = a; r <= b; r++) { const list = sheet.rows.get(r); if (list) for (const c of list) if (c.col >= c1 && c.col <= c2) yield c; }262 } else for (const r of sheet.rowNumbers) { if (r < a || r > b) continue; for (const c of sheet.rows.get(r)) if (c.col >= c1 && c.col <= c2) yield c; }263}264