SPB Git

Repository Public Research preview

xlsx.mjs 6.9 KB 120 lines javascript

1#!/usr/bin/env node2/**3 * =============================================================================4 *                  SP BOUCHER | SOFTWARE & RESEARCH5 *             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING6 * =============================================================================7 *8 * Project          : LLM API toolkits9 * File             : xlsx/skill/excel-model-workbook/scripts/xlsx.mjs10 * Module           : xlsx11 * Repository       : https://git.spboucher.ai/llm-api-toolkits12 * Created          : 2026-11-1013 * Last Modified    : 2026-11-1014 *15 * Author           : Simon-Pierre Boucher16 * Position         : Professor of Finance17 * Institution      : Université du Québec en Outaouais (UQO)18 * Organization     : SP Boucher19 * Website          : https://spboucher.ai20 * Git              : https://git.spboucher.ai21 *22 * Preferred Contact: dev@spboucher.ai23 * Development      : dev@spboucher.ai24 * Support          : support@spboucher.ai25 * Security/Admin   : admin@spboucher.ai26 *27 * Copyright (c) 202628 * Simon-Pierre Boucher29 * All rights reserved where applicable. This file is governed by the30 * applicable project license, copyright notices and relevant law.31 * Existing third-party rights and notices remain unaffected.32 *33 * License          : None specified (no license file in this repository)34 *35 * Do not embed credentials, private keys, access tokens,36 * certificates or confidential personal information.37 *38 * =============================================================================39 * END OF SP BOUCHER SOURCE HEADER40 * =============================================================================41 */4243// Command-line audit of Excel workbooks. No dependency, nothing executed, JSON on stdout.44import { readFileSync } from "node:fs";45import { createApi } from "./lib/api.mjs";4647const USAGE = `Usage: node xlsx.mjs <command> <workbook.xlsx> [options]4849Audit50  summary      <file>                              sheets, counts, names, functions, share outside the evaluated subset51  report       <file> [--out report.md|.json]      the whole audit (Markdown unless --json or a .json destination)52  errors       <file> [--sheet S]                  error cells, root causes first53  circular     <file>                              circular references, loop by loop54  hardcoded    <file> [--sheet S] [--ignore 0,1]   constants inside formulas; typed numbers where a formula is expected55  consistency  <file> [--sheet S] [--min-run 3]    cells breaking the formula pattern of their row or column56  controls     <file> [--sheet S] [--ranges R,R]   the model's own checks57  recalc       <file> [--sheet S]                  stored results against a recomputation of the supported subset58Read59  range        <file> <Sheet!A1:D20> [--offset N]  cells with value, formula, number format, value source60  formulas     <file> [--sheet S] [--group]        formulas, or one line per pattern with --group61  precedents   <file> <Sheet!A1> [--depth 2]       where a number comes from62  dependents   <file> <Sheet!A1> [--depth 2]       what a cell feeds63Compare64  compare      <reference> <submission> [--ranges R,R]65  grade        <reference> <submission> --rubric rubric.json [--out evidence.md|.json]6667Common options: --limit N, --tolerance X, --root DIR (confine every path; default: XLSX_AUDIT_ROOT, else no confinement)68Exit status: 0 done, 2 error (a JSON object with error.code is printed).69Reports and evidence files are never overwritten.`;7071const argv = process.argv.slice(2), pos = [], opt = {};72for (let i = 0; i < argv.length; i++) {73  const a = argv[i];74  if (!a.startsWith("--")) { pos.push(a); continue; }75  const key = a.slice(2).replace(/-/g, "_");76  if (["group", "json", "help"].includes(key)) opt[key] = true;77  else { if (i + 1 >= argv.length) { console.error(`Missing value for ${a}`); process.exit(2); } opt[key] = argv[++i]; }78}79const [cmd, a1, a2] = pos;80if (!cmd || opt.help || cmd === "help") { console.log(USAGE); process.exit(cmd || opt.help ? 0 : 2); }8182const n = (v) => (v === undefined ? undefined : Number(v));83const list = (v) => (v === undefined ? undefined : String(v).split(",").map((s) => s.trim()).filter(Boolean));84const common = { sheet: opt.sheet, limit: n(opt.limit), tolerance: n(opt.tolerance) };85const clean = (o) => Object.fromEntries(Object.entries(o).filter(([, v]) => v !== undefined && !(typeof v === "number" && Number.isNaN(v))));8687try {88  const root = opt.root ?? process.env.XLSX_AUDIT_ROOT ?? null;89  const api = createApi({ root: root || null, env: process.env });90  const run = {91    summary: () => api.workbook_summary({ path: a1 }),92    report: () => api.export_report(clean({ path: a1, output: opt.out, format: opt.json ? "json" : undefined, ...common })),93    errors: () => api.find_errors(clean({ path: a1, ...common })),94    circular: () => api.find_circular(clean({ path: a1, ...common })),95    hardcoded: () => api.find_hardcoded(clean({ path: a1, ignore: list(opt.ignore)?.map(Number), ...common })),96    consistency: () => api.formula_consistency(clean({ path: a1, min_run: n(opt.min_run), ...common })),97    controls: () => api.check_controls(clean({ path: a1, ranges: list(opt.ranges), ...common })),98    recalc: () => api.recalc_check(clean({ path: a1, ...common })),99    range: () => api.read_range(clean({ path: a1, range: a2, offset: n(opt.offset), ...common })),100    formulas: () => api.list_formulas(clean({ path: a1, range: opt.range, group: opt.group, offset: n(opt.offset), ...common })),101    precedents: () => api.trace_precedents(clean({ path: a1, cell: a2, depth: n(opt.depth), limit: common.limit })),102    dependents: () => api.trace_dependents(clean({ path: a1, cell: a2, depth: n(opt.depth), limit: common.limit })),103    compare: () => api.compare_workbooks(clean({ reference: a1, submission: a2, ranges: list(opt.ranges), tolerance: common.tolerance, limit: common.limit })),104    grade: () => {105      if (!opt.rubric) throw Object.assign(new Error("grade needs --rubric rubric.json"), { code: "rubric_invalid" });106      let rubric;107      try { rubric = JSON.parse(readFileSync(opt.rubric, "utf8")); } catch { throw Object.assign(new Error(`Cannot read ${opt.rubric} as JSON.`), { code: "rubric_invalid" }); }108      return api.grade_submission(clean({ reference: a1, submission: a2, rubric, output: opt.out }));109    },110  }[cmd];111  if (!run) throw Object.assign(new Error(`Unknown command ${cmd}. Run with --help.`), { code: "usage" });112  if (!a1) throw Object.assign(new Error(`${cmd} needs a workbook path. Run with --help.`), { code: "usage" });113  if (["range", "precedents", "dependents", "compare", "grade"].includes(cmd) && !a2) throw Object.assign(new Error(`${cmd} needs a second argument. Run with --help.`), { code: "usage" });114  const result = run();115  if (cmd === "report" && !opt.out && !opt.json) console.log(result.content); else console.log(JSON.stringify(result, null, 2));116} catch (e) {117  console.log(JSON.stringify({ error: { code: e.code ?? "error", message: String(e.message ?? e).slice(0, 600) } }, null, 2));118  process.exit(2);119}120
Files main
Menu

Theme