← Spreadsheet results

Spreadsheet

Qwen 3.8 · full

Initial view of Spreadsheet

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 &amp; 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>