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/refs.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 */4142/** A1 reference helpers. Columns and rows are 1-based numbers internally. */43export const MAX_COL = 16384, MAX_ROW = 1048576;4445export function colToNum(s) { let n = 0; for (const ch of s.toUpperCase()) n = n * 26 + (ch.charCodeAt(0) - 64); return n; }46export function numToCol(n) { let s = ""; while (n > 0) { const r = (n - 1) % 26; s = String.fromCharCode(65 + r) + s; n = Math.floor((n - 1) / 26); } return s; }47export const addr = (col, row) => `${numToCol(col)}${row}`;4849export function parseCell(s) {50 const m = /^\$?([A-Za-z]{1,3})\$?(\d{1,7})$/.exec(String(s).trim());51 if (!m) return null;52 const col = colToNum(m[1]), row = Number(m[2]);53 return col >= 1 && col <= MAX_COL && row >= 1 && row <= MAX_ROW ? { col, row } : null;54}55export const quoteSheet = (name) => (/^[A-Za-z_][\w.]*$/.test(name) ? name : `'${name.replace(/'/g, "''")}'`);5657/** "Sheet!A1:B5", "'My sheet'!C3", "A1:B2" → { sheet|null, c1, r1, c2, r2 } or null. */58export function parseRange(s) {59 let str = String(s).trim(), sheet = null;60 const q = /^'((?:[^']|'')+)'!/.exec(str), u = /^([^'!:]+)!/.exec(str);61 if (q) { sheet = q[1].replace(/''/g, "'"); str = str.slice(q[0].length); }62 else if (u) { sheet = u[1]; str = str.slice(u[0].length); }63 const parts = str.split(":");64 if (parts.length > 2) return null;65 const a = parseCell(parts[0]), b = parts.length === 2 ? parseCell(parts[1]) : a;66 if (!a || !b) return null;67 return { sheet, c1: Math.min(a.col, b.col), r1: Math.min(a.row, b.row), c2: Math.max(a.col, b.col), r2: Math.max(a.row, b.row) };68}69export const rangeText = (r) => `${r.sheet ? quoteSheet(r.sheet) + "!" : ""}${addr(r.c1, r.r1)}${r.c1 === r.c2 && r.r1 === r.r2 ? "" : ":" + addr(r.c2, r.r2)}`;70