← Spreadsheet results

Spreadsheet

Qwen 3.6 27b MTP · 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 44,464 bytes · SHA-256 88dde00ad6cb
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Spreadsheet</title>
<!-- ============================================================
     CONSTANTS
     ============================================================ -->
<script>
const CONFIG = {
  COLS: 26,           // A-Z
  ROWS: 100,
  CELL_WIDTH: 100,
  CELL_HEIGHT: 24,
  HEADER_WIDTH: 48,
  HEADER_HEIGHT: 24,
  COLORS: {
    bg: '#ffffff',
    headerBg: '#f8f9fa',
    headerBorder: '#dadce0',
    gridLine: '#e8eaed',
    selectionBorder: '#1a73e8',
    selectedCell: '#e8f0fe',
    textPrimary: '#202124',
    textSecondary: '#5f6368',
    errorColor: '#d93025',
    formulaBarBg: '#ffffff',
    toolbarBg: '#edf2fa',
    hoverBg: '#f1f3f4',
  }
};
</script>
<style>
/* ============================================================
     STYLES
     ============================================================ */
*, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }

body {
  font-family: 'Google Sans', Roboto, -apple-system, BlinkMacSystemFont, sans-serif;
  font-size: 13px;
  color: var(--text-primary);
  background: #fff;
  overflow: hidden;
  height: 100vh;
  display: flex;
  flex-direction: column;
}

/* Toolbar */
#toolbar {
  display: flex;
  align-items: center;
  gap: 4px;
  padding: 4px 8px;
  background: #edf2fa;
  border-bottom: 1px solid #dadce0;
  min-height: 36px;
}

#toolbar button {
  font-size: 12px;
  padding: 4px 10px;
  border: 1px solid #dadce0;
  background: #fff;
  border-radius: 4px;
  cursor: pointer;
  color: #3c4043;
}

#toolbar button:hover { background: #e8eaed; }

/* Formula Bar */
#formula-bar-container {
  display: flex;
  align-items: center;
  padding: 4px 8px;
  border-bottom: 1px solid #dadce0;
  gap: 8px;
  background: #fff;
}

#cell-ref-display {
  font-size: 12px;
  font-weight: 600;
  color: #3c4043;
  min-width: 40px;
  text-align: center;
  padding: 4px 8px;
  background: #f1f3f4;
  border-radius: 4px;
  user-select: none;
}

#formula-bar {
  flex: 1;
  font-family: 'Roboto Mono', monospace;
  font-size: 13px;
  padding: 4px 8px;
  border: 1px solid #dadce0;
  border-radius: 4px;
  outline: none;
  color: #202124;
}

#formula-bar:focus { border-color: #1a73e8; }

/* Grid Container */
#grid-wrapper {
  flex: 1;
  overflow: auto;
  position: relative;
}

#spreadsheet-table {
  border-collapse: collapse;
  table-layout: fixed;
}

#spreadsheet-table th,
#spreadsheet-table td {
  padding: 0;
  border-right: 1px solid #e8eaed;
  border-bottom: 1px solid #e8eaed;
  white-space: nowrap;
  overflow: hidden;
}

/* Column Headers */
.col-header-row th {
  background: #f8f9fa;
  font-weight: 500;
  color: #5f6368;
  text-align: center;
  height: 24px;
  position: sticky;
  top: 0;
  z-index: 2;
  user-select: none;
  cursor: pointer;
}

.col-header-row th:first-child {
  width: 48px;
  min-width: 48px;
  max-width: 48px;
  position: sticky;
  left: 0;
  z-index: 3;
}

/* Row Headers */
.row-header-cell {
  background: #f8f9fa;
  font-weight: 500;
  color: #5f6368;
  text-align: center;
  width: 48px;
  min-width: 48px;
  max-width: 48px;
  position: sticky;
  left: 0;
  z-index: 1;
  user-select: none;
}

/* Data Cells */
.data-cell {
  height: 24px;
  line-height: 24px;
  cursor: cell;
  font-size: 13px;
  color: #202124;
  position: relative;
}

.data-cell.selected {
  outline: 2px solid #1a73e8;
  outline-offset: -2px;
  z-index: 1;
}

.data-cell.editing {
  padding: 0;
  outline: 2px solid #1a73e8;
  outline-offset: -2px;
  z-index: 1;
}

.data-cell .cell-value {
  height: 100%;
  width: 100%;
  overflow: hidden;
  text-overflow: ellipsis;
  display: block;
  line-height: 24px;
  padding-left: 4px;
}

.data-cell .cell-value.right { text-align: right; }
.data-cell .cell-value.left { text-align: left; }
.data-cell .cell-value.error { color: #d93025; font-weight: 600; }

.cell-input {
  width: 100%;
  height: 100%;
  border: none;
  outline: none;
  font-family: 'Roboto Mono', monospace;
  font-size: 13px;
  padding-left: 4px;
  background: #fff;
}

/* Context Menu */
#context-menu {
  display: none;
  position: fixed;
  background: #fff;
  border: 1px solid #dadce0;
  border-radius: 8px;
  box-shadow: 0 2px 10px rgba(0,0,0,0.2);
  z-index: 100;
  min-width: 160px;
  padding: 4px 0;
}

#context-menu .menu-item {
  padding: 8px 16px;
  cursor: pointer;
  font-size: 13px;
  color: #202124;
}

#context-menu .menu-item:hover { background: #e8f0fe; }
#context-menu .menu-separator { border-top: 1px solid #dadce0; margin: 4px 0; }
</style>
</head>
<body>

<!-- Toolbar -->
<div id="toolbar">
  <button onclick="insertRow()">Insert Row Above</button>
  <button onclick="deleteRow()">Delete Row</button>
  <button onclick="insertCol()">Insert Column Left</button>
  <button onclick="deleteCol()">Delete Column</button>
  <span style="flex:1"></span>
  <span style="font-size:12px;color:#5f6368;">Spreadsheet — Vanilla JS</span>
</div>

<!-- Formula Bar -->
<div id="formula-bar-container">
  <div id="cell-ref-display">A1</div>
  <input type="text" id="formula-bar" spellcheck="false" autocomplete="off">
</div>

<!-- Grid -->
<div id="grid-wrapper">
  <table id="spreadsheet-table"></table>
</div>

<!-- Context Menu -->
<div id="context-menu"></div>

<script>
/* ============================================================
   STATE
   ============================================================ */
const state = {
  data: {},           // { "A1": { raw: "=SUM(A1:A5)", value: 42, type: 'number'|'text'|'error'|'formula' } }
  selectedCell: null, // { col: 0, row: 0 } => A1
  editingCell: null,  // currently being edited cell key or null
};

/* ============================================================
   CELL KEY HELPERS
   ============================================================ */
function colLetter(c) { return String.fromCharCode(65 + c); }
function rowNumber(r) { return r + 1; }
function cellKey(col, row) { return colLetter(col) + rowNumber(row); }
function parseCellRef(ref) {
  const m = ref.match(/^([A-Z])(\d+)$/i);
  if (!m) return null;
  return { col: m[1].toUpperCase().charCodeAt(0) - 65, row: parseInt(m[2]) - 1 };
}

/* ============================================================
   FORMULA TOKENIZER
   ============================================================ */
function tokenize(expr) {
  const tokens = [];
  let i = 0;
  while (i < expr.length) {
    // Skip whitespace
    if (expr[i] === ' ') { i++; continue; }

    // Number (including decimals and negative via unary handled in parser)
    if ((expr[i] >= '0' && expr[i] <= '9') || (expr[i] === '.' && i + 1 < expr.length && expr[i+1] >= '0' && expr[i+1] <= '9')) {
      let num = '';
      while (i < expr.length && ((expr[i] >= '0' && expr[i] <= '9') || expr[i] === '.')) {
        num += expr[i]; i++;
      }
      tokens.push({ type: 'NUMBER', value: parseFloat(num) });
      continue;
    }

    // Cell reference or function name (letters)
    if ((expr[i] >= 'A' && expr[i] <= 'Z') || (expr[i] === 'a' && expr[i] <= 'z')) {
      let word = '';
      while (i < expr.length && ((expr[i] >= 'A' && expr[i] <= 'Z') || (expr[i] >= 'a' && expr[i] <= 'z') || (expr[i] >= '0' && expr[i] <= '9'))) {
        word += expr[i]; i++;
      }
      const upper = word.toUpperCase();
      // Check if it's a function name
      if (['SUM','AVG','AVERAGE','MIN','MAX','COUNT'].includes(upper)) {
        tokens.push({ type: 'FUNCTION', value: upper });
      } else {
        // Could be cell reference like A1, B7
        const ref = parseCellRef(word);
        if (ref) {
          tokens.push({ type: 'CELL_REF', value: word.toUpperCase(), col: ref.col, row: ref.row });
        } else {
          tokens.push({ type: 'ERROR_TOKEN', value: word });
        }
      }
      continue;
    }

    // Operators and punctuation
    if (expr[i] === '+') { tokens.push({ type: 'OP', value: '+' }); i++; continue; }
    if (expr[i] === '-') { tokens.push({ type: 'OP', value: '-' }); i++; continue; }
    if (expr[i] === '*') { tokens.push({ type: 'OP', value: '*' }); i++; continue; }
    if (expr[i] === '/') { tokens.push({ type: 'OP', value: '/' }); i++; continue; }
    if (expr[i] === '(') { tokens.push({ type: 'LPAREN' }); i++; continue; }
    if (expr[i] === ')') { tokens.push({ type: 'RPAREN' }); i++; continue; }
    if (expr[i] === ',') { tokens.push({ type: 'COMMA' }); i++; continue; }
    if (expr[i] === ':') { tokens.push({ type: 'COLON' }); i++; continue; }

    // Unknown character
    tokens.push({ type: 'ERROR_TOKEN', value: expr[i] });
    i++;
  }
  return tokens;
}

/* ============================================================
   FORMULA PARSER (Recursive Descent)
   ============================================================ */
function parseFormula(expr, cellKeyForCycleCheck) {
  const tokens = tokenize(expr);
  if (!tokens.length) return { value: '', type: 'text' };

  let pos = 0;
  const dependencies = new Set();

  function peek() { return tokens[pos] || null; }
  function consume(expectedType) {
    const t = tokens[pos];
    if (expectedType && t.type !== expectedType) throw new Error(`Expected ${expectedType}, got ${t ? t.type : 'EOF'}`);
    pos++;
    return t;
  }

  // Expression: handles + and - (lowest precedence)
  function parseExpression() {
    let left = parseTerm();
    while (peek() && peek().type === 'OP' && (peek().value === '+' || peek().value === '-')) {
      const op = consume('OP').value;
      const right = parseTerm();
      left = { type: 'binary', op, left, right };
    }
    return left;
  }

  // Term: handles * and / (higher precedence)
  function parseTerm() {
    let left = parseUnary();
    while (peek() && peek().type === 'OP' && (peek().value === '*' || peek().value === '/')) {
      const op = consume('OP').value;
      const right = parseUnary();
      left = { type: 'binary', op, left, right };
    }
    return left;
  }

  // Unary: handles - (unary minus) and + (unary plus)
  function parseUnary() {
    if (peek() && peek().type === 'OP' && (peek().value === '-' || peek().value === '+')) {
      const op = consume('OP').value;
      const operand = parseUnary();
      return { type: 'unary', op, operand };
    }
    return parsePrimary();
  }

  // Primary: numbers, cell refs, ranges, function calls, parenthesized expressions
  function parsePrimary() {
    const t = peek();
    if (!t) throw new Error('Unexpected end of expression');

    // Number literal
    if (t.type === 'NUMBER') {
      consume('NUMBER');
      return { type: 'literal', value: t.value };
    }

    // Parenthesized expression
    if (t.type === 'LPAREN') {
      consume('LPAREN');
      const expr = parseExpression();
      consume('RPAREN');
      return expr;
    }

    // Function call
    if (t.type === 'FUNCTION') {
      const funcName = consume('FUNCTION').value;
      consume('LPAREN');
      const args = [];
      if (!peek() || peek().type !== 'RPAREN') {
        args.push(parseExpression());
        while (peek() && peek().type === 'COMMA') {
          consume('COMMA');
          args.push(parseExpression());
        }
      }
      consume('RPAREN');
      return { type: 'function', name: funcName, args };
    }

    // Cell reference (possibly followed by : for range)
    if (t.type === 'CELL_REF') {
      const ref = consume('CELL_REF');
      dependencies.add(ref.value);
      if (peek() && peek().type === 'COLON') {
        // Range: A1:A5
        consume('COLON');
        const endRef = consume('CELL_REF');
        dependencies.add(endRef.value);
        return { type: 'range', startCol: ref.col, startRow: ref.row, endCol: endRef.col, endRow: endRef.row };
      }
      return { type: 'cell_ref', col: ref.col, row: ref.row, key: ref.value };
    }

    throw new Error(`Unexpected token: ${t.type}`);
  }

  const ast = parseExpression();
  if (pos < tokens.length) throw new Error('Unexpected trailing tokens');

  return { ast, dependencies };
}

/* ============================================================
   AST EVALUATOR
   ============================================================ */
function evaluateNode(node, cellKeyForCycleCheck) {
  // Cycle detection: track evaluation stack
  const evalStack = new Set();

  function getCellValue(key) {
    if (evalStack.has(key)) return { value: '#CYCLE!', type: 'error' };
    evalStack.add(key);
    try {
      const cell = state.data[key];
      // Always ensure the dependency is computed before reading it
      computeCell(key, new Set());
      return getComputedValue(key);
    } finally {
      evalStack.delete(key);
    }
  }

  function getComputedValue(key) {
    const cell = state.data[key];
    if (!cell || (cell.value === undefined && cell.raw === undefined)) return { value: '', type: 'text' };
    return { value: cell.value, type: cell.type === 'error' ? 'error' : cell.type };
  }

  function evalNode(n) {
    switch (n.type) {
      case 'literal':
        return n.value;

      case 'cell_ref': {
        const val = getCellValue(cellKey(n.col, n.row));
        if (val.type === 'error') throw new Error(val.value);
        // Try to convert text to number for arithmetic
        if (typeof val.value === 'string' && val.value !== '') {
          const num = parseFloat(val.value);
          if (!isNaN(num)) return num;
          throw new Error('#REF!');
        }
        return val.value === '' ? 0 : val.value;
      }

      case 'range': {
        // Expand range to array of values
        const vals = [];
        const sc = Math.min(n.startCol, n.endCol);
        const ec = Math.max(n.startCol, n.endCol);
        const sr = Math.min(n.startRow, n.endRow);
        const er = Math.max(n.startRow, n.endRow);
        for (let c = sc; c <= ec; c++) {
          for (let r = sr; r <= er; r++) {
            const key = cellKey(c, r);
            const val = getCellValue(key);
            if (val.type === 'error') throw new Error(val.value);
            if (typeof val.value === 'number') vals.push(val.value);
            else if (typeof val.value === 'string' && val.value !== '') {
              const num = parseFloat(val.value);
              if (!isNaN(num)) vals.push(num);
            }
          }
        }
        return vals;
      }

      case 'unary': {
        const operand = evalNode(n.operand);
        if (n.op === '-') return -flatten(operand);
        return flatten(operand);
      }

      case 'binary': {
        const left = evalNode(n.left);
        const right = evalNode(n.right);
        const l = flatten(left);
        const r = flatten(right);
        switch (n.op) {
          case '+': return l + r;
          case '-': return l - r;
          case '*': return l * r;
          case '/':
            if (r === 0) throw new Error('#DIV/0!');
            return l / r;
        }
      }

      case 'function': {
        const args = n.args.map(a => evalNode(a));
        // Flatten all args into a single array of numbers
        let nums = [];
        for (const arg of args) {
          if (Array.isArray(arg)) {
            for (const v of arg) {
              if (typeof v === 'number') nums.push(v);
              else if (typeof v === 'string' && v !== '') {
                const n2 = parseFloat(v);
                if (!isNaN(n2)) nums.push(n2);
              }
            }
          } else if (typeof arg === 'number') {
            nums.push(arg);
          } else if (typeof arg === 'string' && arg !== '') {
            const n2 = parseFloat(arg);
            if (!isNaN(n2)) nums.push(n2);
          }
        }
        switch (n.name) {
          case 'SUM': return nums.reduce((a, b) => a + b, 0);
          case 'AVG':
          case 'AVERAGE':
            return nums.length ? nums.reduce((a, b) => a + b, 0) / nums.length : 0;
          case 'MIN': return nums.length ? Math.min(...nums) : 0;
          case 'MAX': return nums.length ? Math.max(...nums) : 0;
          case 'COUNT': return nums.length;
        }
      }

      default:
        throw new Error('#ERR!');
    }
  }

  function flatten(v) {
    if (Array.isArray(v)) {
      if (v.length === 1) return v[0];
      // Array in binary context - sum it? No, treat as error for non-function contexts
      throw new Error('#ERR!');
    }
    return v;
  }

  try {
    const result = evalNode(node);
    if (typeof result === 'number') {
      if (isNaN(result)) throw new Error('#ERR!');
      if (!isFinite(result)) throw new Error('#ERR!');
      // Round to avoid floating point issues
      return Math.round(result * 1e10) / 1e10;
    }
    return result;
  } catch (e) {
    const msg = e.message || '#ERR!';
    if (msg === '#CYCLE!' || msg === '#DIV/0!' || msg === '#REF!' || msg === '#ERR!') throw e;
    throw new Error('#ERR!');
  }
}

/* ============================================================
   DEPENDENCY GRAPH & RECALCULATION
   ============================================================ */

// Build dependency graph: for each cell, which cells depend on it (reverse deps)
function buildDependencyGraph() {
  const reverseDeps = {}; // depKey -> Set of keys that depend on it

  for (const key in state.data) {
    const cell = state.data[key];
    if (!cell.raw || !cell.raw.startsWith('=')) continue;
    try {
      const parsed = parseFormula(cell.raw.substring(1));
      cell._deps = parsed.dependencies;
      for (const dep of parsed.dependencies) {
        if (!reverseDeps[dep]) reverseDeps[dep] = new Set();
        reverseDeps[dep].add(key);
      }
    } catch (e) {
      cell._deps = new Set();
    }
  }

  return reverseDeps;
}

// Get all downstream dependents of a cell (BFS), in topological order
function getDependents(startKey, reverseDeps) {
  const visited = new Set();
  const order = [];
  const queue = [startKey];
  visited.add(startKey);

  while (queue.length > 0) {
    const current = queue.shift();
    order.push(current);
    const deps = reverseDeps[current] || new Set();
    for (const dep of deps) {
      if (!visited.has(dep)) {
        visited.add(dep);
        queue.push(dep);
      }
    }
  }

  return order;
}

// Detect cycles using DFS with coloring on the reverse-dep graph
function detectCycle(reverseDeps) {
  const cycleCells = new Set();
  const WHITE = 0, GRAY = 1, BLACK = 2;
  const color = {};

  // Initialize colors for all formula cells
  for (const key in state.data) {
    if (state.data[key].raw && state.data[key].raw.startsWith('=')) {
      color[key] = WHITE;
    }
  }

  function dfsCycle(node, path) {
    color[node] = GRAY;
    const deps = reverseDeps[node] || new Set();
    for (const dep of deps) {
      if (!(dep in color)) continue; // Not a formula cell
      if (color[dep] === GRAY) {
        // Found cycle - trace back through path to mark all cycle members
        let idx = path.indexOf(dep);
        if (idx >= 0) {
          for (let i = idx; i < path.length; i++) {
            cycleCells.add(path[i]);
          }
        }
      } else if (color[dep] === WHITE) {
        dfsCycle(dep, [...path, dep]);
      }
    }
    color[node] = BLACK;
  }

  for (const key in state.data) {
    if (state.data[key].raw && state.data[key].raw.startsWith('=') && color[key] === WHITE) {
      dfsCycle(key, [key]);
    }
  }

  return cycleCells;
}

// Compute a single cell's value. Uses evalStack for cycle detection during evaluation.
function computeCell(keyArg, globalCycleSet) {
  const cell = state.data[keyArg];
  if (!cell) return;

  // If this cell is in a known global cycle, mark it immediately
  if (globalCycleSet && globalCycleSet.has(keyArg)) {
    cell.value = '#CYCLE!';
    cell.type = 'error';
    return;
  }

  const raw = cell.raw || '';

  // Empty cell
  if (!raw) {
    cell.value = '';
    cell.type = 'text';
    return;
  }

  // Not a formula - display as-is
  if (raw.charAt(0) !== '=') {
    const num = parseFloat(raw);
    if (!isNaN(num) && raw.trim() === String(num)) {
      cell.value = num;
      cell.type = 'number';
    } else {
      cell.value = raw;
      cell.type = 'text';
    }
    return;
  }

  // It's a formula - parse and evaluate with cycle detection
  try {
    const parsed = parseFormula(raw.substring(1));
    cell._deps = parsed.dependencies;
    const result = evaluateNode(parsed.ast, keyArg);
    if (typeof result === 'number') {
      cell.value = result;
      cell.type = 'number';
    } else {
      cell.value = String(result);
      cell.type = 'text';
    }
  } catch (e) {
    const msg = e.message || '#ERR!';
    if (msg === '#CYCLE!' || msg === '#DIV/0!' || msg === '#REF!' || msg === '#ERR!') {
      cell.value = msg;
    } else {
      cell.value = '#ERR!';
    }
    cell.type = 'error';
  }
}

// Recompute a cell and all its dependents (incremental recalculation)
function recalculate(startKeyArg) {
  const reverseDeps = buildDependencyGraph();

  // Detect cycles across the whole sheet
  const cycleSet = detectCycle(reverseDeps);

  // Get all affected cells in BFS order
  const affected = getDependents(startKeyArg, reverseDeps);

  for (const key of affected) {
    computeCell(key, cycleSet);
  }

  // Update display for all affected cells
  for (const key of affected) {
    updateCellDisplay(key);
  }
}

// Full recalculation of all formula cells in topological order
function fullRecalculate() {
  const reverseDeps = buildDependencyGraph();
  const cycleSet = detectCycle(reverseDeps);

  // Compute non-formula cells first (they have no deps)
  for (const key in state.data) {
    const cell = state.data[key];
    if (!cell.raw || !cell.raw.startsWith('=')) {
      computeCell(key, null);
    }
  }

  // Topological sort: repeatedly find formula cells whose deps are all computed
  const done = new Set();
  let changed = true;
  let iterations = 0;
  const maxIterations = CONFIG.COLS * CONFIG.ROWS + 10;

  while (changed && iterations < maxIterations) {
    changed = false;
    iterations++;
    for (const key in state.data) {
      if (!state.data[key].raw || !state.data[key].raw.startsWith('=')) continue;
      if (done.has(key)) continue;

      const deps = state.data[key]._deps || new Set();
      let canCompute = true;
      for (const dep of deps) {
        // If dependency is a formula cell not yet done, skip
        const depCell = state.data[dep];
        if (depCell && depCell.raw && depCell.raw.startsWith('=') && !done.has(dep)) {
          canCompute = false;
          break;
        }
      }

      if (canCompute) {
        computeCell(key, cycleSet);
        done.add(key);
        changed = true;
      }
    }
  }

  // Remaining uncomputed formula cells are in cycles or have broken deps
  for (const key in state.data) {
    if (!state.data[key].raw || !state.data[key].raw.startsWith('=')) continue;
    if (!done.has(key)) {
      computeCell(key, cycleSet);
    }
  }

  // Update all cell displays
  for (const key in state.data) {
    updateCellDisplay(key);
  }
}

/* ============================================================
   GRID RENDERING
   ============================================================ */
function buildGrid() {
  const table = document.getElementById('spreadsheet-table');
  table.innerHTML = '';

  // Column header row
  const headerRow = document.createElement('tr');
  headerRow.className = 'col-header-row';
  const cornerTh = document.createElement('th');
  cornerTh.style.width = CONFIG.HEADER_WIDTH + 'px';
  cornerTh.style.minWidth = CONFIG.HEADER_WIDTH + 'px';
  cornerTh.style.maxWidth = CONFIG.HEADER_WIDTH + 'px';
  headerRow.appendChild(cornerTh);

  for (let c = 0; c < CONFIG.COLS; c++) {
    const th = document.createElement('th');
    th.textContent = colLetter(c);
    th.style.width = CONFIG.CELL_WIDTH + 'px';
    th.dataset.col = c;
    th.addEventListener('contextmenu', onHeaderContextMenu);
    headerRow.appendChild(th);
  }
  table.appendChild(headerRow);

  // Data rows
  for (let r = 0; r < CONFIG.ROWS; r++) {
    const tr = document.createElement('tr');
    const rowHeader = document.createElement('td');
    rowHeader.className = 'row-header-cell';
    rowHeader.textContent = r + 1;
    rowHeader.addEventListener('contextmenu', onHeaderContextMenu);
    tr.appendChild(rowHeader);

    for (let c = 0; c < CONFIG.COLS; c++) {
      const td = document.createElement('td');
      td.className = 'data-cell';
      td.dataset.col = c;
      td.dataset.row = r;
      td.style.width = CONFIG.CELL_WIDTH + 'px';

      // Click to select
      td.addEventListener('mousedown', onCellMouseDown);
      // Double-click to edit
      td.addEventListener('dblclick', onCellDblClick);

      tr.appendChild(td);
    }
    table.appendChild(tr);
  }
}

function getCellElement(col, row) {
  const table = document.getElementById('spreadsheet-table');
  const rows = table.rows;
  if (row >= rows.length - 1 || col >= rows[0].cells.length - 1) return null;
  return rows[row + 1].cells[col + 1]; // +1 for header row/col
}

function updateCellDisplay(keyArg) {
  const ref = parseCellRef(keyArg);
  if (!ref) return;
  const td = getCellElement(ref.col, ref.row);
  if (!td || state.editingCell === keyArg) return;

  const cell = state.data[keyArg];
  td.innerHTML = '';

  if (cell && cell.raw !== undefined) {
    let displayValue = cell.value;
    let alignClass = 'left';
    let errorClass = '';

    if (cell.type === 'number') {
      // Format number: up to 10 decimal places, remove trailing zeros
      displayValue = formatNumber(cell.value);
      alignClass = 'right';
    } else if (cell.type === 'error') {
      errorClass = ' error';
    }

    const span = document.createElement('span');
    span.className = `cell-value ${alignClass}${errorClass}`;
    span.textContent = displayValue !== undefined && displayValue !== null ? displayValue : '';
    td.appendChild(span);
  }

  // Update selection highlight
  if (state.selectedCell) {
    const selKey = cellKey(state.selectedCell.col, state.selectedCell.row);
    if (selKey === keyArg) {
      td.classList.add('selected');
    } else {
      td.classList.remove('selected');
    }
  }
}

function formatNumber(n) {
  if (n === 0) return '0';
  // If it's an integer, show as integer
  if (Number.isInteger(n)) return String(n);
  // Otherwise up to 10 decimal places, trim trailing zeros
  const s = n.toFixed(10).replace(/0+$/, '').replace(/\.$/, '');
  return s;
}

/* ============================================================
   CELL SELECTION & EDITING
   ============================================================ */
let mouseDownTarget = null;

function onCellMouseDown(e) {
  if (e.button !== 0) return; // Only left click
  const td = e.target.closest('.data-cell');
  if (!td) return;
  e.preventDefault();
  mouseDownTarget = td;

  const col = parseInt(td.dataset.col);
  const row = parseInt(td.dataset.row);
  selectCell(col, row);
}

function onCellDblClick(e) {
  const td = e.target.closest('.data-cell');
  if (!td) return;
  const col = parseInt(td.dataset.col);
  const row = parseInt(td.dataset.row);
  startEditing(col, row);
}

function selectCell(col, row) {
  // Clear previous selection
  if (state.selectedCell) {
    const prevTd = getCellElement(state.selectedCell.col, state.selectedCell.row);
    if (prevTd) {
      prevTd.classList.remove('selected');
    }
  }

  state.selectedCell = { col, row };
  const td = getCellElement(col, row);
  if (td) td.classList.add('selected');

  // Update formula bar and cell ref display
  const key = cellKey(col, row);
  document.getElementById('cell-ref-display').textContent = key;

  const cell = state.data[key];
  const fb = document.getElementById('formula-bar');
  fb.value = (cell && cell.raw !== undefined) ? cell.raw : '';
}

function startEditing(col, row) {
  // Commit any current edit first
  commitEdit();

  const key = cellKey(col, row);
  state.editingCell = key;

  const td = getCellElement(col, row);
  if (!td) return;

  const cell = state.data[key];
  td.classList.add('editing');
  td.innerHTML = '';

  const input = document.createElement('input');
  input.type = 'text';
  input.className = 'cell-input';
  input.value = (cell && cell.raw !== undefined) ? cell.raw : '';
  input.spellcheck = false;

  // Sync with formula bar
  const fb = document.getElementById('formula-bar');
  fb.value = input.value;

  input.addEventListener('input', () => {
    fb.value = input.value;
  });

  input.addEventListener('keydown', (e) => onCellInputKeyDown(e, col, row));
  input.addEventListener('blur', () => commitEdit());

  td.appendChild(input);
  input.focus();
  input.select();
}

function onCellInputKeyDown(e, col, row) {
  const key = cellKey(col, row);

  if (e.key === 'Enter') {
    e.preventDefault();
    commitEdit();
    // Move down
    if (row + 1 < CONFIG.ROWS) selectCell(col, row + 1);
    return;
  }

  if (e.key === 'Tab') {
    e.preventDefault();
    commitEdit();
    // Move right
    if (col + 1 < CONFIG.COLS) selectCell(col + 1, row);
    else if (row + 1 < CONFIG.ROWS) selectCell(0, row + 1);
    return;
  }

  if (e.key === 'Escape') {
    e.preventDefault();
    cancelEdit();
    return;
  }
}

function commitEdit() {
  if (!state.editingCell) return;

  const key = state.editingCell;
  const td = getCellElement(
    parseCellRef(key).col,
    parseCellRef(key).row
  );

  // Get value from either the cell input or formula bar (whichever has focus)
  let rawValue = '';
  if (td && td.querySelector('.cell-input')) {
    rawValue = td.querySelector('.cell-input').value;
  } else {
    rawValue = document.getElementById('formula-bar').value;
  }

  // Set cell data
  if (!state.data[key]) state.data[key] = {};
  state.data[key].raw = rawValue;

  state.editingCell = null;

  // Recalculate
  recalculate(key);

  // Update display
  updateCellDisplay(key);

  // Sync formula bar with committed value
  document.getElementById('formula-bar').value = rawValue;

  // Save to localStorage
  saveToLocalStorage();
}

function cancelEdit() {
  if (!state.editingCell) return;
  const key = state.editingCell;
  const ref = parseCellRef(key);
  const td = getCellElement(ref.col, ref.row);
  if (td) {
    td.classList.remove('editing');
  }
  state.editingCell = null;
  updateCellDisplay(key);
}

/* ============================================================
   KEYBOARD HANDLING
   ============================================================ */
document.addEventListener('keydown', (e) => {
  // If editing in formula bar, handle specially
  if (document.activeElement === document.getElementById('formula-bar')) {
    onFormulaBarKeyDown(e);
    return;
  }

  // If a cell input has focus, let it handle keys
  if (document.activeElement && document.activeElement.classList.contains('cell-input')) {
    return;
  }

  if (!state.selectedCell) return;

  const { col, row } = state.selectedCell;

  switch (e.key) {
    case 'ArrowUp':
      e.preventDefault();
      if (row > 0) selectCell(col, row - 1);
      break;
    case 'ArrowDown':
      e.preventDefault();
      if (row < CONFIG.ROWS - 1) selectCell(col, row + 1);
      break;
    case 'ArrowLeft':
      e.preventDefault();
      if (col > 0) selectCell(col - 1, row);
      break;
    case 'ArrowRight':
      e.preventDefault();
      if (col < CONFIG.COLS - 1) selectCell(col + 1, row);
      break;
    case 'Tab':
      e.preventDefault();
      if (e.shiftKey) {
        if (col > 0) selectCell(col - 1, row);
      } else {
        if (col < CONFIG.COLS - 1) selectCell(col + 1, row);
      }
      break;
    case 'Enter':
      e.preventDefault();
      startEditing(col, row);
      break;
    case 'Delete':
    case 'Backspace':
      e.preventDefault();
      clearSelectedCell();
      break;
    default:
      // If it's a printable character, start editing
      if (e.key.length === 1 && !e.ctrlKey && !e.metaKey) {
        e.preventDefault();
        startEditing(col, row);
        // Type the character into the input
        const td = getCellElement(col, row);
        if (td) {
          const input = td.querySelector('.cell-input');
          if (input) {
            input.value = e.key;
            document.getElementById('formula-bar').value = e.key;
          }
        }
      }
  }
});

function onFormulaBarKeyDown(e) {
  if (!state.selectedCell) return;
  const { col, row } = state.selectedCell;
  const key = cellKey(col, row);

  if (e.key === 'Enter') {
    e.preventDefault();
    // Commit the formula bar value
    const fb = document.getElementById('formula-bar');
    if (!state.data[key]) state.data[key] = {};
    state.data[key].raw = fb.value;
    recalculate(key);
    updateCellDisplay(key);
    saveToLocalStorage();

    // Move down
    if (row + 1 < CONFIG.ROWS) selectCell(col, row + 1);
    return;
  }

  if (e.key === 'Escape') {
    e.preventDefault();
    const cell = state.data[key];
    document.getElementById('formula-bar').value = (cell && cell.raw !== undefined) ? cell.raw : '';
    return;
  }

  if (e.key === 'Tab') {
    e.preventDefault();
    // Commit and move right
    const fb = document.getElementById('formula-bar');
    if (!state.data[key]) state.data[key] = {};
    state.data[key].raw = fb.value;
    recalculate(key);
    updateCellDisplay(key);
    saveToLocalStorage();

    if (col + 1 < CONFIG.COLS) selectCell(col + 1, row);
    return;
  }
}

// Sync formula bar changes to cell in real-time when editing a cell
document.getElementById('formula-bar').addEventListener('input', () => {
  if (!state.selectedCell || state.editingCell) return;
  const key = cellKey(state.selectedCell.col, state.selectedCell.row);
  // Don't commit on every keystroke - only on Enter/blur
});

/* ============================================================
   CELL OPERATIONS
   ============================================================ */
function clearSelectedCell() {
  if (!state.selectedCell) return;
  const key = cellKey(state.selectedCell.col, state.selectedCell.row);

  if (state.data[key]) {
    delete state.data[key];
    recalculate(key);
    updateCellDisplay(key);
    saveToLocalStorage();

    // Update formula bar
    document.getElementById('formula-bar').value = '';
  }
}

/* ============================================================
   ROW/COLUMN INSERT/DELETE WITH FORMULA REWRITING
   ============================================================ */
function rewriteFormulasForRowInsert(insertRow) {
  for (const key in state.data) {
    const cell = state.data[key];
    if (!cell.raw || !cell.raw.startsWith('=')) continue;

    // Parse the formula to find all cell references and ranges
    let newRaw = cell.raw;

    // Rewrite cell references: shift rows >= insertRow down by 1
    newRaw = rewriteRefsInFormula(newRaw, (col, row) => {
      if (row >= insertRow) return { col, row: row + 1 };
      return null; // No change
    });

    cell.raw = newRaw;
  }
}

function rewriteFormulasForRowDelete(deleteRow) {
  for (const key in state.data) {
    const cell = state.data[key];
    if (!cell.raw || !cell.raw.startsWith('=')) continue;

    let newRaw = cell.raw;

    // Rewrite cell references: shift rows > deleteRow up by 1, remove refs to deleted row
    newRaw = rewriteRefsInFormula(newRaw, (col, row) => {
      if (row === deleteRow) return 'REF'; // Reference to deleted row
      if (row > deleteRow) return { col, row: row - 1 };
      return null; // No change
    });

    cell.raw = newRaw;
  }
}

function rewriteFormulasForColInsert(insertCol) {
  for (const key in state.data) {
    const cell = state.data[key];
    if (!cell.raw || !cell.raw.startsWith('=')) continue;

    let newRaw = cell.raw;
    newRaw = rewriteRefsInFormula(newRaw, (col, row) => {
      if (col >= insertCol) return { col: col + 1, row };
      return null;
    });

    cell.raw = newRaw;
  }
}

function rewriteFormulasForColDelete(deleteCol) {
  for (const key in state.data) {
    const cell = state.data[key];
    if (!cell.raw || !cell.raw.startsWith('=')) continue;

    let newRaw = cell.raw;
    newRaw = rewriteRefsInFormula(newRaw, (col, row) => {
      if (col === deleteCol) return 'REF';
      if (col > deleteCol) return { col: col - 1, row };
      return null;
    });

    cell.raw = newRaw;
  }
}

function rewriteRefsInFormula(formula, transformFn) {
  // Replace all cell references in the formula string
  // Pattern: letter(s) followed by digits
  let result = formula;

  // We need to be careful not to replace function names or partial matches
  // Process from right to left to avoid offset issues... actually let's use a proper approach
  const tokens = tokenize(formula);
  let newTokens = [];

  for (const token of tokens) {
    if (token.type === 'CELL_REF') {
      const transformed = transformFn(token.col, token.row);
      if (transformed === 'REF') {
        // Replace with #REF! - but we need to handle this in the formula string
        newTokens.push({ type: 'ERROR_TOKEN', value: '#REF!' });
      } else if (transformed) {
        newTokens.push({
          type: 'CELL_REF',
          value: cellKey(transformed.col, transformed.row),
          col: transformed.col,
          row: transformed.row
        });
      } else {
        newTokens.push(token);
      }
    } else {
      newTokens.push(token);
    }
  }

  // Reconstruct formula from tokens
  return reconstructFormula(newTokens);
}

function reconstructFormula(tokens) {
  let result = '';
  for (const t of tokens) {
    switch (t.type) {
      case 'NUMBER': result += String(t.value); break;
      case 'CELL_REF': result += t.value; break;
      case 'FUNCTION': result += t.value; break;
      case 'OP': result += t.value; break;
      case 'LPAREN': result += '('; break;
      case 'RPAREN': result += ')'; break;
      case 'COMMA': result += ','; break;
      case 'COLON': result += ':'; break;
      case 'ERROR_TOKEN': result += t.value; break;
    }
  }
  return result;
}

function shiftDataRows(fromRow, direction) {
  // direction: +1 for insert (shift down), -1 for delete (shift up)
  const newKeys = {};
  for (const key in state.data) {
    const ref = parseCellRef(key);
    if (!ref) continue;

    let newRow = ref.row;
    if (direction > 0 && ref.row >= fromRow) {
      newRow = ref.row + direction;
    } else if (direction < 0 && ref.row > fromRow) {
      newRow = ref.row + direction;
    }

    const newKey = cellKey(ref.col, newRow);
    newKeys[newKey] = state.data[key];
  }
  return newKeys;
}

function shiftDataCols(fromCol, direction) {
  const newKeys = {};
  for (const key in state.data) {
    const ref = parseCellRef(key);
    if (!ref) continue;

    let newCol = ref.col;
    if (direction > 0 && ref.col >= fromCol) {
      newCol = ref.col + direction;
    } else if (direction < 0 && ref.col > fromCol) {
      newCol = ref.col + direction;
    }

    const newKey = cellKey(newCol, ref.row);
    newKeys[newKey] = state.data[key];
  }
  return newKeys;
}

function insertRow() {
  if (!state.selectedCell) return;
  const row = state.selectedCell.row;

  // Rewrite formulas first (before shifting data)
  rewriteFormulasForRowInsert(row);

  // Shift data rows down
  const shiftedData = shiftDataRows(row, 1);

  // Clear deleted-row cells and apply new data
  state.data = {};
  for (const key in shiftedData) {
    state.data[key] = shiftedData[key];
  }

  fullRecalculate();
  saveToLocalStorage();
}

function deleteRow() {
  if (!state.selectedCell) return;
  const row = state.selectedCell.row;

  // Rewrite formulas (before shifting data)
  rewriteFormulasForRowDelete(row);

  // Remove cells in the deleted row and shift remaining up
  const shiftedData = {};
  for (const key in state.data) {
    const ref = parseCellRef(key);
    if (!ref) continue;
    if (ref.row === row) continue; // Skip deleted row
    let newRow = ref.row;
    if (ref.row > row) newRow = ref.row - 1;
    shiftedData[cellKey(ref.col, newRow)] = state.data[key];
  }

  state.data = shiftedData;
  fullRecalculate();
  saveToLocalStorage();
}

function insertCol() {
  if (!state.selectedCell) return;
  const col = state.selectedCell.col;

  rewriteFormulasForColInsert(col);

  const shiftedData = shiftDataCols(col, 1);
  state.data = {};
  for (const key in shiftedData) {
    state.data[key] = shiftedData[key];
  }

  fullRecalculate();
  saveToLocalStorage();
}

function deleteCol() {
  if (!state.selectedCell) return;
  const col = state.selectedCell.col;

  rewriteFormulasForColDelete(col);

  const shiftedData = {};
  for (const key in state.data) {
    const ref = parseCellRef(key);
    if (!ref) continue;
    if (ref.col === col) continue;
    let newCol = ref.col;
    if (ref.col > col) newCol = ref.col - 1;
    shiftedData[cellKey(newCol, ref.row)] = state.data[key];
  }

  state.data = shiftedData;
  fullRecalculate();
  saveToLocalStorage();
}

/* ============================================================
   CONTEXT MENU (for row/column headers)
   ============================================================ */
function onHeaderContextMenu(e) {
  e.preventDefault();
  const menu = document.getElementById('context-menu');
  const target = e.target;

  let col = null, row = null;
  if (target.dataset.col !== undefined) col = parseInt(target.dataset.col);
  // Row headers don't have dataset.row in the header cell itself

  menu.innerHTML = '';

  if (col !== null) {
    addMenuItem(menu, 'Insert Column Left', () => {
      state.selectedCell = { col, row: 0 };
      insertCol();
      hideContextMenu();
    });
    addMenuItem(menu, 'Delete Column', () => {
      state.selectedCell = { col, row: 0 };
      deleteCol();
      hideContextMenu();
    });
  }

  menu.style.display = 'block';
  menu.style.left = e.clientX + 'px';
  menu.style.top = e.clientY + 'px';
}

function addMenuItem(menu, text, onClick) {
  const item = document.createElement('div');
  item.className = 'menu-item';
  item.textContent = text;
  item.addEventListener('click', onClick);
  menu.appendChild(item);
}

function hideContextMenu() {
  document.getElementById('context-menu').style.display = 'none';
}

document.addEventListener('click', (e) => {
  if (!e.target.closest('#context-menu')) {
    hideContextMenu();
  }
});

/* ============================================================
   LOCAL STORAGE PERSISTENCE
   ============================================================ */
function saveToLocalStorage() {
  try {
    const serialized = {};
    for (const key in state.data) {
      serialized[key] = state.data[key].raw;
    }
    localStorage.setItem('spreadsheet-data', JSON.stringify(serialized));
  } catch (e) {
    // Storage full or unavailable
  }
}

function loadFromLocalStorage() {
  try {
    const saved = localStorage.getItem('spreadsheet-data');
    if (!saved) return;
    const parsed = JSON.parse(saved);
    for (const key in parsed) {
      state.data[key] = { raw: parsed[key], value: '', type: 'text', computed: false };
    }
  } catch (e) {
    // Corrupted data
  }
}

/* ============================================================
   INITIALIZATION
   ============================================================ */
function init() {
  buildGrid();
  loadFromLocalStorage();
  fullRecalculate();

  // Select A1 by default
  selectCell(0, 0);

  // Handle formula bar blur - commit changes
  document.getElementById('formula-bar').addEventListener('blur', () => {
    if (!state.selectedCell) return;
    const key = cellKey(state.selectedCell.col, state.selectedCell.row);
    const fb = document.getElementById('formula-bar');
    const cell = state.data[key];
    const currentRaw = (cell && cell.raw !== undefined) ? cell.raw : '';

    if (fb.value !== currentRaw) {
      // Commit formula bar changes
      if (!state.data[key]) state.data[key] = {};
      state.data[key].raw = fb.value;
      recalculate(key);
      updateCellDisplay(key);
      saveToLocalStorage();
    }
  });

  // Click outside grid to deselect context menu
  document.getElementById('grid-wrapper').addEventListener('mousedown', (e) => {
    hideContextMenu();
  });
}

init();
</script>
</body>
</html>
<!-- agent-meta {"model":"qwen3.6-27b-mtp","provider":"lmstudio","persona":"full","sessionId":"34d042b8-00f7-4779-9202-a6722beb59bd","tokensIn":2453634,"tokensOut":42164,"tokensTotal":2495798,"turns":49,"toolCalls":48,"failedToolCalls":0,"timestamp":"2026-07-29T23:44:40.643Z"} -->