Spreadsheet
Qwen 3.8 · full

Reviews & generation details
Reviews
No human review yet. Unreviewed does not mean unsuccessful.
Generation context
Imported historical artifact. Its exact original instructions and lineage are not known.
Generation prompt · exact text unavailable
See the current prompt for reference. It may differ from the instructions used to generate this artifact.
Inspect original source 36,153 bytes · SHA-256 5bb5b7020e64
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Sheet — Vanilla JS Spreadsheet</title>
<style>
:root {
--cell-w: 96px; --cell-h: 28px; --header-w: 48px;
--blue: #1a73e8; --blue-light: #e8f0fe;
--border: #e0e4e8; --header-bg: #f8f9fa; --header-text: #5f6368;
--text: #202124; --error: #d93025;
}
* { box-sizing: border-box; }
html, body { height: 100%; margin: 0; }
body {
font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
color: var(--text);
display: flex; flex-direction: column;
overflow: hidden;
background: #fff;
}
#app { display: flex; flex-direction: column; height: 100%; }
/* ---------- toolbar ---------- */
#toolbar {
display: flex; align-items: center; gap: 8px;
padding: 6px 12px; border-bottom: 1px solid var(--border);
background: #fff; flex-wrap: wrap;
}
#toolbar .title { font-weight: 600; font-size: 14px; margin-right: 6px; letter-spacing: .2px; }
.tbtn {
font: inherit; font-size: 12.5px; padding: 5px 10px;
border: 1px solid #dadce0; border-radius: 4px;
background: #fff; cursor: pointer; color: #3c4043;
}
.tbtn:hover { background: #f1f3f4; border-color: #bdc1c6; }
.tbtn:active { background: #e8eaed; }
#toolbar .sep { width: 1px; height: 20px; background: var(--border); margin: 0 4px; }
#toolbar .hint { font-size: 12px; color: #80868b; margin-left: auto; }
/* ---------- formula bar ---------- */
#formulaBar {
display: flex; align-items: center; height: 38px;
border-bottom: 1px solid var(--border); background: #fff;
}
#nameBox {
width: 64px; height: 26px; margin: 0 0 0 12px;
border: 1px solid #dadce0; border-radius: 3px;
text-align: center; font-size: 12px; font-family: inherit; color: var(--text);
}
#nameBox:focus { outline: none; border-color: var(--blue); }
#fx {
margin: 0 10px 0 8px; color: var(--header-text);
font-family: Georgia, "Times New Roman", serif; font-style: italic; font-size: 14px;
}
#barInput {
flex: 1; height: 26px; border: 1px solid transparent; border-radius: 3px;
font-size: 13px; font-family: inherit; padding: 0 8px; color: var(--text);
}
#barInput:focus { outline: none; border-color: var(--blue); }
/* ---------- grid ---------- */
#gridWrap { flex: 1; overflow: auto; background: #fff; user-select: none; }
table { border-collapse: separate; border-spacing: 0; }
th, td { border-right: 1px solid var(--border); border-bottom: 1px solid var(--border); }
th {
position: sticky; background: var(--header-bg); color: var(--header-text);
font-weight: 500; font-size: 12px; user-select: none;
}
th.corner {
top: 0; left: 0; z-index: 6;
width: var(--header-w); min-width: var(--header-w); max-width: var(--header-w);
}
th.colHead {
top: 0; z-index: 4; height: 24px; text-align: center;
width: var(--cell-w); min-width: var(--cell-w); max-width: var(--cell-w);
}
th.rowHead {
left: 0; z-index: 3; text-align: center;
width: var(--header-w); min-width: var(--header-w); max-width: var(--header-w);
}
th.selHead { background: var(--blue-light); color: var(--blue); }
td {
width: var(--cell-w); min-width: var(--cell-w); max-width: var(--cell-w);
height: var(--cell-h); padding: 0 6px; font-size: 13px;
position: relative; white-space: nowrap; overflow: hidden; cursor: default;
}
td.num { text-align: right; }
td.err { color: var(--error); }
td.sel { box-shadow: inset 0 0 0 2px var(--blue); }
input.cellInput {
position: absolute; inset: 0; width: 100%; height: 100%;
border: none; outline: none; font: inherit; padding: 0 6px;
background: #fff; z-index: 2; user-select: text;
}
/* ---------- context menu ---------- */
#ctxMenu {
position: fixed; z-index: 100; display: none;
background: #fff; border: 1px solid #dadce0; border-radius: 6px;
box-shadow: 0 4px 16px rgba(60,64,67,.22);
padding: 4px 0; min-width: 210px;
}
#ctxMenu .mi { padding: 7px 16px; font-size: 13px; cursor: pointer; }
#ctxMenu .mi:hover { background: #f1f3f4; }
</style>
</head>
<body>
<div id="app">
<div id="toolbar">
<span class="title">Sheet</span>
<span class="sep"></span>
<button class="tbtn" id="btnInsRow" title="Insert a row above the selected row">Insert row</button>
<button class="tbtn" id="btnDelRow" title="Delete the selected row">Delete row</button>
<button class="tbtn" id="btnInsCol" title="Insert a column to the left of the selected column">Insert column</button>
<button class="tbtn" id="btnDelCol" title="Delete the selected column">Delete column</button>
<span class="sep"></span>
<button class="tbtn" id="btnClear" title="Clear the selected cell (Del)">Clear cell</button>
<span class="hint">Right-click a row / column header for insert & delete options</span>
</div>
<div id="formulaBar">
<input id="nameBox" value="A1" spellcheck="false" title="Cell reference — type a reference and press Enter to jump">
<span id="fx">ƒx</span>
<input id="barInput" spellcheck="false" autocomplete="off" title="Formula bar">
</div>
<div id="gridWrap">
<table id="grid"></table>
</div>
</div>
<div id="ctxMenu"></div>
<script>
'use strict';
/* ================================================================
SECTION 1 — CONFIG (tunable constants)
================================================================ */
const CFG = {
COLS: 26, // initial columns (A–Z)
ROWS: 100, // initial rows
MAX_COLS: 52, // hard cap for column inserts
MAX_ROWS: 300, // hard cap for row inserts
CELL_W: 96, // px, cell width
CELL_H: 28, // px, cell height
HEADER_W: 48, // px, row/column header width
LS_KEY: 'vanilla.sheet.v1',
SAVE_DEBOUNCE_MS: 250,
COLORS: {
blue: '#1a73e8',
blueLight: '#e8f0fe',
border: '#e0e4e8',
headerBg: '#f8f9fa',
headerText: '#5f6368',
error: '#d93025',
},
FUNCTIONS: ['SUM', 'AVG', 'AVERAGE', 'MIN', 'MAX', 'COUNT'],
};
/* ================================================================
SECTION 2 — STATE
================================================================ */
let COLS = CFG.COLS;
let ROWS = CFG.ROWS;
let cells = new Map(); // "A1" -> { raw } raw input (formulas keep the '=')
const values = new Map(); // "A1" -> { v, err } computed value
let deps = new Map(); // "A1" -> Set of keys this cell depends on
let dependents = new Map(); // "A1" -> Set of keys that depend on this cell
const astCache = new Map(); // "A1" -> { raw, node, err }
let sel = { col: 0, row: 0 };
let mode = 'select'; // 'select' | 'edit'
let editSource = null; // 'cell' | 'bar'
let editText = '';
let cellEls = new Map(); // "A1" -> <td>
let colHeadEls = [];
let rowHeadEls = [];
let saveTimer = null;
let grid, gridWrap, barInput, nameBox, ctxMenu;
const NUM_RE = /^-?(\d+(\.\d*)?|\.\d+)$/;
const SENTINEL = 999999999; // row/col index used to mark "deleted target" refs -> #REF!
/* ================================================================
SECTION 3 — GRID HELPERS (column letters, keys, bounds)
================================================================ */
function colToLetter(c) {
let s = '';
do { s = String.fromCharCode(65 + (c % 26)) + s; c = Math.floor(c / 26) - 1; } while (c >= 0);
return s;
}
function letterToCol(s) {
let c = 0;
for (let i = 0; i < s.length; i++) c = c * 26 + (s.charCodeAt(i) - 65);
return c;
}
function keyOf(col, row) { return colToLetter(col) + (row + 1); }
function parseKey(k) {
let i = 0;
while (i < k.length && k.charCodeAt(i) >= 65 && k.charCodeAt(i) <= 90) i++;
return { col: letterToCol(k.slice(0, i)), row: parseInt(k.slice(i), 10) - 1 };
}
function inBounds(col, row) { return col >= 0 && row >= 0 && col < COLS && row < ROWS; }
/* ================================================================
SECTION 4 — TOKENIZER
================================================================ */
function tokenize(src) {
const tokens = [];
const n = src.length;
let i = 0;
while (i < n) {
const ch = src[i];
if (ch === ' ' || ch === '\t') { i++; continue; }
const isDigit = ch >= '0' && ch <= '9';
if (isDigit || (ch === '.' && src[i + 1] >= '0' && src[i + 1] <= '9')) {
let j = i;
while (j < n && src[j] >= '0' && src[j] <= '9') j++;
if (j < n && src[j] === '.' && !(src[j + 1] >= 'A' && src[j + 1] <= 'Z')) {
j++;
while (j < n && src[j] >= '0' && src[j] <= '9') j++;
}
tokens.push({ t: 'num', v: parseFloat(src.slice(i, j)) });
i = j;
continue;
}
if ((ch >= 'A' && ch <= 'Z') || (ch >= 'a' && ch <= 'z')) {
let j = i;
while (j < n && ((src[j] >= 'A' && src[j] <= 'Z') || (src[j] >= 'a' && src[j] <= 'z'))) j++;
tokens.push({ t: 'name', v: src.slice(i, j).toUpperCase() });
i = j;
continue;
}
if ('+-*/():,'.indexOf(ch) !== -1) { tokens.push({ t: 'op', v: ch }); i++; continue; }
throw { err: '#ERR!', msg: 'Unexpected character: ' + ch };
}
return tokens;
}
/* ================================================================
SECTION 5 — PARSER (recursive descent, no eval)
expr := term (('+'|'-') term)*
term := factor (('*'|'/') factor)*
factor := ('-'|'+') factor | primary
primary := NUMBER | '(' expr ')' | NAME '(' args ')' | REF (':' REF)?
================================================================ */
function parse(tokens) {
let pos = 0;
const peek = () => tokens[pos];
const next = () => tokens[pos++];
const isOp = (t, v) => t && t.t === 'op' && t.v === v;
function expectOp(v) { if (!isOp(next(), v)) throw { err: '#ERR!' }; }
function parseExpr() {
let left = parseTerm();
while (isOp(peek(), '+') || isOp(peek(), '-')) {
const op = next().v;
left = { t: 'bin', op, l: left, r: parseTerm() };
}
return left;
}
function parseTerm() {
let left = parseFactor();
while (isOp(peek(), '*') || isOp(peek(), '/')) {
const op = next().v;
left = { t: 'bin', op, l: left, r: parseFactor() };
}
return left;
}
function parseFactor() {
const t = peek();
if (isOp(t, '-') || isOp(t, '+')) {
next();
const x = parseFactor();
return t.v === '-' ? { t: 'un', x } : x;
}
return parsePrimary();
}
function parseRef(nameTok) {
const tn = next();
if (!tn || tn.t !== 'num' || !Number.isInteger(tn.v)) throw { err: '#ERR!' };
const col = letterToCol(nameTok.v);
const row = tn.v - 1;
if (isOp(peek(), ':')) {
next();
const a = next(), b = next();
if (!a || a.t !== 'name' || !b || b.t !== 'num' || !Number.isInteger(b.v)) throw { err: '#ERR!' };
let c1 = col, r1 = row, c2 = letterToCol(a.v), r2 = b.v - 1;
if (c2 < c1) { const x = c1; c1 = c2; c2 = x; }
if (r2 < r1) { const x = r1; r1 = r2; r2 = x; }
return { t: 'range', c1, r1, c2, r2 };
}
return { t: 'ref', col, row };
}
function parsePrimary() {
const t = next();
if (!t) throw { err: '#ERR!' };
if (t.t === 'num') return { t: 'num', v: t.v };
if (isOp(t, '(')) {
const e = parseExpr();
expectOp(')');
return e;
}
if (t.t === 'name') {
if (isOp(peek(), '(')) {
if (CFG.FUNCTIONS.indexOf(t.v) === -1) throw { err: '#ERR!' };
next();
const args = [];
if (!isOp(peek(), ')')) {
args.push(parseExpr());
while (isOp(peek(), ',')) { next(); args.push(parseExpr()); }
}
expectOp(')');
return { t: 'call', name: t.v, args };
}
return parseRef(t);
}
throw { err: '#ERR!' };
}
const ast = parseExpr();
if (pos < tokens.length) throw { err: '#ERR!' }; // trailing junk
return ast;
}
/* ================================================================
SECTION 6 — AST CACHE & DEPENDENCY GRAPH
================================================================ */
function getAst(key, raw) {
const hit = astCache.get(key);
if (hit && hit.raw === raw) return hit;
let node = null, err = null;
try {
node = parse(tokenize(raw.slice(1)));
} catch (e) {
err = (e && e.err) ? e.err : '#ERR!';
}
const entry = { raw, node, err };
astCache.set(key, entry);
return entry;
}
function collectRefs(node, out) {
switch (node.t) {
case 'ref':
out.add(keyOf(node.col, node.row));
break;
case 'range':
if (node.c1 < 0 || node.r1 < 0 || node.c2 >= COLS || node.r2 >= ROWS) return; // out-of-bounds: eval will #REF!
for (let c = node.c1; c <= node.c2; c++)
for (let r = node.r1; r <= node.r2; r++)
out.add(keyOf(c, r));
break;
case 'bin':
collectRefs(node.l, out);
collectRefs(node.r, out);
break;
case 'un':
collectRefs(node.x, out);
break;
case 'call':
for (const a of node.args) collectRefs(a, out);
break;
}
}
function buildGraph() {
deps = new Map();
dependents = new Map();
for (const [key, cell] of cells) {
if (!cell.raw.startsWith('=')) continue;
const ast = getAst(key, cell.raw);
if (ast.err) continue;
const refs = new Set();
collectRefs(ast.node, refs);
if (refs.size === 0) continue;
deps.set(key, refs);
for (const r of refs) {
let ds = dependents.get(r);
if (!ds) { ds = new Set(); dependents.set(r, ds); }
ds.add(key);
}
}
}
/* ================================================================
SECTION 7 — EVALUATOR (errors: #REF! #DIV/0! #ERR! #CYCLE!)
================================================================ */
function isRange(x) { return x !== null && typeof x === 'object' && x.t === 'range'; }
function asNum(x) { return typeof x === 'number' ? x : 0; }
function evalNode(node) {
switch (node.t) {
case 'num':
return node.v;
case 'ref': {
if (!inBounds(node.col, node.row)) throw { err: '#REF!' };
const v = values.get(keyOf(node.col, node.row));
if (v && v.err) throw { err: v.err }; // propagate upstream errors
return (v && typeof v.v === 'number') ? v.v : 0;
}
case 'range': {
if (node.c1 < 0 || node.r1 < 0 || node.c2 >= COLS || node.r2 >= ROWS) throw { err: '#REF!' };
const ks = [];
for (let c = node.c1; c <= node.c2; c++)
for (let r = node.r1; r <= node.r2; r++)
ks.push(keyOf(c, r));
return { t: 'range', cells: ks };
}
case 'bin': {
const l = evalNode(node.l);
const r = evalNode(node.r);
if (isRange(l) || isRange(r)) throw { err: '#ERR!' };
const a = asNum(l), b = asNum(r);
switch (node.op) {
case '+': return a + b;
case '-': return a - b;
case '*': return a * b;
case '/':
if (b === 0) throw { err: '#DIV/0!' };
return a / b;
}
break;
}
case 'un': {
const x = evalNode(node.x);
if (isRange(x)) throw { err: '#ERR!' };
return -asNum(x);
}
case 'call': {
const nums = [];
for (const arg of node.args) {
const v = evalNode(arg); // throws on error cells / bad refs -> propagation
if (isRange(v)) {
for (const k of v.cells) {
const cv = values.get(k);
if (cv && cv.err) throw { err: cv.err };
if (cv && typeof cv.v === 'number') nums.push(cv.v);
}
} else if (arg.t === 'ref') {
// direct ref: only real numbers count (text/empty contributes 0, not counted)
const cv = values.get(keyOf(arg.col, arg.row));
if (cv && typeof cv.v === 'number') nums.push(cv.v);
} else if (typeof v === 'number') {
nums.push(v);
}
}
const sum = nums.reduce((a, b) => a + b, 0);
switch (node.name) {
case 'SUM': return sum;
case 'COUNT': return nums.length;
case 'AVG':
case 'AVERAGE':
if (nums.length === 0) throw { err: '#DIV/0!' };
return sum / nums.length;
case 'MIN': return nums.length ? Math.min.apply(null, nums) : 0;
case 'MAX': return nums.length ? Math.max.apply(null, nums) : 0;
}
throw { err: '#ERR!' };
}
}
throw { err: '#ERR!' };
}
function evaluateCell(key) {
const cell = cells.get(key);
if (!cell) { values.delete(key); return; }
const raw = cell.raw;
if (raw.startsWith('=')) {
const ast = getAst(key, raw);
if (ast.err) { values.set(key, { v: null, err: ast.err }); return; }
try {
const v = evalNode(ast.node);
if (isRange(v)) { values.set(key, { v: null, err: '#ERR!' }); return; }
values.set(key, { v: v, err: null });
} catch (e) {
values.set(key, { v: null, err: (e && e.err) ? e.err : '#ERR!' });
}
} else if (NUM_RE.test(raw)) {
values.set(key, { v: parseFloat(raw), err: null });
} else {
values.set(key, { v: raw, err: null });
}
}
/* ================================================================
SECTION 8 — RECOMPUTE (topological order, cycle-safe)
Recomputes only the changed cell + transitive dependents.
Cells trapped in (or downstream of) a cycle get #CYCLE!.
================================================================ */
function affectedSet(changed) {
const affected = new Set(changed);
const stack = [];
for (const k of changed) stack.push(k);
while (stack.length) {
const k = stack.pop();
const ds = dependents.get(k);
if (ds) for (const d of ds) if (!affected.has(d)) { affected.add(d); stack.push(d); }
}
return affected;
}
function recompute(affected) {
const indeg = new Map();
for (const k of affected) {
let d = 0;
const ds = deps.get(k);
if (ds) for (const r of ds) if (affected.has(r)) d++;
indeg.set(k, d);
}
const queue = [];
for (const k of affected) if (indeg.get(k) === 0) queue.push(k);
const done = new Set();
let qi = 0;
while (qi < queue.length) {
const k = queue[qi++];
done.add(k);
evaluateCell(k);
const ds = dependents.get(k);
if (ds) for (const d of ds) {
if (!affected.has(d)) continue;
const nd = indeg.get(d) - 1;
indeg.set(d, nd);
if (nd === 0) queue.push(d);
}
}
for (const k of affected) {
if (!done.has(k)) values.set(k, { v: null, err: '#CYCLE!' });
}
return affected;
}
/* ================================================================
SECTION 9 — MUTATIONS (cell edits, row/col ops, formula rewriting)
================================================================ */
function setCellRaw(key, raw) {
raw = (raw || '').trim();
if (raw === '') cells.delete(key);
else cells.set(key, { raw });
astCache.delete(key);
buildGraph();
const affected = recompute(affectedSet([key]));
renderKeys(affected);
scheduleSave();
}
/* Rewrite a formula string, applying transform(col,row)->{c,r} to every
cell reference. Function names are left untouched. Refs whose target
was deleted map to a far out-of-bounds index, which evaluates to #REF!. */
function rewriteFormula(raw, transform) {
if (!raw.startsWith('=')) return raw;
let toks;
try { toks = tokenize(raw.slice(1)); } catch (e) { return raw; }
let out = '=';
let changed = false;
for (let i = 0; i < toks.length; i++) {
const tk = toks[i];
const nx = toks[i + 1];
if (tk.t === 'name' && nx && nx.t === 'num' && Number.isInteger(nx.v)) {
const col = letterToCol(tk.v);
const row = nx.v - 1;
const nr = transform(col, row);
if (nr.c !== col || nr.r !== row) changed = true;
out += colToLetter(nr.c) + (nr.r + 1);
i++;
} else {
out += (tk.t === 'num') ? String(tk.v) : tk.v;
}
}
return changed ? out : raw;
}
function remapData(transform) {
const next = new Map();
for (const [k, cell] of cells) {
const p = parseKey(k);
const t = transform(p.col, p.row);
if (t) next.set(keyOf(t.c, t.r), cell);
}
cells = next;
}
function rewriteAllFormulas(transform) {
for (const cell of cells.values()) {
if (cell.raw.startsWith('=')) cell.raw = rewriteFormula(cell.raw, transform);
}
astCache.clear();
}
function insertRow(at) {
at = Math.max(0, Math.min(at, ROWS));
if (ROWS >= CFG.MAX_ROWS) return;
ROWS++;
remapData((c, r) => ({ c, r: r >= at ? r + 1 : r }));
rewriteAllFormulas((c, r) => ({ c, r: r >= at ? r + 1 : r }));
afterStructuralChange();
}
function deleteRow(at) {
if (ROWS <= 1) return;
at = Math.max(0, Math.min(at, ROWS - 1));
ROWS--;
remapData((c, r) => (r === at ? null : { c, r: r > at ? r - 1 : r }));
rewriteAllFormulas((c, r) => (r === at ? { c, r: SENTINEL } : { c, r: r > at ? r - 1 : r }));
afterStructuralChange();
}
function insertCol(at) {
at = Math.max(0, Math.min(at, COLS));
if (COLS >= CFG.MAX_COLS) return;
COLS++;
remapData((c, r) => ({ c: c >= at ? c + 1 : c, r }));
rewriteAllFormulas((c, r) => ({ c: c >= at ? c + 1 : c, r }));
afterStructuralChange();
}
function deleteCol(at) {
if (COLS <= 1) return;
at = Math.max(0, Math.min(at, COLS - 1));
COLS--;
remapData((c, r) => (c === at ? null : { c: c > at ? c - 1 : c, r }));
rewriteAllFormulas((c, r) => (c === at ? { c: SENTINEL, r } : { c: c > at ? c - 1 : c, r }));
afterStructuralChange();
}
function afterStructuralChange() {
values.clear();
buildGraph();
recompute(new Set(cells.keys()));
rebuildGrid();
renderAll();
setSelection(sel.col, sel.row);
syncBar();
save();
}
/* ================================================================
SECTION 10 — RENDERING
================================================================ */
function applyCssVars() {
const s = document.documentElement.style;
s.setProperty('--cell-w', CFG.CELL_W + 'px');
s.setProperty('--cell-h', CFG.CELL_H + 'px');
s.setProperty('--header-w', CFG.HEADER_W + 'px');
s.setProperty('--blue', CFG.COLORS.blue);
s.setProperty('--blue-light', CFG.COLORS.blueLight);
s.setProperty('--border', CFG.COLORS.border);
s.setProperty('--header-bg', CFG.COLORS.headerBg);
s.setProperty('--header-text', CFG.COLORS.headerText);
s.setProperty('--error', CFG.COLORS.error);
}
function rebuildGrid() {
grid.innerHTML = '';
cellEls = new Map();
colHeadEls = [];
rowHeadEls = [];
const thead = document.createElement('thead');
const trH = document.createElement('tr');
const corner = document.createElement('th');
corner.className = 'corner';
trH.appendChild(corner);
for (let c = 0; c < COLS; c++) {
const th = document.createElement('th');
th.className = 'colHead';
th.dataset.idx = String(c);
th.textContent = colToLetter(c);
trH.appendChild(th);
colHeadEls[c] = th;
}
thead.appendChild(trH);
grid.appendChild(thead);
const tbody = document.createElement('tbody');
for (let r = 0; r < ROWS; r++) {
const tr = document.createElement('tr');
const th = document.createElement('th');
th.className = 'rowHead';
th.dataset.idx = String(r);
th.textContent = String(r + 1);
tr.appendChild(th);
rowHeadEls[r] = th;
for (let c = 0; c < COLS; c++) {
const td = document.createElement('td');
const key = keyOf(c, r);
td.dataset.key = key;
tr.appendChild(td);
cellEls.set(key, td);
}
tbody.appendChild(tr);
}
grid.appendChild(tbody);
}
function fmtNum(n) {
if (typeof n !== 'number' || !isFinite(n)) return '#ERR!';
if (Number.isInteger(n) && Math.abs(n) < 1e15) return String(n);
return String(parseFloat(n.toPrecision(12)));
}
function renderCell(key) {
const el = cellEls.get(key);
if (!el) return;
const selCls = el.classList.contains('sel') ? ' sel' : '';
const cell = cells.get(key);
let text = '', cls = '';
if (cell) {
if (cell.raw.startsWith('=')) {
const v = values.get(key);
if (v && v.err) { text = v.err; cls = 'err'; }
else if (v && typeof v.v === 'number') { text = fmtNum(v.v); cls = 'num'; }
else if (v && typeof v.v === 'string') { text = v.v; cls = ''; }
} else if (NUM_RE.test(cell.raw)) {
text = fmtNum(parseFloat(cell.raw));
cls = 'num';
} else {
text = cell.raw;
cls = '';
}
}
el.className = cls + selCls;
el.textContent = text;
}
function renderKeys(keys) { for (const k of keys) renderCell(k); }
function renderAll() { for (const k of cells.keys()) renderCell(k); }
function setSelection(col, row) {
col = Math.max(0, Math.min(COLS - 1, col));
row = Math.max(0, Math.min(ROWS - 1, row));
const pc = sel.col, pr = sel.row;
sel = { col, row };
const pe = cellEls.get(keyOf(pc, pr));
if (pe) pe.classList.remove('sel');
const ne = cellEls.get(keyOf(col, row));
if (ne) { ne.classList.add('sel'); ne.scrollIntoView({ block: 'nearest', inline: 'nearest' }); }
if (colHeadEls[pc]) colHeadEls[pc].classList.remove('selHead');
if (rowHeadEls[pr]) rowHeadEls[pr].classList.remove('selHead');
if (colHeadEls[col]) colHeadEls[col].classList.add('selHead');
if (rowHeadEls[row]) rowHeadEls[row].classList.add('selHead');
updateNameBox();
if (mode === 'select') syncBar();
}
function updateNameBox() {
if (document.activeElement !== nameBox) nameBox.value = keyOf(sel.col, sel.row);
}
function syncBar() {
const c = cells.get(keyOf(sel.col, sel.row));
barInput.value = c ? c.raw : '';
}
/* ================================================================
SECTION 11 — EDITING & KEYBOARD
================================================================ */
function beginEdit(source, initialText) {
if (mode === 'edit') return;
const key = keyOf(sel.col, sel.row);
mode = 'edit';
editSource = source;
const c = cells.get(key);
editText = initialText !== undefined ? initialText : (c ? c.raw : '');
barInput.value = editText;
if (source === 'cell') {
const td = cellEls.get(key);
const inp = document.createElement('input');
inp.className = 'cellInput';
inp.value = editText;
inp.spellcheck = false;
inp.addEventListener('input', () => { editText = inp.value; barInput.value = editText; });
inp.addEventListener('keydown', (e) => {
e.stopPropagation();
if (e.key === 'Enter') { e.preventDefault(); commitEdit('down'); }
else if (e.key === 'Tab') { e.preventDefault(); commitEdit(e.shiftKey ? 'left' : 'right'); }
else if (e.key === 'Escape') { e.preventDefault(); cancelEdit(); }
});
inp.addEventListener('blur', () => {
if (mode === 'edit' && editSource === 'cell') commitEdit(null);
});
td.appendChild(inp);
inp.focus();
const L = inp.value.length;
try { inp.setSelectionRange(L, L); } catch (e) { /* ignore */ }
} else {
renderCellRawTemp();
}
}
function commitEdit(move) {
if (mode !== 'edit') return;
const key = keyOf(sel.col, sel.row);
const text = editText;
mode = 'select';
editSource = null;
const td = cellEls.get(key);
if (td) {
const inp = td.querySelector('input.cellInput');
if (inp) inp.remove();
}
setCellRaw(key, text);
if (move) moveSelection(move);
syncBar();
}
function cancelEdit() {
if (mode !== 'edit') return;
const key = keyOf(sel.col, sel.row);
mode = 'select';
editSource = null;
const td = cellEls.get(key);
if (td) {
const inp = td.querySelector('input.cellInput');
if (inp) inp.remove();
}
renderCell(key);
syncBar();
}
function renderCellRawTemp() {
const td = cellEls.get(keyOf(sel.col, sel.row));
if (!td) return;
const selCls = td.classList.contains('sel') ? ' sel' : '';
td.className = selCls;
td.textContent = editText;
}
function moveSelection(move) {
let col = sel.col, row = sel.row;
if (move === 'down') row++;
else if (move === 'up') row--;
else if (move === 'right') { col++; if (col >= COLS) { col = 0; row++; } }
else if (move === 'left') { col--; if (col < 0) { col = COLS - 1; row--; } }
if (row < 0) row = 0;
if (row >= ROWS) row = ROWS - 1;
setSelection(col, row);
}
/* ================================================================
SECTION 12 — EVENT WIRING (grid, formula bar, name box, toolbar,
context menu, global keys)
================================================================ */
function wireEvents() {
grid = document.getElementById('grid');
gridWrap = document.getElementById('gridWrap');
barInput = document.getElementById('barInput');
nameBox = document.getElementById('nameBox');
ctxMenu = document.getElementById('ctxMenu');
grid.addEventListener('click', (e) => {
const td = e.target.closest('td');
if (!td) return;
if (mode === 'edit' && editSource === 'bar') commitEdit(null);
// (cell-source edits are already committed by the blur handler)
const p = parseKey(td.dataset.key);
setSelection(p.col, p.row);
});
grid.addEventListener('dblclick', (e) => {
const td = e.target.closest('td');
if (!td) return;
const p = parseKey(td.dataset.key);
setSelection(p.col, p.row);
beginEdit('cell');
});
grid.addEventListener('contextmenu', (e) => {
const th = e.target.closest('th.colHead, th.rowHead');
if (!th) return;
e.preventDefault();
const idx = parseInt(th.dataset.idx, 10);
if (th.classList.contains('rowHead')) {
showMenu(e.clientX, e.clientY, [
{ label: 'Insert row above', fn: () => insertRow(idx) },
{ label: 'Insert row below', fn: () => insertRow(idx + 1) },
{ label: 'Delete row ' + (idx + 1), fn: () => deleteRow(idx) },
]);
} else {
showMenu(e.clientX, e.clientY, [
{ label: 'Insert column to the left', fn: () => insertCol(idx) },
{ label: 'Insert column to the right', fn: () => insertCol(idx + 1) },
{ label: 'Delete column ' + colToLetter(idx), fn: () => deleteCol(idx) },
]);
}
});
// ----- formula bar -----
barInput.addEventListener('focus', () => {
if (mode === 'edit' && editSource === 'cell') commitEdit(null);
});
barInput.addEventListener('input', () => {
if (mode === 'select') beginEdit('bar', barInput.value);
else if (editSource === 'bar') { editText = barInput.value; renderCellRawTemp(); }
});
barInput.addEventListener('keydown', (e) => {
e.stopPropagation();
if (e.key === 'Enter') {
e.preventDefault();
if (mode === 'edit') commitEdit('down');
else setSelection(sel.col, sel.row + 1);
} else if (e.key === 'Escape') {
e.preventDefault();
if (mode === 'edit') cancelEdit();
barInput.blur();
}
});
barInput.addEventListener('blur', () => {
if (mode === 'edit' && editSource === 'bar') commitEdit(null);
});
// ----- name box -----
nameBox.addEventListener('keydown', (e) => {
e.stopPropagation();
if (e.key === 'Enter') {
const m = /^([A-Za-z]{1,3})(\d+)$/.exec(nameBox.value.trim());
if (m) {
const col = letterToCol(m[1].toUpperCase());
const row = parseInt(m[2], 10) - 1;
if (col >= 0 && col < COLS && row >= 0 && row < ROWS) setSelection(col, row);
}
syncBar();
nameBox.blur();
} else if (e.key === 'Escape') {
nameBox.value = keyOf(sel.col, sel.row);
nameBox.blur();
}
});
// ----- global keys (selection mode) -----
window.addEventListener('keydown', (e) => {
if (mode === 'edit') return;
const t = e.target;
if (t === barInput || t === nameBox) return;
const k = e.key;
if (k === 'ArrowUp') { e.preventDefault(); setSelection(sel.col, sel.row - 1); }
else if (k === 'ArrowDown') { e.preventDefault(); setSelection(sel.col, sel.row + 1); }
else if (k === 'ArrowLeft') { e.preventDefault(); setSelection(sel.col - 1, sel.row); }
else if (k === 'ArrowRight') { e.preventDefault(); setSelection(sel.col + 1, sel.row); }
else if (k === 'Enter') { e.preventDefault(); setSelection(sel.col, sel.row + 1); }
else if (k === 'Tab') { e.preventDefault(); moveSelection(e.shiftKey ? 'left' : 'right'); }
else if (k === 'F2') { e.preventDefault(); beginEdit('cell'); }
else if (k === 'Delete' || k === 'Backspace') { e.preventDefault(); setCellRaw(keyOf(sel.col, sel.row), ''); }
else if (k.length === 1 && !e.ctrlKey && !e.metaKey && !e.altKey) {
e.preventDefault();
beginEdit('cell', k);
}
});
// ----- toolbar -----
const wireBtn = (id, fn) => {
const b = document.getElementById(id);
b.addEventListener('click', () => { fn(); b.blur(); });
};
wireBtn('btnInsRow', () => insertRow(sel.row));
wireBtn('btnDelRow', () => deleteRow(sel.row));
wireBtn('btnInsCol', () => insertCol(sel.col));
wireBtn('btnDelCol', () => deleteCol(sel.col));
wireBtn('btnClear', () => setCellRaw(keyOf(sel.col, sel.row), ''));
// ----- context menu dismissal -----
document.addEventListener('click', hideMenu);
document.addEventListener('keydown', (e) => { if (e.key === 'Escape') hideMenu(); });
gridWrap.addEventListener('scroll', hideMenu, { passive: true });
}
function showMenu(x, y, items) {
ctxMenu.innerHTML = '';
for (const it of items) {
const d = document.createElement('div');
d.className = 'mi';
d.textContent = it.label;
d.addEventListener('click', () => { hideMenu(); it.fn(); });
ctxMenu.appendChild(d);
}
ctxMenu.style.display = 'block';
const r = ctxMenu.getBoundingClientRect();
ctxMenu.style.left = Math.max(4, Math.min(x, window.innerWidth - r.width - 4)) + 'px';
ctxMenu.style.top = Math.max(4, Math.min(y, window.innerHeight - r.height - 4)) + 'px';
}
function hideMenu() { ctxMenu.style.display = 'none'; }
/* ================================================================
SECTION 13 — PERSISTENCE (localStorage: raw formulas + dimensions)
================================================================ */
function save() {
try {
const data = { cols: COLS, rows: ROWS, sel: sel, cells: {} };
for (const [k, c] of cells) data.cells[k] = c.raw;
localStorage.setItem(CFG.LS_KEY, JSON.stringify(data));
} catch (e) { /* storage unavailable */ }
}
function scheduleSave() {
clearTimeout(saveTimer);
saveTimer = setTimeout(save, CFG.SAVE_DEBOUNCE_MS);
}
function load() {
try {
const s = localStorage.getItem(CFG.LS_KEY);
if (!s) return false;
const data = JSON.parse(s);
if (!data || typeof data !== 'object' || !data.cells) return false;
COLS = Math.max(1, Math.min(CFG.MAX_COLS, (data.cols | 0) || CFG.COLS));
ROWS = Math.max(1, Math.min(CFG.MAX_ROWS, (data.rows | 0) || CFG.ROWS));
for (const k of Object.keys(data.cells)) {
const raw = data.cells[k];
if (typeof raw !== 'string') continue;
const p = parseKey(k);
if (inBounds(p.col, p.row)) cells.set(keyOf(p.col, p.row), { raw });
}
if (data.sel && typeof data.sel.col === 'number' && typeof data.sel.row === 'number' && inBounds(data.sel.col, data.sel.row)) {
sel = { col: data.sel.col, row: data.sel.row };
}
return true;
} catch (e) { return false; }
}
/* ================================================================
SECTION 14 — INIT
================================================================ */
function seedDemo() {
const put = (k, v) => cells.set(k, { raw: v });
put('A1', '5'); put('A2', '10'); put('A3', '15');
put('B1', '=SUM(A1:A3)');
put('B2', '=AVG(A1:A3)');
put('D1', 'Item');
put('E1', '500'); put('E2', '300');
put('E3', '=SUM(E1:E2)');
}
function init() {
applyCssVars();
wireEvents();
if (!load()) seedDemo();
rebuildGrid();
buildGraph();
recompute(new Set(cells.keys()));
renderAll();
setSelection(sel.col, sel.row);
syncBar();
}
// Small API surface (used by tests / console)
window.Sheet = {
get COLS() { return COLS; },
get ROWS() { return ROWS; },
get cells() { return cells; },
get values() { return values; },
setCellRaw, insertRow, deleteRow, insertCol, deleteCol, save,
};
init();
</script>
</body>
</html>