Spreadsheet
Qwen 3.6 27b MTP · 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 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"} -->