// ═══════════════════════════════════════════════════ LEON Office — Home + Sheets
// Two things live in this file: the Office shell that every document opens
// from, and LEON Sheets. They are together because the shell owns autosave,
// version history and comments for all three apps, and Sheets is the app that
// justifies building Office inside the Hub at all — a schedule that READS the
// job instead of being copied out of it.
//
// The constraint that shapes every decision below: documents persist to
// localStorage, which this browser caps at roughly 13 MB for the WHOLE app.
// So — a document references LEON records by id and never copies them; sheet
// cells are stored sparsely keyed "r,c"; version history is bounded and says
// out loud when it drops the oldest snapshot. Blow the quota and the app loses
// its state, not just its documents.
//
// The document record itself (makeOfficeDocument, makeSheetBody, OFFICE_APPS,
// OFFICE_STATUSES, OFFICE_SCOPES) is defined in data.jsx and is fixed. Nothing
// here redefines it.
// ── Addresses ─────────────────────────────────────────────────────────────
// Everything internal is (row, col) zero-based. A1 notation exists only at the
// two edges: what a user types, and what a formula reads.
function sheetColLabel(c) {
let s = '', n = c + 1;
while (n > 0) { const m = (n - 1) % 26; s = String.fromCharCode(65 + m) + s; n = Math.floor((n - 1) / 26); }
return s;
}
function sheetColIndex(label) {
let n = 0;
const s = String(label || '').toUpperCase();
for (let i = 0; i < s.length; i++) n = n * 26 + (s.charCodeAt(i) - 64);
return n - 1;
}
function sheetA1(r, c) { return sheetColLabel(c) + (r + 1); }
function sheetKey(r, c) { return r + ',' + c; }
function sheetParseKey(k) { const p = String(k).split(','); return { r: Number(p[0]) || 0, c: Number(p[1]) || 0 }; }
function sheetRangeA1(r1, c1, r2, c2) { return sheetA1(r1, c1) + ':' + sheetA1(r2, c2); }
const SHEET_MAX_ROWS = 50000;
const SHEET_MAX_COLS = 200;
const SHEET_DEFAULT_ROWS = 200;
const SHEET_DEFAULT_COLS = 26;
const SHEET_ROW_H = 26;
const SHEET_COL_W = 104;
const SHEET_HEAD_H = 24;
const SHEET_GUTTER_W = 52;
// A single formula is allowed to touch this many cells. Without a cap,
// =SUM(A:A) on a 50,000-row sheet locks the tab; with it the user gets #REF!
// and an explanation instead of a hung browser.
const SHEET_MAX_RANGE_CELLS = 200000;
// ── Errors ────────────────────────────────────────────────────────────────
const SHEET_ERR_DIV0 = '#DIV/0!';
const SHEET_ERR_VALUE = '#VALUE!';
const SHEET_ERR_REF = '#REF!';
const SHEET_ERR_NAME = '#NAME?';
const SHEET_ERR_NA = '#N/A';
const SHEET_ERR_CIRC = '#CIRC';
const SHEET_ERRORS = [SHEET_ERR_DIV0, SHEET_ERR_VALUE, SHEET_ERR_REF, SHEET_ERR_NAME, SHEET_ERR_NA, SHEET_ERR_CIRC];
function sheetIsErr(v) { return typeof v === 'string' && SHEET_ERRORS.indexOf(v) >= 0; }
// Errors travel as thrown objects so a nested call cannot silently swallow one
// by coercing it to a number. Only the cell boundary catches.
function sheetThrow(code) { const e = new Error(code); e.__sheetErr = code; throw e; }
function sheetErrOf(e) { return e && e.__sheetErr ? e.__sheetErr : null; }
// ── Dates ─────────────────────────────────────────────────────────────────
// Stored as Excel-style serial numbers so date arithmetic (=A1+7) just works,
// and rendered through a date format. Storing ISO strings instead would make
// every date sum a #VALUE!.
const SHEET_EPOCH = Date.UTC(1899, 11, 30);
function sheetSerialFromParts(y, m, d) { return Math.round((Date.UTC(y, m - 1, d) - SHEET_EPOCH) / 86400000); }
function sheetDateFromSerial(n) { return new Date(SHEET_EPOCH + Math.round(Number(n) * 86400000) / 1); }
function sheetSerialToISO(n) {
const ms = SHEET_EPOCH + Math.floor(Number(n)) * 86400000;
const d = new Date(ms);
const p = x => String(x).padStart(2, '0');
return d.getUTCFullYear() + '-' + p(d.getUTCMonth() + 1) + '-' + p(d.getUTCDate());
}
const SHEET_ISO_RE = /^(\d{4})-(\d{2})-(\d{2})$/;
const SHEET_US_RE = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/;
function sheetDateSerial(v) {
if (typeof v === 'number') return v;
const s = String(v == null ? '' : v).trim();
let m = SHEET_ISO_RE.exec(s);
if (m) return sheetSerialFromParts(Number(m[1]), Number(m[2]), Number(m[3]));
m = SHEET_US_RE.exec(s);
if (m) return sheetSerialFromParts(Number(m[3]), Number(m[1]), Number(m[2]));
return null;
}
// ── Coercion ──────────────────────────────────────────────────────────────
function sheetToNum(v) {
if (v === null || v === undefined || v === '') return 0;
if (typeof v === 'number') return isFinite(v) ? v : sheetThrow(SHEET_ERR_VALUE);
if (typeof v === 'boolean') return v ? 1 : 0;
if (sheetIsErr(v)) sheetThrow(v);
const s = String(v).trim();
const d = sheetDateSerial(s);
if (d !== null) return d;
const cleaned = s.replace(/[$,\s]/g, '').replace(/^\((.*)\)$/, '-$1');
if (cleaned.endsWith('%')) {
const n = Number(cleaned.slice(0, -1));
if (!isNaN(n) && cleaned.length > 1) return n / 100;
}
const n = Number(cleaned);
if (cleaned !== '' && !isNaN(n)) return n;
return sheetThrow(SHEET_ERR_VALUE);
}
function sheetToStr(v) {
if (v === null || v === undefined) return '';
if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE';
if (sheetIsErr(v)) sheetThrow(v);
return String(v);
}
function sheetToBool(v) {
if (typeof v === 'boolean') return v;
if (v === null || v === undefined || v === '') return false;
if (sheetIsErr(v)) sheetThrow(v);
if (typeof v === 'string') {
const s = v.trim().toUpperCase();
if (s === 'TRUE') return true;
if (s === 'FALSE') return false;
}
return sheetToNum(v) !== 0;
}
function sheetIsBlank(v) { return v === null || v === undefined || v === ''; }
// A raw typed value becomes a number when it plainly is one. Everything else
// stays text, because "007" and "1-2" are not numbers a user meant to enter.
function sheetCoerceInput(raw) {
const s = String(raw == null ? '' : raw);
if (s === '') return '';
if (s[0] === '=') return s;
const t = s.trim();
if (/^-?\d+(\.\d+)?$/.test(t)) return Number(t);
if (/^-?\d*\.?\d+(e[-+]?\d+)?$/i.test(t) && t !== '.') return Number(t);
if (/^\(\d+(\.\d+)?\)$/.test(t)) return -Number(t.slice(1, -1));
if (/^-?\$[\d,]+(\.\d+)?$/.test(t)) return Number(t.replace(/[$,]/g, ''));
if (/^-?[\d,]+(\.\d+)?%$/.test(t)) return Number(t.replace(/[,%]/g, '')) / 100;
return s;
}
// ── Tokenizer ─────────────────────────────────────────────────────────────
// Positions are recorded on reference tokens because the fill handle rewrites
// formulas by re-emitting the ORIGINAL text with only the refs shifted — that
// keeps spacing, casing and everything else the user typed intact.
const SHEET_SHEETQ = "(?:'((?:[^']|'')+)'|([A-Za-z_\\u00C0-\\u024F][A-Za-z0-9_\\u00C0-\\u024F .]*?))!";
const SHEET_CELLPAT = '(\\$?)([A-Za-z]{1,3})(\\$?)([0-9]{1,7})';
const SHEET_REF_RE = new RegExp('^(?:' + SHEET_SHEETQ + ')?' + SHEET_CELLPAT + '(?::(?:' + SHEET_SHEETQ + ')?' + SHEET_CELLPAT + ')?');
const SHEET_NAME_RE = /^[A-Za-z_][A-Za-z0-9_.]*/;
const SHEET_ERR_RE = /^#(DIV\/0!|VALUE!|REF!|NAME\?|N\/A|CIRC)/;
function sheetTokenize(src) {
const out = [];
const s = String(src || '');
let i = 0;
while (i < s.length) {
const ch = s[i];
if (ch === ' ' || ch === '\t' || ch === '\n' || ch === '\r') { i++; continue; }
if (ch === '"') {
let j = i + 1, str = '';
while (j < s.length) {
if (s[j] === '"') { if (s[j + 1] === '"') { str += '"'; j += 2; continue; } break; }
str += s[j]; j++;
}
out.push({ t: 'str', v: str, p: i, len: (j + 1) - i });
i = j + 1; continue;
}
if (ch === '#') {
const m = SHEET_ERR_RE.exec(s.slice(i));
if (m) { out.push({ t: 'err', v: m[0], p: i, len: m[0].length }); i += m[0].length; continue; }
}
if ((ch >= '0' && ch <= '9') || (ch === '.' && s[i + 1] >= '0' && s[i + 1] <= '9')) {
let j = i, seenDot = false;
while (j < s.length && ((s[j] >= '0' && s[j] <= '9') || (s[j] === '.' && !seenDot))) { if (s[j] === '.') seenDot = true; j++; }
if (s[j] === 'e' || s[j] === 'E') {
let k = j + 1;
if (s[k] === '+' || s[k] === '-') k++;
if (s[k] >= '0' && s[k] <= '9') { j = k; while (j < s.length && s[j] >= '0' && s[j] <= '9') j++; }
}
out.push({ t: 'num', v: parseFloat(s.slice(i, j)), p: i, len: j - i });
i = j; continue;
}
const rm = SHEET_REF_RE.exec(s.slice(i));
if (rm) {
// LOG10( looks exactly like a cell reference. A '(' after it settles it:
// that is a function call, not column LOG row 10.
let k = i + rm[0].length;
while (s[k] === ' ') k++;
const isCall = s[k] === '(';
if (!isCall) {
out.push({
t: 'ref', p: i, len: rm[0].length, raw: rm[0],
sheet: rm[1] ? rm[1].replace(/''/g, "'") : (rm[2] || null),
absC: rm[3] === '$', col: sheetColIndex(rm[4]), absR: rm[5] === '$', row: Number(rm[6]) - 1,
// The second half is present when its COLUMN LETTERS matched — the
// "$" groups around it match the empty string, which is falsy.
has2: rm[10] !== undefined,
sheet2: rm[7] ? rm[7].replace(/''/g, "'") : (rm[8] || null),
absC2: rm[9] === '$', col2: rm[10] ? sheetColIndex(rm[10]) : null,
absR2: rm[11] === '$', row2: rm[12] ? Number(rm[12]) - 1 : null,
});
i += rm[0].length; continue;
}
}
const nm = SHEET_NAME_RE.exec(s.slice(i));
if (nm) {
let k = i + nm[0].length;
while (s[k] === ' ') k++;
const up = nm[0].toUpperCase();
if (s[k] === '(') out.push({ t: 'fn', v: up, p: i, len: nm[0].length });
else if (up === 'TRUE' || up === 'FALSE') out.push({ t: 'bool', v: up === 'TRUE', p: i, len: nm[0].length });
else out.push({ t: 'name', v: nm[0], p: i, len: nm[0].length });
i += nm[0].length; continue;
}
const two = s.slice(i, i + 2);
if (two === '<=' || two === '>=' || two === '<>') { out.push({ t: 'op', v: two, p: i, len: 2 }); i += 2; continue; }
if ('+-*/^&=<>%(),:'.indexOf(ch) >= 0) { out.push({ t: 'op', v: ch, p: i, len: 1 }); i++; continue; }
// Anything else is a character no formula grammar accepts.
out.push({ t: 'bad', v: ch, p: i, len: 1 }); i++;
}
return out;
}
// ── Parser ────────────────────────────────────────────────────────────────
// Recursive descent, standard spreadsheet precedence. The AST is deliberately
// plain data so it can be walked twice: once to evaluate, once to collect the
// cells a formula reads (which is what builds the dependency graph).
function sheetParse(formula) {
const src = String(formula || '');
const body = src[0] === '=' ? src.slice(1) : src;
const toks = sheetTokenize(body);
let i = 0;
const peek = () => toks[i];
const isOp = v => { const t = toks[i]; return t && t.t === 'op' && t.v === v; };
const eat = v => { if (isOp(v)) { i++; return true; } return false; };
const fail = () => { throw new Error('parse'); };
function refNode(t) {
if (t.has2) {
return {
k: 'range', sheet: t.sheet, sheet2: t.sheet2,
r1: Math.min(t.row, t.row2), c1: Math.min(t.col, t.col2),
r2: Math.max(t.row, t.row2), c2: Math.max(t.col, t.col2),
};
}
return { k: 'ref', sheet: t.sheet, r: t.row, c: t.col };
}
function primary() {
const t = peek();
if (!t) fail();
if (t.t === 'num') { i++; return { k: 'num', v: t.v }; }
if (t.t === 'str') { i++; return { k: 'str', v: t.v }; }
if (t.t === 'bool') { i++; return { k: 'bool', v: t.v }; }
if (t.t === 'err') { i++; return { k: 'err', v: t.v }; }
if (t.t === 'ref') { i++; return refNode(t); }
if (t.t === 'name') { i++; return { k: 'name', v: t.v }; }
if (t.t === 'fn') {
i++;
if (!eat('(')) fail();
const args = [];
if (!isOp(')')) {
for (;;) {
args.push(expr());
if (eat(',')) continue;
break;
}
}
if (!eat(')')) fail();
return { k: 'fn', name: t.v, args };
}
if (t.t === 'op' && t.v === '(') { i++; const e = expr(); if (!eat(')')) fail(); return e; }
fail();
}
function postfix() {
let n = primary();
while (isOp('%')) { i++; n = { k: 'pct', a: n }; }
return n;
}
function power() {
const a = postfix();
if (isOp('^')) { i++; return { k: 'bin', op: '^', a, b: unary() }; }
return a;
}
function unary() {
if (isOp('-')) { i++; return { k: 'un', op: '-', a: unary() }; }
if (isOp('+')) { i++; return unary(); }
return power();
}
function mul() {
let a = unary();
while (isOp('*') || isOp('/')) { const op = peek().v; i++; a = { k: 'bin', op, a, b: unary() }; }
return a;
}
function add() {
let a = mul();
while (isOp('+') || isOp('-')) { const op = peek().v; i++; a = { k: 'bin', op, a, b: mul() }; }
return a;
}
function concat() {
let a = add();
while (isOp('&')) { i++; a = { k: 'bin', op: '&', a, b: add() }; }
return a;
}
function expr() {
let a = concat();
while (isOp('=') || isOp('<') || isOp('>') || isOp('<=') || isOp('>=') || isOp('<>')) {
const op = peek().v; i++; a = { k: 'bin', op, a, b: concat() };
}
return a;
}
const root = expr();
if (i < toks.length) fail();
return root;
}
// Parsed ASTs are cached — a 5,000-cell recalc otherwise re-parses the same
// dragged-down formula 5,000 times.
const SHEET_AST_CACHE = new Map();
function sheetAst(formula) {
const key = String(formula);
if (SHEET_AST_CACHE.has(key)) return SHEET_AST_CACHE.get(key);
let ast;
try { ast = sheetParse(key); } catch (e) { ast = { k: 'err', v: SHEET_ERR_NAME }; }
if (SHEET_AST_CACHE.size > 4000) SHEET_AST_CACHE.clear();
SHEET_AST_CACHE.set(key, ast);
return ast;
}
// ── Evaluation ────────────────────────────────────────────────────────────
// E is the workbook view a formula sees: E.cell(sheetName, r, c) returns an
// already-evaluated scalar, E.sheetName is the sheet the formula lives on.
// Ranges are lazy — a range node evaluates to a descriptor, and only the
// functions that want cells expand it, so =INDEX(A1:Z50000, 3, 2) never reads
// fifty thousand rows.
function sheetRangeCells(E, rng) {
const rows = rng.r2 - rng.r1 + 1, cols = rng.c2 - rng.c1 + 1;
if (rows * cols > SHEET_MAX_RANGE_CELLS) sheetThrow(SHEET_ERR_REF);
const out = [];
for (let r = rng.r1; r <= rng.r2; r++) {
const line = [];
for (let c = rng.c1; c <= rng.c2; c++) line.push(E.cell(rng.sheet || E.sheetName, r, c));
out.push(line);
}
return out;
}
function sheetIsRange(v) { return !!(v && v.__rng); }
function sheetFlat(E, v, out) {
const acc = out || [];
if (sheetIsRange(v)) { sheetRangeCells(E, v).forEach(line => line.forEach(x => acc.push(x))); return acc; }
if (Array.isArray(v)) { v.forEach(x => sheetFlat(E, x, acc)); return acc; }
acc.push(v);
return acc;
}
function sheetScalar(E, v) {
if (!sheetIsRange(v)) return v;
// A range used where a value is wanted collapses to its first cell, which is
// what makes =A1:A5*2 wrong rather than mysterious.
const cells = sheetRangeCells(E, v);
return cells.length && cells[0].length ? cells[0][0] : '';
}
function sheetCompare(a, b) {
const an = typeof a === 'number', bn = typeof b === 'number';
if (an && bn) return a < b ? -1 : a > b ? 1 : 0;
if (typeof a === 'boolean' || typeof b === 'boolean') {
const x = sheetToBool(a) ? 1 : 0, y = sheetToBool(b) ? 1 : 0;
return x < y ? -1 : x > y ? 1 : 0;
}
const as = sheetToStr(a).toLowerCase(), bs = sheetToStr(b).toLowerCase();
return as < bs ? -1 : as > bs ? 1 : 0;
}
function sheetEval(ast, E) {
switch (ast.k) {
case 'num': return ast.v;
case 'str': return ast.v;
case 'bool': return ast.v;
case 'err': sheetThrow(ast.v); return null;
case 'name': sheetThrow(SHEET_ERR_NAME); return null;
case 'ref': {
if (ast.r < 0 || ast.c < 0) sheetThrow(SHEET_ERR_REF);
return E.cell(ast.sheet || E.sheetName, ast.r, ast.c);
}
case 'range': {
if (ast.r1 < 0 || ast.c1 < 0) sheetThrow(SHEET_ERR_REF);
return { __rng: true, sheet: ast.sheet || E.sheetName, r1: ast.r1, c1: ast.c1, r2: ast.r2, c2: ast.c2 };
}
case 'pct': return sheetToNum(sheetScalar(E, sheetEval(ast.a, E))) / 100;
case 'un': return -sheetToNum(sheetScalar(E, sheetEval(ast.a, E)));
case 'bin': {
const op = ast.op;
const a = sheetScalar(E, sheetEval(ast.a, E));
const b = sheetScalar(E, sheetEval(ast.b, E));
if (op === '&') return sheetToStr(a) + sheetToStr(b);
if (op === '=' ) return sheetCompare(a, b) === 0;
if (op === '<>') return sheetCompare(a, b) !== 0;
if (op === '<' ) return sheetCompare(a, b) < 0;
if (op === '>' ) return sheetCompare(a, b) > 0;
if (op === '<=') return sheetCompare(a, b) <= 0;
if (op === '>=') return sheetCompare(a, b) >= 0;
const x = sheetToNum(a), y = sheetToNum(b);
if (op === '+') return x + y;
if (op === '-') return x - y;
if (op === '*') return x * y;
if (op === '/') { if (y === 0) sheetThrow(SHEET_ERR_DIV0); return x / y; }
if (op === '^') { const v = Math.pow(x, y); if (!isFinite(v)) sheetThrow(SHEET_ERR_VALUE); return v; }
sheetThrow(SHEET_ERR_VALUE); return null;
}
case 'fn': {
const name = ast.name;
// hasOwnProperty, not a bare lookup: =TOSTRING() would otherwise find
// Object.prototype.toString and call it as a worksheet function.
const own = (o, k) => Object.prototype.hasOwnProperty.call(o, k);
if (own(SHEET_LAZY_FUNCS, name)) return SHEET_LAZY_FUNCS[name](ast.args, E);
if (!own(SHEET_FUNCS, name)) sheetThrow(SHEET_ERR_NAME);
return SHEET_FUNCS[name](ast.args.map(a => sheetEval(a, E)), E);
}
default: sheetThrow(SHEET_ERR_VALUE); return null;
}
}
// ── Criteria: ">10", "<>Open", "Cab*" ─────────────────────────────────────
function sheetCriterion(crit) {
if (crit === null || crit === undefined) return () => false;
if (typeof crit === 'number' || typeof crit === 'boolean') return v => sheetCompare(v, crit) === 0;
const s = String(crit).trim();
const m = /^(<=|>=|<>|=|<|>)(.*)$/.exec(s);
if (m) {
const op = m[1];
const rawRhs = m[2].trim();
const rhs = rawRhs === '' ? '' : sheetCoerceInput(rawRhs);
return v => {
if (sheetIsBlank(v) && rhs === '') return op === '=' || op === '<=' || op === '>=';
const cmp = sheetCompare(v, rhs);
if (op === '=') return cmp === 0;
if (op === '<>') return cmp !== 0;
if (op === '<') return cmp < 0;
if (op === '>') return cmp > 0;
if (op === '<=') return cmp <= 0;
return cmp >= 0;
};
}
if (s.indexOf('*') >= 0 || s.indexOf('?') >= 0) {
const rx = new RegExp('^' + s.replace(/[.+^${}()|[\]\\]/g, '\\$&').replace(/\*/g, '.*').replace(/\?/g, '.') + '$', 'i');
return v => rx.test(String(v == null ? '' : v));
}
const target = sheetCoerceInput(s);
return v => sheetCompare(v, target) === 0;
}
// ── Functions ─────────────────────────────────────────────────────────────
// Deliberately a closed list. An unknown name is #NAME? rather than something
// that looks like it worked — see SHEET_NOT_BUILT for what is missing on
// purpose.
function sheetNums(E, args) {
const out = [];
sheetFlat(E, args).forEach(v => {
if (sheetIsErr(v)) sheetThrow(v);
if (typeof v === 'number') out.push(v);
else if (typeof v === 'boolean') { /* booleans are ignored inside ranges, as in Excel */ }
else if (!sheetIsBlank(v)) { const d = sheetDateSerial(v); if (d !== null) out.push(d); else { const n = Number(String(v).replace(/[$,\s]/g, '')); if (String(v).trim() !== '' && !isNaN(n)) out.push(n); } }
});
return out;
}
const SHEET_FUNCS = {
SUM: (a, E) => sheetNums(E, a).reduce((x, y) => x + y, 0),
AVERAGE: (a, E) => { const n = sheetNums(E, a); if (!n.length) sheetThrow(SHEET_ERR_DIV0); return n.reduce((x, y) => x + y, 0) / n.length; },
MIN: (a, E) => { const n = sheetNums(E, a); return n.length ? Math.min.apply(null, n) : 0; },
MAX: (a, E) => { const n = sheetNums(E, a); return n.length ? Math.max.apply(null, n) : 0; },
COUNT: (a, E) => sheetNums(E, a).length,
COUNTA: (a, E) => sheetFlat(E, a).filter(v => !sheetIsBlank(v)).length,
ROUND: (a, E) => { const n = sheetToNum(sheetScalar(E, a[0])); const d = a.length > 1 ? Math.trunc(sheetToNum(sheetScalar(E, a[1]))) : 0; const f = Math.pow(10, d); return Math.round((n * f + (n >= 0 ? 1e-9 : -1e-9))) / f; },
ABS: (a, E) => Math.abs(sheetToNum(sheetScalar(E, a[0]))),
AND: (a, E) => { const v = sheetFlat(E, a).filter(x => !sheetIsBlank(x)); return v.length ? v.every(x => sheetToBool(x)) : false; },
OR: (a, E) => sheetFlat(E, a).filter(x => !sheetIsBlank(x)).some(x => sheetToBool(x)),
NOT: (a, E) => !sheetToBool(sheetScalar(E, a[0])),
VLOOKUP: (a, E) => {
const key = sheetScalar(E, a[0]);
if (!sheetIsRange(a[1])) sheetThrow(SHEET_ERR_VALUE);
const grid = sheetRangeCells(E, a[1]);
const col = Math.trunc(sheetToNum(sheetScalar(E, a[2])));
const approx = a.length > 3 ? sheetToBool(sheetScalar(E, a[3])) : true;
if (col < 1 || !grid.length || col > grid[0].length) sheetThrow(SHEET_ERR_REF);
let best = -1;
for (let i = 0; i < grid.length; i++) {
const cmp = sheetCompare(grid[i][0], key);
if (cmp === 0) { best = i; break; }
if (approx && cmp < 0) best = i;
}
if (best < 0) sheetThrow(SHEET_ERR_NA);
return grid[best][col - 1];
},
HLOOKUP: (a, E) => {
const key = sheetScalar(E, a[0]);
if (!sheetIsRange(a[1])) sheetThrow(SHEET_ERR_VALUE);
const grid = sheetRangeCells(E, a[1]);
const row = Math.trunc(sheetToNum(sheetScalar(E, a[2])));
const approx = a.length > 3 ? sheetToBool(sheetScalar(E, a[3])) : true;
if (row < 1 || row > grid.length) sheetThrow(SHEET_ERR_REF);
const head = grid[0] || [];
let best = -1;
for (let i = 0; i < head.length; i++) {
const cmp = sheetCompare(head[i], key);
if (cmp === 0) { best = i; break; }
if (approx && cmp < 0) best = i;
}
if (best < 0) sheetThrow(SHEET_ERR_NA);
return grid[row - 1][best];
},
INDEX: (a, E) => {
if (!sheetIsRange(a[0])) sheetThrow(SHEET_ERR_VALUE);
const rng = a[0];
const rows = rng.r2 - rng.r1 + 1, cols = rng.c2 - rng.c1 + 1;
let ri = a.length > 1 ? Math.trunc(sheetToNum(sheetScalar(E, a[1]))) : 0;
let ci = a.length > 2 ? Math.trunc(sheetToNum(sheetScalar(E, a[2]))) : 0;
// A one-column or one-row range takes a single index, as everywhere else.
if (a.length === 2) { if (cols === 1) ci = 1; else if (rows === 1) { ci = ri; ri = 1; } else ci = 1; }
if (ri < 1 || ci < 1 || ri > rows || ci > cols) sheetThrow(SHEET_ERR_REF);
return E.cell(rng.sheet, rng.r1 + ri - 1, rng.c1 + ci - 1);
},
MATCH: (a, E) => {
const key = sheetScalar(E, a[0]);
if (!sheetIsRange(a[1])) sheetThrow(SHEET_ERR_VALUE);
const list = sheetFlat(E, a[1]);
const type = a.length > 2 ? Math.trunc(sheetToNum(sheetScalar(E, a[2]))) : 1;
if (type === 0) {
const test = sheetCriterion(typeof key === 'string' ? key : key);
for (let i = 0; i < list.length; i++) if (sheetCompare(list[i], key) === 0 || (typeof key === 'string' && test(list[i]))) return i + 1;
sheetThrow(SHEET_ERR_NA);
}
let best = -1;
for (let i = 0; i < list.length; i++) {
const cmp = sheetCompare(list[i], key);
if (type === 1 && cmp <= 0) best = i;
if (type === -1 && cmp >= 0) best = i;
}
if (best < 0) sheetThrow(SHEET_ERR_NA);
return best + 1;
},
SUMIF: (a, E) => {
const grid = sheetFlat(E, a[0]);
const test = sheetCriterion(sheetScalar(E, a[1]));
const sumFrom = a.length > 2 ? sheetFlat(E, a[2]) : grid;
let t = 0;
grid.forEach((v, i) => { if (test(v)) { const n = Number(sumFrom[i]); if (typeof sumFrom[i] === 'number') t += sumFrom[i]; else if (!isNaN(n) && !sheetIsBlank(sumFrom[i])) t += n; } });
return t;
},
AVERAGEIF: (a, E) => {
const grid = sheetFlat(E, a[0]);
const test = sheetCriterion(sheetScalar(E, a[1]));
const from = a.length > 2 ? sheetFlat(E, a[2]) : grid;
const hits = [];
grid.forEach((v, i) => { if (test(v)) { const x = from[i]; if (typeof x === 'number') hits.push(x); else { const n = Number(x); if (!sheetIsBlank(x) && !isNaN(n)) hits.push(n); } } });
if (!hits.length) sheetThrow(SHEET_ERR_DIV0);
return hits.reduce((x, y) => x + y, 0) / hits.length;
},
COUNTIF: (a, E) => {
const grid = sheetFlat(E, a[0]);
const test = sheetCriterion(sheetScalar(E, a[1]));
return grid.filter(test).length;
},
SUMIFS: (a, E) => {
const sumFrom = sheetFlat(E, a[0]);
const pairs = [];
for (let i = 1; i + 1 < a.length; i += 2) pairs.push({ grid: sheetFlat(E, a[i]), test: sheetCriterion(sheetScalar(E, a[i + 1])) });
let t = 0;
sumFrom.forEach((v, i) => {
if (pairs.every(p => p.test(p.grid[i]))) { if (typeof v === 'number') t += v; else { const n = Number(v); if (!sheetIsBlank(v) && !isNaN(n)) t += n; } }
});
return t;
},
COUNTIFS: (a, E) => {
const pairs = [];
for (let i = 0; i + 1 < a.length; i += 2) pairs.push({ grid: sheetFlat(E, a[i]), test: sheetCriterion(sheetScalar(E, a[i + 1])) });
if (!pairs.length) return 0;
let n = 0;
for (let i = 0; i < pairs[0].grid.length; i++) if (pairs.every(p => p.test(p.grid[i]))) n++;
return n;
},
CONCAT: (a, E) => sheetFlat(E, a).map(v => sheetToStr(v)).join(''),
TEXTJOIN: (a, E) => {
const delim = sheetToStr(sheetScalar(E, a[0]));
const skipEmpty = sheetToBool(sheetScalar(E, a[1]));
const parts = sheetFlat(E, a.slice(2)).map(v => sheetToStr(v));
return (skipEmpty ? parts.filter(s => s !== '') : parts).join(delim);
},
LEFT: (a, E) => sheetToStr(sheetScalar(E, a[0])).slice(0, a.length > 1 ? Math.max(0, Math.trunc(sheetToNum(sheetScalar(E, a[1])))) : 1),
RIGHT: (a, E) => { const s = sheetToStr(sheetScalar(E, a[0])); const n = a.length > 1 ? Math.max(0, Math.trunc(sheetToNum(sheetScalar(E, a[1])))) : 1; return n === 0 ? '' : s.slice(-n); },
MID: (a, E) => { const s = sheetToStr(sheetScalar(E, a[0])); const st = Math.trunc(sheetToNum(sheetScalar(E, a[1]))); const n = Math.trunc(sheetToNum(sheetScalar(E, a[2]))); if (st < 1 || n < 0) sheetThrow(SHEET_ERR_VALUE); return s.substr(st - 1, n); },
LEN: (a, E) => sheetToStr(sheetScalar(E, a[0])).length,
TRIM: (a, E) => sheetToStr(sheetScalar(E, a[0])).replace(/\s+/g, ' ').trim(),
UPPER: (a, E) => sheetToStr(sheetScalar(E, a[0])).toUpperCase(),
LOWER: (a, E) => sheetToStr(sheetScalar(E, a[0])).toLowerCase(),
PROPER: (a, E) => sheetToStr(sheetScalar(E, a[0])).toLowerCase().replace(/(^|[^A-Za-z'])([a-z])/g, (m, p, ch) => p + ch.toUpperCase()),
SUBSTITUTE: (a, E) => {
const s = sheetToStr(sheetScalar(E, a[0]));
const find = sheetToStr(sheetScalar(E, a[1]));
const rep = sheetToStr(sheetScalar(E, a[2]));
if (find === '') return s;
if (a.length > 3) {
const which = Math.trunc(sheetToNum(sheetScalar(E, a[3])));
let idx = -1, n = 0, from = 0;
for (;;) { idx = s.indexOf(find, from); if (idx < 0) return s; n++; if (n === which) return s.slice(0, idx) + rep + s.slice(idx + find.length); from = idx + find.length; }
}
return s.split(find).join(rep);
},
TODAY: () => sheetDateSerial(todayISO()),
NOW: () => { const d = new Date(); return sheetSerialFromParts(d.getFullYear(), d.getMonth() + 1, d.getDate()) + (d.getHours() * 3600 + d.getMinutes() * 60 + d.getSeconds()) / 86400; },
DATE: (a, E) => sheetSerialFromParts(Math.trunc(sheetToNum(sheetScalar(E, a[0]))), Math.trunc(sheetToNum(sheetScalar(E, a[1]))), Math.trunc(sheetToNum(sheetScalar(E, a[2])))),
YEAR: (a, E) => sheetDateFromSerial(sheetToNum(sheetScalar(E, a[0]))).getUTCFullYear(),
MONTH: (a, E) => sheetDateFromSerial(sheetToNum(sheetScalar(E, a[0]))).getUTCMonth() + 1,
DAY: (a, E) => sheetDateFromSerial(sheetToNum(sheetScalar(E, a[0]))).getUTCDate(),
};
// IF must not evaluate the branch it did not take, and IFERROR exists purely to
// swallow an error the first argument raises — both need the AST, not a value.
const SHEET_LAZY_FUNCS = {
IF: (args, E) => {
const cond = sheetToBool(sheetScalar(E, sheetEval(args[0], E)));
if (cond) return args.length > 1 ? sheetScalar(E, sheetEval(args[1], E)) : true;
return args.length > 2 ? sheetScalar(E, sheetEval(args[2], E)) : false;
},
IFS: (args, E) => {
for (let i = 0; i + 1 < args.length; i += 2) {
if (sheetToBool(sheetScalar(E, sheetEval(args[i], E)))) return sheetScalar(E, sheetEval(args[i + 1], E));
}
sheetThrow(SHEET_ERR_NA);
},
IFERROR: (args, E) => {
try {
const v = sheetScalar(E, sheetEval(args[0], E));
if (sheetIsErr(v)) return args.length > 1 ? sheetScalar(E, sheetEval(args[1], E)) : '';
return v;
} catch (e) {
if (!sheetErrOf(e)) throw e;
return args.length > 1 ? sheetScalar(E, sheetEval(args[1], E)) : '';
}
},
};
const SHEET_FUNC_NAMES = Object.keys(SHEET_FUNCS).concat(Object.keys(SHEET_LAZY_FUNCS)).sort();
// What each function takes and what it is for. The engine knows the NAMES, but
// a name alone is not usable — nobody remembers the argument order of VLOOKUP.
// Grouped the way a spreadsheet's own function browser groups them, because
// that is where people look for them.
const SHEET_FUNC_HELP = {
SUM: { g: 'Maths', a: 'value1, [value2], …', d: 'Adds numbers or ranges.' },
AVERAGE: { g: 'Maths', a: 'value1, [value2], …', d: 'The mean of the numbers.' },
MIN: { g: 'Maths', a: 'value1, [value2], …', d: 'The smallest number.' },
MAX: { g: 'Maths', a: 'value1, [value2], …', d: 'The largest number.' },
ROUND: { g: 'Maths', a: 'number, digits', d: 'Rounds to a number of decimal places.' },
ABS: { g: 'Maths', a: 'number', d: 'Drops the sign.' },
COUNT: { g: 'Counting', a: 'value1, [value2], …', d: 'How many of them are numbers.' },
COUNTA: { g: 'Counting', a: 'value1, [value2], …', d: 'How many are not empty.' },
COUNTIF: { g: 'Counting', a: 'range, criterion', d: 'How many meet one test.' },
COUNTIFS: { g: 'Counting', a: 'range1, criterion1, …', d: 'How many meet every test.' },
SUMIF: { g: 'Counting', a: 'range, criterion, [sum range]', d: 'Adds the ones that meet a test.' },
SUMIFS: { g: 'Counting', a: 'sum range, range1, criterion1, …', d: 'Adds the ones that meet every test.' },
AVERAGEIF: { g: 'Counting', a: 'range, criterion, [average range]', d: 'Averages the ones that meet a test.' },
IF: { g: 'Logic', a: 'test, then, otherwise', d: 'One answer if the test holds, another if not.' },
IFS: { g: 'Logic', a: 'test1, value1, test2, value2, …', d: 'The first test that holds decides.' },
IFERROR: { g: 'Logic', a: 'value, fallback', d: 'Use the fallback when the value is an error.' },
AND: { g: 'Logic', a: 'test1, [test2], …', d: 'True only if all of them are.' },
OR: { g: 'Logic', a: 'test1, [test2], …', d: 'True if any of them is.' },
NOT: { g: 'Logic', a: 'test', d: 'Turns true into false.' },
VLOOKUP: { g: 'Lookup', a: 'key, range, column, [exact]', d: 'Finds a row by its first column and reads across.' },
HLOOKUP: { g: 'Lookup', a: 'key, range, row, [exact]', d: 'Finds a column by its first row and reads down.' },
INDEX: { g: 'Lookup', a: 'range, row, [column]', d: 'The cell at a position in a range.' },
MATCH: { g: 'Lookup', a: 'key, range, [type]', d: 'Where in a range a value sits.' },
LEFT: { g: 'Text', a: 'text, count', d: 'The first characters.' },
RIGHT: { g: 'Text', a: 'text, count', d: 'The last characters.' },
MID: { g: 'Text', a: 'text, start, count', d: 'Characters from the middle.' },
LEN: { g: 'Text', a: 'text', d: 'How many characters.' },
TRIM: { g: 'Text', a: 'text', d: 'Removes the extra spaces.' },
UPPER: { g: 'Text', a: 'text', d: 'ALL CAPS.' },
LOWER: { g: 'Text', a: 'text', d: 'all lower case.' },
PROPER: { g: 'Text', a: 'text', d: 'Title Case.' },
CONCAT: { g: 'Text', a: 'text1, [text2], …', d: 'Joins text together.' },
TEXTJOIN: { g: 'Text', a: 'separator, ignore empty, text1, …', d: 'Joins with a separator between.' },
SUBSTITUTE: { g: 'Text', a: 'text, find, replace', d: 'Swaps one piece of text for another.' },
TODAY: { g: 'Date', a: '', d: "Today's date." },
NOW: { g: 'Date', a: '', d: 'The date and time now.' },
DATE: { g: 'Date', a: 'year, month, day', d: 'Builds a date.' },
YEAR: { g: 'Date', a: 'date', d: 'The year out of a date.' },
MONTH: { g: 'Date', a: 'date', d: 'The month out of a date.' },
DAY: { g: 'Date', a: 'date', d: 'The day out of a date.' },
};
const SHEET_FUNC_GROUPS = ['Maths', 'Counting', 'Logic', 'Lookup', 'Text', 'Date'];
// A formula whose ROOT is a date function should display as a date rather than
// as the serial number underneath. Anything deeper is the user's business.
function sheetFormulaIsDate(formula) {
const ast = sheetAst(formula);
return !!(ast && ast.k === 'fn' && (ast.name === 'TODAY' || ast.name === 'NOW' || ast.name === 'DATE'));
}
// ── Cell access ───────────────────────────────────────────────────────────
// A stored cell is { v } plus optional style keys. Absent means empty — the
// grid never materialises a cell it was not given, which is the whole reason
// a 50,000-row sheet costs nothing until it is filled in.
function sheetCellAt(ws, r, c) { return ws && ws.cells ? ws.cells[sheetKey(r, c)] : undefined; }
function sheetRaw(ws, r, c) { const cell = sheetCellAt(ws, r, c); return cell ? cell.v : ''; }
function sheetIsFormula(v) { return typeof v === 'string' && v.length > 1 && v[0] === '='; }
function sheetWsByName(body, name) {
const list = (body && body.sheets) || [];
const lower = String(name || '').toLowerCase();
return list.find(s => String(s.name || '').toLowerCase() === lower) || null;
}
function sheetWsById(body, id) { return ((body && body.sheets) || []).find(s => s.id === id) || null; }
// ── The engine ────────────────────────────────────────────────────────────
// Lazy, memoised evaluation with a reverse-dependency index. Two consequences
// that matter:
// • Only cells someone actually asks for are computed — the grid asks for the
// visible window, so opening a huge workbook does not evaluate all of it.
// • On an edit we clear the changed cell and everything transitively
// downstream of it, and NOTHING else. That is the "recalculate only what
// depends on the change" requirement, and it is why typing in an unrelated
// corner of a sheet full of formulas stays instant.
// Cycles are caught by an in-progress set: re-entering a cell that is already
// being computed yields #CIRC instead of blowing the stack.
function sheetMakeEngine(body) {
const state = { body };
const cache = new Map(); // "sheetId!r,c" -> value
const dependents = new Map(); // "sheetId!r,c" -> Set of cell ids that read it
const active = new Set();
let reads = 0;
function idOf(wsId, r, c) { return wsId + '!' + r + ',' + c; }
function link(fromId, toId) {
let set = dependents.get(fromId);
if (!set) { set = new Set(); dependents.set(fromId, set); }
set.add(toId);
}
function valueOfWs(ws, r, c, requester) {
if (!ws) return SHEET_ERR_REF;
const id = idOf(ws.id, r, c);
if (requester && requester !== id) link(id, requester);
if (cache.has(id)) return cache.get(id);
if (active.has(id)) return SHEET_ERR_CIRC;
const cell = sheetCellAt(ws, r, c);
const raw = cell ? cell.v : '';
if (!sheetIsFormula(raw)) {
// A stored leading apostrophe is the "this is text" marker (=SUM(A1)
// typed as a label, an imported cell that looked like a formula). It is
// part of the STORED value so it round-trips through the editor, and is
// stripped here so nothing downstream ever sees it.
let v = raw === undefined ? '' : raw;
if (typeof v === 'string' && v.length > 1 && v[0] === "'") v = v.slice(1);
cache.set(id, v);
return v;
}
active.add(id);
let out;
try {
reads++;
const ast = sheetAst(raw);
const E = {
sheetName: ws.name,
cell: (sheetName, rr, cc) => {
const target = String(sheetName || '').toLowerCase() === String(ws.name || '').toLowerCase()
? ws : sheetWsByName(state.body, sheetName);
if (!target) return SHEET_ERR_REF;
const v = valueOfWs(target, rr, cc, id);
if (sheetIsErr(v)) sheetThrow(v);
return v;
},
};
out = sheetScalar(E, sheetEval(ast, E));
if (out === undefined || out === null) out = '';
} catch (e) {
out = sheetErrOf(e) || SHEET_ERR_VALUE;
} finally {
active.delete(id);
}
cache.set(id, out);
return out;
}
function invalidate(wsId, r, c) {
const start = idOf(wsId, r, c);
const stack = [start];
const seen = new Set();
while (stack.length) {
const id = stack.pop();
if (seen.has(id)) continue;
seen.add(id);
cache.delete(id);
const deps = dependents.get(id);
// The reverse edges are rebuilt on the next evaluation, so they are
// dropped here — a stale edge would keep a cell dirty forever.
dependents.delete(id);
if (deps) deps.forEach(d => stack.push(d));
}
return seen.size;
}
return {
setBody(next) { state.body = next; },
get body() { return state.body; },
value(wsId, r, c) {
const ws = sheetWsById(state.body, wsId);
return valueOfWs(ws, r, c, null);
},
valueByName(name, r, c) { return valueOfWs(sheetWsByName(state.body, name), r, c, null); },
invalidate,
invalidateAll() { cache.clear(); dependents.clear(); },
stats() { return { cached: cache.size, evaluated: reads }; },
};
}
// ══════════════════════════════════════════════════════════════════════════
// Workbook defaults — Excel's own, out of the reference Sheet.xlsx
// ══════════════════════════════════════════════════════════════════════════
// SHEET_DEFAULTS (data.jsx) is what a blank workbook made in real Excel
// declares: Aptos Narrow 12pt, row height 16 POINTS, base column width 10
// CHARACTERS. Those units are Excel's and are kept: a width of 10 means the
// same thing in any workbook, where "104 pixels" means nothing outside this
// browser at this zoom. Everything here is per workbook and editable, because
// a company that sets its schedules in Century Gothic should not have to
// restyle every sheet by hand.
function sheetBookDefaults(book) {
const d = (book && book.defaults) || {};
return {
fontName: d.fontName || SHEET_DEFAULTS.fontName,
fontSizePt: d.fontSizePt || SHEET_DEFAULTS.fontSizePt,
rowHeight: d.rowHeight || SHEET_DEFAULTS.rowHeight, // points
baseColWidth: d.baseColWidth || SHEET_DEFAULTS.baseColWidth, // characters
};
}
const SHEET_FONTS = ['Aptos Narrow', 'Aptos', 'Century Gothic Leon', 'Arial', 'Calibri',
'Helvetica', 'Times New Roman', 'Georgia', 'Verdana', 'Courier New'];
const SHEET_FONT_SIZES = [8, 9, 10, 11, 12, 14, 16, 18, 20, 24, 28, 36];
function sheetPtToPx(pt) { return Math.round(Number(pt) * 4 / 3); }
function sheetPxToPt(px) { return Math.round((Number(px) * 3 / 4) * 10) / 10; }
// Excel's character unit is the width of the digit zero in the workbook's
// default font, plus a fixed 5px of cell padding. 0.62em is that ratio for the
// narrow geometric faces this app ships; it is an approximation of a metric we
// cannot measure without loading the real font file, and it is only ever used
// to convert a stored width for display.
function sheetCharPx(book) {
const d = sheetBookDefaults(book);
return Math.max(4, Math.round(d.fontSizePt * 0.62));
}
function sheetCharsToPx(chars, book) { return Math.max(0, Math.round(Number(chars) * sheetCharPx(book) + 5)); }
function sheetPxToChars(px, book) { return Math.max(0, Math.round(((Number(px) - 5) / sheetCharPx(book)) * 100) / 100); }
// ══════════════════════════════════════════════════════════════════════════
// Theme slots
// ══════════════════════════════════════════════════════════════════════════
// Office stores a SLOT, never a colour — which is the only reason "apply a
// theme" restyles a whole workbook instead of doing nothing. A fill, a font
// colour or a border colour here may be a literal '#rrggbb' OR a slot name, and
// 'accent1/85' is that slot mixed 85% of the way towards the light background,
// so a themed fill stays readable under black text without inventing a slot.
const SHEET_THEME_SLOTS = ['dk1', 'lt1', 'dk2', 'lt2', 'accent1', 'accent2', 'accent3',
'accent4', 'accent5', 'accent6', 'hlink', 'folHlink'];
const SHEET_THEMES = [makeOfficeTheme({ id: 'leon' }), OFFICE_THEME_OFFICE];
function sheetBookTheme(book) {
const t = book && book.theme;
if (t && typeof t === 'object') return t;
if (typeof t === 'string') return SHEET_THEMES.find(x => x.id === t) || SHEET_THEMES[0];
return SHEET_THEMES[0];
}
function sheetHexParts(hex) {
const s = String(hex || '').replace('#', '');
if (s.length !== 6) return null;
return [parseInt(s.slice(0, 2), 16), parseInt(s.slice(2, 4), 16), parseInt(s.slice(4, 6), 16)];
}
function sheetMixHex(a, b, t) {
const pa = sheetHexParts(a), pb = sheetHexParts(b);
if (!pa || !pb) return a;
const p = x => ('0' + Math.max(0, Math.min(255, Math.round(x))).toString(16)).slice(-2);
return '#' + p(pa[0] + (pb[0] - pa[0]) * t) + p(pa[1] + (pb[1] - pa[1]) * t) + p(pa[2] + (pb[2] - pa[2]) * t);
}
function sheetColor(book, v) {
if (!v) return undefined;
const s = String(v);
if (s[0] === '#') return s;
const th = sheetBookTheme(book);
if (SHEET_THEME_SLOTS.indexOf(s) >= 0) return th[s] || undefined;
const m = /^([A-Za-z0-9]+)\/(\d{1,3})$/.exec(s);
if (m && SHEET_THEME_SLOTS.indexOf(m[1]) >= 0) return sheetMixHex(th[m[1]] || '#000000', th.lt1 || '#FFFFFF', Number(m[2]) / 100);
return s; // any other CSS colour someone typed in
}
// ══════════════════════════════════════════════════════════════════════════
// Named cell styles
// ══════════════════════════════════════════════════════════════════════════
// Excel's own set. A cell stores the style ID (`s.st`), not a copy of its
// properties — which is what makes redefining a style restyle every cell using
// it, and what lets the title styles follow the theme. Direct formatting a
// person applied on top always wins over the style, as it does in Excel.
const SHEET_CELL_STYLES = [
{ id: 'Normal', name: 'Normal', group: 'General', s: {} },
{ id: 'Title', name: 'Title', group: 'Titles and Headings', s: { b: true, fs: 18, fg: 'dk2' } },
{ id: 'Heading1', name: 'Heading 1', group: 'Titles and Headings', s: { b: true, fs: 15, fg: 'accent1', bd: 'bottom', bdc: 'accent1' } },
{ id: 'Heading2', name: 'Heading 2', group: 'Titles and Headings', s: { b: true, fs: 13, fg: 'accent1', bd: 'bottom', bdc: 'accent1' } },
{ id: 'Heading3', name: 'Heading 3', group: 'Titles and Headings', s: { b: true, fs: 12, fg: 'accent1' } },
{ id: 'Heading4', name: 'Heading 4', group: 'Titles and Headings', s: { b: true, fs: 12, fg: 'dk2' } },
{ id: 'Total', name: 'Total', group: 'Titles and Headings', s: { b: true, bd: 'topDouble', bdc: 'accent1' } },
// Excel's Good / Bad / Neutral are literal colours in Excel too — they mean
// "this reads as good" and must not drift when the theme changes.
{ id: 'Good', name: 'Good', group: 'Good, Bad and Neutral', s: { bg: '#C6EFCE', fg: '#006100' } },
{ id: 'Bad', name: 'Bad', group: 'Good, Bad and Neutral', s: { bg: '#FFC7CE', fg: '#9C0006' } },
{ id: 'Neutral', name: 'Neutral', group: 'Good, Bad and Neutral', s: { bg: '#FFEB9C', fg: '#9C6500' } },
{ id: 'Input', name: 'Input', group: 'Data and Model', s: { bg: '#FFCC99', fg: '#3F3F76', bd: 'all', bdc: '#7F7F7F' } },
{ id: 'Output', name: 'Output', group: 'Data and Model', s: { b: true, bg: '#F2F2F2', fg: '#3F3F3F', bd: 'all', bdc: '#3F3F3F' } },
{ id: 'Calculation', name: 'Calculation', group: 'Data and Model', s: { b: true, bg: '#F2F2F2', fg: '#FA7D00', bd: 'all', bdc: '#7F7F7F' } },
{ id: 'Currency', name: 'Currency', group: 'Number Format', s: { nf: 44 } },
{ id: 'Percent', name: 'Percent', group: 'Number Format', s: { nf: 9 } },
{ id: 'Comma', name: 'Comma', group: 'Number Format', s: { nf: 4 } },
];
function sheetNamedStyle(book, id) {
if (!id) return null;
const custom = ((book && book.cellStyles) || []).find(x => x.id === id);
return custom || SHEET_CELL_STYLES.find(x => x.id === id) || null;
}
function sheetAllCellStyles(book) { return SHEET_CELL_STYLES.concat((book && book.cellStyles) || []); }
const SHEET_EMPTY_STYLE = {};
function sheetEffectiveStyle(cell, book) {
const own = (cell && cell.s) || null;
if (!own) return SHEET_EMPTY_STYLE;
if (!own.st) return own;
const named = sheetNamedStyle(book, own.st);
if (!named) return own;
return Object.assign({}, named.s, own);
}
// ══════════════════════════════════════════════════════════════════════════
// Number formats — a real pattern formatter
// ══════════════════════════════════════════════════════════════════════════
// The old model was a handful of MODE NAMES ('auto' / 'currency' / 'percent' /
// 'date' / 'text') and a decimal count, and it could only ever do the five
// things it had names for. Excel stores an id and a PATTERN, and the pattern is
// the whole point — it is what lets a cell read "1,240 pcs" or a negative be
// red and bracketed without a new mode being invented for it.
//
// Every one of those old mode strings is still sitting in workbooks saved
// before this change, so `sheetLegacyPattern` maps each to the pattern that
// renders IDENTICALLY to what the old code produced. Nothing stored is
// rewritten: the mapping happens on read, so a workbook cannot be damaged by a
// migration that goes wrong halfway.
const SHEET_NF_BY_ID = {};
SHEET_NUMBER_FORMATS.forEach(f => { SHEET_NF_BY_ID[f.id] = f; });
// A few patterns Excel offers that the built-in id list does not carry, kept
// separate so nothing here redeclares SHEET_NUMBER_FORMATS.
const SHEET_NF_PRESETS = [
{ name: 'Currency', pattern: '$#,##0.00' },
{ name: 'Currency, red negatives', pattern: '$#,##0.00;[Red]-$#,##0.00' },
{ name: 'Accounting, red brackets', pattern: '_("$"* #,##0.00_);[Red]_("$"* \\(#,##0.00\\)_);_("$"* "-"??_);_(@_)' },
{ name: 'Thousands (in 000s)', pattern: '#,##0,"k"' },
{ name: 'Date — 2026-09-05', pattern: 'yyyy-mm-dd' },
{ name: 'Date — 5 September 2026', pattern: 'd mmmm yyyy' },
{ name: 'Date — Sat 05 Sep', pattern: 'ddd dd mmm' },
{ name: 'Date & time — 09/05/26 14:30', pattern: 'mm/dd/yy hh:mm' },
{ name: 'Time — 2:30 PM', pattern: 'h:mm AM/PM' },
{ name: 'Duration — 36:15', pattern: '[h]:mm' },
];
// The old renderer's exact output, expressed as a pattern.
function sheetLegacyPattern(fmt, dp) {
const d = dp === undefined || dp === null ? (fmt === 'currency' ? 2 : fmt === 'percent' ? 0 : 2) : Math.max(0, Math.min(10, dp));
const dec = d > 0 ? '.' + '0'.repeat(d) : '';
if (fmt === 'currency') return '$#,##0' + dec;
if (fmt === 'percent') return '#,##0' + dec + '%';
if (fmt === 'number') return '#,##0' + dec;
if (fmt === 'date') return 'yyyy-mm-dd';
if (fmt === 'text') return '@';
return 'General';
}
// The pattern a cell is actually formatted with. `nfp` (a literal pattern) wins
// over `nf` (a built-in id), which wins over the legacy mode.
function sheetStylePattern(s) {
if (!s) return 'General';
if (s.nfp) return String(s.nfp);
if (s.nf !== undefined && s.nf !== null && SHEET_NF_BY_ID[s.nf]) return SHEET_NF_BY_ID[s.nf].pattern;
if (s.fmt) return sheetLegacyPattern(s.fmt, s.dp);
return 'General';
}
function sheetCellPattern(cell, book) { return sheetStylePattern(sheetEffectiveStyle(cell, book)); }
function sheetIsTextPattern(p) { return String(p == null ? '' : p).trim() === '@'; }
// The label the picker shows for whatever a cell currently carries.
function sheetPatternLabel(pattern) {
const p = String(pattern || 'General');
const built = SHEET_NUMBER_FORMATS.find(f => f.pattern === p);
if (built) return built.name;
const preset = SHEET_NF_PRESETS.find(f => f.pattern === p);
if (preset) return preset.name;
return 'Custom';
}
const SHEET_NF_COLORS = {
black: '#000000', blue: '#0000FF', cyan: '#00FFFF', green: '#008000',
magenta: '#FF00FF', red: '#C00000', white: '#FFFFFF', yellow: '#BF8F00',
};
// Sections are separated by ';' — positive; negative; zero; text — and a ';'
// inside a quoted literal or behind a backslash is not a separator.
function sheetSplitSections(pattern) {
const out = [];
const s = String(pattern == null ? '' : pattern);
let cur = '', q = false;
for (let i = 0; i < s.length; i++) {
const ch = s[i];
if (ch === '\\') { cur += ch + (s[i + 1] || ''); i++; continue; }
if (ch === '"') { q = !q; cur += ch; continue; }
if (ch === ';' && !q) { out.push(cur); cur = ''; continue; }
cur += ch;
}
out.push(cur);
return out;
}
// Which section a value uses, and whether the sign is the section's job. Excel
// formats the NEGATIVE section from the absolute value — that is what makes
// "0.00;(0.00)" print (5.00) rather than (-5.00).
function sheetPickSection(secs, value) {
if (typeof value !== 'number') return { sec: secs.length >= 4 ? secs[3] : null, abs: false, autoSign: false };
if (secs.length <= 1) return { sec: secs[0], abs: false, autoSign: true };
if (secs.length === 2) return value < 0 ? { sec: secs[1], abs: true, autoSign: false } : { sec: secs[0], abs: false, autoSign: false };
if (value > 0) return { sec: secs[0], abs: false, autoSign: false };
if (value < 0) return { sec: secs[1], abs: true, autoSign: false };
return { sec: secs[2], abs: false, autoSign: false };
}
// One pass over a section, producing atoms. Everything downstream reads atoms,
// which is why one scanner serves both the number and the date renderer.
function sheetScanSection(sec) {
const atoms = [];
let color = null;
const s = String(sec == null ? '' : sec);
for (let i = 0; i < s.length; i++) {
const ch = s[i];
if (ch === '\\') { atoms.push({ k: 'lit', s: s[i + 1] || '' }); i++; continue; }
if (ch === '"') {
let j = i + 1, lit = '';
while (j < s.length && s[j] !== '"') { lit += s[j]; j++; }
atoms.push({ k: 'lit', s: lit }); i = j; continue;
}
if (ch === '[') {
let j = i + 1, inner = '';
while (j < s.length && s[j] !== ']') { inner += s[j]; j++; }
const named = SHEET_NF_COLORS[inner.trim().toLowerCase()];
if (named) color = named;
else if (/^h+$|^m+$|^s+$/i.test(inner.trim())) atoms.push({ k: 'elapsed', s: inner.trim().toLowerCase() });
// [$-409] locale ids and [>100] conditions are read and ignored — see
// the limitations note in the format picker.
i = j; continue;
}
// '_' reserves the width of the next character; a browser cell cannot
// measure that, so it becomes one space, which is what it looks like.
if (ch === '_') { atoms.push({ k: 'lit', s: ' ' }); i++; continue; }
if (ch === '*') { atoms.push({ k: 'fill', s: s[i + 1] || ' ' }); i++; continue; }
if (ch === '0' || ch === '#' || ch === '?') { atoms.push({ k: 'd', s: ch }); continue; }
if (ch === '.') { atoms.push({ k: 'dot' }); continue; }
if (ch === ',') { atoms.push({ k: 'comma' }); continue; }
if (ch === '%') { atoms.push({ k: 'pct' }); continue; }
if (ch === '/') { atoms.push({ k: 'slash' }); continue; }
if (ch === '@') { atoms.push({ k: 'at' }); continue; }
if ((ch === 'E' || ch === 'e') && (s[i + 1] === '+' || s[i + 1] === '-')) { atoms.push({ k: 'exp', s: s[i + 1] }); i++; continue; }
if (/[ydmhsaApP]/.test(ch)) {
const ap = /^(AM\/PM|A\/P)/i.exec(s.slice(i));
if (ap) { atoms.push({ k: 'ampm', s: ap[0].toUpperCase() }); i += ap[0].length - 1; continue; }
let j = i, run = '';
while (j < s.length && s[j].toLowerCase() === ch.toLowerCase()) { run += s[j]; j++; }
atoms.push({ k: 't', s: run[0].toLowerCase(), n: run.length }); i = j - 1; continue;
}
atoms.push({ k: 'lit', s: ch });
}
return { atoms, color };
}
// A section is a date format when it holds date tokens and NO digit
// placeholders. That single rule settles 'm/d/yy' (a date) against '#,##0'
// (a number) and '0.00E+00' (scientific) without a list of special cases.
function sheetSectionIsDate(scan) {
const hasT = scan.atoms.some(a => a.k === 't' || a.k === 'ampm' || a.k === 'elapsed');
const hasNum = scan.atoms.some(a => a.k === 'd' || a.k === 'exp');
return hasT && !hasNum;
}
// Rounds half away from zero on the decimal string, which is what a
// spreadsheet does and what toFixed does not reliably do near a binary
// midpoint (1.005 → "1.00" via toFixed, "1.01" here).
function sheetFixed(n, dp) {
const d = Math.max(0, Math.min(20, dp | 0));
const f = Math.pow(10, d);
const r = Math.round(Math.abs(n) * f + 1e-9) / f;
return r.toFixed(d);
}
function sheetGroup3(intStr) {
return intStr.replace(/\B(?=(\d{3})+(?!\d))/g, ',');
}
// Plain grouped number, for the chart axis labels — those are not cell values
// and have no cell format to obey.
function sheetFmtNumber(n, dp) {
const d = dp === undefined || dp === null ? 2 : dp;
return Number(n).toLocaleString('en-US', { minimumFractionDigits: d, maximumFractionDigits: d });
}
// Best rational approximation with a bounded denominator — a continued
// fraction, which is how Excel picks 3/8 rather than 375/1000.
function sheetBestFraction(x, maxDen) {
let bestN = 0, bestD = 1, bestErr = Infinity;
let h1 = 1, h0 = 0, k1 = 0, k0 = 1, v = x;
for (let i = 0; i < 24; i++) {
const a = Math.floor(v);
const h2 = a * h1 + h0, k2 = a * k1 + k0;
if (k2 > maxDen || !isFinite(k2)) break;
h0 = h1; h1 = h2; k0 = k1; k1 = k2;
const err = Math.abs(x - h1 / k1);
if (err < bestErr) { bestErr = err; bestN = h1; bestD = k1; }
if (err < 1e-12) break;
const frac = v - a;
if (frac < 1e-12) break;
v = 1 / frac;
}
if (!bestD) { bestD = 1; bestN = Math.round(x); }
return { n: bestN, d: bestD };
}
// Renders one NUMBER section. Returns the pieces rather than a string, because
// an Accounting format aligns its currency sign left and its figure right and
// the grid needs both halves to do that.
function sheetRenderNumberSection(value, scan, opt) {
const atoms = scan.atoms;
const o = opt || {};
let n = Number(value);
const pctCount = atoms.filter(a => a.k === 'pct').length;
if (pctCount) n *= Math.pow(100, pctCount);
// Sort the digit placeholders into integer / decimal / exponent, and decide
// what each comma means: one with a placeholder still to come on its right is
// a thousands separator; one trailing the last integer placeholder divides by
// a thousand, which is how "#,##0," means "in thousands".
const intAtoms = [], decAtoms = [], expAtoms = [];
let phase = 'int', grouping = false, scale = 0, expSign = '+', sawExp = false;
const slashAt = atoms.findIndex(a => a.k === 'slash');
atoms.forEach((a, i) => {
if (a.k === 'dot') { if (phase === 'int') phase = 'dec'; return; }
if (a.k === 'exp') { phase = 'exp'; sawExp = true; expSign = a.s; return; }
if (a.k === 'd') { (phase === 'int' ? intAtoms : phase === 'dec' ? decAtoms : expAtoms).push({ a, i }); return; }
if (a.k === 'comma' && phase === 'int') {
const rest = atoms.slice(i + 1);
const nextD = rest.findIndex(x => x.k === 'd');
const nextDot = rest.findIndex(x => x.k === 'dot');
if (nextD >= 0 && (nextDot < 0 || nextD < nextDot)) grouping = true;
else scale++;
}
});
if (scale) n = n / Math.pow(1000, scale);
const negative = n < 0;
const abs = Math.abs(n);
let intStr = '', decStr = '', expStr = '', fracStr = '', fracHasWhole = false;
if (slashAt >= 0 && intAtoms.length + decAtoms.length > 0) {
// Fraction: digits after the slash are the denominator. Literal digits
// there ('# ?/8') fix it; placeholders bound it by their count.
const after = atoms.slice(slashAt + 1);
const denPlaces = after.filter(a => a.k === 'd').length;
const denLiteral = after.filter(a => a.k === 'lit').map(a => a.s).join('').replace(/[^0-9]/g, '');
const beforeAtoms = atoms.slice(0, slashAt).filter(a => a.k === 'd');
// A literal between two digit runs before the slash separates a whole
// number from the numerator: "# ?/?" shows 1 1/2, "?/?" shows 3/2.
const hasWhole = /d.*lit.*d/.test(atoms.slice(0, slashAt).map(a => a.k).join('.')) && beforeAtoms.length > 1;
fracHasWhole = hasWhole;
const whole = hasWhole ? Math.floor(abs) : 0;
const rest = abs - whole;
const maxDen = denLiteral ? Number(denLiteral) : Math.pow(10, Math.max(1, denPlaces)) - 1;
let fr = denLiteral
? { n: Math.round(rest * Number(denLiteral)), d: Number(denLiteral) }
: sheetBestFraction(rest, maxDen);
if (fr.d === 0) fr = { n: 0, d: 1 };
// A '#' whole-number placeholder on a value under 1 shows nothing, which is
// what makes "# ?/?" print " 3/8" rather than "0 3/8".
const wholeHasZero = beforeAtoms.length > 0 && beforeAtoms[0].s === '0';
intStr = hasWhole && (whole !== 0 || wholeHasZero) ? String(whole) : '';
fracStr = fr.n === 0 && hasWhole ? '' : fr.n + '/' + fr.d;
if (grouping && intStr) intStr = sheetGroup3(intStr);
} else if (sawExp) {
const decCount = decAtoms.length;
const expDigits = Math.max(1, expAtoms.length || 2);
let e = abs === 0 ? 0 : Math.floor(Math.log10(abs));
let mant = abs === 0 ? 0 : abs / Math.pow(10, e);
let mStr = sheetFixed(mant, decCount);
if (Number(mStr) >= 10) { e += 1; mStr = sheetFixed(abs / Math.pow(10, e), decCount); }
const mp = mStr.split('.');
intStr = mp[0]; decStr = mp[1] || '';
const sign = e < 0 ? '-' : (expSign === '+' ? '+' : '');
expStr = sign + String(Math.abs(e)).padStart(expDigits, '0');
} else {
const decCount = decAtoms.length;
const fixed = sheetFixed(abs, decCount);
const parts = fixed.split('.');
intStr = parts[0];
decStr = parts[1] || '';
// Trailing '#' decimals are optional and disappear; trailing '?' decimals
// hold their column with a space, which is what lines figures up.
for (let i = decAtoms.length - 1; i >= 0 && decStr.length; i--) {
const ph = decAtoms[i].a.s;
if (ph === '0') break;
if (decStr[i] !== '0') break;
decStr = decStr.slice(0, i) + (ph === '?' ? ' ' : '');
}
const minInt = intAtoms.filter(x => x.a.s === '0').length;
if (intStr.length < minInt) intStr = intStr.padStart(minInt, '0');
// "#.00" on 0.5 reads ".50" — with no '0' placeholder, the leading zero is
// suppressed. With no decimals at all it stays, or the cell shows nothing.
if (intStr === '0' && minInt === 0 && (decStr.length || fracStr)) intStr = '';
if (grouping && intStr) intStr = sheetGroup3(intStr);
}
// Emit in pattern order. The first digit placeholder of a run carries the
// whole rendered number; the rest carry nothing, so literals keep the places
// the pattern gave them.
let left = null, out = '';
let intDone = false, decDone = false, expDone = false, fracDone = false, sawDot = false;
const emit = t => { out += t; };
atoms.forEach((a, ai) => {
// A fixed denominator ("# ?/8") is literal digits that fracStr has already
// printed; re-emitting them gives 2/88.
if (slashAt >= 0 && ai > slashAt && a.k === 'lit' && /^[0-9]+$/.test(a.s)) return;
if (a.k === 'lit') { emit(a.s); return; }
if (a.k === 'fill') { if (left === null) { left = out; out = ''; } return; }
if (a.k === 'pct') { emit('%'); return; }
if (a.k === 'comma') return;
if (a.k === 'dot') { sawDot = true; if (decStr.length) emit('.'); return; }
// The slash itself is already inside fracStr ("3/8"), so it emits nothing.
if (a.k === 'slash') return;
if (a.k === 'exp') { if (!expDone) { emit('E' + expStr); expDone = true; } return; }
if (a.k === 'd') {
if (slashAt >= 0) {
// whole | numerator/denominator. When the pattern HAS a whole-number
// placeholder, the first run belongs to it even if it prints nothing —
// otherwise "# ?/?" on 0.375 puts the fraction in the whole's place.
if (fracHasWhole && !intDone) { emit(intStr); intDone = true; return; }
if (!fracDone) { emit(fracStr); fracDone = true; return; }
return;
}
if (!sawDot) { if (!intDone) { emit(intStr); intDone = true; } return; }
if (!decDone) { emit(decStr); decDone = true; }
return;
}
});
// An automatic minus goes in FRONT of the whole rendered figure, which is why
// "$#,##0.00" on -5 reads -$5.00 and not $-5.00. A pattern that supplies its
// own negative section is not touched — it already said what it wanted.
if (o.autoSign && negative) { if (left !== null) left = '-' + left; else out = '-' + out; }
const text = left === null ? out : (left + ' ' + out);
return left === null
? { text, color: scan.color || null }
: { text, left, right: out, color: scan.color || null };
}
// Renders one DATE section. The serial's integer part is the day and its
// fraction is the time, which is exactly how the value was stored.
function sheetRenderDateSection(value, scan) {
const serial = Number(value);
let days = Math.floor(serial);
let sec = Math.round((serial - days) * 86400);
if (sec >= 86400) { sec -= 86400; days += 1; }
const d = sheetDateFromSerial(days);
const Y = d.getUTCFullYear(), Mo = d.getUTCMonth() + 1, Da = d.getUTCDate(), Wd = d.getUTCDay();
const h24 = Math.floor(sec / 3600), mi = Math.floor(sec / 60) % 60, ss = sec % 60;
const atoms = scan.atoms;
const has12 = atoms.some(a => a.k === 'ampm');
const h12 = h24 % 12 === 0 ? 12 : h24 % 12;
const p2 = x => String(x).padStart(2, '0');
// 'm' is minutes when it sits beside an hour or a second — the one genuinely
// ambiguous token in the whole format language.
const tIdx = [];
atoms.forEach((a, i) => { if (a.k === 't' || a.k === 'elapsed') tIdx.push(i); });
const isMinute = i => {
const pos = tIdx.indexOf(i);
const prev = pos > 0 ? atoms[tIdx[pos - 1]] : null;
const next = pos >= 0 && pos < tIdx.length - 1 ? atoms[tIdx[pos + 1]] : null;
return (prev && prev.s === 'h') || (next && next.s === 's');
};
let left = null, out = '';
const emit = t => { out += t; };
atoms.forEach((a, i) => {
if (a.k === 'lit') { emit(a.s); return; }
if (a.k === 'fill') { if (left === null) { left = out; out = ''; } return; }
if (a.k === 'ampm') { emit(a.s === 'A/P' ? (h24 < 12 ? 'A' : 'P') : (h24 < 12 ? 'AM' : 'PM')); return; }
if (a.k === 'elapsed') {
const total = days * 24 + h24 + mi / 60 + ss / 3600;
if (a.s[0] === 'h') emit(String(Math.floor(total)));
else if (a.s[0] === 'm') emit(String(Math.floor(total * 60)));
else emit(String(Math.floor(total * 3600)));
return;
}
if (a.k !== 't') { if (a.k === 'dot') emit('.'); else if (a.k === 'comma') emit(','); else if (a.k === 'slash') emit('/'); return; }
if (a.s === 'y') { emit(a.n <= 2 ? p2(Y % 100) : String(Y)); return; }
if (a.s === 'd') {
if (a.n === 1) emit(String(Da));
else if (a.n === 2) emit(p2(Da));
else if (a.n === 3) emit(SHEET_DAYS_SHORT[(Wd + 6) % 7]);
else emit(SHEET_DAYS[(Wd + 6) % 7]);
return;
}
if (a.s === 'h') { const hv = has12 ? h12 : h24; emit(a.n >= 2 ? p2(hv) : String(hv)); return; }
if (a.s === 's') { emit(a.n >= 2 ? p2(ss) : String(ss)); return; }
if (a.s === 'm') {
if (isMinute(i)) { emit(a.n >= 2 ? p2(mi) : String(mi)); return; }
if (a.n === 1) emit(String(Mo));
else if (a.n === 2) emit(p2(Mo));
else if (a.n === 3) emit(SHEET_MONTHS_SHORT[Mo - 1]);
else emit(SHEET_MONTHS[Mo - 1]);
return;
}
emit('');
});
const text = left === null ? out : (left + ' ' + out);
return left === null ? { text, color: scan.color || null } : { text, left, right: out, color: scan.color || null };
}
// Automatic: keep whatever precision the value actually has, up to 10 places.
// Unchanged from the original renderer, deliberately — General is the format
// almost every cell in every existing workbook is in.
function sheetGeneralText(value) {
if (typeof value !== 'number') return String(value);
const rounded = Math.round(value * 1e10) / 1e10;
return String(rounded);
}
// The one entry point. Returns { text, left?, right?, color? } — left/right are
// only present when the pattern used a '*' fill, which is how Accounting pins
// the currency sign to one edge and the figure to the other.
function sheetFormatValue(value, pattern) {
const pat = String(pattern == null ? 'General' : pattern);
if (typeof value === 'boolean') return { text: value ? 'TRUE' : 'FALSE' };
if (!pat || /^general$/i.test(pat.trim())) return { text: sheetGeneralText(value) };
const secs = sheetSplitSections(pat);
const pick = sheetPickSection(secs, value);
if (typeof value !== 'number') {
// A text value in a numeric format prints as typed. The fourth section is
// the text section; a one-section format built around '@' (which is what
// Excel's built-in Text format is) applies to text too.
let sec = pick.sec;
if (!sec && secs.length && secs[0].indexOf('@') >= 0) sec = secs[0];
if (!sec) return { text: String(value) };
const scan = sheetScanSection(sec);
if (!scan.atoms.some(a => a.k === 'at')) return { text: String(value), color: scan.color || null };
let out = '';
scan.atoms.forEach(a => {
if (a.k === 'at') out += String(value);
else if (a.k === 'lit') out += a.s;
});
return { text: out, color: scan.color || null };
}
if (pick.sec === undefined || pick.sec === null) return { text: sheetGeneralText(value) };
const scan = sheetScanSection(pick.sec);
if (!scan.atoms.length) return { text: '' };
if (sheetSectionIsDate(scan)) return sheetRenderDateSection(value, scan);
const n = pick.abs ? Math.abs(value) : value;
return sheetRenderNumberSection(n, scan, { autoSign: pick.autoSign });
}
// What the grid paints, in parts. A formula whose root is a date function
// reads as a date even with no format set, because a bare serial number there
// is never what was meant.
function sheetDisplayParts(cell, value, book) {
if (value === null || value === undefined || value === '') return { text: '' };
if (sheetIsErr(value)) return { text: value };
let pattern = sheetCellPattern(cell, book);
if ((!pattern || /^general$/i.test(pattern)) && cell && sheetIsFormula(cell.v) && sheetFormulaIsDate(cell.v)) pattern = 'yyyy-mm-dd';
return sheetFormatValue(value, pattern);
}
// Kept as a string-returning function with its original argument order, so
// every existing call site keeps working; `book` is optional and only matters
// for a cell that references a named style.
function sheetDisplay(cell, value, book) { return sheetDisplayParts(cell, value, book).text; }
// What the editor shows when a cell is opened — the formula, or the raw entry,
// never the formatted display. Editing "$1,200.00" back into a number is how a
// spreadsheet quietly turns figures into text.
function sheetEditText(cell, book) {
if (!cell) return '';
const v = cell.v;
if (v === null || v === undefined) return '';
if (typeof v === 'number') {
const scan = sheetScanSection(sheetSplitSections(sheetCellPattern(cell, book))[0]);
if (sheetSectionIsDate(scan)) return sheetSerialToISO(v);
}
return String(v);
}
// A cell formatted Text keeps exactly what was typed: "007" stays "007" and
// "1-2" stays "1-2". This is the ONE place a number format is allowed to
// change a stored value rather than only its display — because that is what
// Text format means, and getting it wrong is a correctness bug, not a cosmetic
// one. A leading "=" is stored behind the existing text marker so the engine
// can never execute it.
function sheetTextInput(raw) {
const s = String(raw == null ? '' : raw);
if (s === '') return '';
if (s[0] === '=') return "'" + s;
return s;
}
// Writing a format onto a cell: a pattern that IS one of Excel's built-ins is
// stored as its id, so the workbook says "this is Accounting" rather than
// carrying a copy of the pattern; anything else is stored literally. Both clear
// the legacy mode keys, which is how an old workbook stops carrying them once a
// person actually changes a format.
function sheetPatternStylePatch(pattern) {
const p = String(pattern || 'General');
const built = SHEET_NUMBER_FORMATS.find(f => f.pattern === p);
if (built) return { nf: built.id, nfp: undefined, fmt: undefined, dp: undefined };
return { nfp: p, nf: undefined, fmt: undefined, dp: undefined };
}
// More / fewer decimals, on the PATTERN rather than on a decimal counter — so
// the button works on a custom format someone typed, not only on the four modes
// the old model had names for.
function sheetBumpSectionDecimals(sec, delta) {
const scan = sheetScanSection(sec);
if (!scan.atoms.some(a => a.k === 'd')) return sec;
if (sheetSectionIsDate(scan)) return sec;
// Quoted literals and escapes are masked first, so the edit can never land
// inside "0.00 per unit".
const masks = [];
let m = String(sec).replace(/\\.|"[^"]*"/g, x => { masks.push(x); return '\u0001' + (masks.length - 1) + '\u0001'; });
const dot = m.indexOf('.');
if (delta > 0) {
if (dot < 0) {
let last = -1;
for (let i = 0; i < m.length; i++) if ('0#?'.indexOf(m[i]) >= 0) last = i;
if (last < 0) return sec;
m = m.slice(0, last + 1) + '.0' + m.slice(last + 1);
} else {
let end = dot + 1;
while (end < m.length && '0#?'.indexOf(m[end]) >= 0) end++;
m = m.slice(0, end) + '0' + m.slice(end);
}
} else {
if (dot < 0) return sec;
let end = dot + 1;
while (end < m.length && '0#?'.indexOf(m[end]) >= 0) end++;
if (end === dot + 1) return sec;
m = m.slice(0, end - 1) + m.slice(end);
// Taking the last decimal off leaves a stranded point. `indexOf('')` is 0,
// not -1, so the end of the string has to be tested explicitly.
const nxt = m[dot + 1];
if (m[dot] === '.' && (nxt === undefined || '0#?'.indexOf(nxt) < 0)) m = m.slice(0, dot) + m.slice(dot + 1);
}
return m.replace(/\u0001(\d+)\u0001/g, (x, i) => masks[Number(i)]);
}
function sheetBumpPatternDecimals(pattern, delta) {
const p = String(pattern || 'General');
if (/^general$/i.test(p.trim())) return delta > 0 ? '0.0' : '0';
if (sheetIsTextPattern(p)) return p;
return sheetSplitSections(p).map(sec => sheetBumpSectionDecimals(sec, delta)).join(';');
}
// ── Formula shifting (fill handle, copy/paste) ────────────────────────────
// Rewrites only the reference tokens and re-emits everything else verbatim, so
// a dragged formula keeps the spacing and casing it was typed with. Absolute
// parts ($A$1) are left exactly where they are — that is what makes them
// absolute.
function sheetShiftFormula(formula, dr, dc) {
if (!sheetIsFormula(formula)) return formula;
const body = formula.slice(1);
const toks = sheetTokenize(body);
let out = '', last = 0;
toks.forEach(t => {
if (t.t !== 'ref') return;
out += body.slice(last, t.p);
const part = (absC, col, absR, row) => {
const nc = absC ? col : col + dc;
const nr = absR ? row : row + dr;
if (nc < 0 || nr < 0 || nc >= SHEET_MAX_COLS || nr >= SHEET_MAX_ROWS) return null;
return (absC ? '$' : '') + sheetColLabel(nc) + (absR ? '$' : '') + (nr + 1);
};
const q = n => (/[^A-Za-z0-9_.]/.test(n) ? "'" + String(n).replace(/'/g, "''") + "'" : n);
const a = part(t.absC, t.col, t.absR, t.row);
const b = t.has2 ? part(t.absC2, t.col2, t.absR2, t.row2) : null;
if (a === null || (t.has2 && b === null)) { out += SHEET_ERR_REF; }
else {
out += (t.sheet ? q(t.sheet) + '!' : '') + a;
if (t.has2) out += ':' + (t.sheet2 ? q(t.sheet2) + '!' : '') + b;
}
last = t.p + t.len;
});
out += body.slice(last);
return '=' + out;
}
// ── Fill series ───────────────────────────────────────────────────────────
// The seed is what the user selected; this returns the next `count` values.
// Order matters: a formula is a formula before it is anything else, and a run
// of numbers is a progression before it is repeated text.
const SHEET_MONTHS = ['January','February','March','April','May','June','July','August','September','October','November','December'];
const SHEET_MONTHS_SHORT = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
const SHEET_DAYS = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'];
const SHEET_DAYS_SHORT = ['Mon','Tue','Wed','Thu','Fri','Sat','Sun'];
function sheetListStep(list, seed) {
const idx = seed.map(v => list.findIndex(x => x.toLowerCase() === String(v).trim().toLowerCase()));
if (idx.some(i => i < 0)) return null;
const step = idx.length > 1 ? idx[1] - idx[0] : 1;
return { idx, step: step === 0 ? 1 : step };
}
function sheetFillSeries(seed, count, dr, dc) {
const out = [];
if (!seed.length || count <= 0) return out;
const n = seed.length;
// Formulas: shift by how far each output cell sits from the cell it copies.
if (seed.every(v => sheetIsFormula(v))) {
for (let i = 0; i < count; i++) {
const src = seed[i % n];
const steps = Math.floor(i / n) + 1;
out.push(sheetShiftFormula(src, dr * steps * n, dc * steps * n));
}
return out;
}
// Numbers: arithmetic progression from the last gap (or +1 from a lone seed).
const nums = seed.map(v => (typeof v === 'number' ? v : NaN));
if (nums.every(v => !isNaN(v))) {
const step = n > 1 ? (nums[n - 1] - nums[0]) / (n - 1) : 1;
let cur = nums[n - 1];
for (let i = 0; i < count; i++) { cur += step; out.push(Math.round(cur * 1e10) / 1e10); }
return out;
}
// Dates typed as text (2026-01-05, 01/05/2026) keep their written shape.
const serials = seed.map(v => sheetDateSerial(v));
if (serials.every(s => s !== null)) {
const step = n > 1 ? (serials[n - 1] - serials[0]) / (n - 1) : 1;
let cur = serials[n - 1];
const iso = SHEET_ISO_RE.test(String(seed[0]).trim()) || typeof seed[0] === 'number';
for (let i = 0; i < count; i++) {
cur += step;
const d = sheetDateFromSerial(cur);
out.push(iso ? sheetSerialToISO(cur) : (d.getUTCMonth() + 1) + '/' + d.getUTCDate() + '/' + d.getUTCFullYear());
}
return out;
}
// Month and weekday names, long or short.
const lists = [SHEET_MONTHS, SHEET_MONTHS_SHORT, SHEET_DAYS, SHEET_DAYS_SHORT];
for (let li = 0; li < lists.length; li++) {
const info = sheetListStep(lists[li], seed);
if (info) {
let cur = info.idx[info.idx.length - 1];
for (let i = 0; i < count; i++) { cur += info.step; out.push(lists[li][((cur % lists[li].length) + lists[li].length) % lists[li].length]); }
return out;
}
}
// Text ending in a number — "Unit 3", "L-07" — increments the number and
// keeps any leading zeros it was written with.
const parts = seed.map(v => /^(.*?)(\d+)$/.exec(String(v)));
if (parts.every(p => p) && parts.every(p => p[1] === parts[0][1])) {
const width = parts[0][2].length;
const numsT = parts.map(p => Number(p[2]));
const step = n > 1 ? (numsT[n - 1] - numsT[0]) / (n - 1) : 1;
let cur = numsT[n - 1];
for (let i = 0; i < count; i++) { cur += step; out.push(parts[0][1] + String(Math.round(cur)).padStart(width, '0')); }
return out;
}
// Nothing recognisable: repeat the selection, which is what a plain copy does.
for (let i = 0; i < count; i++) out.push(seed[i % n]);
return out;
}
// ── Used range, geometry ──────────────────────────────────────────────────
function sheetUsedRange(ws) {
let maxR = -1, maxC = -1;
Object.keys((ws && ws.cells) || {}).forEach(k => {
const p = sheetParseKey(k);
if (p.r > maxR) maxR = p.r;
if (p.c > maxC) maxC = p.c;
});
return { r1: 0, c1: 0, r2: maxR, c2: maxC, empty: maxR < 0 };
}
function sheetDims(ws) {
const used = sheetUsedRange(ws);
return {
rows: Math.min(SHEET_MAX_ROWS, Math.max(SHEET_DEFAULT_ROWS, used.r2 + 30)),
cols: Math.min(SHEET_MAX_COLS, Math.max(SHEET_DEFAULT_COLS, used.c2 + 6)),
};
}
// A column carries EITHER a dragged pixel width (`w`) or a width in Excel's
// character unit (`wch`); both are kept because a person dragging a border
// means pixels and a person typing a width means characters, and converting one
// into the other on every save would make both drift.
function sheetColW(ws, c, book) {
const meta = ws.cols && ws.cols[c];
if (meta && meta.hidden) return 0;
if (meta && meta.w) return meta.w;
if (meta && meta.wch) return sheetCharsToPx(meta.wch, book);
return book ? sheetCharsToPx(sheetBookDefaults(book).baseColWidth, book) : SHEET_COL_W;
}
function sheetRowH(ws, r, hiddenByFilter, book) {
if (hiddenByFilter && hiddenByFilter.has(r)) return 0;
const meta = ws.rows && ws.rows[r];
if (meta && meta.hidden) return 0;
if (meta && meta.h) return meta.h;
if (meta && meta.hpt) return Math.max(14, sheetPtToPx(meta.hpt));
return book ? Math.max(18, sheetPtToPx(sheetBookDefaults(book).rowHeight)) : SHEET_ROW_H;
}
// Prefix sums, so a scroll position maps to a row index by binary search
// instead of a walk. Rebuilt only when the row/col metadata changes.
function sheetOffsets(count, sizeOf) {
const arr = new Float64Array(count + 1);
for (let i = 0; i < count; i++) arr[i + 1] = arr[i] + sizeOf(i);
return arr;
}
function sheetIndexAt(offsets, pos) {
let lo = 0, hi = offsets.length - 1;
while (lo < hi) { const mid = (lo + hi + 1) >> 1; if (offsets[mid] <= pos) lo = mid; else hi = mid - 1; }
return lo;
}
function sheetNormRange(sel) {
return {
r1: Math.min(sel.r, sel.r2), c1: Math.min(sel.c, sel.c2),
r2: Math.max(sel.r, sel.r2), c2: Math.max(sel.c, sel.c2),
};
}
function sheetMergeAt(ws, r, c) {
return (ws.merges || []).find(m => r >= m.r1 && r <= m.r2 && c >= m.c1 && c <= m.c2) || null;
}
// ── Print setup ───────────────────────────────────────────────────────────
// Page setup belongs to the SHEET, as it does in Excel: one workbook routinely
// holds a portrait cover sheet and a landscape schedule. A schedule that cannot
// be printed sensibly is not usable in a shop, and the repeating header row is
// the single thing that decides whether page four of a cut list is readable.
const SHEET_PAPER_SIZES = [
{ key: 'Letter', label: 'Letter — 8.5 × 11 in', css: 'Letter' },
{ key: 'Legal', label: 'Legal — 8.5 × 14 in', css: 'Legal' },
{ key: 'Tabloid', label: 'Tabloid — 11 × 17 in', css: '11in 17in' },
{ key: 'A4', label: 'A4 — 210 × 297 mm', css: 'A4' },
{ key: 'A3', label: 'A3 — 297 × 420 mm', css: 'A3' },
];
const SHEET_PAGE_DEFAULT = {
paper: 'Letter', orientation: 'portrait',
margins: { top: 12.7, right: 12.7, bottom: 12.7, left: 12.7 }, // mm
fitToWidth: true, scale: 100,
repeatRows: null, // { from, to } — zero-based, inclusive
printArea: null, // { r1, c1, r2, c2 } — null means the used range
gridlines: true, headings: false, centerH: false,
headerText: '', footerText: '',
};
function sheetPage(ws) {
const p = (ws && ws.page) || {};
return Object.assign({}, SHEET_PAGE_DEFAULT, p, { margins: Object.assign({}, SHEET_PAGE_DEFAULT.margins, p.margins || {}) });
}
// A cap, because the print region is real DOM: a print of a 50,000-row sheet
// would build a million cells before the dialog ever opened.
const SHEET_PRINT_CELL_CAP = 12000;
// &-codes are Excel's. Page numbers are deliberately NOT among the ones
// substituted: a browser cannot count its own printed pages, and the print
// dialog's own header/footer already offers them.
function sheetHeaderText(text, o) {
// "&&" is a literal ampersand and must be taken out of the way FIRST — doing
// it last turns "R&&D" into "R&" plus today's date.
return String(text || '').split('&&').map(seg => seg
.replace(/&F/gi, (o && o.docName) || '')
.replace(/&A/gi, (o && o.sheetName) || '')
.replace(/&D/gi, fmtDate(todayISO()))).join('&');
}
// ── Writing cells ─────────────────────────────────────────────────────────
// Every write goes through here and returns an undo patch alongside the new
// body. Undo therefore stores only the cells that actually changed, not a copy
// of the workbook — which is the difference between a 60-step history and a
// blown localStorage quota.
function sheetWriteCells(body, wsId, writes) {
const idx = (body.sheets || []).findIndex(s => s.id === wsId);
if (idx < 0) return { body, undo: null, touched: [] };
const ws = body.sheets[idx];
const cells = Object.assign({}, ws.cells);
const before = {};
const touched = [];
writes.forEach(w => {
const key = sheetKey(w.r, w.c);
if (!(key in before)) before[key] = key in cells ? cells[key] : null;
if (w.clear) { delete cells[key]; touched.push({ r: w.r, c: w.c }); return; }
const prev = cells[key] || {};
const next = Object.assign({}, prev);
if ('v' in w) next.v = w.v;
if ('s' in w) {
if (w.s === null) delete next.s;
else {
next.s = Object.assign({}, prev.s || {}, w.s);
// A key set to undefined is how "clear this" is expressed by every
// caller; DELETING it is what makes the style genuinely empty again, so
// the cell below can be dropped instead of persisting as a husk.
Object.keys(next.s).forEach(k => { if (next.s[k] === undefined) delete next.s[k]; });
}
}
// A cell with neither content nor style is not a cell. Keeping it would
// grow the file with nothing in it.
const styleEmpty = !next.s || Object.keys(next.s).length === 0;
if ((next.v === '' || next.v === undefined || next.v === null) && styleEmpty) delete cells[key];
else cells[key] = next;
touched.push({ r: w.r, c: w.c });
});
const sheets = body.sheets.slice();
sheets[idx] = Object.assign({}, ws, { cells });
return { body: Object.assign({}, body, { sheets }), undo: { wsId, cells: before }, touched };
}
// The inverse: put the recorded cells back exactly as they were.
function sheetRestoreCells(body, patch) {
const idx = (body.sheets || []).findIndex(s => s.id === patch.wsId);
if (idx < 0) return { body, undo: patch, touched: [] };
const ws = body.sheets[idx];
const cells = Object.assign({}, ws.cells);
const before = {};
const touched = [];
Object.keys(patch.cells).forEach(key => {
before[key] = key in cells ? cells[key] : null;
const p = sheetParseKey(key);
touched.push({ r: p.r, c: p.c });
if (patch.cells[key] === null) delete cells[key];
else cells[key] = patch.cells[key];
});
const sheets = body.sheets.slice();
sheets[idx] = Object.assign({}, ws, { cells });
return { body: Object.assign({}, body, { sheets }), undo: { wsId: patch.wsId, cells: before }, touched };
}
// Sheet-level metadata (name, cols, rows, merges, frozen, filter, rules).
// Kept separate from cell writes because these are not undoable cell edits and
// mixing them would make the undo stack lie about what it restores.
function sheetPatchWs(body, wsId, fields) {
const sheets = (body.sheets || []).map(s => (s.id === wsId ? Object.assign({}, s, fields) : s));
return Object.assign({}, body, { sheets });
}
// ── Data validation ───────────────────────────────────────────────────────
const SHEET_VALIDATION_KINDS = [
{ key: 'list', label: 'Dropdown from a list' },
{ key: 'number', label: 'Number in a range' },
{ key: 'required', label: 'Must not be empty' },
];
function sheetValidationAt(ws, r, c) {
return (ws.validations || []).find(v => r >= v.r1 && r <= v.r2 && c >= v.c1 && c <= v.c2) || null;
}
// Returns a complaint, or null when the value is fine. Entry is FLAGGED, not
// blocked — a rule someone added last month should not stop today's work, and
// a red corner that can be seen and fixed is more useful than a refusal.
function sheetValidate(rule, value) {
if (!rule) return null;
const blank = sheetIsBlank(value);
if (rule.kind === 'required') return blank ? 'This cell is required.' : null;
if (blank) return null;
if (rule.kind === 'list') {
const opts = (rule.options || []).map(o => String(o).toLowerCase());
return opts.indexOf(String(value).toLowerCase()) >= 0 ? null : 'Not one of the allowed values.';
}
if (rule.kind === 'number') {
const n = typeof value === 'number' ? value : Number(String(value).replace(/[$,\s%]/g, ''));
if (isNaN(n)) return 'Must be a number.';
if (rule.min !== null && rule.min !== undefined && rule.min !== '' && n < Number(rule.min)) return 'Below the minimum (' + rule.min + ').';
if (rule.max !== null && rule.max !== undefined && rule.max !== '' && n > Number(rule.max)) return 'Above the maximum (' + rule.max + ').';
}
return null;
}
// ── Conditional formatting ────────────────────────────────────────────────
const SHEET_COND_KINDS = [
{ key: 'lt', label: 'Less than' }, { key: 'lte', label: 'Less than or equal' },
{ key: 'gt', label: 'Greater than' }, { key: 'gte', label: 'Greater than or equal' },
{ key: 'eq', label: 'Equal to' }, { key: 'ne', label: 'Not equal to' },
{ key: 'between', label: 'Between' }, { key: 'contains', label: 'Text contains' },
{ key: 'empty', label: 'Is empty' }, { key: 'notEmpty', label: 'Is not empty' },
];
function sheetCondHit(rule, value) {
const k = rule.kind;
if (k === 'empty') return sheetIsBlank(value);
if (k === 'notEmpty') return !sheetIsBlank(value);
if (k === 'contains') return String(value == null ? '' : value).toLowerCase().indexOf(String(rule.v1 || '').toLowerCase()) >= 0;
if (sheetIsBlank(value) || sheetIsErr(value)) return false;
const n = typeof value === 'number' ? value : Number(String(value).replace(/[$,\s%]/g, ''));
const a = Number(rule.v1), b = Number(rule.v2);
if (isNaN(n)) {
if (k === 'eq') return String(value).toLowerCase() === String(rule.v1 || '').toLowerCase();
if (k === 'ne') return String(value).toLowerCase() !== String(rule.v1 || '').toLowerCase();
return false;
}
if (k === 'lt') return n < a;
if (k === 'lte') return n <= a;
if (k === 'gt') return n > a;
if (k === 'gte') return n >= a;
if (k === 'eq') return n === a;
if (k === 'ne') return n !== a;
if (k === 'between') return n >= Math.min(a, b) && n <= Math.max(a, b);
return false;
}
// Later rules win, so the list reads top-to-bottom like every other rule list.
function sheetCondStyle(ws, r, c, value) {
let out = null;
(ws.condFormats || []).forEach(rule => {
if (r < rule.r1 || r > rule.r2 || c < rule.c1 || c > rule.c2) return;
if (!sheetCondHit(rule, value)) return;
out = Object.assign({}, out || {}, { bg: rule.bg || undefined, fg: rule.fg || undefined, b: rule.bold || undefined });
});
return out;
}
// ── Filter ────────────────────────────────────────────────────────────────
// Filtered-out rows are hidden by giving them zero height in the same prefix
// sums that hide a manually hidden row — one mechanism, so the two can never
// disagree about where row 40 is drawn.
function sheetHiddenByFilter(ws, valueAt) {
const f = ws.filter;
const out = new Set();
if (!f || !f.criteria) return out;
const cols = Object.keys(f.criteria);
if (!cols.length) return out;
const used = sheetUsedRange(ws);
if (used.empty) return out;
const head = f.headerRow === undefined || f.headerRow === null ? 0 : f.headerRow;
for (let r = head + 1; r <= used.r2; r++) {
let keep = true;
for (let i = 0; i < cols.length && keep; i++) {
const c = Number(cols[i]);
const crit = f.criteria[cols[i]];
const v = valueAt(r, c);
const text = String(v == null ? '' : v);
if (crit.kind === 'contains') keep = text.toLowerCase().indexOf(String(crit.text || '').toLowerCase()) >= 0;
else if (crit.kind === 'values') keep = (crit.values || []).indexOf(text) >= 0;
else if (crit.kind === 'expr') keep = sheetCriterion(crit.text)(v);
}
if (!keep) out.add(r);
}
return out;
}
// ── Sort ──────────────────────────────────────────────────────────────────
// Sorting rewrites cells rather than reordering a view, because a spreadsheet's
// row order IS the data. Formulas move with their rows unshifted — the same
// caveat every spreadsheet carries, and the modal says so.
function sheetSortRange(body, wsId, range, byCol, dir, hasHeader, valueAt) {
const ws = sheetWsById(body, wsId);
if (!ws) return body;
const first = range.r1 + (hasHeader ? 1 : 0);
const rows = [];
for (let r = first; r <= range.r2; r++) {
const cells = [];
for (let c = range.c1; c <= range.c2; c++) cells.push(sheetCellAt(ws, r, c) || null);
rows.push({ r, cells, key: valueAt(r, byCol) });
}
const sign = dir === 'desc' ? -1 : 1;
rows.sort((a, b) => {
const ab = sheetIsBlank(a.key), bb = sheetIsBlank(b.key);
if (ab && bb) return 0;
if (ab) return 1; // blanks always sink, in both directions
if (bb) return -1;
return sign * sheetCompare(a.key, b.key);
});
const writes = [];
rows.forEach((row, i) => {
const target = first + i;
for (let c = range.c1; c <= range.c2; c++) {
const cell = row.cells[c - range.c1];
if (cell) writes.push({ r: target, c, v: cell.v, s: cell.s || null });
else writes.push({ r: target, c, clear: true });
}
});
return sheetWriteCells(body, wsId, writes).body;
}
// ── Connected LEON tables ─────────────────────────────────────────────────
// The reason Sheets exists. A table here keeps its SOURCE, so Refresh re-reads
// the live records rather than the user re-exporting and re-pasting. Nothing is
// copied that already has a home: every row is read out of ctx at refresh time
// and only the flattened text lands in cells.
//
// Money columns are dropped when the viewer cannot see cost (contract rule 4) —
// dropped at build time, so a sheet made by someone with financial access does
// not leak figures when someone else refreshes it.
function sheetSourceProjects(ctx) { return ctx.deptProjects(ctx.projects || []); }
function sheetProjectOf(ctx, projectId) { return (ctx.projects || []).find(p => p.id === projectId) || null; }
function sheetScopeName(project, scopeId) {
const s = project && (project.scopes || []).find(x => x.id === scopeId);
return s ? s.name : '';
}
function sheetAccountName(ctx, accountId) {
const a = (ctx.accounts || []).find(x => x.id === accountId);
return a ? a.name : '';
}
function sheetVendorName(ctx, vendorId) {
const v = (ctx.vendors || []).find(x => x.id === vendorId);
return v ? v.name : '';
}
function sheetNum0(v) { const n = Number(v); return isNaN(n) ? 0 : n; }
const SHEET_SOURCES = [
{
key: 'projects', label: 'Project list', icon: '🏗️', level: 'global',
blurb: 'Every project in your department, with its account, status and dates.',
columns: ctx => [
{ key: 'name', label: 'Project' },
{ key: 'number', label: 'Job No.' },
{ key: 'account', label: 'Account' },
{ key: 'department', label: 'Department' },
{ key: 'status', label: 'Status' },
{ key: 'start', label: 'Start', fmt: 'date' },
{ key: 'scopes', label: 'Scopes', fmt: 'number' },
].concat(ctx.canSeeFin ? [{ key: 'contract', label: 'Contract Value', fmt: 'currency' }] : []),
rows: ctx => sheetSourceProjects(ctx).map(p => ({
__id: p.id,
name: p.name || '', number: p.projectNumber || p.number || '',
account: sheetAccountName(ctx, p.accountId), department: p.companyDepartment || '',
status: p.status || p.pipelineStatus || '', start: p.startDate || '',
scopes: (p.scopes || []).length,
contract: sheetNum0(p.originalContractValue),
})),
},
{
key: 'doors', label: 'Door schedule', icon: '🚪', level: 'project',
blurb: 'project.doors — the same records LEON Doors draws its elevations from.',
columns: () => [
{ key: 'mark', label: 'Mark' }, { key: 'location', label: 'Location' },
{ key: 'unit', label: 'Unit' }, { key: 'qty', label: 'Qty', fmt: 'number' },
{ key: 'leafW', label: 'Leaf W (mm)', fmt: 'number' }, { key: 'leafH', label: 'Leaf H (mm)', fmt: 'number' },
{ key: 'handing', label: 'Handing' }, { key: 'fireRating', label: 'Fire' },
{ key: 'scope', label: 'Scope' }, { key: 'status', label: 'Status' },
],
rows: (ctx, opt) => {
const p = sheetProjectOf(ctx, opt.projectId);
if (!p) return [];
const types = p.doorTypes || [];
return (p.doors || []).map(d => {
const type = types.find(t => t.id === d.typeId) || null;
const rd = typeof resolveDoor === 'function' ? resolveDoor(d, type) : d;
return {
__id: d.id, mark: d.mark || '', location: d.location || '', unit: d.unit || '',
qty: sheetNum0(d.qty) || 1, leafW: sheetNum0(rd.leafW), leafH: sheetNum0(rd.leafH),
handing: rd.handing || '', fireRating: rd.fireRating || '',
scope: sheetScopeName(p, d.scopeId), status: d.status || '',
};
});
},
},
{
key: 'casework', label: 'Casework schedule', icon: '🗄️', level: 'project',
blurb: 'project.caseworkItems — cabinet marks, sizes and fronts as LEON Casework holds them.',
columns: () => [
{ key: 'mark', label: 'Mark' }, { key: 'room', label: 'Room' }, { key: 'unit', label: 'Unit' },
{ key: 'qty', label: 'Qty', fmt: 'number' },
{ key: 'width', label: 'W (mm)', fmt: 'number' }, { key: 'height', label: 'H (mm)', fmt: 'number' },
{ key: 'depth', label: 'D (mm)', fmt: 'number' },
{ key: 'doorStyle', label: 'Front' }, { key: 'scope', label: 'Scope' }, { key: 'status', label: 'Status' },
],
rows: (ctx, opt) => {
const p = sheetProjectOf(ctx, opt.projectId);
if (!p) return [];
return (p.caseworkItems || []).map(c => ({
__id: c.id, mark: c.mark || '', room: c.room || '', unit: c.unit || '',
qty: sheetNum0(c.qty) || 1, width: sheetNum0(c.width), height: sheetNum0(c.height), depth: sheetNum0(c.depth),
doorStyle: c.doorStyle || c.frontStyle || '', scope: sheetScopeName(p, c.scopeId), status: c.status || '',
}));
},
},
{
key: 'takeoffs', label: 'Take-off items', icon: '📐', level: 'project',
blurb: 'project.takeOffs — the take-off records filed on this job.',
columns: () => [
{ key: 'name', label: 'Take-off' }, { key: 'revision', label: 'Rev', fmt: 'number' },
{ key: 'date', label: 'Date', fmt: 'date' }, { key: 'preparedBy', label: 'Prepared by' },
{ key: 'scope', label: 'Scope' }, { key: 'department', label: 'Department' },
{ key: 'lines', label: 'Lines', fmt: 'number' }, { key: 'note', label: 'Note' },
],
rows: (ctx, opt) => {
const p = sheetProjectOf(ctx, opt.projectId);
if (!p) return [];
return (p.takeOffs || []).map(t => ({
__id: t.id, name: t.name || t.title || '', revision: sheetNum0(t.revision),
date: t.date || '', preparedBy: t.preparedBy || '', scope: sheetScopeName(p, t.scopeId),
department: t.department || '', lines: ((t.items || t.lines || []).length), note: t.note || '',
}));
},
},
{
key: 'vendors', label: 'Vendor list', icon: '🏭', level: 'global',
blurb: 'ctx.vendors — the vendor directory as it stands right now.',
columns: () => [
{ key: 'name', label: 'Vendor' }, { key: 'contact', label: 'Contact' },
{ key: 'phone', label: 'Phone' }, { key: 'email', label: 'Email' },
{ key: 'terms', label: 'Payment terms' }, { key: 'country', label: 'Country' },
],
rows: ctx => (ctx.vendors || []).map(v => ({
__id: v.id, name: v.name || '', contact: v.contactPerson || '', phone: v.phone || '',
email: v.email || '', terms: v.defaultPaymentTerms || '', country: v.country || v.address || '',
})),
},
{
key: 'purchaseOrders', label: 'Purchase orders', icon: '🧾', level: 'project',
blurb: 'project.purchaseOrders — issued POs with their status and scope.',
columns: ctx => [
{ key: 'poNumber', label: 'PO No.' }, { key: 'vendor', label: 'Vendor' },
{ key: 'category', label: 'Category' }, { key: 'scope', label: 'Scope' },
{ key: 'issued', label: 'Issued', fmt: 'date' }, { key: 'status', label: 'Status' },
].concat(ctx.canSeeFin ? [{ key: 'amount', label: 'Amount', fmt: 'currency' }] : []),
rows: (ctx, opt) => {
const p = sheetProjectOf(ctx, opt.projectId);
if (!p) return [];
return (p.purchaseOrders || []).map(po => ({
__id: po.id, poNumber: po.poNumber || '', vendor: po.vendorName || sheetVendorName(ctx, po.vendorId),
category: po.category || '', scope: sheetScopeName(p, po.scopeId),
issued: po.issuedDate || '', status: po.status || '', amount: sheetNum0(po.amount),
}));
},
},
{
key: 'materialReq', label: 'Material requirements', icon: '📦', level: 'project',
blurb: 'The material lines on this job’s proforma invoices — what is actually on order.',
columns: ctx => [
{ key: 'pi', label: 'PI No.' }, { key: 'vendor', label: 'Vendor' },
{ key: 'itemCode', label: 'Item code' }, { key: 'description', label: 'Description' },
{ key: 'dimensions', label: 'Dimensions' },
{ key: 'quantity', label: 'Qty', fmt: 'number' }, { key: 'unit', label: 'Unit' },
{ key: 'scope', label: 'Scope' },
].concat(ctx.canSeeFin ? [{ key: 'unitCost', label: 'Unit cost', fmt: 'currency' }, { key: 'lineTotal', label: 'Line total', fmt: 'currency' }] : []),
rows: (ctx, opt) => {
const p = sheetProjectOf(ctx, opt.projectId);
if (!p) return [];
const out = [];
(p.proformaInvoices || []).forEach(pi => {
(pi.materialLines || []).forEach(l => out.push({
__id: l.id, pi: pi.piNumber || '', vendor: pi.vendorName || '',
itemCode: l.itemCode || '', description: l.description || l.itemName || '',
dimensions: l.dimensions || '', quantity: sheetNum0(l.quantity), unit: l.unit || '',
scope: sheetScopeName(p, pi.scopeId),
unitCost: sheetNum0(l.unitCost), lineTotal: sheetNum0(l.quantity) * sheetNum0(l.unitCost),
}));
});
return out;
},
},
{
key: 'inventory', label: 'Inventory', icon: '🏬', level: 'global',
blurb: 'ctx.warehouseMaterials — live stock, straight from the warehouse.',
columns: ctx => [
{ key: 'itemId', label: 'Item no.' }, { key: 'name', label: 'Item' },
{ key: 'category', label: 'Category' }, { key: 'finishColor', label: 'Finish / colour' },
{ key: 'stock', label: 'On hand', fmt: 'number' }, { key: 'uom', label: 'UoM' },
{ key: 'location', label: 'Location' },
].concat(ctx.canSeeFin ? [{ key: 'unitCost', label: 'Unit cost', fmt: 'currency' }, { key: 'value', label: 'Stock value', fmt: 'currency' }] : []),
rows: ctx => (ctx.warehouseMaterials || []).filter(m => m.active !== false).map(m => ({
__id: m.id, itemId: m.itemId || '', name: m.name || '', category: m.category || '',
finishColor: m.finishColor || '', stock: sheetNum0(m.currentStock), uom: m.unitOfMeasure || '',
location: m.storageLocation || '', unitCost: sheetNum0(m.unitCost),
value: sheetNum0(m.currentStock) * sheetNum0(m.unitCost),
})),
},
{
key: 'slabs', label: 'Slab inventory', icon: '🪨', level: 'global',
blurb: 'ctx.slabs — every physical slab, with its batch and lot.',
columns: ctx => [
{ key: 'slabId', label: 'Slab ID' }, { key: 'material', label: 'Material' },
{ key: 'batch', label: 'Batch' }, { key: 'lot', label: 'Lot' },
{ key: 'length', label: 'L (mm)', fmt: 'number' }, { key: 'width', label: 'W (mm)', fmt: 'number' },
{ key: 'thickness', label: 'Thk (mm)', fmt: 'number' }, { key: 'area', label: 'Area (m²)', fmt: 'number' },
{ key: 'status', label: 'Status' },
].concat(ctx.canSeeFin ? [{ key: 'landedCost', label: 'Landed cost', fmt: 'currency' }] : []),
rows: ctx => (ctx.slabs || []).map(s => ({
__id: s.id, slabId: s.slabId || '', material: s.material || '', batch: s.batch || '', lot: s.lot || '',
length: sheetNum0(s.lengthMm), width: sheetNum0(s.widthMm), thickness: sheetNum0(s.thicknessMm),
area: sheetNum0(s.area), status: s.status || '', landedCost: sheetNum0(s.landedCost),
})),
},
{
key: 'selections', label: 'Selections', icon: '🎨', level: 'project',
blurb: 'What has been selected per scope and application area, read through the scope library.',
columns: () => [
{ key: 'scope', label: 'Scope' }, { key: 'family', label: 'Family' },
{ key: 'area', label: 'Application area' }, { key: 'category', label: 'Category' },
{ key: 'selection', label: 'Selection' }, { key: 'locked', label: 'Locked' },
],
rows: (ctx, opt) => {
const p = sheetProjectOf(ctx, opt.projectId);
if (!p) return [];
const out = [];
(p.scopes || []).forEach(scope => {
const family = (ctx.scopeLibrary || []).find(f => f.name === scope.familyName);
if (!family) return;
const areas = [{ name: scope.mainAreaName || 'Main area', map: scope.selections || {} }]
.concat((scope.selectionAreas || []).map(a => ({ name: a.name || 'Area', map: a.selections || {} })));
areas.forEach(area => {
(family.categories || []).forEach(cat => {
const optId = area.map[cat.id];
if (!optId) return;
const chosen = (cat.options || []).find(o => o.id === optId);
out.push({
__id: scope.id + ':' + area.name + ':' + cat.id,
scope: scope.name || '', family: scope.familyName || '', area: area.name,
category: cat.name || '', selection: chosen ? chosen.name : '',
locked: scope.selectionsLocked ? 'Yes' : 'No',
});
});
});
});
return out;
},
},
];
function sheetSourceByKey(key) { return SHEET_SOURCES.find(s => s.key === key) || null; }
function sheetBuildTable(ctx, sourceKey, opt) {
const src = sheetSourceByKey(sourceKey);
if (!src) return null;
const columns = src.columns(ctx, opt || {});
const rows = src.rows(ctx, opt || {}) || [];
return { src, columns, rows };
}
// ── What LEON Sheets does not do ──────────────────────────────────────────
// Named here rather than half-built. Each line says what to use instead, so
// nobody spends an afternoon looking for a menu that was never going to exist.
const SHEET_NOT_BUILT = [
{ what: 'Pivot tables', why: 'Not built. For a cross-tab, use a connected LEON table plus SUMIFS/COUNTIFS against it — or Reports, which already cross-tabs the same records.' },
{ what: 'Real-time co-editing', why: 'Not possible here. Two people editing one sheet at once needs a server to arbitrate; this app has none. The document is saved to this browser only.' },
{ what: 'AI functions', why: 'Not built. There is no model behind this app, so a formula that claimed to call one would be a lie.' },
{ what: 'Macros / scripting', why: 'Not built. Running stored code a colleague wrote inside the Hub is a security problem, not a feature gap.' },
{ what: 'A lossless .xlsx round-trip', why: 'Not possible here, and we do not pretend otherwise. LEON Sheets follows Excel’s MODEL — its number formats, theme slots, named cell styles and page setup. The FILE is written by SheetJS in this browser, which carries values, number formats, widths, heights and merges, and does not carry fonts, fills, borders, theme colours, conditional formatting, validation, charts or print setup. The Export panel lists both halves before you download.' },
{ what: 'Printing a picture of the grid', why: 'Deliberately not that. The Print layout section renders the print area as a real table of real text, which is what 🖨 and 📄 hand to the print path — selectable, searchable and sharp, rather than a screenshot.' },
];
// ── Charts ────────────────────────────────────────────────────────────────
// SVG drawn from cell values, no chart library. Same reasoning as every Gantt
// in this app: a chart that is markup can be printed, scaled and themed.
const SHEET_CHART_KINDS = [
{ key: 'column', label: 'Column' }, { key: 'bar', label: 'Bar' },
{ key: 'line', label: 'Line' }, { key: 'pie', label: 'Pie' },
];
const SHEET_CHART_COLORS = ['#8a6a4f', '#3f6f8f', '#7a8f5a', '#b08a3e', '#8f5a6f', '#5a7f7a', '#a9705a', '#6a6f8f'];
function sheetChartData(range, valueAt, hasHeader) {
const labels = [];
const seriesNames = [];
const series = [];
const firstRow = range.r1 + (hasHeader ? 1 : 0);
for (let c = range.c1 + 1; c <= range.c2; c++) {
seriesNames.push(hasHeader ? String(valueAt(range.r1, c) || sheetColLabel(c)) : sheetColLabel(c));
series.push([]);
}
for (let r = firstRow; r <= range.r2; r++) {
labels.push(String(valueAt(r, range.c1) === '' ? sheetA1(r, range.c1) : valueAt(r, range.c1)));
for (let c = range.c1 + 1; c <= range.c2; c++) {
const v = valueAt(r, c);
series[c - range.c1 - 1].push(typeof v === 'number' ? v : Number(String(v || '').replace(/[$,\s%]/g, '')) || 0);
}
}
return { labels, seriesNames, series };
}
function SheetChartSvg({ kind, data, title, width, height }) {
const W = width || 520, H = height || 260;
const padL = 54, padR = 12, padT = title ? 26 : 10, padB = 46;
const iw = Math.max(10, W - padL - padR), ih = Math.max(10, H - padT - padB);
const { labels, seriesNames, series } = data;
if (!labels.length || !series.length) {
return
Select a range with a label column and at least one number column.
;
}
const all = series.reduce((a, s) => a.concat(s), []);
const maxV = Math.max.apply(null, all.concat([0]));
const minV = Math.min.apply(null, all.concat([0]));
const span = maxV - minV || 1;
if (kind === 'pie') {
const vals = series[0].map(v => Math.max(0, v));
const total = vals.reduce((a, b) => a + b, 0) || 1;
const cx = W / 2, cy = padT + ih / 2, rad = Math.min(iw, ih) / 2 - 6;
let angle = -Math.PI / 2;
const arcs = vals.map((v, i) => {
const a0 = angle, a1 = angle + (v / total) * Math.PI * 2;
angle = a1;
const large = a1 - a0 > Math.PI ? 1 : 0;
const x0 = cx + rad * Math.cos(a0), y0 = cy + rad * Math.sin(a0);
const x1 = cx + rad * Math.cos(a1), y1 = cy + rad * Math.sin(a1);
return { d: `M ${cx} ${cy} L ${x0} ${y0} A ${rad} ${rad} 0 ${large} 1 ${x1} ${y1} Z`, i, pct: v / total };
});
return (
);
}
const horizontal = kind === 'bar';
const scale = v => (v - Math.min(0, minV)) / span;
const zeroY = padT + ih - scale(0) * ih;
const groupSize = horizontal ? ih / labels.length : iw / labels.length;
const barW = Math.max(2, (groupSize * 0.72) / series.length);
return (
);
}
// ── Templates ─────────────────────────────────────────────────────────────
// A template is a body, not a file. Each one is the shape of a document this
// company actually produces, with the formulas already written — the point is
// that nobody has to remember how to compute a margin at 4pm on a Friday.
function sheetCells(rows, startR, startC) {
const cells = {};
rows.forEach((line, ri) => line.forEach((entry, ci) => {
if (entry === null || entry === undefined || entry === '') return;
const key = sheetKey((startR || 0) + ri, (startC || 0) + ci);
cells[key] = (entry && typeof entry === 'object' && 'v' in entry) ? entry : { v: entry };
}));
return cells;
}
// A template's header follows the workbook's THEME slot rather than a fixed
// beige, so switching the theme restyles the templates too.
const SHEET_HEAD = { s: { b: true, bg: 'lt2', a: 'center' } };
function sheetH(text) { return { v: text, s: SHEET_HEAD.s }; }
function sheetMoney(v) { return { v, s: { nfp: '$#,##0.00' } }; }
function sheetPct(v) { return { v, s: { nfp: '#,##0.0%' } }; }
const SHEET_TEMPLATES = [
{
// LEON's own take-off workbook, not a generic grid — 19 sheets, one per
// scope, reproduced from templates/Leon Take-Off Template v4.xlsx. It sits
// first because it is the one people come here for. The builder lives in
// takeoff/leon-takeoff-sheet.js; the guard means a missing script degrades
// to the template simply not being offered, rather than a broken option.
key: 'leonTakeoff', label: 'LEON Take-Off Template', icon: '📐',
blurb: 'The real thing — a tab per scope, waste allowances, unit matrix, summaries and validation.',
build: () => leonTakeoffTemplateBody(),
available: () => typeof leonTakeoffTemplateBody === 'function',
},
{
key: 'estimate', fin: true, label: 'Estimate', icon: '💲',
blurb: 'Line items with quantity, unit cost, markup and a total that adds itself up.',
build: () => {
const rows = [
[sheetH('Item'), sheetH('Scope'), sheetH('Qty'), sheetH('Unit'), sheetH('Unit Cost'), sheetH('Cost'), sheetH('Markup %'), sheetH('Sell'), sheetH('Margin %')],
];
for (let i = 0; i < 12; i++) {
const r = i + 2;
rows.push(['', '', '', '', sheetMoney(''), { v: `=C${r}*E${r}`, s: { nfp: '$#,##0.00' } },
sheetPct(0.25), { v: `=F${r}*(1+G${r})`, s: { nfp: '$#,##0.00' } },
{ v: `=IFERROR((H${r}-F${r})/H${r},"")`, s: { nfp: '#,##0.0%' } }]);
}
rows.push([{ v: 'TOTAL', s: { b: true } }, '', '', '', '', { v: '=SUM(F2:F13)', s: { b: true, nfp: '$#,##0.00' } }, '',
{ v: '=SUM(H2:H13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=IFERROR((H14-F14)/H14,"")', s: { b: true, nfp: '#,##0.0%' } }]);
return {
sheets: [{
id: uid('ws'), name: 'Estimate', cells: sheetCells(rows, 0, 0),
cols: { 0: { w: 200 }, 1: { w: 150 } }, rows: {}, merges: [], frozen: { r: 1, c: 1 },
condFormats: [{ id: uid('cf'), r1: 1, c1: 8, r2: 13, c2: 8, kind: 'lt', v1: 0.2, v2: '', bg: '#fbe3e3', fg: '#8c2f2f', bold: true }],
validations: [], filter: null,
}],
namedRanges: {}, connections: [], charts: [],
};
},
},
{
key: 'unitMatrix', label: 'Unit Matrix', icon: '🏢',
blurb: 'Unit type against scope — the grid a residential job is counted on.',
build: () => {
const rows = [[sheetH('Unit'), sheetH('Type'), sheetH('Floor'), sheetH('Kitchen'), sheetH('Vanities'), sheetH('Closets'), sheetH('Doors'), sheetH('Total')]];
for (let i = 0; i < 20; i++) {
const r = i + 2;
rows.push(['', '', '', '', '', '', '', { v: `=SUM(D${r}:G${r})` }]);
}
rows.push([{ v: 'TOTAL', s: { b: true } }, '', '',
{ v: '=SUM(D2:D21)', s: { b: true } }, { v: '=SUM(E2:E21)', s: { b: true } },
{ v: '=SUM(F2:F21)', s: { b: true } }, { v: '=SUM(G2:G21)', s: { b: true } },
{ v: '=SUM(H2:H21)', s: { b: true } }]);
return { sheets: [{ id: uid('ws'), name: 'Unit Matrix', cells: sheetCells(rows, 0, 0), cols: {}, rows: {}, merges: [], frozen: { r: 1, c: 1 }, condFormats: [], validations: [], filter: null }], namedRanges: {}, connections: [], charts: [] };
},
},
{
key: 'doorSchedule', label: 'Door Schedule', icon: '🚪',
blurb: 'The door schedule columns, ready for a LEON Doors connected table underneath.',
build: () => ({
sheets: [{
id: uid('ws'), name: 'Door Schedule',
cells: sheetCells([[sheetH('Mark'), sheetH('Location'), sheetH('Unit'), sheetH('Qty'), sheetH('Leaf W'), sheetH('Leaf H'), sheetH('Handing'), sheetH('Fire'), sheetH('Scope'), sheetH('Status')]], 0, 0),
cols: { 1: { w: 160 } }, rows: {}, merges: [], frozen: { r: 1, c: 1 }, condFormats: [], validations: [], filter: null,
}],
namedRanges: {}, connections: [], charts: [],
}),
},
{
// Renamed to say what it is, now that the REAL take-off template sits at
// the top of this list. This one is a seven-column scratch grid for a quick
// count; it is not the workbook a job is bid from.
key: 'takeoff', label: 'Quick count', icon: '🔢',
blurb: 'A scratch grid — counted quantities, a waste allowance and an order quantity that follows it.',
build: () => {
const rows = [[sheetH('Area'), sheetH('Item'), sheetH('Unit'), sheetH('Counted'), sheetH('Waste %'), sheetH('Order Qty'), sheetH('Notes')]];
for (let i = 0; i < 15; i++) {
const r = i + 2;
rows.push(['', '', '', '', sheetPct(0.1), { v: `=IF(D${r}="","",ROUND(D${r}*(1+E${r}),0))` }, '']);
}
rows.push([{ v: 'TOTAL', s: { b: true } }, '', '', { v: '=SUM(D2:D16)', s: { b: true } }, '', { v: '=SUM(F2:F16)', s: { b: true } }, '']);
return { sheets: [{ id: uid('ws'), name: 'Take-off', cells: sheetCells(rows, 0, 0), cols: { 0: { w: 150 }, 1: { w: 200 } }, rows: {}, merges: [], frozen: { r: 1, c: 0 }, condFormats: [], validations: [], filter: null }], namedRanges: {}, connections: [], charts: [] };
},
},
{
key: 'procurement', label: 'Procurement Tracker', icon: '📦',
blurb: 'PO through delivery, with days-late computed from today rather than eyeballed.',
build: () => {
const rows = [[sheetH('PO No.'), sheetH('Vendor'), sheetH('Scope'), sheetH('Issued'), sheetH('Promised'), sheetH('Received'), sheetH('Status'), sheetH('Days Late')]];
for (let i = 0; i < 15; i++) {
const r = i + 2;
rows.push(['', '', '', { v: '', s: { nfp: 'yyyy-mm-dd' } }, { v: '', s: { nfp: 'yyyy-mm-dd' } }, { v: '', s: { nfp: 'yyyy-mm-dd' } }, '',
{ v: `=IF(E${r}="","",IF(F${r}="",MAX(0,TODAY()-E${r}),MAX(0,F${r}-E${r})))` }]);
}
return {
sheets: [{
id: uid('ws'), name: 'Procurement', cells: sheetCells(rows, 0, 0),
cols: { 1: { w: 160 }, 2: { w: 160 } }, rows: {}, merges: [], frozen: { r: 1, c: 1 },
condFormats: [{ id: uid('cf'), r1: 1, c1: 7, r2: 16, c2: 7, kind: 'gt', v1: 0, v2: '', bg: '#fbe3e3', fg: '#8c2f2f', bold: true }],
validations: [{ id: uid('dv'), r1: 1, c1: 6, r2: 16, c2: 6, kind: 'list', options: ['Not Issued', 'Issued', 'In Production', 'Shipped', 'Received', 'Cancelled'], min: '', max: '' }],
filter: null,
}],
namedRanges: {}, connections: [], charts: [],
};
},
},
{
key: 'profitability', fin: true, label: 'Profitability', icon: '📈',
blurb: 'Sell against cost per scope, with the margin highlighted the moment it drops under 20%.',
build: () => {
const rows = [[sheetH('Scope'), sheetH('Contract'), sheetH('Budget Cost'), sheetH('Actual Cost'), sheetH('Committed'), sheetH('Forecast Cost'), sheetH('Forecast Margin'), sheetH('Margin %')]];
for (let i = 0; i < 12; i++) {
const r = i + 2;
rows.push(['', sheetMoney(''), sheetMoney(''), sheetMoney(''), sheetMoney(''),
{ v: `=MAX(D${r}+E${r},C${r})`, s: { nfp: '$#,##0.00' } },
{ v: `=B${r}-F${r}`, s: { nfp: '$#,##0.00' } },
{ v: `=IFERROR(G${r}/B${r},"")`, s: { nfp: '#,##0.0%' } }]);
}
rows.push([{ v: 'TOTAL', s: { b: true } },
{ v: '=SUM(B2:B13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=SUM(C2:C13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=SUM(D2:D13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=SUM(E2:E13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=SUM(F2:F13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=SUM(G2:G13)', s: { b: true, nfp: '$#,##0.00' } },
{ v: '=IFERROR(G14/B14,"")', s: { b: true, nfp: '#,##0.0%' } }]);
return {
sheets: [{
id: uid('ws'), name: 'Profitability', cells: sheetCells(rows, 0, 0),
cols: { 0: { w: 200 } }, rows: {}, merges: [], frozen: { r: 1, c: 1 },
condFormats: [{ id: uid('cf'), r1: 1, c1: 7, r2: 13, c2: 7, kind: 'lt', v1: 0.2, v2: '', bg: '#fbe3e3', fg: '#8c2f2f', bold: true }],
validations: [], filter: null,
}],
namedRanges: {}, connections: [], charts: [],
};
},
},
];
// ── XLSX / CSV ────────────────────────────────────────────────────────────
// window.XLSX (SheetJS) is already on the page for the PI material-list import.
// Reused here rather than hand-rolling a parser that would be wrong about
// quoting on the first real file someone opened.
function sheetXlsxAvailable() { return !!window.XLSX; }
// Accepts either a plain 2D array of values or the RICH grid the importer
// builds — entries of { v, t, z, f } straight off a SheetJS worksheet. The rich
// form is what carries a number format across, which is the difference between
// importing a schedule and importing a wall of unformatted serial numbers.
function sheetGridToCells(grid, opts) {
const o = opts || {};
const cells = {};
grid.forEach((line, r) => (line || []).forEach((entry, c) => {
const rich = entry && typeof entry === 'object' && !Array.isArray(entry);
const raw = rich ? entry.v : entry;
if (raw === '' || raw === null || raw === undefined) {
// A cell with a formula but no cached result still tells us something.
if (!(rich && entry.f)) return;
}
let value;
if (rich && (raw === '' || raw === null || raw === undefined) && entry.f) {
value = "'=" + entry.f;
} else if (rich && entry.t === 'n') {
value = Number(raw);
} else if (rich && entry.t === 'b') {
value = !!raw;
} else {
const coerced = (o.raw || (rich && entry.t === 's' && String(entry.z || '') === '@')) ? raw : sheetCoerceInput(raw);
// An imported "=..." string is TEXT. Trusting a formula out of someone
// else's file would let a spreadsheet rewrite this one on open.
value = (typeof coerced === 'string' && coerced[0] === '=') ? "'" + coerced : coerced;
}
const cell = { v: value };
if (rich) {
const s = sheetStyleFromNumFmt(entry.z);
if (s) cell.s = s;
}
cells[sheetKey(r + (o.dr || 0), c + (o.dc || 0))] = cell;
}));
return cells;
}
function sheetDownloadBlob(name, blob) {
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url; a.download = name;
document.body.appendChild(a); a.click();
document.body.removeChild(a);
setTimeout(() => URL.revokeObjectURL(url), 2000);
}
function sheetCsvCell(v) {
const s = v === null || v === undefined ? '' : String(v);
return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
}
function sheetToTsv(grid) { return grid.map(line => line.map(v => String(v === null || v === undefined ? '' : v).replace(/\t/g, ' ').replace(/\n/g, ' ')).join('\t')).join('\n'); }
function sheetFromTsv(text) {
return String(text).replace(/\r\n/g, '\n').replace(/\r/g, '\n').split('\n').map(line => line.split('\t'));
}
// SheetJS's number format on a cell is `z`. Mapping it back to one of Excel's
// built-in ids where it matches keeps the workbook saying "Accounting" rather
// than carrying a private copy of the pattern; anything else is kept verbatim,
// which is the whole reason the model is an id AND a pattern.
function sheetStyleFromNumFmt(z) {
if (z === undefined || z === null || z === '') return null;
if (typeof z === 'number') return SHEET_NF_BY_ID[z] ? { nf: z } : null;
const p = String(z);
if (/^general$/i.test(p.trim())) return null;
const built = SHEET_NUMBER_FORMATS.find(f => f.pattern === p);
return built ? { nf: built.id } : { nfp: p };
}
// One worksheet, as SheetJS cell objects rather than an array of arrays — that
// is the only way `z` (the number format) reaches the file at all. Values are
// the EVALUATED ones: a formula means nothing outside this workbook, and the
// recipient wants the number.
function sheetToXlsxSheet(ws, book, valueAt) {
const used = sheetUsedRange(ws);
const out = {};
if (used.empty) { out['!ref'] = 'A1'; return out; }
for (let r = 0; r <= used.r2; r++) {
for (let c = 0; c <= used.c2; c++) {
const v = valueAt(r, c);
if (v === '' || v === null || v === undefined) continue;
const cell = sheetCellAt(ws, r, c);
const pattern = sheetCellPattern(cell, book);
const addr = XLSX.utils.encode_cell({ r, c });
if (typeof v === 'number') {
out[addr] = /^general$/i.test(pattern) ? { t: 'n', v } : { t: 'n', v, z: pattern };
} else if (typeof v === 'boolean') {
out[addr] = { t: 'b', v };
} else {
out[addr] = sheetIsTextPattern(pattern) ? { t: 's', v: String(v), z: '@' } : { t: 's', v: String(v) };
}
}
}
out['!ref'] = XLSX.utils.encode_range({ s: { r: 0, c: 0 }, e: { r: used.r2, c: used.c2 } });
const cols = [];
for (let c = 0; c <= used.c2; c++) {
const meta = (ws.cols || {})[c];
if (meta && meta.hidden) { cols.push({ hidden: true }); continue; }
cols.push({ wch: meta && meta.wch ? meta.wch : sheetPxToChars(sheetColW(ws, c, book), book) });
}
out['!cols'] = cols;
const rows = [];
for (let r = 0; r <= used.r2; r++) {
const meta = (ws.rows || {})[r];
if (meta && meta.hidden) { rows.push({ hidden: true }); continue; }
rows.push({ hpt: meta && meta.hpt ? meta.hpt : sheetPxToPt(sheetRowH(ws, r, null, book)) });
}
out['!rows'] = rows;
if ((ws.merges || []).length) {
out['!merges'] = ws.merges.map(m => ({ s: { r: m.r1, c: m.c1 }, e: { r: m.r2, c: m.c2 } }));
}
const page = sheetPage(ws);
const mm = x => Math.round((x / 25.4) * 1000) / 1000;
out['!margins'] = { left: mm(page.margins.left), right: mm(page.margins.right),
top: mm(page.margins.top), bottom: mm(page.margins.bottom), header: 0.3, footer: 0.3 };
return out;
}
// Said at the export point rather than left for someone to discover. We are
// matching Excel's MODEL; SheetJS handles the FORMAT, and in a browser it
// handles a documented subset of it.
const SHEET_XLSX_CARRIES = [
'Cell values — every formula is exported as the number or text it evaluated to.',
'Number formats, including custom patterns, so a figure still reads as currency or a date in Excel.',
'Column widths in characters, row heights in points, hidden rows and columns.',
'Merged cells, sheet names and sheet order, and the page margins from Print setup.',
];
const SHEET_XLSX_DROPS = [
'Formulas themselves. What travels is the result — reopening the file in Excel will not recalculate it.',
'Fonts, fills, borders, alignment and named cell styles. The SheetJS build in this browser writes values and number formats, not cell styling.',
'Theme slots. A themed colour is a reference here; there is nothing in the exported file for it to reference.',
'Print setup beyond the margins — paper size, orientation, print area and repeating rows are not written.',
'Conditional formatting, data validation, charts, freeze panes and connected LEON tables.',
];
// ── The printed page ──────────────────────────────────────────────────────
// The grid is a virtualised absolutely-positioned thing; none of that belongs
// on paper. This renders the print area as an ordinary
of real text,
// which is exactly what the app's existing print path wants: `printRegion`
// clones it, `exportPdf` reads the same clone through jsPDF + autoTable, and
// both produce selectable text rather than a picture of a screen. No new PDF
// library is involved, and none is needed.
//
// The repeating header rows go in , because the print stylesheet already
// declares `thead { display: table-header-group }` — which is what makes a
// header repeat on every page AND reserve its space.
function SheetPrintLayout({ book, ws, valueAt, docName }) {
const page = sheetPage(ws);
const used = sheetUsedRange(ws);
const area = page.printArea || (used.empty
? { r1: 0, c1: 0, r2: 0, c2: 0 }
: { r1: 0, c1: 0, r2: used.r2, c2: used.c2 });
const rep = page.repeatRows;
const paper = SHEET_PAPER_SIZES.find(p => p.key === page.paper) || SHEET_PAPER_SIZES[0];
const meta = { docName, sheetName: ws.name };
const bookDef = sheetBookDefaults(book);
const scale = Math.max(40, Math.min(200, Number(page.scale) || 100)) / 100;
const covered = new Set();
(ws.merges || []).forEach(m => {
for (let r = m.r1; r <= m.r2; r++) for (let c = m.c1; c <= m.c2; c++) if (r !== m.r1 || c !== m.c1) covered.add(sheetKey(r, c));
});
const totalCells = Math.max(0, (area.r2 - area.r1 + 1) * (area.c2 - area.c1 + 1));
const capped = totalCells > SHEET_PRINT_CELL_CAP;
const lastRow = capped
? Math.min(area.r2, area.r1 + Math.floor(SHEET_PRINT_CELL_CAP / Math.max(1, area.c2 - area.c1 + 1)) - 1)
: area.r2;
function renderRow(r, key) {
const cells = [];
for (let c = area.c1; c <= area.c2; c++) {
if (covered.has(sheetKey(r, c))) continue;
const merge = sheetMergeAt(ws, r, c);
const cell = sheetCellAt(ws, r, c);
const value = valueAt(r, c);
const shown = sheetDisplayParts(cell, value, book);
const s = sheetEffectiveStyle(cell, book);
const numeric = typeof value === 'number';
const st = {
fontWeight: s.b ? 700 : 400,
fontStyle: s.i ? 'italic' : 'normal',
fontFamily: s.fn || bookDef.fontName,
fontSize: Math.round(sheetPtToPx(s.fs || bookDef.fontSizePt) * scale) + 'px',
textAlign: s.a || (numeric ? 'right' : 'left'),
background: sheetColor(book, s.bg) || undefined,
color: sheetColor(book, s.fg) || shown.color || undefined,
border: page.gridlines ? '1px solid #b9b2aa' : 'none',
padding: '2px 5px',
whiteSpace: 'pre-wrap',
};
cells.push(
;
}
const headRows = [];
if (rep && rep.from !== null && rep.from !== undefined) {
for (let r = rep.from; r <= rep.to; r++) headRows.push(renderRow(r, 'h' + r));
}
const bodyRows = [];
for (let r = area.r1; r <= lastRow; r++) {
if (rep && r >= rep.from && r <= rep.to) continue; // already in
if (sheetRowH(ws, r, null, book) === 0) continue; // hidden rows do not print
bodyRows.push(renderRow(r, 'b' + r));
}
const headerLine = sheetHeaderText(page.headerText, meta);
const footerLine = sheetHeaderText(page.footerText, meta);
return (
{/* @page is the only way to ask a browser for a paper size, an
orientation and page margins. It has no scoping mechanism — a rule is
a rule for the whole document — so it is only in the DOM while this
section is open, and the note at the bottom says as much. */}
{headerLine &&
{headerLine}
}
{page.headings && (
{(() => { const th = []; for (let c = area.c1; c <= area.c2; c++) th.push(
Only the first {lastRow - area.r1 + 1} rows are laid out — a printed page is real markup, and past
about {SHEET_PRINT_CELL_CAP.toLocaleString()} cells building it locks the tab. Set a print area to narrow this down.
)}
This is what 🖨 and 📄 above produce — real text and a real table, not a picture of the grid.
Page numbers come from your browser’s own print dialog (Headers and footers); a page cannot count itself.
While this section is open, its paper size, orientation and margins apply to anything else you print from
this screen too — a browser has no way to scope a page rule — so collapse it if you are printing something else.
);
}
// ── The grid ──────────────────────────────────────────────────────────────
// Virtualised: only the cells inside the scroll window (plus frozen rows and
// columns, which are always drawn) reach the DOM. Everything is absolutely
// positioned off prefix-sum offsets, which is also what makes hidden rows,
// filtered rows and per-row heights the same mechanism instead of three.
//
// Freeze panes are done by moving a pinned cell by the current scroll offset
// rather than with position:sticky — sticky and absolute positioning do not
// combine, and this way one geometry model covers the whole grid.
function SheetGrid({
ws, book, valueAt, editable, sel, onSel, onEdit, onFill, onResizeCol, onResizeRow,
hiddenRows, connAt, height, onOpenCellMenu,
}) {
const scroller = useRef(null);
const [scroll, setScroll] = useState({ top: 0, left: 0 });
const [viewport, setViewport] = useState({ w: 900, h: height || 520 });
const [edit, setEdit] = useState(null); // { r, c, text }
const [drag, setDrag] = useState(null); // { kind:'select'|'fill' }
const [resize, setResize] = useState(null);
const editRef = useRef(null);
// What was last copied FROM this grid, so a paste can shift the formulas by
// how far it moved. The text is kept as the fingerprint: if the clipboard no
// longer matches, the copy came from somewhere else and nothing is shifted.
const lastCopy = useRef(null);
const dims = useMemo(() => sheetDims(ws), [ws]);
const colOff = useMemo(() => sheetOffsets(dims.cols, c => sheetColW(ws, c, book)), [ws.cols, dims.cols, ws, book]);
const rowOff = useMemo(() => sheetOffsets(dims.rows, r => sheetRowH(ws, r, hiddenRows, book)), [ws.rows, dims.rows, hiddenRows, ws, book]);
const bookDefaults = sheetBookDefaults(book);
const totalW = colOff[dims.cols], totalH = rowOff[dims.rows];
const frozen = ws.frozen || { r: 0, c: 0 };
useEffect(() => {
function measure() {
if (scroller.current) setViewport({ w: scroller.current.clientWidth, h: scroller.current.clientHeight });
}
measure();
window.addEventListener('resize', measure);
return () => window.removeEventListener('resize', measure);
}, []);
useEffect(() => { if (edit && editRef.current) { editRef.current.focus(); editRef.current.select(); } }, [edit && edit.r, edit && edit.c]);
// Visible window, plus overscan so a fast scroll does not flash empty rows.
const firstRow = Math.max(0, sheetIndexAt(rowOff, scroll.top) - 4);
const lastRow = Math.min(dims.rows - 1, sheetIndexAt(rowOff, scroll.top + viewport.h) + 4);
const firstCol = Math.max(0, sheetIndexAt(colOff, scroll.left) - 2);
const lastCol = Math.min(dims.cols - 1, sheetIndexAt(colOff, scroll.left + viewport.w) + 2);
const rowsToDraw = [];
for (let r = 0; r < frozen.r && r < dims.rows; r++) rowsToDraw.push(r);
for (let r = Math.max(firstRow, frozen.r); r <= lastRow; r++) rowsToDraw.push(r);
const colsToDraw = [];
for (let c = 0; c < frozen.c && c < dims.cols; c++) colsToDraw.push(c);
for (let c = Math.max(firstCol, frozen.c); c <= lastCol; c++) colsToDraw.push(c);
const range = sheetNormRange(sel);
const covered = new Set();
(ws.merges || []).forEach(m => {
for (let r = m.r1; r <= m.r2; r++) for (let c = m.c1; c <= m.c2; c++) if (r !== m.r1 || c !== m.c1) covered.add(sheetKey(r, c));
});
function xOf(c) { return SHEET_GUTTER_W + colOff[c] + (c < frozen.c ? scroll.left : 0); }
function yOf(r) { return SHEET_HEAD_H + rowOff[r] + (r < frozen.r ? scroll.top : 0); }
function zOf(r, c) { return (r < frozen.r ? 2 : 0) + (c < frozen.c ? 2 : 0) + 1; }
function ensureVisible(r, c) {
const el = scroller.current;
if (!el) return;
const y0 = rowOff[r], y1 = rowOff[Math.min(dims.rows, r + 1)];
const x0 = colOff[c], x1 = colOff[Math.min(dims.cols, c + 1)];
const padT = rowOff[Math.min(frozen.r, dims.rows)];
const padL = colOff[Math.min(frozen.c, dims.cols)];
if (y0 - padT < el.scrollTop) el.scrollTop = Math.max(0, y0 - padT);
else if (y1 > el.scrollTop + el.clientHeight - SHEET_HEAD_H) el.scrollTop = y1 - el.clientHeight + SHEET_HEAD_H;
if (x0 - padL < el.scrollLeft) el.scrollLeft = Math.max(0, x0 - padL);
else if (x1 > el.scrollLeft + el.clientWidth - SHEET_GUTTER_W) el.scrollLeft = x1 - el.clientWidth + SHEET_GUTTER_W;
}
function move(dr, dc, extend) {
const r = Math.max(0, Math.min(dims.rows - 1, (extend ? sel.r2 : sel.r) + dr));
const c = Math.max(0, Math.min(dims.cols - 1, (extend ? sel.c2 : sel.c) + dc));
onSel(extend ? { r: sel.r, c: sel.c, r2: r, c2: c } : { r, c, r2: r, c2: c });
ensureVisible(r, c);
}
function startEdit(r, c, seed) {
if (!editable) return;
const conn = connAt && connAt(r, c);
if (conn && !conn.allowEdits) { onOpenCellMenu && onOpenCellMenu('connected', conn); return; }
const cell = sheetCellAt(ws, r, c);
setEdit({ r, c, text: seed !== undefined ? seed : sheetEditText(cell, book) });
}
function commitEdit(moveDr, moveDc) {
if (!edit) return;
const raw = edit.text;
const prev = sheetCellAt(ws, edit.r, edit.c);
const prevText = sheetEditText(prev, book);
if (raw !== prevText) {
// A leading apostrophe forces text — the only way to type something that
// looks like a formula or a number and keep it as written. The marker is
// STORED, so re-opening the cell shows it again and it survives an edit.
// A cell formatted Text does not coerce: that is the whole meaning of
// the format, and it is why "007" survives being typed into one.
const asText = sheetIsTextPattern(sheetCellPattern(prev, book));
const v = raw.length > 1 && raw[0] === "'" ? raw
: asText ? sheetTextInput(raw)
: sheetCoerceInput(raw);
onEdit([{ r: edit.r, c: edit.c, v }]);
}
setEdit(null);
if (moveDr || moveDc) {
const r = Math.max(0, Math.min(dims.rows - 1, edit.r + (moveDr || 0)));
const c = Math.max(0, Math.min(dims.cols - 1, edit.c + (moveDc || 0)));
onSel({ r, c, r2: r, c2: c });
ensureVisible(r, c);
}
}
function onKeyDown(e) {
if (edit) {
if (e.key === 'Enter') { e.preventDefault(); commitEdit(1, 0); }
else if (e.key === 'Tab') { e.preventDefault(); commitEdit(0, e.shiftKey ? -1 : 1); }
else if (e.key === 'Escape') { e.preventDefault(); setEdit(null); }
return;
}
const meta = e.metaKey || e.ctrlKey;
if (meta && (e.key === 'z' || e.key === 'Z')) { e.preventDefault(); onEdit(e.shiftKey ? '__redo' : '__undo'); return; }
if (meta && (e.key === 'y' || e.key === 'Y')) { e.preventDefault(); onEdit('__redo'); return; }
if (meta && (e.key === 'c' || e.key === 'x' || e.key === 'v')) return; // handled by the clipboard events
if (e.key === 'ArrowUp') { e.preventDefault(); move(-1, 0, e.shiftKey); return; }
if (e.key === 'ArrowDown') { e.preventDefault(); move(1, 0, e.shiftKey); return; }
if (e.key === 'ArrowLeft') { e.preventDefault(); move(0, -1, e.shiftKey); return; }
if (e.key === 'ArrowRight') { e.preventDefault(); move(0, 1, e.shiftKey); return; }
if (e.key === 'Tab') { e.preventDefault(); move(0, e.shiftKey ? -1 : 1, false); return; }
if (e.key === 'Enter') { e.preventDefault(); if (editable) startEdit(sel.r, sel.c); return; }
if (e.key === 'F2') { e.preventDefault(); startEdit(sel.r, sel.c); return; }
if (e.key === 'Home') { e.preventDefault(); onSel({ r: sel.r, c: 0, r2: sel.r, c2: 0 }); ensureVisible(sel.r, 0); return; }
if (e.key === 'PageDown') { e.preventDefault(); move(20, 0, e.shiftKey); return; }
if (e.key === 'PageUp') { e.preventDefault(); move(-20, 0, e.shiftKey); return; }
if (e.key === 'Delete' || e.key === 'Backspace') {
e.preventDefault();
if (!editable) return;
const writes = [];
for (let r = range.r1; r <= range.r2; r++) for (let c = range.c1; c <= range.c2; c++) writes.push({ r, c, v: '' });
onEdit(writes);
return;
}
if (!meta && e.key.length === 1) { e.preventDefault(); startEdit(sel.r, sel.c, e.key); }
}
function gridClipboard() {
const grid = [];
for (let r = range.r1; r <= range.r2; r++) {
const line = [];
for (let c = range.c1; c <= range.c2; c++) {
const cell = sheetCellAt(ws, r, c);
// Formulas travel as formulas so a paste keeps the calculation; a
// paste into another app gets the text of the formula, which is the
// honest thing to hand over.
line.push(cell && sheetIsFormula(cell.v) ? cell.v : sheetDisplay(cell, valueAt(r, c), book));
}
grid.push(line);
}
return grid;
}
function onCopy(e) {
if (edit) return;
e.preventDefault();
const text = sheetToTsv(gridClipboard());
lastCopy.current = { text, r: range.r1, c: range.c1 };
e.clipboardData.setData('text/plain', text);
}
function onCut(e) {
if (edit) return;
onCopy(e);
// Cut-and-paste MOVES cells; their formulas keep pointing where they
// pointed. Only copy-and-paste shifts, so the origin is dropped here.
lastCopy.current = null;
if (!editable) return;
const writes = [];
for (let r = range.r1; r <= range.r2; r++) for (let c = range.c1; c <= range.c2; c++) writes.push({ r, c, v: '' });
onEdit(writes);
}
function onPaste(e) {
if (edit || !editable) return;
e.preventDefault();
const text = e.clipboardData.getData('text/plain');
if (!text) return;
const grid = sheetFromTsv(text);
// A formula copied from inside this grid is re-pointed by how far it moved,
// which is what makes copying a row of calculations down do the right
// thing. Text pasted from anywhere else is taken literally, because there
// is no origin to measure from.
const from = lastCopy.current && lastCopy.current.text === text ? lastCopy.current : null;
const shiftR = from ? sel.r - from.r : 0;
const shiftC = from ? sel.c - from.c : 0;
const writes = [];
grid.forEach((line, dr) => line.forEach((raw, dc) => {
const r = sel.r + dr, c = sel.c + dc;
if (r >= SHEET_MAX_ROWS || c >= SHEET_MAX_COLS) return;
const asText = sheetIsTextPattern(sheetCellPattern(sheetCellAt(ws, r, c), book));
const v = (sheetIsFormula(raw) && !asText)
? ((shiftR || shiftC) ? sheetShiftFormula(raw, shiftR, shiftC) : raw)
: asText ? sheetTextInput(raw) : sheetCoerceInput(raw);
writes.push({ r, c, v });
}));
if (writes.length) {
onEdit(writes);
const lastR = sel.r + grid.length - 1;
const lastC = sel.c + Math.max.apply(null, grid.map(l => l.length)) - 1;
onSel({ r: sel.r, c: sel.c, r2: Math.min(dims.rows - 1, lastR), c2: Math.min(dims.cols - 1, lastC) });
}
}
// Mouse selection and the fill handle share one drag, because they are the
// same gesture with a different starting point.
function cellFromEvent(e) {
const el = scroller.current;
if (!el) return null;
const box = el.getBoundingClientRect();
let x = e.clientX - box.left + el.scrollLeft - SHEET_GUTTER_W;
let y = e.clientY - box.top + el.scrollTop - SHEET_HEAD_H;
// Inside a frozen band the pointer is over a pinned cell, so the scroll
// offset must come back off again.
if (e.clientY - box.top < SHEET_HEAD_H + rowOff[Math.min(frozen.r, dims.rows)]) y -= el.scrollTop;
if (e.clientX - box.left < SHEET_GUTTER_W + colOff[Math.min(frozen.c, dims.cols)]) x -= el.scrollLeft;
const r = Math.max(0, Math.min(dims.rows - 1, sheetIndexAt(rowOff, Math.max(0, y))));
const c = Math.max(0, Math.min(dims.cols - 1, sheetIndexAt(colOff, Math.max(0, x))));
return { r, c };
}
useEffect(() => {
if (!drag) return undefined;
function onMove(e) {
const p = cellFromEvent(e);
if (!p) return;
if (drag.kind === 'select') onSel({ r: drag.r, c: drag.c, r2: p.r, c2: p.c });
else setDrag(Object.assign({}, drag, { to: p }));
}
function onUp() {
if (drag.kind === 'fill' && drag.to) onFill(sheetNormRange(sel), drag.to);
setDrag(null);
}
window.addEventListener('mousemove', onMove);
window.addEventListener('mouseup', onUp);
return () => { window.removeEventListener('mousemove', onMove); window.removeEventListener('mouseup', onUp); };
}, [drag, sel]);
useEffect(() => {
if (!resize) return undefined;
function onMove(e) {
const delta = resize.axis === 'col' ? e.clientX - resize.x0 : e.clientY - resize.y0;
setResize(Object.assign({}, resize, { size: Math.max(resize.axis === 'col' ? 28 : 16, resize.start + delta) }));
}
function onUp() {
if (resize.axis === 'col') onResizeCol(resize.i, Math.round(resize.size));
else onResizeRow(resize.i, Math.round(resize.size));
setResize(null);
}
window.addEventListener('mousemove', onMove);
window.addEventListener('mouseup', onUp);
return () => { window.removeEventListener('mousemove', onMove); window.removeEventListener('mouseup', onUp); };
}, [resize]);
// The order is: the named cell style, then the cell's own direct formatting,
// then conditional formatting on top of both. Colours resolve through the
// theme, so `fg: 'accent1'` follows whatever theme the workbook is on.
function cellStyleOf(r, c, cell, value, fmtColor) {
const base = sheetEffectiveStyle(cell, book);
const cond = sheetCondStyle(ws, r, c, value);
const s = cond ? Object.assign({}, base, cond) : base;
const numeric = typeof value === 'number';
const bdc = sheetColor(book, s.bdc) || '#9a9a9a';
const style = {
fontWeight: s.b ? 700 : 400,
fontStyle: s.i ? 'italic' : 'normal',
textDecoration: s.u ? 'underline' : undefined,
fontFamily: s.fn || bookDefaults.fontName,
fontSize: sheetPtToPx(s.fs || bookDefaults.fontSizePt) + 'px',
textAlign: s.a || (numeric ? 'right' : 'left'),
background: sheetColor(book, s.bg) || undefined,
// A red negative comes from the FORMAT, not from the cell's own colour,
// so it must not override a colour someone deliberately set.
color: sheetIsErr(value) ? '#8c2f2f' : (sheetColor(book, s.fg) || fmtColor || undefined),
};
if (s.bd === 'all') style.border = '1px solid ' + bdc;
else if (s.bd === 'outline') style.boxShadow = 'inset 0 0 0 1px ' + bdc;
else if (s.bd === 'bottom') style.borderBottom = '2px solid ' + bdc;
else if (s.bd === 'top') style.borderTop = '2px solid ' + bdc;
else if (s.bd === 'topDouble') style.borderTop = '3px double ' + bdc;
return style;
}
const activeConn = connAt ? connAt(sel.r, sel.c) : null;
const fillTo = drag && drag.kind === 'fill' ? drag.to : null;
return (
🔗{activeConn.label} — connected table
{activeConn.mode === 'snapshot' ? ', frozen as a snapshot' : ', refreshes from the live records'}
{activeConn.allowEdits ? '. Edits allowed.' : '. Read-only until you allow edits.'}
)}
);
}
// ── Connections ───────────────────────────────────────────────────────────
// A connected table remembers exactly what it wrote last time. That record is
// what makes Refresh able to say "these three cells changed at source, and you
// edited these two" BEFORE it overwrites anything — which is the difference
// between a live table and a table that eats your work.
const SHEET_WRITTEN_CAP = 6000;
function sheetConnCovers(conn, r, c) {
return r >= conn.anchor.r && r < conn.anchor.r + conn.rowCount + 1
&& c >= conn.anchor.c && c < conn.anchor.c + conn.colCount;
}
function sheetConnAt(body, wsId, r, c) {
return (body.connections || []).find(x => x.sheetId === wsId && sheetConnCovers(x, r, c)) || null;
}
function sheetConnWrites(conn, columns, rows) {
const writes = [];
const written = {};
const a = conn.anchor;
columns.forEach((col, i) => {
writes.push({ r: a.r, c: a.c + i, v: col.label, s: { b: true, bg: 'lt2' } });
});
rows.forEach((row, ri) => columns.forEach((col, ci) => {
const v = row[col.key] === undefined || row[col.key] === null ? '' : row[col.key];
// The column descriptor's `fmt` is a source-table vocabulary, not a cell
// style; it is translated into a real pattern here.
const s = col.fmt === 'currency' ? { nfp: '$#,##0.00' }
: col.fmt === 'date' ? { nfp: 'yyyy-mm-dd' }
: col.fmt === 'number' ? { nf: 3 } : null;
const w = { r: a.r + 1 + ri, c: a.c + ci, v };
if (s) w.s = s;
writes.push(w);
if (Object.keys(written).length < SHEET_WRITTEN_CAP) written[sheetKey(w.r, w.c)] = String(v);
}));
return { writes, written };
}
function sheetConnDiff(conn, columns, rows, ws) {
const a = conn.anchor;
const prev = conn.written || {};
const sourceChanged = [];
const userEdited = [];
rows.forEach((row, ri) => columns.forEach((col, ci) => {
const r = a.r + 1 + ri, c = a.c + ci;
const key = sheetKey(r, c);
const next = String(row[col.key] === undefined || row[col.key] === null ? '' : row[col.key]);
const was = key in prev ? prev[key] : null;
const cell = sheetCellAt(ws, r, c);
const now = cell === undefined || cell.v === undefined ? '' : String(cell.v);
if (was !== null && now !== was) userEdited.push({ r, c, was, now, label: col.label });
if (was !== null && next !== was) sourceChanged.push({ r, c, was, next, label: col.label });
}));
return {
sourceChanged, userEdited,
rowsBefore: conn.rowCount, rowsAfter: rows.length,
colsBefore: conn.colCount, colsAfter: columns.length,
capped: Object.keys(prev).length >= SHEET_WRITTEN_CAP,
};
}
// ── LEON Sheets ───────────────────────────────────────────────────────────
// `onChange(nextBody)` replaces doc.body; the shell above owns autosave,
// version history and the Saving/Saved indicator, so nothing here writes to
// ctx.officeDocs directly.
function OfficeSheetEditor({ ctx, doc, onChange, editable }) {
// A record saved before it had a body (or with the wrong app's body) gets one
// here — built ONCE and held, because rebuilding it every render would mint
// new sheet ids each pass and detach the selection from the sheet it is on.
const fallbackBody = useRef(null);
if (!fallbackBody.current) fallbackBody.current = makeSheetBody();
const body = (doc && doc.body && doc.body.sheets && doc.body.sheets.length) ? doc.body : fallbackBody.current;
const [activeWsId, setActiveWsId] = useState(body.sheets[0].id);
const [sel, setSel] = useState({ r: 0, c: 0, r2: 0, c2: 0 });
const [modal, setModal] = useState(null); // { kind, ...payload }
const [notice, setNotice] = useState(null);
const [fxText, setFxText] = useState(null);
const fxRef = useRef(null);
const undoStack = useRef([]);
const redoStack = useRef([]);
const engRef = useRef(null);
const lastBody = useRef(null);
const expected = useRef(null);
// Falls back to the first sheet if the active one was removed, so a stale
// id can never render nothing.
const ws = sheetWsById(body, activeWsId) || body.sheets[0];
if (!engRef.current || engRef.current.docId !== doc.id) {
engRef.current = { docId: doc.id, e: sheetMakeEngine(body) };
lastBody.current = null;
}
const eng = engRef.current.e;
if (lastBody.current !== body) {
eng.setBody(body);
// A body that did not come from a commit here (undo of a whole document,
// a restored version, a fresh import) invalidates everything — there is no
// way to know which cells moved.
if (expected.current !== body) eng.invalidateAll();
lastBody.current = body;
}
const wsId = ws.id;
const valueAt = useCallback((r, c) => eng.value(wsId, r, c), [eng, wsId, body]);
const hiddenRows = useMemo(() => sheetHiddenByFilter(ws, valueAt), [ws, valueAt]);
const connAt = useCallback((r, c) => {
const conn = sheetConnAt(body, wsId, r, c);
return conn ? Object.assign({}, conn, { label: conn.title }) : null;
}, [body, wsId]);
function commit(nextBody, touched, undoPatch) {
if (undoPatch) {
undoStack.current.push(undoPatch);
// 60 steps is a working session's worth. An unbounded stack is a slow
// memory leak in a tab that stays open all week.
if (undoStack.current.length > 60) undoStack.current.shift();
redoStack.current = [];
}
eng.setBody(nextBody);
(touched || []).forEach(t => eng.invalidate(undoPatch ? undoPatch.wsId : wsId, t.r, t.c));
expected.current = nextBody;
lastBody.current = nextBody;
onChange(nextBody);
}
// Walk up from the selected cell while the cells hold numbers, and sum that
// run. Stopping at the first gap is what makes it land on the bottom of a
// column of figures, which is where it is nearly always wanted.
function autoSum() {
if (!editable) return;
let top = sel.r;
while (top > 0) {
const v = eng.value(wsId, top - 1, sel.c);
if (typeof v !== 'number' || sheetIsErr(v)) break;
top -= 1;
}
if (top === sel.r) { setNotice('Nothing to add — AutoSum sums the run of numbers directly above the selected cell.'); return; }
const a = sheetA1(top, sel.c), b = sheetA1(sel.r - 1, sel.c);
writeCells([{ r: sel.r, c: sel.c, v: `=SUM(${a}:${b})` }]);
setFxText(null);
}
// Puts `=NAME(` in the cell and leaves the formula bar focused with the
// caret inside the brackets, which is where the arguments go.
function insertFunction(name) {
if (!editable || !name) return;
const text = `=${name}(`;
setFxText(text);
// Focused on the next frame, after the controlled input has taken the value.
setTimeout(() => {
const el = fxRef.current;
if (!el) return;
el.focus();
try { el.setSelectionRange(text.length, text.length); } catch (e) { /* not all inputs support it */ }
}, 0);
}
function writeCells(writes, targetWsId) {
if (!editable) return;
const id = targetWsId || wsId;
const res = sheetWriteCells(body, id, writes);
commit(res.body, res.touched, res.undo);
}
// Sheet metadata only — widths, freeze, merges, filters, rules. None of it
// changes a value, so the evaluation cache is deliberately left alone:
// clearing it on every column drag would re-evaluate the workbook for nothing.
function patchWs(fields, targetWsId) {
if (!editable) return;
const next = sheetPatchWs(body, targetWsId || wsId, fields);
eng.setBody(next);
expected.current = next;
lastBody.current = next;
onChange(next);
}
// Workbook structure — the sheet list, connections, charts. Adding or
// removing a sheet DOES change what a cross-sheet formula resolves to, so
// this one clears the cache.
function patchBody(fields) {
const next = Object.assign({}, body, fields);
eng.setBody(next);
eng.invalidateAll();
expected.current = next;
lastBody.current = next;
onChange(next);
}
function undo() {
const patch = undoStack.current.pop();
if (!patch) return;
const res = sheetRestoreCells(body, patch);
redoStack.current.push(res.undo);
eng.setBody(res.body);
res.touched.forEach(t => eng.invalidate(patch.wsId, t.r, t.c));
expected.current = res.body;
lastBody.current = res.body;
onChange(res.body);
}
function redo() {
const patch = redoStack.current.pop();
if (!patch) return;
const res = sheetRestoreCells(body, patch);
undoStack.current.push(res.undo);
eng.setBody(res.body);
res.touched.forEach(t => eng.invalidate(patch.wsId, t.r, t.c));
expected.current = res.body;
lastBody.current = res.body;
onChange(res.body);
}
function onGridEdit(writes) {
if (writes === '__undo') return undo();
if (writes === '__redo') return redo();
writeCells(writes);
}
function onFill(range, to) {
const writes = [];
if (to.r > range.r2) {
const count = to.r - range.r2;
for (let c = range.c1; c <= range.c2; c++) {
const seed = [];
for (let r = range.r1; r <= range.r2; r++) seed.push(sheetRaw(ws, r, c));
const series = sheetFillSeries(seed, count, 1, 0);
series.forEach((v, i) => {
const src = sheetCellAt(ws, range.r1 + (i % seed.length), c);
writes.push({ r: range.r2 + 1 + i, c, v, s: (src && src.s) || null });
});
}
} else if (to.c > range.c2) {
const count = to.c - range.c2;
for (let r = range.r1; r <= range.r2; r++) {
const seed = [];
for (let c = range.c1; c <= range.c2; c++) seed.push(sheetRaw(ws, r, c));
const series = sheetFillSeries(seed, count, 0, 1);
series.forEach((v, i) => {
const src = sheetCellAt(ws, r, range.c1 + (i % seed.length));
writes.push({ r, c: range.c2 + 1 + i, v, s: (src && src.s) || null });
});
}
}
if (writes.length) writeCells(writes);
}
const range = sheetNormRange(sel);
function styleSelection(s) {
const writes = [];
for (let r = range.r1; r <= range.r2; r++) for (let c = range.c1; c <= range.c2; c++) writes.push({ r, c, s });
writeCells(writes);
}
function toggleStyle(key) {
// Read through the named style, so B on a cell that is already bold because
// of its style turns it OFF rather than setting a flag that changes nothing.
const on = !!sheetEffectiveStyle(sheetCellAt(ws, sel.r, sel.c), body)[key];
const s = {};
s[key] = on ? undefined : true;
styleSelection(s);
}
// Decimals are added to and taken off the PATTERN, per cell, so the buttons
// work on a custom format someone typed as well as on a built-in — and a cell
// still carrying a legacy mode is converted to its equivalent pattern the
// first time it is touched rather than by a migration.
function bumpDecimals(delta) {
const writes = [];
for (let r = range.r1; r <= range.r2; r++) for (let c = range.c1; c <= range.c2; c++) {
const cell = sheetCellAt(ws, r, c);
const next = sheetBumpPatternDecimals(sheetCellPattern(cell, body), delta);
writes.push({ r, c, s: sheetPatternStylePatch(next) });
}
writeCells(writes);
}
function applyNumberFormat(pattern) { styleSelection(sheetPatternStylePatch(pattern)); }
// A named style is stored as its ID. Redefining the style then restyles every
// cell using it, and the title styles follow the workbook's theme — which is
// the only reason named styles are worth having over copying formatting.
function applyCellStyle(id) {
styleSelection(id === 'Normal'
? { st: undefined, b: undefined, i: undefined, u: undefined, fs: undefined, fg: undefined, bg: undefined, bd: undefined, bdc: undefined }
: { st: id });
}
function saveCellStyleFromSelection(name) {
const trimmed = String(name || '').trim();
if (!trimmed) return;
const cur = sheetCellAt(ws, sel.r, sel.c);
const own = Object.assign({}, (cur && cur.s) || {});
delete own.st;
const id = 'user-' + uid('cs');
patchBody({ cellStyles: ((body.cellStyles) || []).concat([{ id, name: trimmed, group: 'This workbook', custom: true, s: own }]) });
setNotice('Saved “' + trimmed + '” as a cell style. Every cell you apply it to follows it — change the style and they all change.');
}
function removeCellStyle(id) {
patchBody({ cellStyles: ((body.cellStyles) || []).filter(x => x.id !== id) });
setNotice('Style removed. Cells that used it fall back to plain formatting; nothing they contain was touched.');
}
// Applying a theme repaints every slot-referenced fill, font colour and
// border in the workbook at once, so the workbook as it stands is snapshotted
// first — this is the one formatting action that is not a single-cell undo.
function applyTheme(theme, alsoFont) {
const nextBody = Object.assign({}, body, { theme });
if (alsoFont) nextBody.defaults = Object.assign({}, sheetBookDefaults(body), { fontName: theme.minorFont });
const cap = officeHomeCaptureVersion(Object.assign({}, doc, { body }),
'Saved automatically before applying the “' + theme.name + '” theme', ctx.currentUserName);
eng.setBody(nextBody);
expected.current = nextBody;
lastBody.current = nextBody;
onChange({ body: nextBody, versions: cap.versions, revision: cap.n });
setNotice('Theme set to “' + theme.name + '”. The workbook as it stood was kept as version ' + cap.n + '.');
}
function setBookDefaults(fields) { patchBody({ defaults: Object.assign({}, sheetBookDefaults(body), fields) }); }
// Excel measures a column in characters and a row in points; both are offered
// in those units because that is what a person copying a layout out of Excel
// has written down.
function setColWidthChars(chars) {
const map = Object.assign({}, ws.cols);
for (let c = range.c1; c <= range.c2; c++) map[c] = Object.assign({}, map[c] || {}, { wch: chars, w: undefined });
patchWs({ cols: map });
}
function setRowHeightPt(pt) {
const map = Object.assign({}, ws.rows);
for (let r = range.r1; r <= range.r2; r++) map[r] = Object.assign({}, map[r] || {}, { hpt: pt, h: undefined });
patchWs({ rows: map });
}
function patchPage(fields) { patchWs({ page: Object.assign({}, sheetPage(ws), fields) }); }
function mergeSelection() {
if (range.r1 === range.r2 && range.c1 === range.c2) { setNotice('Select more than one cell to merge.'); return; }
const merges = (ws.merges || []).filter(m => m.r2 < range.r1 || m.r1 > range.r2 || m.c2 < range.c1 || m.c1 > range.c2);
merges.push({ r1: range.r1, c1: range.c1, r2: range.r2, c2: range.c2 });
patchWs({ merges });
}
function unmergeSelection() {
patchWs({ merges: (ws.merges || []).filter(m => m.r2 < range.r1 || m.r1 > range.r2 || m.c2 < range.c1 || m.c1 > range.c2) });
}
function setHidden(axis, hidden) {
const map = Object.assign({}, axis === 'row' ? ws.rows : ws.cols);
const from = axis === 'row' ? range.r1 : range.c1;
const to = axis === 'row' ? range.r2 : range.c2;
for (let i = from; i <= to; i++) map[i] = Object.assign({}, map[i] || {}, { hidden });
patchWs(axis === 'row' ? { rows: map } : { cols: map });
}
// ── Sheet tabs ──────────────────────────────────────────────────────────
function addSheet() {
const n = body.sheets.length + 1;
let name = 'Sheet ' + n, i = n;
while (sheetWsByName(body, name)) { i++; name = 'Sheet ' + i; }
const nextWs = { id: uid('ws'), name, cells: {}, cols: {}, rows: {}, merges: [], frozen: { r: 0, c: 0 }, condFormats: [], validations: [], filter: null };
patchBody({ sheets: body.sheets.concat([nextWs]) });
setActiveWsId(nextWs.id);
}
function renameSheet(id, name) {
const trimmed = String(name || '').trim();
if (!trimmed) return;
const clash = body.sheets.find(s => s.id !== id && String(s.name).toLowerCase() === trimmed.toLowerCase());
if (clash) { setNotice('Another sheet is already called “' + trimmed + '”.'); return; }
// Formulas reference a sheet BY NAME, so a rename has to rewrite them or
// every cross-sheet formula breaks silently.
const old = sheetWsById(body, id);
const sheets = body.sheets.map(s => {
const renamed = s.id === id ? Object.assign({}, s, { name: trimmed }) : s;
const cells = {};
let changed = false;
Object.keys(renamed.cells || {}).forEach(k => {
const cell = renamed.cells[k];
if (!sheetIsFormula(cell.v) || !old || cell.v.toLowerCase().indexOf(String(old.name).toLowerCase()) < 0) { cells[k] = cell; return; }
const rewritten = sheetRenameSheetInFormula(cell.v, old.name, trimmed);
if (rewritten !== cell.v) changed = true;
cells[k] = Object.assign({}, cell, { v: rewritten });
});
return changed ? Object.assign({}, renamed, { cells }) : renamed;
});
patchBody({ sheets });
eng.invalidateAll();
}
function removeSheet(id) {
if (body.sheets.length < 2) { setNotice('A workbook keeps at least one sheet.'); return; }
const sheets = body.sheets.filter(s => s.id !== id);
patchBody({
sheets,
connections: (body.connections || []).filter(x => x.sheetId !== id),
charts: (body.charts || []).filter(x => x.sheetId !== id),
});
if (activeWsId === id) setActiveWsId(sheets[0].id);
eng.invalidateAll();
}
// ── Connected tables ────────────────────────────────────────────────────
function insertLeonTable(sourceKey, opt, mode, title) {
const built = sheetBuildTable(ctx, sourceKey, opt);
if (!built) return;
const conn = {
id: uid('conn'), sheetId: wsId, source: sourceKey, title: title || built.src.label,
projectId: (opt && opt.projectId) || null, scopeId: (opt && opt.scopeId) || null,
anchor: { r: sel.r, c: sel.c },
columns: built.columns.map(c => c.key), columnLabels: built.columns.map(c => c.label),
colCount: built.columns.length, rowCount: built.rows.length,
mode: mode || 'live', allowEdits: false,
lastRefresh: todayISO(), refreshedBy: ctx.currentUserName || '',
written: {},
};
const { writes, written } = sheetConnWrites(conn, built.columns, built.rows);
conn.written = written;
const res = sheetWriteCells(body, wsId, writes);
const next = Object.assign({}, res.body, { connections: (body.connections || []).concat([conn]) });
commit(next, res.touched, res.undo);
setModal(null);
setNotice(built.rows.length + ' row' + (built.rows.length === 1 ? '' : 's') + ' inserted from ' + built.src.label + '.');
}
function applyRefresh(conn, keepEdits) {
const built = sheetBuildTable(ctx, conn.source, { projectId: conn.projectId, scopeId: conn.scopeId });
if (!built) return;
const nextConn = Object.assign({}, conn, {
colCount: built.columns.length, rowCount: built.rows.length,
columns: built.columns.map(c => c.key), columnLabels: built.columns.map(c => c.label),
lastRefresh: todayISO(), refreshedBy: ctx.currentUserName || '',
});
const { writes, written } = sheetConnWrites(nextConn, built.columns, built.rows);
const prev = conn.written || {};
const kept = keepEdits
? writes.filter(w => {
const key = sheetKey(w.r, w.c);
if (!(key in prev)) return true;
const cell = sheetCellAt(ws, w.r, w.c);
const now = cell === undefined || cell.v === undefined ? '' : String(cell.v);
return now === prev[key]; // untouched since the last refresh
})
: writes;
// Rows the source dropped are cleared rather than left behind pretending
// to be current.
const clears = [];
for (let r = nextConn.anchor.r + 1 + built.rows.length; r < conn.anchor.r + 1 + conn.rowCount; r++) {
for (let c = conn.anchor.c; c < conn.anchor.c + conn.colCount; c++) clears.push({ r, c, clear: true });
}
nextConn.written = written;
const res = sheetWriteCells(body, conn.sheetId, kept.concat(clears));
const next = Object.assign({}, res.body, {
connections: (body.connections || []).map(x => (x.id === conn.id ? nextConn : x)),
});
commit(next, res.touched, res.undo);
setModal(null);
setNotice('Refreshed “' + conn.title + '” — ' + built.rows.length + ' row' + (built.rows.length === 1 ? '' : 's') + ' now.');
}
function updateConn(id, fields) {
patchBody({ connections: (body.connections || []).map(x => (x.id === id ? Object.assign({}, x, fields) : x)) });
}
function removeConn(id) {
// The cells stay. Disconnecting is "stop refreshing this", not "delete what
// it produced" — the numbers may already be in an issued report.
patchBody({ connections: (body.connections || []).filter(x => x.id !== id) });
setNotice('Disconnected. The cells it wrote are still here and are now ordinary data.');
}
// ── Export ──────────────────────────────────────────────────────────────
function exportWorkbook(kind) {
if (!sheetXlsxAvailable()) { setNotice('The Excel library did not load — reload the page and try again.'); return; }
const name = safeFileName(doc.name || 'LEON Sheet');
if (kind === 'csv') {
// CSV goes out FORMATTED — a date serial or a raw 0.185 in a text file
// is not what anyone opening it wants. XLSX below keeps real numbers,
// because there the format travels with them.
const used = sheetUsedRange(ws);
const aoa = [];
for (let r = 0; r <= Math.max(0, used.r2); r++) {
const line = [];
for (let c = 0; c <= Math.max(0, used.c2); c++) line.push(sheetDisplay(sheetCellAt(ws, r, c), valueAt(r, c), body));
aoa.push(line);
}
const csv = aoa.map(line => line.map(sheetCsvCell).join(',')).join('\r\n');
sheetDownloadBlob(name + ' — ' + ws.name + '.csv', new Blob([csv], { type: 'text/csv;charset=utf-8;' }));
return;
}
// Cell objects, not an array of arrays — an aoa cannot carry a number
// format, and dropping every format to General on the way out is exactly
// the dishonesty this pass was meant to remove.
const wb = XLSX.utils.book_new();
body.sheets.forEach(s => {
const at = (r, c) => eng.value(s.id, r, c);
XLSX.utils.book_append_sheet(wb, sheetToXlsxSheet(s, body, at), String(s.name).slice(0, 31));
});
XLSX.writeFile(wb, name + '.xlsx');
}
const activeCell = sheetCellAt(ws, sel.r, sel.c);
const activeValue = valueAt(sel.r, sel.c);
const fx = fxText === null ? sheetEditText(activeCell, body) : fxText;
const stats = eng.stats();
const activeStyle = sheetEffectiveStyle(activeCell, body);
const activePattern = sheetStylePattern(activeStyle);
const patternIsListed = SHEET_NUMBER_FORMATS.some(f => f.pattern === activePattern)
|| SHEET_NF_PRESETS.some(f => f.pattern === activePattern);
const bookDef = sheetBookDefaults(body);
const theme = sheetBookTheme(body);
const cellStyleGroups = [];
sheetAllCellStyles(body).forEach(st => {
let g = cellStyleGroups.find(x => x.name === st.group);
if (!g) { g = { name: st.group, items: [] }; cellStyleGroups.push(g); }
g.items.push(st);
});
return (
{/* The ribbon. Excel's own tab set, and the controls moved into it
rather than rewritten — thirty-five of them in two undifferentiated
rows is what a ribbon exists to fix. */}
},
{ label: 'Font', items: <>
toggleStyle('b')} disabled={!editable} />
toggleStyle('i')} disabled={!editable} />
toggleStyle('u')} disabled={!editable} />
},
{ label: 'Alignment', items: <>
styleSelection({ a: 'left' })} disabled={!editable} />
styleSelection({ a: 'center' })} disabled={!editable} />
styleSelection({ a: 'right' })} disabled={!editable} />
},
{ label: 'Number', items: <>
setModal({ kind: 'numfmt', pattern: activePattern })} />
bumpDecimals(-1)} disabled={!editable} />
bumpDecimals(1)} disabled={!editable} />
},
{ label: 'Styles', items: <>
{((body.cellStyles) || []).length > 0 && (
)}
},
{ label: 'Cells', items: <>
{/* Fills are THEME SLOTS, not hex — which is what makes "apply a
theme" repaint a workbook instead of doing nothing. */}
{['accent1/88', 'accent2/88', 'accent3/88', 'accent4/88', 'lt2', ''].map(slot => (
},
] },
{ key: 'insert', label: 'Insert', groups: [
{ label: 'LEON data', items: <>
},
{ label: 'Charts', items: <>
},
] },
{ key: 'formulas', label: 'Formulas', groups: [
{ label: 'Function library', items: <>
},
] },
{ key: 'data', label: 'Data', groups: [
{ label: 'Sort & filter', items: <>
},
{ label: 'Data tools', items: <>
},
{ label: 'Get & save', items: <>
},
] },
{ key: 'view', label: 'View', groups: [
{ label: 'Window', items: <>
{(ws.frozen && (ws.frozen.r || ws.frozen.c)) ? (
) : null}
},
{ label: 'Workbook', items: <>
},
] },
]} />
{/* Formula bar */}
{range.r1 === range.r2 && range.c1 === range.c2 ? sheetA1(sel.r, sel.c) : sheetRangeA1(range.r1, range.c1, range.r2, range.c2)}
{/* This was a static label. Anyone coming from Excel clicks it expecting
a function browser, gets nothing, and reports that the formula
button does not work — which is exactly what happened. */}
setFxText(e.target.value)}
onBlur={() => {
if (fxText === null) return;
const asText = sheetIsTextPattern(activePattern);
const v = fxText.length > 1 && fxText[0] === "'" ? fxText
: asText ? sheetTextInput(fxText)
: sheetCoerceInput(fxText);
writeCells([{ r: sel.r, c: sel.c, v }]);
setFxText(null);
}}
onKeyDown={e => { if (e.key === 'Enter') e.target.blur(); if (e.key === 'Escape') setFxText(null); }}
className="!py-1 !text-xs font-mono" />
{sheetIsErr(activeValue) ? {activeValue} : sheetDisplay(activeCell, activeValue, body) || '—'}
{notice && (
{notice} setNotice(null)}>✕
)}
{
setSel(s); setFxText(null);
// Published for the shell's comment composer, which offers "the cell
// I am on" without the editor needing a prop the fixed signature
// does not have. Same module-level registry pattern the app already
// uses for role permissions and supplier overrides.
officeSetAnchorHint(ws.name + '!' + sheetA1(s.r, s.c));
}}
onEdit={onGridEdit}
onFill={onFill}
onResizeCol={(c, w) => patchWs({ cols: Object.assign({}, ws.cols, { [c]: Object.assign({}, (ws.cols || {})[c] || {}, { w }) }) })}
onResizeRow={(r, h) => patchWs({ rows: Object.assign({}, ws.rows, { [r]: Object.assign({}, (ws.rows || {})[r] || {}, { h }) }) })}
hiddenRows={hiddenRows}
connAt={connAt}
onOpenCellMenu={(kind, conn) => { if (kind === 'connected') setNotice('“' + conn.title + '” is a connected table and is read-only. Open Connected tables below to allow edits or freeze it as a snapshot.'); }}
height={520} />
{/* Sheet tabs */}
)}
{/* Connected tables */}
{/* The print layout is a real section, not a hidden one: the 🖨 and 📄
buttons on its own header act on exactly what is drawn here, so what
you see is what comes out. */}
{(() => { const p = sheetPage(ws); return (SHEET_PAPER_SIZES.find(x => x.key === p.paper) || {}).key + ' · ' + p.orientation + (p.repeatRows ? ' · rows ' + (p.repeatRows.from + 1) + '–' + (p.repeatRows.to + 1) + ' repeat' : ''); })()}
}
printLines={[doc.name || '', ws.name]}>
setModal({ kind: 'refresh', conn })}
onUpdate={updateConn} onRemove={removeConn} />
Applies to columns {sheetColLabel(range.c1)}–{sheetColLabel(range.c2)}. Dragging a column border still sets pixels; typing a number here sets characters.
);
}
// ── Custom number format ──────────────────────────────────────────────────
// The id/pattern split only earns its keep if a person can write a pattern, so
// this is a pattern editor with a live sample rather than a longer list of
// modes. Everything it claims to support is tested against the same formatter
// the grid uses, so the preview cannot promise something the cell will not do.
function SheetNumberFormatModal({ pattern, sample, onClose, onApply }) {
const [p, setP] = useState(String(pattern || 'General'));
const samples = [
typeof sample === 'number' ? sample : 1234.567,
-1234.567, 0, 0.185, 46266, // 46266 is 2026-09-05 as a serial
];
return (
}>
setP(e.target.value)} className="font-mono" />
Digits — 0 always shows, # shows only if there is one, ? holds the column with a space. , between digits groups thousands; after the last digit it divides by a thousand. . is the decimal point, % multiplies by 100.
Dates — d dd ddd dddd, m mm mmm mmmm, yy yyyy, h hh, mm (minutes, when it sits beside an hour or a second), ss, AM/PM, and [h] for elapsed hours.
The rest — @ is the text, $ and "any words" print literally, \ escapes one character, _x is a space, *x splits the cell so what follows sits at the right edge (that is what Accounting does), [Red] colours a section, and 0.00E+00 is scientific.
Not supported: locale codes like [$-409] and conditional sections like [>100] — both are read and ignored rather than guessed at.
Formatting a cell as Text (@) also changes how it accepts what you type: 007 stays 007 and 1-2 stays 1-2.
A fill, a font colour or a border here can reference a theme slot rather than a fixed colour — which is
the only reason changing the theme changes anything. Cells given a literal colour keep it, deliberately.
{SHEET_THEMES.map(t => (
))}
The workbook as it stands is saved as a version first, because this is the one formatting action that is not a single-cell undo.
A blank workbook made in Excel declares Aptos Narrow 12pt, a row height of 16 points and a base column
width of 10 characters. Those are the starting defaults here; every cell that has not been given a font
or a size of its own follows them.
Page numbers are not offered here: a page in a browser cannot count itself. Your print dialog’s own
“Headers and footers” option adds them. None of this reaches an exported .xlsx except the margins —
the Export panel says exactly what does and does not travel.
);
}
// ── Export ────────────────────────────────────────────────────────────────
// Said before the download, not after. We are matching Excel's MODEL; SheetJS
// handles the FORMAT, and it is worth being exact about the difference.
function SheetExportModal({ onClose, onExport }) {
return (
}>
LEON Sheets follows Excel’s model — its number formats, its theme slots, its named cell styles, its page
setup. The file format is written by SheetJS in this browser, and it
carries a subset of that. This is what survives and what does not:
Carried into the file
{SHEET_XLSX_CARRIES.map(x =>
{x}
)}
Not carried
{SHEET_XLSX_DROPS.map(x =>
{x}
)}
A .csv carries the formatted text of this sheet only — a date serial or a raw 0.185 in a text file
is not what anyone opening it wants. For a copy that keeps the look, use the Print layout section’s 📄.
);
}
// Rewrites `Old!A1` to `New!A1` across one formula. Done on the token stream,
// so a sheet name that also appears inside a string literal is left alone.
function sheetRenameSheetInFormula(formula, oldName, newName) {
if (!sheetIsFormula(formula)) return formula;
const src = formula.slice(1);
const toks = sheetTokenize(src);
const lower = String(oldName).toLowerCase();
const q = n => (/[^A-Za-z0-9_.]/.test(n) ? "'" + String(n).replace(/'/g, "''") + "'" : n);
let out = '', last = 0, hit = false;
toks.forEach(t => {
if (t.t !== 'ref') return;
const s1 = t.sheet && String(t.sheet).toLowerCase() === lower;
const s2 = t.sheet2 && String(t.sheet2).toLowerCase() === lower;
if (!s1 && !s2) return;
hit = true;
out += src.slice(last, t.p);
const a = (t.absC ? '$' : '') + sheetColLabel(t.col) + (t.absR ? '$' : '') + (t.row + 1);
out += (t.sheet ? q(s1 ? newName : t.sheet) + '!' : '') + a;
if (t.has2) {
const b = (t.absC2 ? '$' : '') + sheetColLabel(t.col2) + (t.absR2 ? '$' : '') + (t.row2 + 1);
out += ':' + (t.sheet2 ? q(s2 ? newName : t.sheet2) + '!' : '') + b;
}
last = t.p + t.len;
});
if (!hit) return formula;
out += src.slice(last);
return '=' + out;
}
// ── Connected tables panel ────────────────────────────────────────────────
function SheetConnectionsPanel({ body, ws, editable, onRefresh, onUpdate, onRemove }) {
const conns = body.connections || [];
if (!conns.length) {
return (
);
}
return (
The table keeps its source. Refresh re-reads the live records and shows
what changed — including anything you edited — before it writes. It lands at {sheetA1(anchor.r, anchor.c)}.
Formulas move with their rows but their references are not re-pointed — the same caveat every spreadsheet
carries. Blank cells always sort to the bottom, in both directions.
A failing cell is marked with a red corner and explains itself on hover. Entry is flagged, not blocked —
a rule someone added last month should not stop today’s work.
Built from {sheetRangeA1(range.r1, range.c1, range.r2, range.c2)} — the first column is the labels,
every column after it is a series. Drawn as SVG, so it prints and scales like the rest of the app.
setTitle(e.target.value)} placeholder="Cost by scope" />
);
}
// ── Import ────────────────────────────────────────────────────────────────
function SheetImportModal({ onClose, onApply }) {
const [wb, setWb] = useState(null);
const [names, setNames] = useState([]);
const [pick, setPick] = useState('');
const [skip, setSkip] = useState(0);
const [target, setTarget] = useState('new');
const [err, setErr] = useState('');
function onFile(e) {
const file = e.target.files && e.target.files[0];
if (!file) return;
if (!sheetXlsxAvailable()) { setErr('The Excel library did not load — reload the page and try again.'); return; }
const reader = new FileReader();
reader.onload = () => {
try {
// cellNF asks SheetJS to keep each cell's number format on `z`. Without
// it every imported figure arrives as General and the file's own
// formatting is silently thrown away on the way in.
const book = XLSX.read(new Uint8Array(reader.result), { type: 'array', cellNF: true, cellStyles: true });
setWb(book);
setNames(book.SheetNames || []);
setPick((book.SheetNames || [])[0] || '');
setErr('');
} catch (ex) {
setErr('That file could not be read as a spreadsheet. .xlsx, .xls and .csv are supported.');
}
};
reader.onerror = () => setErr('The file could not be read.');
reader.readAsArrayBuffer(file);
}
// Read cell by cell rather than through sheet_to_json: that helper returns
// values only, and the number format lives on the cell object.
const grid = useMemo(() => {
if (!wb || !pick) return [];
const sh = wb.Sheets[pick];
if (!sh || !sh['!ref']) return [];
const rng = XLSX.utils.decode_range(sh['!ref']);
const out = [];
for (let r = rng.s.r; r <= rng.e.r; r++) {
const line = [];
let any = false;
for (let c = rng.s.c; c <= rng.e.c; c++) {
const cell = sh[XLSX.utils.encode_cell({ r, c })];
if (!cell) { line.push(null); continue; }
any = true;
line.push({ v: cell.v, t: cell.t, z: cell.z, f: cell.f, w: cell.w });
}
if (any) out.push(line); // blank rows are dropped, as before
}
return out.slice(skip);
}, [wb, pick, skip]);
const colMeta = useMemo(() => {
if (!wb || !pick) return null;
const sh = wb.Sheets[pick];
return sh && sh['!cols'] ? sh['!cols'] : null;
}, [wb, pick]);
const formatted = useMemo(() => grid.reduce((n, line) => n + line.filter(e => e && e.z && !/^general$/i.test(String(e.z))).length, 0), [grid]);
return (
}>
{err &&
{formatted
? formatted.toLocaleString() + ' cell' + (formatted === 1 ? '' : 's') + ' carry a number format in the file — currency, dates, percentages — and those come across with the values.'
: 'No cell in this sheet carries a number format of its own, so everything arrives as General.'}
)}
A cell that arrives looking like a formula is kept as text, and a formula’s cached result is imported as a
plain value. Executing a formula out of someone else’s file would let that file rewrite this one the moment
it opened. Fonts, fills, borders and conditional formatting are not read.
);
}
// ═══════════════════════════════════════════════════ Office Home
// The shell every document opens from. It owns the things all four apps share
// — the list, links, autosave, version history, comments and status — so Word,
// Sheets, Presentation and PDF do not each grow their own version of them.
// Where the editors publish "the thing the cursor is on", so a comment can be
// anchored without changing the fixed editor signature. Module-level registry,
// the same pattern App() uses for role permissions and supplier overrides.
let officeAnchorHint = null;
function officeSetAnchorHint(a) { officeAnchorHint = a; }
function officeActiveAnchorHint() { return officeAnchorHint; }
const OFFICE_HOME_VIEWS = [
{ key: 'recent', label: 'Recent', icon: '🕒' },
{ key: 'favourites', label: 'Favourites', icon: '★' },
{ key: 'templates', label: 'Templates', icon: '🧩' },
{ key: 'project', label: 'Project Files', icon: '🏗️' },
{ key: 'mine', label: 'My Files', icon: '👤' },
{ key: 'archived', label: 'Archived', icon: '🗄️' },
];
// Twelve is what fits. Each snapshot is a whole body, and the app's persisted
// state has about 13 MB in TOTAL — an unbounded history would take the rest of
// the Hub down with it. The oldest is dropped, and the drop is announced in the
// document's own activity rather than happening quietly.
const OFFICE_VERSION_LIMIT = 12;
function officeHomeAppMeta(key) { return OFFICE_APPS.find(a => a.key === key) || OFFICE_APPS[0]; }
function officeHomeLogActivity(doc, text, by) {
const entry = { id: uid('act'), date: todayISO(), by: by || '', text };
const activity = [entry].concat(doc.activity || []).slice(0, 80);
return activity;
}
function officeHomeTouch(doc, ctx, text) {
return Object.assign({}, doc, {
modifiedDate: todayISO(), modifiedBy: ctx.currentUserName || '',
activity: text ? officeHomeLogActivity(doc, text, ctx.currentUserName) : doc.activity,
});
}
// A rough size for one record, used only to keep the storage line on the home
// screen honest. A PDF record is small by design: its bytes live in IndexedDB
// and the record holds an asset id.
function officeHomeBytes(doc) {
try { return JSON.stringify(doc).length; } catch (e) { return 0; }
}
function officeHomeFmtBytes(n) {
if (n < 1024) return n + ' B';
if (n < 1024 * 1024) return (n / 1024).toFixed(0) + ' KB';
return (n / 1024 / 1024).toFixed(1) + ' MB';
}
// ── Version snapshots ─────────────────────────────────────────────────────
function officeHomeCaptureVersion(doc, note, by) {
const n = (doc.versions || []).length ? Math.max.apply(null, doc.versions.map(v => v.n)) + 1 : 1;
const snapshot = { n, date: todayISO(), by: by || '', note: note || '', body: cloneDeep(doc.body) };
let versions = [snapshot].concat(doc.versions || []);
let dropped = null;
if (versions.length > OFFICE_VERSION_LIMIT) {
dropped = versions[versions.length - 1];
versions = versions.slice(0, OFFICE_VERSION_LIMIT);
}
return { versions, n, dropped };
}
// One flattening for all four bodies, so the diff below does not need to know
// which app it is looking at.
function officeHomeFlattenBody(app, body) {
const out = [];
if (!body) return out;
if (app === 'sheet') {
(body.sheets || []).forEach(s => {
Object.keys(s.cells || {}).sort().forEach(k => {
const p = sheetParseKey(k);
const cell = s.cells[k];
if (cell === undefined || cell.v === undefined || cell.v === '') return;
out.push({ path: s.name + '!' + sheetA1(p.r, p.c), text: String(cell.v) });
});
});
(body.connections || []).forEach(c => out.push({ path: 'Connected: ' + c.title, text: c.source + ' · ' + c.rowCount + ' rows · ' + c.mode }));
(body.charts || []).forEach(c => out.push({ path: 'Chart: ' + (c.title || c.id), text: c.kind + ' ' + (c.range || '') }));
return out;
}
if (app === 'word') {
(body.blocks || []).forEach((b, i) => out.push({ path: 'Block ' + (i + 1) + (b.kind ? ' (' + b.kind + ')' : ''), text: String(b.text || b.content || JSON.stringify(b)) }));
if (body.header) out.push({ path: 'Header', text: String(body.header) });
if (body.footer) out.push({ path: 'Footer', text: String(body.footer) });
return out;
}
if (app === 'slides') {
(body.slides || []).forEach((s, i) => {
out.push({ path: 'Slide ' + (i + 1), text: String(s.title || s.layout || '') });
(s.elements || []).forEach((el, j) => out.push({ path: 'Slide ' + (i + 1) + ' · element ' + (j + 1), text: String(el.text || el.kind || '') }));
});
return out;
}
if (app === 'pdf') {
out.push({ path: 'Pages', text: String(body.pageCount || (body.pages || []).length) });
out.push({ path: 'Page order', text: (body.pages || []).map(p => p.label || p.index).join(', ') });
['annotations', 'bookmarks', 'fields', 'signatures', 'redactions'].forEach(k => {
out.push({ path: k.charAt(0).toUpperCase() + k.slice(1), text: String((body[k] || []).length) });
});
out.push({ path: 'Status', text: [body.ocrStatus, body.formStatus, body.signatureStatus].filter(Boolean).join(' · ') });
return out;
}
return out;
}
function officeHomeVersionDiff(app, aBody, bBody) {
const a = officeHomeFlattenBody(app, aBody);
const b = officeHomeFlattenBody(app, bBody);
const ma = new Map(a.map(x => [x.path, x.text]));
const mb = new Map(b.map(x => [x.path, x.text]));
const added = [], removed = [], changed = [];
mb.forEach((text, path) => {
if (!ma.has(path)) added.push({ path, text });
else if (ma.get(path) !== text) changed.push({ path, from: ma.get(path), to: text });
});
ma.forEach((text, path) => { if (!mb.has(path)) removed.push({ path, text }); });
return { added, removed, changed };
}
// ── The editor router ─────────────────────────────────────────────────────
// Word, Presentation and PDF live in sibling files built by other people. A
// missing file must degrade to a sentence, not a blank screen — hence the
// typeof guard on each.
function OfficeHomeEditorMissing({ app }) {
const meta = officeHomeAppMeta(app);
return (
{meta.icon}
{meta.label} is not loaded
The document itself is safe — this is only the editor. Its file has not loaded in this browser;
reload the page, and if it still says this, the module has not been added to the page yet.
);
}
function OfficeHomeEditorFor({ ctx, doc, onChange, editable }) {
if (doc.app === 'sheet') return ;
if (doc.app === 'word') {
return typeof OfficeWordEditor === 'function'
?
: ;
}
if (doc.app === 'slides') {
return typeof OfficeSlidesEditor === 'function'
?
: ;
}
if (doc.app === 'pdf') {
return typeof OfficePdfEditor === 'function'
?
: ;
}
return ;
}
// ── Links ─────────────────────────────────────────────────────────────────
// Every one of these is an id pointing at a record that already exists. A
// document that "knows which job it belongs to" is the whole reason Office is
// inside the Hub instead of beside it.
function OfficeHomeLinks({ ctx, doc, onChange, editable }) {
const projects = ctx.deptProjects(ctx.projects || []);
const project = (ctx.projects || []).find(p => p.id === doc.projectId) || null;
const scopes = project ? (project.scopes || []) : [];
const units = project ? (project.units || []) : [];
const rooms = project ? (project.rooms || []) : [];
return (