SPB Git

Repository Public Research preview

workbook.mjs 14.9 KB 264 lines javascript

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
Files main
Menu

Theme