Spreadsheets are powerful UI patterns. Here is how to build a lightweight one with formulas and keyboard navigation.
Cell Types and State
// types.ts
export interface CellData {
raw: string; // What the user typed (e.g., "=SUM(A1:A3)")
computed: string | number; // The displayed result
error?: string;
}
export type CellAddress = { col: number; row: number };
export function addressToKey(col: number, row: number): string {
return `${colToLetter(col)}${row + 1}`;
}
export function colToLetter(col: number): string {
let result = "";
let c = col;
while (c >= 0) {
result = String.fromCharCode((c % 26) + 65) + result;
c = Math.floor(c / 26) - 1;
}
return result;
}
export function parseAddress(ref: string): CellAddress | null {
const match = ref.match(/^([A-Z]+)(\d+)$/);
if (!match) return null;
let col = 0;
for (let i = 0; i < match[1].length; i++) {
col = col * 26 + match[1].charCodeAt(i) - 64;
}
return { col: col - 1, row: parseInt(match[2], 10) - 1 };
}
Formula Engine
// formula.ts
import { addressToKey, parseAddress, type CellData } from "./types";
type CellMap = Map<string, CellData>;
export function evaluateCell(
raw: string,
cells: CellMap,
visited: Set<string> = new Set(),
currentKey?: string,
): string | number {
if (!raw.startsWith("=")) {
const num = Number(raw);
return isNaN(num) ? raw : num;
}
if (currentKey && visited.has(currentKey)) {
throw new Error("#CIRC!");
}
if (currentKey) visited.add(currentKey);
const formula = raw.slice(1).toUpperCase();
// SUM(A1:A5)
const sumMatch = formula.match(/^SUM\(([A-Z]+\d+):([A-Z]+\d+)\)$/);
if (sumMatch) {
const values = getRangeValues(sumMatch[1], sumMatch[2], cells, visited);
return values.reduce((sum, v) => sum + (typeof v === "number" ? v : 0), 0);
}
// AVG(A1:A5)
const avgMatch = formula.match(/^AVG\(([A-Z]+\d+):([A-Z]+\d+)\)$/);
if (avgMatch) {
const values = getRangeValues(avgMatch[1], avgMatch[2], cells, visited);
const nums = values.filter((v): v is number => typeof v === "number");
return nums.length ? nums.reduce((s, n) => s + n, 0) / nums.length : 0;
}
// COUNT(A1:A5)
const countMatch = formula.match(/^COUNT\(([A-Z]+\d+):([A-Z]+\d+)\)$/);
if (countMatch) {
const values = getRangeValues(countMatch[1], countMatch[2], cells, visited);
return values.filter((v) => typeof v === "number").length;
}
// Simple cell reference =A1
const refMatch = formula.match(/^([A-Z]+\d+)$/);
if (refMatch) {
const addr = parseAddress(refMatch[1]);
if (!addr) throw new Error("#REF!");
const key = addressToKey(addr.col, addr.row);
const cell = cells.get(key);
if (!cell) return 0;
return evaluateCell(cell.raw, cells, visited, key);
}
// Basic arithmetic: =A1+B1, =A1*2
try {
const resolved = formula.replace(/[A-Z]+\d+/g, (ref) => {
const addr = parseAddress(ref);
if (!addr) return "0";
const key = addressToKey(addr.col, addr.row);
const cell = cells.get(key);
if (!cell) return "0";
const val = evaluateCell(cell.raw, cells, visited, key);
return String(typeof val === "number" ? val : 0);
});
// Only allow numbers and basic operators
if (/^[\d+\-*/(). ]+$/.test(resolved)) {
return Function(`"use strict"; return (${resolved})`)();
}
} catch {
throw new Error("#ERR!");
}
throw new Error("#UNKNOWN!");
}
function getRangeValues(
startRef: string,
endRef: string,
cells: CellMap,
visited: Set<string>,
): (string | number)[] {
const start = parseAddress(startRef);
const end = parseAddress(endRef);
if (!start || !end) return [];
const values: (string | number)[] = [];
for (let row = start.row; row <= end.row; row++) {
for (let col = start.col; col <= end.col; col++) {
const key = addressToKey(col, row);
const cell = cells.get(key);
if (cell) {
try {
values.push(evaluateCell(cell.raw, cells, visited, key));
} catch {
// Skip errored cells
}
}
}
}
return values;
}
Spreadsheet Component
"use client";
import { useState, useCallback, useRef } from "react";
import { addressToKey, colToLetter, type CellData, type CellAddress } from "./types";
import { evaluateCell } from "./formula";
interface SpreadsheetProps {
rows?: number;
cols?: number;
}
export function Spreadsheet({ rows = 20, cols = 10 }: SpreadsheetProps) {
const [cells, setCells] = useState<Map<string, CellData>>(new Map());
const [editing, setEditing] = useState<CellAddress | null>(null);
const [active, setActive] = useState<CellAddress>({ col: 0, row: 0 });
const inputRef = useRef<HTMLInputElement>(null);
const recompute = useCallback(
(nextCells: Map<string, CellData>) => {
const updated = new Map(nextCells);
for (const [key, cell] of updated) {
try {
const computed = evaluateCell(cell.raw, updated, new Set(), key);
updated.set(key, { ...cell, computed, error: undefined });
} catch (e) {
updated.set(key, {
...cell,
computed: (e as Error).message,
error: (e as Error).message,
});
}
}
return updated;
},
[],
);
const commitEdit = useCallback(
(raw: string) => {
if (!editing) return;
const key = addressToKey(editing.col, editing.row);
const nextCells = new Map(cells);
if (raw) {
nextCells.set(key, { raw, computed: raw });
} else {
nextCells.delete(key);
}
setCells(recompute(nextCells));
setEditing(null);
},
[editing, cells, recompute],
);
const handleKeyDown = useCallback(
(e: React.KeyboardEvent) => {
if (editing) {
if (e.key === "Enter") {
commitEdit(inputRef.current?.value ?? "");
setActive((a) => ({ ...a, row: Math.min(a.row + 1, rows - 1) }));
}
if (e.key === "Escape") setEditing(null);
return;
}
switch (e.key) {
case "ArrowUp":
e.preventDefault();
setActive((a) => ({ ...a, row: Math.max(0, a.row - 1) }));
break;
case "ArrowDown":
e.preventDefault();
setActive((a) => ({ ...a, row: Math.min(rows - 1, a.row + 1) }));
break;
case "ArrowLeft":
e.preventDefault();
setActive((a) => ({ ...a, col: Math.max(0, a.col - 1) }));
break;
case "ArrowRight":
e.preventDefault();
setActive((a) => ({ ...a, col: Math.min(cols - 1, a.col + 1) }));
break;
case "Enter":
e.preventDefault();
setEditing(active);
break;
case "Delete":
case "Backspace":
e.preventDefault();
const key = addressToKey(active.col, active.row);
const nextCells = new Map(cells);
nextCells.delete(key);
setCells(recompute(nextCells));
break;
default:
if (e.key.length === 1 && !e.ctrlKey && !e.metaKey) {
setEditing(active);
}
}
},
[editing, active, cells, rows, cols, commitEdit, recompute],
);
return (
<div
className="overflow-auto border rounded-lg"
onKeyDown={handleKeyDown}
tabIndex={0}
>
<table className="border-collapse text-sm">
<thead>
<tr>
<th className="w-10 bg-muted border px-1 py-0.5 text-xs text-muted-foreground sticky left-0 z-10" />
{Array.from({ length: cols }, (_, c) => (
<th
key={c}
className="min-w-[100px] bg-muted border px-2 py-0.5 text-xs font-medium text-muted-foreground"
>
{colToLetter(c)}
</th>
))}
</tr>
</thead>
<tbody>
{Array.from({ length: rows }, (_, r) => (
<tr key={r}>
<td className="bg-muted border px-2 py-0.5 text-xs text-muted-foreground text-center sticky left-0">
{r + 1}
</td>
{Array.from({ length: cols }, (_, c) => {
const key = addressToKey(c, r);
const cell = cells.get(key);
const isActive = active.col === c && active.row === r;
const isEditing =
editing?.col === c && editing?.row === r;
return (
<td
key={c}
className={`border px-1 py-0.5 cursor-cell ${
isActive ? "outline outline-2 outline-primary -outline-offset-1" : ""
} ${cell?.error ? "text-red-500" : ""}`}
onClick={() => {
setActive({ col: c, row: r });
if (isActive) setEditing({ col: c, row: r });
}}
onDoubleClick={() => setEditing({ col: c, row: r })}
>
{isEditing ? (
<input
ref={inputRef}
autoFocus
defaultValue={cell?.raw ?? ""}
className="w-full bg-transparent outline-none"
onBlur={(e) => commitEdit(e.target.value)}
/>
) : (
<span className="block truncate">
{cell?.computed ?? ""}
</span>
)}
</td>
);
})}
</tr>
))}
</tbody>
</table>
</div>
);
}
Usage
<Spreadsheet rows={20} cols={8} />
Enter numbers, text, or formulas like =SUM(A1:A5), =A1+B1, or =AVG(B2:B10).
Need Custom Business Tools?
We build spreadsheet-like interfaces and data management tools for businesses. Contact us to discuss your project.