Spreadsheet
ling 3.0 tiny:free · 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 43,104 bytes · SHA-256 b20b383f6703
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Spreadsheet</title>
<style>
:root {
--bg: #f5f5f5;
--surface: #fff;
--header-bg: #e8e8e8;
--header-fg: #333;
--grid-line: #d0d0d0;
--cell-bg: #fff;
--cell-border: #ccc;
--cell-hover: #f0f8ff;
--accent: #0078D4;
--accent-dark: #005a9e;
--accent-light: #d9e8f8;
--selected-bg: #cce5ff;
--selected-border: #0078D4;
--formula-bg: #fafafa;
--formula-border: #ddd;
--text: #333;
--text-secondary: #666;
--error: #d00;
--error-bg: #fff0f0;
--shadow: 0 1px 3px rgba(0,0,0,0.12);
--radius: 2px;
}
* { margin: 0; padding: 0; box-sizing: border-box; }
body {
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
background: var(--bg);
color: var(--text);
height: 100vh;
display: flex;
flex-direction: column;
overflow: hidden;
}
#toolbar {
display: flex;
align-items: center;
gap: 8px;
padding: 4px 12px;
background: var(--header-bg);
border-bottom: 1px solid var(--grid-line);
min-height: 36px;
user-select: none;
}
#toolbar button {
padding: 4px 10px;
border: 1px solid #bbb;
background: var(--surface);
border-radius: var(--radius);
cursor: pointer;
font-size: 12px;
font-weight: 500;
color: var(--text);
transition: background 0.1s;
}
#toolbar button:hover { background: var(--accent-light); }
#toolbar button.active { background: var(--accent); color: #fff; border-color: var(--accent); }
#toolbar input {
border: 1px solid #bbb;
border-radius: var(--radius);
padding: 4px 8px;
font-size: 12px;
width: 200px;
background: var(--surface);
}
#toolbar input:focus { outline: none; border-color: var(--accent); }
#formula-bar {
display: flex;
align-items: center;
gap: 6px;
padding: 4px 12px;
background: var(--formula-bg);
border-bottom: 2px solid var(--accent);
min-height: 34px;
position: sticky;
top: 0;
z-index: 5;
}
#formula-bar .label {
font-size: 11px;
font-weight: 600;
color: var(--text-secondary);
text-transform: uppercase;
white-space: nowrap;
}
#formula-bar input {
flex: 1;
border: 1px solid var(--formula-border);
border-radius: 3px;
padding: 4px 8px;
font-size: 13px;
font-family: 'Courier New', monospace;
background: var(--surface);
outline: none;
min-width: 100px;
}
#formula-bar input:focus { border-color: var(--accent); box-shadow: 0 0 0 2px var(--accent-light); }
#formula-bar .hint {
font-size: 11px;
color: var(--text-secondary);
font-style: italic;
}
#grid-container {
flex: 1;
overflow: auto;
position: relative;
}
#spreadsheet {
border-collapse: collapse;
border-spacing: 0;
font-size: 13px;
table-layout: fixed;
width: max-content;
}
#spreadsheet thead th {
background: var(--header-bg);
color: var(--header-fg);
font-weight: 600;
font-size: 11px;
text-transform: uppercase;
letter-spacing: 0.5px;
user-select: none;
position: sticky;
top: 0;
z-index: 1;
border: 1px solid var(--grid-line);
}
#spreadsheet thead th.col-header {
min-width: 52px;
width: 52px;
text-align: center;
cursor: default;
border-right: 1px solid var(--grid-line);
}
#spreadsheet td {
border: 1px solid var(--cell-border);
min-height: 24px;
width: 52px;
height: 24px;
font-size: 13px;
font-family: 'Courier New', monospace;
text-align: right;
font-weight: 400;
line-height: 24px;
background: var(--cell-bg);
transition: background 0.05s, border-color 0.05s;
cursor: text;
word-break: break-all;
position: relative;
}
#spreadsheet td.data-cell {
padding: 0;
cursor: text;
}
#spreadsheet td.data-cell:hover { background: var(--cell-hover); }
#spreadsheet td.selected {
outline: 2px solid var(--selected-border);
background: var(--selected-bg);
z-index: 2;
}
#spreadsheet td.selected-above {
outline: 2px solid var(--selected-border);
background: var(--selected-bg);
z-index: 2;
}
#spreadsheet td.error {
color: var(--error);
font-weight: 500;
}
#spreadsheet thead {
position: sticky;
top: 0;
z-index: 10;
}
#formula-bar {
position: sticky;
top: 46px;
z-index: 5;
background: #fafafa;
}
#grid-container::-webkit-scrollbar { width: 8px; height: 8px; }
#grid-container::-webkit-scrollbar-track { background: transparent; }
#grid-container::-webkit-scrollbar-thumb { background: #ccc; border-radius: 4px; }
#grid-container::-webkit-scrollbar-thumb:hover { background: #aaa; }
#context-menu {
position: fixed;
background: var(--surface);
border: 1px solid #ccc;
border-radius: 4px;
box-shadow: 0 2px 8px rgba(0,0,0,0.15);
padding: 4px 0;
min-width: 120px;
display: none;
z-index: 1000;
}
#context-menu button {
display: block;
width: 100%;
padding: 6px 16px;
border: none;
background: none;
cursor: pointer;
font-size: 13px;
text-align: left;
color: var(--text);
transition: background 0.1s;
}
#context-menu button:hover { background: var(--accent-light); }
#status-bar {
height: 22px;
background: #fafafa;
border-top: 1px solid var(--grid-line);
display: flex;
align-items: center;
padding: 0 8px;
font-size: 10px;
color: var(--text-secondary);
user-select: none;
}
.key-hint {
display: inline-block;
background: #e0e0e0;
border-radius: 3px;
padding: 0 4px;
font-size: 11px;
font-family: monospace;
min-width: 18px;
text-align: center;
}
@media (max-width: 1200px) {
#spreadsheet td { width: 44px; min-height: 22px; font-size: 12px; }
}
</style>
</head>
<body>
<div id="app">
<header id="toolbar">
<button id="btn-insert-row" title="Insert Row (Ctrl+Shift+I)">Insert Row</button>
<button id="btn-delete-row" title="Delete Row (Ctrl+Shift+D)">Delete Row</button>
<button id="btn-insert-col" title="Insert Column (Ctrl+Shift+C)">Insert Col</button>
<button id="btn-delete-col" title="Delete Column (Ctrl+Shift+X)">Delete Col</button>
<button id="btn-clear" title="Clear Cell">Clear</button>
<label>Formula Bar:</label>
<input type="text" id="formula-bar-input" placeholder="=A1*2" spellcheck="false">
<span class="hint" id="formula-hint">Double-click or click to edit</span>
</header>
<div id="formula-bar" style="display:none;">
<span class="label">Formula:</span>
<input type="text" id="formula-bar-input" placeholder="=A1*2" spellcheck="false">
</div>
<div id="grid-container">
<table id="spreadsheet"></table>
</div>
<div id="status-bar">
<span id="status-info">Ready</span>
</div>
<div id="context-menu">
<button data-action="insert-row">Insert Row</button>
<button data-action="delete-row">Delete Row</button>
<button data-action="insert-col">Insert Column</button>
<button data-action="delete-col">Delete Column</button>
</div>
</div>
<script>
// ============================================================
// Configuration
// ============================================================
const CFG = {
ROWS: 100,
COLS: 26,
CELL_W: 56,
CELL_H: 24,
COL_W: 52,
ROW_H: 24,
HEADER_H: 28,
FORMULA_H: 32,
COL_LABELS: 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
};
// ============================================================
// Formula Tokenizer
// ============================================================
function tokenize(formula) {
const tokens = [];
let i = 0;
const s = formula;
while (i < s.length) {
const c = s[i];
if (c === ' ' || c === '\t') { i++; continue; }
if (c === '=') { tokens.push({ type: 'ASSIGN', value: '=' }); i++; continue; }
if (c === '+') { tokens.push({ type: 'OP', value: '+' }); i++; continue; }
if (c === '-') { tokens.push({ type: 'OP', value: '-' }); i++; continue; }
if (c === '*') { tokens.push({ type: 'OP', value: '*' }); i++; continue; }
if (c === '/') { tokens.push({ type: 'OP', value: '/' }); i++; continue; }
if (c === '(') { tokens.push({ type: 'LPAREN', value: '(' }); i++; continue; }
if (c === ')') { tokens.push({ type: 'RPAREN', value: ')' }); i++; continue; }
if (c === ':') { tokens.push({ type: 'COLON', value: ':' }); i++; continue; }
if (c === ',') { tokens.push({ type: 'COMMA', value: ',' }); i++; continue; }
if (/[a-zA-Z]/.test(c)) {
let ident = '';
while (i < s.length && /[a-zA-Z0-9]/.test(s[i])) {
ident += s[i];
i++;
}
// Check for function call or cell reference
if (i < s.length && s[i] === '(') {
tokens.push({ type: 'IDENT', value: ident });
} else if (i < s.length && /[0-9]/.test(s[i])) {
// Cell reference (letter + digits)
let cellRef = '';
let j = i;
while (j < s.length && /[a-zA-Z0-9]/.test(s[j])) {
cellRef += s[j];
j++;
}
tokens.push({ type: 'CELLREF', value: cellRef });
i = j;
} else {
tokens.push({ type: 'IDENT', value: ident });
}
continue;
}
if (/[0-9]/.test(c)) {
let num = '';
while (i < s.length && /[0-9.]/.test(s[i])) {
num += s[i];
i++;
}
tokens.push({ type: 'NUMBER', value: parseFloat(num) });
continue;
}
i++;
}
tokens.push({ type: 'EOF', value: null });
return tokens;
}
// ============================================================
// Formula Parser (Recursive Descent)
// ============================================================
function parse(tokens) {
let pos = 0;
const peek = () => tokens[pos] || null;
const advance = () => tokens[pos++];
function parseExpression() {
let left = parseAddSub();
while (peek() && peek().type === 'OP' && (peek().value === '+' || peek().value === '-')) {
const op = advance();
const right = parseAddSub();
left = { type: 'BinaryOp', op: op.value, left, right };
}
return left;
}
function parseAddSub() {
let left = parseMulDiv();
while (peek() && peek().type === 'OP' && (peek().value === '+' || peek().value === '-')) {
const op = advance();
const right = parseMulDiv();
left = { type: 'BinaryOp', op: op.value, left, right };
}
return left;
}
function parseMulDiv() {
let left = parsePrimary();
while (peek() && peek().type === 'OP' && (peek().value === '*' || peek().value === '/')) {
const op = advance();
const right = parsePrimary();
left = { type: 'BinaryOp', op: op.value, left, right };
}
return left;
}
function parsePrimary() {
const token = peek();
if (!token) throw new Error('Unexpected end of formula');
if (token.type === 'NUMBER') {
advance();
return { type: 'Number', value: token.value };
}
if (token.type === 'IDENT') {
const ident = token.value;
if (peek() && peek().type === 'LPAREN') {
advance(); // consume IDENT
const args = parseFunctionArgs();
advance(); // consume RPAREN
return { type: 'Function', name: ident, args };
}
advance();
return { type: 'CellRef', value: ident };
}
if (token.type === 'LPAREN') {
advance();
const expr = parseExpression();
advance(); // consume RPAREN
return expr;
}
if (token.type === 'CELLREF') {
advance();
return { type: 'CellRef', value: token.value };
}
if (token.type === 'COLON') {
// Handle colon as range separator
advance();
const startToken = peek();
if (startToken && startToken.type === 'CELLREF') {
advance();
const endToken = advance(); // COLON is already consumed
const endRef = peek();
if (endRef && endRef.type === 'CELLREF') {
advance();
return { type: 'Range', start: startToken.value, end: endRef.value };
}
}
throw new Error('Unexpected colon');
}
throw new Error('Unexpected token: ' + JSON.stringify(token));
}
function parseFunctionArgs() {
const args = [];
while (peek() && peek().type !== 'RPAREN') {
args.push(parseExpression());
if (peek() && peek().type === 'COMMA') {
advance();
}
}
return args;
}
const expr = parseExpression();
return expr;
}
// ============================================================
// Formula Evaluator
// ============================================================
function evaluate(node, sheet, visited, memo, depth) {
if (depth > 100) return '#CYCLE!';
switch (node.type) {
case 'Number':
return node.value;
case 'CellRef': {
const key = cellRefKey(node.value);
const cell = sheet.getCell(key);
if (!cell) return '#REF!';
return resolveCell(cell, sheet, visited, memo, depth);
}
case 'Identifier':
return node.value;
case 'Range': {
const rangeResult = evaluateRange(node, sheet, visited, memo, depth);
if (rangeResult === '#CYCLE!') return '#CYCLE!';
return rangeResult;
}
case 'Function': {
return evaluateFunction(node, sheet, visited, memo, depth);
}
case 'BinaryOp': {
const left = evaluate(node.left, sheet, visited, memo, depth);
if (left === '#CYCLE!' || left === '#ERR!') return left;
const right = evaluate(node.right, sheet, visited, memo, depth);
if (right === '#CYCLE!' || right === '#ERR!') return right;
if (left === '#REF!' || right === '#REF!') return '#REF!';
if (left === '#ERR!' || right === '#ERR!') return '#ERR!';
let result;
switch (node.op) {
case '+': result = left + right; break;
case '-': result = left - right; break;
case '*': result = left * right; break;
case '/':
if (right === 0) return '#DIV/0!';
result = left / right;
break;
default: return '#ERR!';
}
if (!isFinite(result)) return '#ERR!';
return result;
}
case 'UnaryOp': {
const operand = evaluate(node.operand, sheet, visited, memo, depth);
if (operand === '#CYCLE!' || operand === '#ERR!') return operand;
if (operand === '#REF!') return '#REF!';
if (typeof operand === 'number') {
if (node.op === '-') return -operand;
}
return operand;
}
case 'Paren':
return evaluate(node.expression, sheet, visited, memo, depth);
default:
return '#ERR!';
}
}
function resolveCell(cell, sheet, visited, memo, depth) {
if (visited.has(cell.key)) return '#CYCLE!';
if (cell.error) return cell.error;
if (cell.value === null || cell.value === undefined) return cell.value;
if (typeof cell.value === 'number' && !isNaN(cell.value)) return cell.value;
if (typeof cell.value === 'string') return cell.value;
// Try to evaluate formula
if (cell.formula && cell.formula.startsWith('=')) {
visited.add(cell.key);
const result = evaluate(cell.formula, sheet, visited, memo, depth);
visited.delete(cell.key);
return result;
}
return cell.value;
}
function evaluateRange(node, sheet, visited, memo, depth) {
if (node.type === 'Range') {
const cells = getCellsInRange(node, sheet);
if (cells.length === 0) return 0;
const values = cells.map(k => {
const cell = sheet.getCell(k);
if (!cell) return '#REF!';
const val = resolveCell(cell, sheet, visited, memo, depth);
if (val === '#CYCLE!' || val === '#REF!' || val === '#ERR!') return val;
return val;
});
// Filter out errors and non-numbers
const numbers = values.filter(v => typeof v === 'number' && isFinite(v));
if (numbers.length === 0) return '#ERR!';
switch (node.name) {
case 'SUM':
return numbers.reduce((a, b) => a + b, 0);
case 'AVG':
case 'AVERAGE':
return numbers.reduce((a, b) => a + b, 0) / numbers.length;
case 'MIN':
return Math.min(...numbers);
case 'MAX':
return Math.max(...numbers);
case 'COUNT':
return numbers.length;
default:
return '#ERR!';
}
}
return '#ERR!';
}
function evaluateFunction(node, sheet, visited, memo, depth) {
const args = node.args.map(arg => evaluate(arg, sheet, visited, memo, depth));
if (args.some(v => v === '#CYCLE!' || v === '#REF!' || v === '#ERR!')) return '#CYCLE!';
if (args.some(v => typeof v !== 'number')) return '#ERR!';
switch (node.name) {
case 'SUM':
return args.reduce((a, b) => a + b, 0);
case 'AVG':
case 'AVERAGE':
return args.reduce((a, b) => a + b, 0) / args.length;
case 'MIN':
return Math.min(...args);
case 'MAX':
return Math.max(...args);
case 'COUNT':
return args.filter(v => typeof v === 'number').length;
default:
return '#ERR!';
}
}
function cellRefKey(cellRef) {
const col = cellRef.charCodeAt(0) - 65;
const row = parseInt(cellRef.slice(1));
if (isNaN(col) || isNaN(row) || col < 0 || col >= CFG.COLS || row < 1 || row > CFG.ROWS) {
return null;
}
return String.fromCharCode(65 + col) + row;
}
function getCellsInRange(range, sheet) {
const cells = [];
const parts = range.value.split(':');
const startParts = parts[0].split('');
const endParts = parts[1].split('');
const startCol = startParts[0].charCodeAt(0) - 65;
const startRow = parseInt(startParts[1]);
const endCol = endParts[0].charCodeAt(0) - 65;
const endRow = parseInt(endParts[1]);
for (let r = startRow; r <= endRow; r++) {
for (let c = startCol; c <= endCol; c++) {
const key = String.fromCharCode(65 + c) + r;
cells.push(key);
}
}
return cells;
}
// ============================================================
// Spreadsheet Model
// ============================================================
class SpreadsheetModel {
constructor(ROWS, COLS) {
this.ROWS = ROWS;
this.COLS = COLS;
this.cells = new Map();
this.dependents = new Map();
this.dependencies = new Map();
this.onChange = null;
this._dirtyCells = new Set();
}
getCellKey(row, col) {
return String.fromCharCode(65 + col) + row;
}
getCell(row, col) {
return this.cells.get(this.getCellKey(row, col)) || null;
}
setCell(row, col, value) {
const key = this.getCellKey(row, col);
const cell = {
key,
formula: value instanceof String ? value : (value === '' ? '' : String(value)),
value: value instanceof String ? null : (value === '' ? null : value),
error: null,
_value: value instanceof String ? null : (value === '' ? null : value),
_formula: value instanceof String ? value : (value === '' ? '' : String(value)),
};
// If formula, check if we need to recompute
if (cell.formula && cell.formula.startsWith('=')) {
cell.value = null;
cell.error = null;
}
this.cells.set(key, cell);
// Update dependencies
this._updateDependents(key);
if (this.onChange) this.onChange(key);
}
_updateDependents(cellKey) {
this.dependencies.delete(cellKey);
this.dependents.delete(cellKey);
const cell = this.cells.get(cellKey);
if (!cell || !cell.formula) return;
const deps = new Set();
this._extractDependencies(cell.formula, deps);
for (const dep of deps) {
if (!this.dependents.has(dep)) this.dependents.set(dep, new Set());
this.dependents.get(dep).add(cellKey);
}
this.dependencies.set(cellKey, deps);
}
_extractDependencies(formula, deps) {
// Find all cell references (A1, B7, etc.)
const cellRefPattern = /\b([A-Z])(\d+)\b/g;
let match;
while ((match = cellRefPattern.exec(formula)) !== null) {
const col = match[1].charCodeAt(0) - 65;
const row = parseInt(match[2]);
if (row >= 1 && row <= this.ROWS && col >= 0 && col < this.COLS) {
deps.add(this.getCellKey(row, col));
}
}
// Find ranges (A1:A5)
const rangePattern = /([A-Z])(\d+):([A-Z])(\d+)/g;
while ((match = rangePattern.exec(formula)) !== null) {
const startCol = match[1].charCodeAt(0) - 65;
const startRow = parseInt(match[2]);
const endCol = match[3].charCodeAt(0) - 65;
const endRow = parseInt(match[4]);
for (let r = startRow; r <= endRow; r++) {
for (let c = startCol; c <= endCol; c++) {
deps.add(this.getCellKey(r, c));
}
}
}
}
clearCell(row, col) {
const key = this.getCellKey(row, col);
this.cells.delete(key);
this.dependencies.delete(key);
this.dependents.delete(key);
}
getDependents(cellKey) {
return this.dependents.get(cellKey) || new Set();
}
getDependencies(cellKey) {
return this.dependencies.get(cellKey) || new Set();
}
// Topological sort of all cells
getTopoOrder() {
const order = [];
const visited = new Set();
const inStack = new Set();
const visit = (key) => {
if (visited.has(key)) return;
if (inStack.has(key)) return; // cycle
inStack.add(key);
const deps = this.getDependencies(key);
for (const dep of deps) {
visit(dep);
}
inStack.delete(key);
visited.add(key);
order.push(key);
};
for (const [key] of this.cells) {
visit(key);
}
return order;
}
// Serialize to localStorage
serialize() {
const data = {};
for (const [key, cell] of this.cells) {
data[key] = {
formula: cell.formula,
value: cell.value,
error: cell.error,
};
}
return data;
}
// Deserialize from localStorage
deserialize(data) {
if (!data) return;
this.cells.clear();
this.dependencies.clear();
this.dependents.clear();
for (const key in data) {
const entry = data[key];
this.cells.set(key, {
key,
formula: entry.formula,
value: entry.value,
error: entry.error,
_value: entry.value,
_formula: entry.formula,
});
}
// Rebuild dependency graph
for (const [key] of this.cells) {
this._updateDependents(key);
}
}
}
// ============================================================
// UI Controller
// ============================================================
class SpreadsheetUI {
constructor(model) {
this.model = model;
this.grid = document.getElementById('spreadsheet');
this.formulaBarInput = document.getElementById('formula-bar-input');
this.formulaBarDiv = document.getElementById('formula-bar');
this.statusInfo = document.getElementById('status-info');
this.contextMenu = document.getElementById('context-menu');
this.selectedCell = null;
this.formulaBarVisible = false;
this.isEditing = false;
this.cellElements = new Map();
this._formulaBarValue = '';
this._buildGrid();
this._bindEvents();
}
_buildGrid() {
const { ROWS, COLS } = CFG;
const table = this.grid;
table.innerHTML = '';
this.cellElements.clear();
// Create thead
const thead = document.createElement('thead');
const headRow = document.createElement('tr');
// Column header (col A)
const colHeader = document.createElement('th');
colHeader.className = 'col-header';
colHeader.style.width = CFG.COL_W + 'px';
colHeader.textContent = 'A';
headRow.appendChild(colHeader);
// Data column headers
for (let c = 1; c < COLS; c++) {
const th = document.createElement('th');
th.className = 'col-header';
th.style.width = CFG.COL_W + 'px';
th.textContent = CFG.COL_LABELS[c];
headRow.appendChild(th);
}
// Row headers
for (let r = 1; r <= ROWS; r++) {
const th = document.createElement('th');
th.className = 'row-header';
th.style.width = CFG.COL_W + 'px';
th.textContent = r;
headRow.appendChild(th);
}
thead.appendChild(headRow);
// Build tbody
const tbody = document.createElement('tbody');
this.tbody = tbody;
for (let r = 1; r <= ROWS; r++) {
const tr = document.createElement('tr');
// Row header
const rh = document.createElement('th');
rh.className = 'row-header';
rh.style.width = CFG.COL_W + 'px';
rh.textContent = r;
tr.appendChild(rh);
// Data cells
for (let c = 0; c < COLS; c++) {
const td = document.createElement('td');
td.className = 'data-cell';
td.style.width = CFG.CELL_W + 'px';
td.style.height = CFG.CELL_H + 'px';
td.setAttribute('data-row', r);
td.setAttribute('data-col', c);
tr.appendChild(td);
this.cellElements.set(r + '-' + c, td);
}
tbody.appendChild(tr);
}
table.appendChild(thead);
table.appendChild(tbody);
}
_getCellEl(row, col) {
return this.cellElements.get(row + '-' + col);
}
_selectCell(row, col) {
this.selectedCell = { row, col };
this._updateFormulaBar();
// Remove all selected classes
this.grid.querySelectorAll('td.selected').forEach(td => td.classList.remove('selected'));
this.grid.querySelectorAll('td.selected-above').forEach(td => td.classList.remove('selected-above'));
this.grid.querySelectorAll('td.error').forEach(td => td.classList.remove('error'));
const el = this._getCellEl(row, col);
if (el) el.classList.add('selected');
// Show formula bar
this.formulaBarDiv.style.display = 'flex';
this.formulaBarInput.value = '';
this.formulaBarVisible = true;
this._updateFormulaBar();
}
_updateFormulaBar() {
if (!this.selectedCell) {
this.formulaBarInput.value = '';
this._formulaBarValue = '';
return;
}
const cell = this.model.getCell(this.selectedCell.row, this.selectedCell.col);
if (!cell) {
this.formulaBarInput.value = '';
this._formulaBarValue = '';
return;
}
if (cell.formula) {
this.formulaBarInput.value = cell.formula;
this._formulaBarValue = cell.formula;
} else if (cell.value !== null && cell.value !== undefined) {
this.formulaBarInput.value = String(cell.value);
this._formulaBarValue = String(cell.value);
} else {
this.formulaBarInput.value = '';
this._formulaBarValue = '';
}
}
_updateCellDisplay(row, col) {
const cell = this.model.getCell(row, col);
const el = this._getCellEl(row, col);
if (!el) return;
el.classList.remove('selected', 'error');
el.textContent = '';
if (cell.error) {
el.textContent = cell.error;
el.classList.add('error');
return;
}
if (cell.value === null || cell.value === undefined) {
el.textContent = '';
return;
}
const val = cell.value;
if (typeof val === 'number' && isNaN(val)) {
el.textContent = '';
return;
}
el.textContent = String(val);
el.classList.remove('error');
}
_recomputeCell(row, col) {
const cell = this.model.getCell(row, col);
if (!cell) return;
if (!cell.formula) {
cell.value = cell._value;
this._updateCellDisplay(row, col);
return;
}
if (cell.formula.startsWith('=')) {
try {
const tokens = tokenize(cell.formula);
const ast = parse(tokens);
const visited = new Set();
const memo = new Map();
const result = evaluate(ast, this.model, visited, memo, 0);
if (result === '#CYCLE!') {
cell.value = '#CYCLE!';
cell.error = '#CYCLE!';
} else if (result === '#REF!' || result === '#ERR!') {
cell.value = result;
cell.error = result;
} else if (typeof result === 'number' && isNaN(result)) {
cell.value = '#ERR!';
cell.error = '#ERR!';
} else if (result === '#DIV/0!') {
cell.value = '#DIV/0!';
cell.error = '#DIV/0!';
} else {
cell.value = result;
cell.error = null;
}
} catch (e) {
cell.value = '#ERR!';
cell.error = '#ERR!';
}
this._updateCellDisplay(row, col);
}
}
_recomputeDependentCells(cellKey) {
const dependents = this.model.getDependents(cellKey);
if (!dependents || dependents.size === 0) return;
// Topological order: process dependencies first
const order = [];
const visited = new Set();
const inStack = new Set();
const visit = (key) => {
if (visited.has(key)) return;
if (inStack.has(key)) return;
inStack.add(key);
const deps = this.model.getDependencies(key);
for (const dep of deps) {
visit(dep);
}
inStack.delete(key);
visited.add(key);
order.push(key);
};
for (const dep of dependents) {
visit(dep);
}
// Reverse to get correct evaluation order (dependents computed first)
order.reverse();
for (const key of order) {
this._recomputeCell(key.split('-')[0], parseInt(key.split('-')[1]));
}
}
_recomputeCell(row, col) {
const cell = this.model.getCell(row, col);
if (!cell) return;
if (!cell.formula || !cell.formula.startsWith('=')) return;
this._recomputeCellInternal(row, col);
}
_recomputeCellInternal(row, col) {
const cell = this.model.getCell(row, col);
if (!cell) return;
if (!cell.formula || !cell.formula.startsWith('=')) return;
try {
const tokens = tokenize(cell.formula);
const ast = parse(tokens);
const visited = new Set();
const memo = new Map();
const result = evaluate(ast, this.model, visited, memo, 0);
if (result === '#CYCLE!') {
cell.value = '#CYCLE!';
cell.error = '#CYCLE!';
} else if (result === '#REF!' || result === '#ERR!') {
cell.value = result;
cell.error = result;
} else if (typeof result === 'number' && isNaN(result)) {
cell.value = '#ERR!';
cell.error = '#ERR!';
} else if (result === '#DIV/0!') {
cell.value = '#DIV/0!';
cell.error = '#DIV/0!';
} else {
cell.value = result;
cell.error = null;
}
} catch (e) {
cell.value = '#ERR!';
cell.error = '#ERR!';
}
this._updateCellDisplay(row, col);
}
_recomputeAll() {
const topoOrder = this.model.getTopoOrder();
for (const key of topoOrder) {
const parts = key.split('-');
this._recomputeCell(parseInt(parts[1]), parts[0].charCodeAt(0) - 65);
}
}
_bindEvents() {
// Grid click
this.grid.addEventListener('click', (e) => {
const td = e.target.closest('td');
if (!td) return;
const row = parseInt(td.getAttribute('data-row'));
const col = parseInt(td.getAttribute('data-col'));
this._selectCell(row, col);
this._startEdit(row, col);
});
// Double-click to enter edit mode
this.grid.addEventListener('dblclick', (e) => {
const td = e.target.closest('td');
if (!td) return;
const row = parseInt(td.getAttribute('data-row'));
const col = parseInt(td.getAttribute('data-col'));
this._selectCell(row, col);
this._startEdit(row, col);
});
// Formula bar input
this.formulaBarInput.addEventListener('input', (e) => {
if (!this.selectedCell) return;
const formula = e.target.value;
const cell = this.model.getCell(this.selectedCell.row, this.selectedCell.col);
if (cell) {
cell._formula = formula;
cell._value = null;
cell.error = null;
this._updateFormulaBar();
}
});
this.formulaBarInput.addEventListener('keydown', (e) => {
if (e.key === 'Enter') {
e.preventDefault();
if (this.selectedCell) {
const cell = this.model.getCell(this.selectedCell.row, this.selectedCell.col);
if (cell) {
cell._formula = this.formulaBarInput.value;
cell._value = null;
cell.error = null;
this._recomputeCellInternal(this.selectedCell.row, this.selectedCell.col);
}
}
}
});
// Document-level keydown
document.addEventListener('keydown', (e) => {
// Ctrl+arrow for navigation
if (e.ctrlKey && e.key === 'ArrowRight') { e.preventDefault(); }
if (e.ctrlKey && e.key === 'ArrowLeft') { e.preventDefault(); }
if (e.ctrlKey && e.key === 'ArrowDown') { e.preventDefault(); }
if (e.ctrlKey && e.key === 'ArrowUp') { e.preventDefault(); }
// Ctrl+Shift+I insert row
if (e.ctrlKey && e.shiftKey && e.key === 'I') {
e.preventDefault();
this._insertRow();
}
// Ctrl+Shift+D delete row
if (e.ctrlKey && e.shiftKey && e.key === 'D') {
e.preventDefault();
this._deleteRow();
}
// Ctrl+Shift+C insert col
if (e.ctrlKey && e.shiftKey && e.key === 'C') {
e.preventDefault();
this._insertCol();
}
// Ctrl+Shift+X delete col
if (e.ctrlKey && e.shiftKey && e.key === 'X') {
e.preventDefault();
this._deleteCol();
}
});
// Context menu
this.grid.addEventListener('contextmenu', (e) => {
e.preventDefault();
const td = e.target.closest('td');
if (!td) return;
const row = parseInt(td.getAttribute('data-row'));
const col = parseInt(td.getAttribute('data-col'));
this.selectedCell = { row, col };
this._updateFormulaBar();
this.formulaBarVisible = true;
this.contextMenu.style.left = e.clientX + 'px';
this.contextMenu.style.top = e.clientY + 'px';
this.contextMenu.style.display = 'block';
});
document.addEventListener('click', () => {
this.contextMenu.style.display = 'none';
});
// Context menu actions
this.contextMenu.addEventListener('click', (e) => {
const action = e.target.closest('button')?.getAttribute('data-action');
if (!action) return;
e.stopPropagation();
if (action === 'insert-row') this._insertRow();
if (action === 'delete-row') this._deleteRow();
if (action === 'insert-col') this._insertCol();
if (action === 'delete-col') this._deleteCol();
this.contextMenu.style.display = 'none';
});
// Toolbar buttons
document.getElementById('btn-insert-row').addEventListener('click', () => this._insertRow());
document.getElementById('btn-delete-row').addEventListener('click', () => this._deleteRow());
document.getElementById('btn-insert-col').addEventListener('click', () => this._insertCol());
document.getElementById('btn-delete-col').addEventListener('click', () => this._deleteCol());
document.getElementById('btn-clear').addEventListener('click', () => {
if (this.selectedCell) {
this.model.clearCell(this.selectedCell.row, this.selectedCell.col);
this._recomputeCellInternal(this.selectedCell.row, this.selectedCell.col);
}
});
// Cell keydown for navigation
this.grid.addEventListener('keydown', (e) => {
if (!this.selectedCell) return;
let { row, col } = this.selectedCell;
if (e.key === 'Enter' || e.key === 'Tab') {
e.preventDefault();
if (e.key === 'Tab' && col < CFG.COLS - 1) {
col++;
} else if (e.key === 'Enter' && row < CFG.ROWS) {
row++;
} else {
if (col === CFG.COLS - 1 && row === CFG.ROWS - 1) return;
if (col === CFG.COLS - 1) { col = 0; row++; }
else { col++; }
}
this._selectCell(row, col);
this._startEdit(row, col);
return;
}
if (e.key === 'ArrowLeft') {
e.preventDefault();
if (col > 0) { col--; this._selectCell(row, col); }
return;
}
if (e.key === 'ArrowRight') {
e.preventDefault();
if (col < CFG.COLS - 1) { col++; this._selectCell(row, col); }
return;
}
if (e.key === 'ArrowUp') {
e.preventDefault();
if (row > 1) { row--; this._selectCell(row, col); }
return;
}
if (e.key === 'ArrowDown') {
e.preventDefault();
if (row < CFG.ROWS) { row++; this._selectCell(row, col); }
return;
}
if (e.key === 'Delete') {
e.preventDefault();
const cell = this.model.getCell(row, col);
if (cell) {
cell._value = null;
cell.error = null;
this._recomputeCellInternal(row, col);
}
return;
}
if (e.key === 'Escape') {
e.preventDefault();
if (this.isEditing) {
this.isEditing = false;
this._cancelEdit();
}
return;
}
});
// Save on unload
window.addEventListener('beforeunload', () => this._save());
}
_startEdit(row, col) {
this.isEditing = true;
this.formulaBarInput.value = this.model.getCell(row, col)._formula || '';
this.formulaBarVisible = true;
}
_cancelEdit() {
this.isEditing = false;
this.formulaBarVisible = false;
this._updateFormulaBar();
}
_insertRow() {
// Insert a new row at the top (row 1)
// Shift all cells down
for (let r = CFG.ROWS; r >= 2; r--) {
for (let c = 0; c < CFG.COLS; c++) {
const oldKey = CFG.COL_LABELS[c] + r;
const newKey = CFG.COL_LABELS[c] + (r - 1);
const cell = this.model.getCell(r, c);
if (cell) {
this.model.setCell(r - 1, c, cell._formula || cell._value || '');
this.model.clearCell(r, c);
}
}
}
// Update row numbers
for (let c = 0; c < CFG.COLS; c++) {
const key = CFG.COL_LABELS[c] + 1;
this.model.setCell(1, c, key);
}
this._recomputeAll();
this._save();
}
_deleteRow() {
// Delete the last row
for (let c = 0; c < CFG.COLS; c++) {
const key = CFG.COL_LABELS[c] + CFG.ROWS;
this.model.clearCell(CFG.ROWS, c);
}
this._recomputeAll();
this._save();
}
_insertCol() {
// Insert a new column at the left (col 0, which is column A)
// Shift all cells right
for (let r = 1; r <= CFG.ROWS; r++) {
for (let c = CFG.COLS - 1; c >= 0; c--) {
const oldKey = CFG.COL_LABELS[c] + r;
const newKey = CFG.COL_LABELS[c + 1] + r;
const cell = this.model.getCell(r, c);
if (cell) {
this.model.setCell(r, c + 1, cell._formula || cell._value || '');
this.model.clearCell(r, c);
}
}
}
this._recomputeAll();
this._save();
}
_deleteCol() {
// Delete the last column (col 25 = Z)
for (let r = 1; r <= CFG.ROWS; r++) {
const key = CFG.COL_LABELS[CFG.COLS - 1] + r;
this.model.clearCell(r, CFG.COLS - 1);
}
this._recomputeAll();
this._save();
}
_save() {
try {
localStorage.setItem('spreadsheet_data', JSON.stringify(this.model.serialize()));
} catch (e) {}
}
_load() {
try {
const data = localStorage.getItem('spreadsheet_data');
if (data) {
this.model.deserialize(JSON.parse(data));
}
} catch (e) {}
}
_updateFormulaBar() {
if (!this.selectedCell) {
this.formulaBarInput.value = '';
this._formulaBarValue = '';
return;
}
const cell = this.model.getCell(this.selectedCell.row, this.selectedCell.col);
if (!cell) {
this.formulaBarInput.value = '';
return;
}
if (cell.formula) {
this.formulaBarInput.value = cell.formula;
} else if (cell.value !== null && cell.value !== undefined) {
this.formulaBarInput.value = String(cell.value);
} else {
this.formulaBarInput.value = '';
}
}
_bindCellEvents() {
this.grid.addEventListener('click', (e) => {
const td = e.target.closest('td');
if (!td) return;
const row = parseInt(td.getAttribute('data-row'));
const col = parseInt(td.getAttribute('data-col'));
this._selectCell(row, col);
this._startEdit(row, col);
});
this.grid.addEventListener('dblclick', (e) => {
const td = e.target.closest('td');
if (!td) return;
const row = parseInt(td.getAttribute('data-row'));
const col = parseInt(td.getAttribute('data-col'));
this._selectCell(row, col);
this._startEdit(row, col);
});
this.formulaBarInput.addEventListener('input', (e) => {
if (!this.selectedCell) return;
const formula = e.target.value;
const cell = this.model.getCell(this.selectedCell.row, this.selectedCell.col);
if (cell) {
cell._formula = formula;
cell._value = null;
cell.error = null;
this._updateFormulaBar();
}
});
this.formulaBarInput.addEventListener('keydown', (e) => {
if (e.key === 'Enter') {
e.preventDefault();
if (this.selectedCell) {
const cell = this.model.getCell(this.selectedCell.row, this.selectedCell.col);
if (cell) {
cell._formula = this.formulaBarInput.value;
cell._value = null;
cell.error = null;
this._recomputeCellInternal(this.selectedCell.row, this.selectedCell.col);
}
}
}
});
// Document keydown for arrow keys
document.addEventListener('keydown', (e) => {
if (e.ctrlKey && e.key === 'ArrowRight') { e.preventDefault(); }
if (e.ctrlKey && e.key === 'ArrowLeft') { e.preventDefault(); }
if (e.ctrlKey && e.key === 'ArrowDown') { e.preventDefault(); }
if (e.ctrlKey && e.key === 'ArrowUp') { e.preventDefault(); }
});
}
}
// ============================================================
// Initialize
// ============================================================
const sheet = new SpreadsheetModel(CFG.ROWS, CFG.COLS);
const ui = new SpreadsheetUI(sheet);
// Load from localStorage
ui._load();
// Set up change handler
sheet.onChange = (cellKey) => {
ui._recomputeDependentCells(cellKey);
};
// Select first cell
ui._selectCell(1, 1);
// Handle cell editing in formula bar
document.getElementById('formula-bar-input').addEventListener('input', (e) => {
if (!ui.selectedCell) return;
const formula = e.target.value;
const cell = sheet.getCell(ui.selectedCell.row, ui.selectedCell.col);
if (cell) {
cell._formula = formula;
cell._value = null;
cell.error = null;
ui._updateFormulaBar();
}
});
// Handle Enter in formula bar
document.getElementById('formula-bar-input').addEventListener('keydown', (e) => {
if (e.key === 'Enter') {
e.preventDefault();
if (ui.selectedCell) {
const cell = sheet.getCell(ui.selectedCell.row, ui.selectedCell.col);
if (cell) {
cell._formula = e.target.value;
cell._value = null;
cell.error = null;
ui._recomputeCellInternal(ui.selectedCell.row, ui.selectedCell.col);
}
}
}
});
// Handle cell double-click
const spreadsheetEl = document.getElementById('spreadsheet');
spreadsheetEl.addEventListener('dblclick', (e) => {
const td = e.target.closest('td');
if (!td) return;
const row = parseInt(td.getAttribute('data-row'));
const col = parseInt(td.getAttribute('data-col'));
ui._selectCell(row, col);
ui._startEdit(row, col);
});
// Set initial grid
console.log('Spreadsheet initialized');
</script>
</body>
</html>