Spreadsheet-UX op een echte relationele engine
Deze repository wordt als momentopname gepubliceerd: een clone bevat de volledige inhoud, maar geen commitgeschiedenis.
import {
useCallback,
useEffect,
useMemo,
useRef,
useState,
type CSSProperties,
type ReactNode,
} from 'react';
import { useVirtualizer } from '@tanstack/react-virtual';
import {
aggKeepsUnits,
aggsFor,
editableValue,
formatCell,
formatCount,
isComputed,
type AggFn,
type CellValue,
type ColumnMeta,
type Locale,
type Row,
} from '@wellformed/shared';
import { api, ApiError } from '../api/client';
import { useLinkTargets, useRows, useSummary } from '../api/hooks';
import { useI18n, type TranslateFn } from '../i18n';
import { useWorkspace } from '../state/workspace';
import { useCurrency, useSession } from '../state/session';
import { useToasts } from '../state/toasts';
import { useColumnWidths } from '../state/columnWidths';
import { diffValues } from '../state/undo';
import { GridCell, type EditorState, type Move } from './GridCell';
import { Menu, useMenu, type MenuEntry } from './Menu';
import { RecordPanel } from './RecordPanel';
import { typeMark } from './Icons';
const ROW_HEIGHT = 31;
/** Row id of the unsaved row being composed; never reaches the server. */
const DRAFT_ID = '__draft__';
/** Start loading the next page this many rows before the end comes into view. */
const PREFETCH_MARGIN = 40;
const MAX_PASTE_CELLS = 500;
interface GridProps {
onEditColumn(column: ColumnMeta): void;
/** Open the tighten dialog: type and constraints live there, not in edit. */
onTightenColumn(column: ColumnMeta): void;
onDeleteColumn(column: ColumnMeta): void;
onAddColumn(): void;
}
export function Grid({ onEditColumn, onTightenColumn, onDeleteColumn, onAddColumn }: GridProps): ReactNode {
const ws = useWorkspace();
const session = useSession();
const currency = useCurrency();
const toasts = useToasts();
const { t, locale } = useI18n();
const table = ws.table;
const allColumns = useMemo(() => table?.columns ?? [], [table]);
/** What the grid shows: the table's columns minus the ones tucked away. */
const columns = useMemo(
() => allColumns.filter((c) => !ws.hiddenCols.includes(c.name)),
[allColumns, ws.hiddenCols],
);
// Only the visible stored columns: the server leaves hidden columns out of
// the SELECT, so the rows here carry no values to snapshot for them.
const storedColumnNames = useMemo(
() => columns.filter((c) => !isComputed(c.type)).map((c) => c.name),
[columns],
);
const query = useRows({
table: ws.tableName,
viewId: ws.viewId,
filters: ws.adHocFilters,
sort: ws.sortOverride,
hidden: ws.hiddenOverride,
search: ws.search,
enabled: Boolean(ws.tableName),
});
const { rows, columnErrors, hasNextPage, isFetchingNextPage, fetchNextPage } = query;
// The aggregate row asks separately, over every matching row rather than the
// pages loaded so far — a total that grew as you scrolled would be useless.
const summary = useSummary({
table: ws.tableName,
viewId: ws.viewId,
filters: ws.adHocFilters,
hidden: ws.hiddenOverride,
search: ws.search,
summaries: ws.summaries,
enabled: Boolean(ws.tableName),
});
const linkTargets = useMemo(
() => [...new Set(columns.filter((c) => c.type === 'link' && c.target).map((c) => c.target!))],
[columns],
);
const linkOptions = useLinkTargets(linkTargets);
const canEdit = session.can('editor');
const canAlter = session.can('admin');
/**
* What a blocked edit says, instead of nothing.
*
* A spreadsheet person's first instinct is to double-click a cell and type,
* and a grid that silently swallows that reads as broken, not as read-only.
* Once is enough, though — the throttle keeps a burst of typing from
* becoming a wall of the same toast.
*/
const lastRefusal = useRef(0);
const refuseEdit = useCallback(() => {
if (canEdit) return;
const now = Date.now();
if (now - lastRefusal.current < 15_000) return;
lastRefusal.current = now;
toasts.push(t(session.visiting ? 'grid.demoReadOnly' : 'grid.viewerReadOnly'), 'info');
}, [canEdit, session.visiting, toasts, t]);
const [editing, setEditing] = useState<EditorState | null>(null);
const [busy, setBusy] = useState(false);
const [linkRecord, setLinkRecord] = useState<{ table: string; rowId: string } | null>(null);
/**
* A row being composed, before it is allowed to exist.
*
* A required column is a promise that no row exists without it, so a blank
* insert is not something the server can honestly accept. Rather than move
* row creation into a dialog, the new row is held here and typed into in
* place; it is written the moment it satisfies every required column.
*/
const [draft, setDraft] = useState<Record<string, string> | null>(null);
// What a draft must be given before it can exist. A self-numbering column
// is left out even when required: the server fills it on insert, so asking
// someone to type the invoice number would defeat the numbering.
const requiredColumns = useMemo(
() => allColumns.filter((c) => !isComputed(c.type) && c.required && !c.sequence),
[allColumns],
);
const scrollRef = useRef<HTMLDivElement>(null);
const {
widthOf,
setWidth,
reset: resetWidth,
totalWidth,
} = useColumnWidths(ws.tableName, columns);
const headerMenu = useMenu<ColumnMeta>();
const rowMenu = useMenu<Row>();
const summaryMenu = useMenu<ColumnMeta>();
const selectionMenu = useMenu<null>();
// react-hooks warns that this API is not React Compiler friendly. Wellformed
// does not run the compiler, and windowing is what keeps a 100k-row table
// usable, so the trade is worth taking knowingly.
const virtualizer = useVirtualizer({
count: rows.length,
getScrollElement: () => scrollRef.current,
estimateSize: () => ROW_HEIGHT,
overscan: 14,
});
const virtualRows = virtualizer.getVirtualItems();
const paddingTop = virtualRows[0]?.start ?? 0;
const paddingBottom =
virtualizer.getTotalSize() - (virtualRows[virtualRows.length - 1]?.end ?? 0);
// Pull the next page in as the viewport approaches the end of what is loaded.
const lastVisible = virtualRows[virtualRows.length - 1]?.index ?? 0;
useEffect(() => {
if (hasNextPage && !isFetchingNextPage && lastVisible >= rows.length - PREFETCH_MARGIN) {
void fetchNextPage();
}
}, [hasNextPage, isFetchingNextPage, lastVisible, rows.length, fetchNextPage]);
const selection = ws.selection;
const rowIndex = selection ? rows.findIndex((r) => r.id === selection.rowId) : -1;
const colIndex = selection ? columns.findIndex((c) => c.name === selection.col) : -1;
/**
* The block selection: rows and columns chosen individually, the selected
* cells being wherever they overlap. A drag across cells picks the two
* contiguous ranges it happened to cross; after that Ctrl+click on a row
* number or column header toggles membership one by one, until nothing is
* left.
*/
const [block, setBlock] = useState<{ rowIds: string[]; cols: string[] } | null>(null);
const blockRows = useMemo(() => new Set(block?.rowIds), [block]);
const blockCols = useMemo(() => new Set(block?.cols), [block]);
/** Where a possible drag started, in loaded-row and column indexes. */
const dragFrom = useRef<{ row: number; col: number } | null>(null);
const [dragSelecting, setDragSelecting] = useState(false);
// The rows and columns of one table mean nothing in the next.
useEffect(() => setBlock(null), [ws.tableName]);
/**
* Move the cursor. `wrap` carries Tab's manners: running off the end of a
* row continues on the next one, which is what makes tabbing across a wide
* table a way to fill it in rather than a dead end at the last column.
*/
const moveSelection = useCallback(
(dRow: number, dCol: number, wrap = false) => {
if (!rows.length || !columns.length) return;
// Moving the cursor is leaving the block: arrows collapse a
// multi-selection the way they do in every spreadsheet.
setBlock(null);
// Nothing is selected yet: the first key lands on the first cell rather
// than counting a step from a cursor that was never there.
if (rowIndex < 0 || colIndex < 0) {
ws.setSelection({ rowId: rows[0]!.id, col: columns[0]!.name });
virtualizer.scrollToIndex(0, { align: 'auto' });
return;
}
let r = rowIndex + dRow;
let c = colIndex + dCol;
if (wrap && c > columns.length - 1) {
c = 0;
r += 1;
} else if (wrap && c < 0) {
c = columns.length - 1;
r -= 1;
}
r = Math.min(rows.length - 1, Math.max(0, r));
c = Math.min(columns.length - 1, Math.max(0, c));
const row = rows[r];
const col = columns[c];
if (!row || !col) return;
ws.setSelection({ rowId: row.id, col: col.name });
virtualizer.scrollToIndex(r, { align: 'auto' });
},
[rows, columns, rowIndex, colIndex, ws, virtualizer],
);
/**
* Keep the selected cell where it can be seen.
*
* The virtualizer scrolls in row coordinates and knows nothing of what
* covers them: the sticky header over the top of the viewport, the
* horizontal scrollbar over the bottom, the frozen first column over the
* left — and it does not scroll sideways at all, so moving right used to
* walk the cursor straight off the edge of a wide table.
*/
useEffect(() => {
const wrap = scrollRef.current;
if (!wrap || !selection) return;
const cell = wrap.querySelector<HTMLElement>('td.sel');
if (!cell) return;
const box = cell.getBoundingClientRect();
const wrapBox = wrap.getBoundingClientRect();
const header = wrap.querySelector('thead th')?.getBoundingClientRect().height ?? 0;
// The frozen columns only hide a cell that scrolls under them.
const stuck = cell.classList.contains('frozen')
? 0
: (wrap.querySelector('thead th.frozen')?.getBoundingClientRect().right ?? wrapBox.left) -
wrapBox.left;
const top = wrapBox.top + header;
const bottom = wrapBox.top + wrap.clientHeight;
const left = wrapBox.left + stuck;
const right = wrapBox.left + wrap.clientWidth;
if (box.bottom > bottom) wrap.scrollTop += box.bottom - bottom;
else if (box.top < top) wrap.scrollTop -= top - box.top;
if (box.right > right) wrap.scrollLeft += box.right - right;
else if (box.left < left) wrap.scrollLeft -= left - box.left;
}, [selection, rows, columns]);
/* ---- writing ---------------------------------------------------------- */
const commit = useCallback(
async (rowId: string, patch: Record<string, unknown>, describe: string) => {
const row = rows.find((r) => r.id === rowId);
if (!row) return;
const before: Record<string, CellValue> = {};
for (const name of storedColumnNames) before[name] = row.values[name] ?? null;
setBusy(true);
try {
const result = await api.updateRow(ws.tableName, rowId, patch);
const delta = diffValues(before, result.row.values, storedColumnNames);
if (Object.keys(delta.after).length) {
ws.undo.record({
kind: 'update',
table: ws.tableName,
rowId,
before: delta.before,
after: delta.after,
label: describe,
});
}
for (const workflow of result.workflows) {
toasts.push(t('grid.ruleFired', workflow), 'info');
}
await ws.refresh();
} catch (error) {
toasts.fail(error);
if (error instanceof ApiError && error.isAuth) await ws.refresh();
} finally {
setBusy(false);
}
},
[rows, storedColumnNames, ws, toasts, t],
);
/**
* Take the keys back after an editor closed on a keystroke.
*
* The input that had focus is gone; without this it falls to the document
* and the next Enter, arrow or F2 reaches nothing, so a keyboard-driven pass
* down a column stops dead after the first cell. A commit by clicking away
* does not come through here: there the user has chosen where focus goes.
*/
const takeKeysBack = useCallback(() => scrollRef.current?.focus(), []);
const commitCell = useCallback(
(rowId: string, colName: string, raw: string, move?: Move) => {
setEditing(null);
if (move) takeKeysBack();
if (move === 'down') moveSelection(1, 0);
else if (move === 'right') moveSelection(0, 1, true);
else if (move === 'left') moveSelection(0, -1, true);
const row = rows.find((r) => r.id === rowId);
const col = columns.find((c) => c.name === colName);
if (!row || !col) return;
const current = editableValue(row.values[colName] ?? null);
if (current === raw) return;
const label = describeRow(row, table?.display);
void commit(rowId, { [colName]: raw }, t('grid.undoCellEdit', col.label, label));
},
[rows, columns, table, commit, moveSelection, takeKeysBack, t],
);
const addRow = useCallback(
async (seed: Record<string, string> = {}) => {
setBusy(true);
try {
const result = await api.createRow(ws.tableName, seed);
const values: Record<string, CellValue> = {};
for (const name of storedColumnNames) values[name] = result.row.values[name] ?? null;
ws.undo.record({
kind: 'insert',
table: ws.tableName,
rowId: result.row.id,
values,
label: t('grid.undoNewRow', table?.label ?? ws.tableName),
});
await ws.refresh();
const first = columns.find((c) => !isComputed(c.type));
if (first) ws.setSelection({ rowId: result.row.id, col: first.name });
return true;
} catch (error) {
toasts.fail(error);
return false;
} finally {
setBusy(false);
}
},
[ws, table, columns, storedColumnNames, toasts, t],
);
/** Start composing a row, sitting on the first thing it cannot exist without. */
const beginDraft = useCallback(() => {
// A hidden required column comes back first: the row cannot exist without
// it, and a draft cannot be typed into a column that is not on screen.
const hiddenRequired = requiredColumns.filter((c) => ws.hiddenCols.includes(c.name));
if (hiddenRequired.length) ws.revealColumns(hiddenRequired.map((c) => c.name));
const first = requiredColumns[0] ?? columns.find((c) => !isComputed(c.type));
setDraft({});
if (first) setEditing({ rowId: DRAFT_ID, col: first.name, draft: '' });
}, [requiredColumns, columns, ws]);
const discardDraft = useCallback(
(byKey = true) => {
setEditing(null);
setDraft(null);
if (byKey) takeKeysBack();
},
[takeKeysBack],
);
/**
* Take a value into the draft.
*
* Enter means "that is the row": it is written as soon as every required
* column has something, and otherwise the cursor goes to the next one still
* missing. Tab means "carry on": it steps along the row so the optional
* columns can be filled in too, which from the keyboard was otherwise
* impossible — the cursor only ever visited the required ones.
*/
const commitDraft = useCallback(
(colName: string, raw: string, move?: Move) => {
if (!draft) return;
const next = { ...draft, [colName]: raw };
setEditing(null);
if (move === 'right' || move === 'left') {
setDraft(next);
takeKeysBack();
const fillable = columns.filter((c) => !isComputed(c.type));
const at = fillable.findIndex((c) => c.name === colName);
const target =
fillable[Math.min(fillable.length - 1, Math.max(0, at + (move === 'right' ? 1 : -1)))];
if (target) {
setEditing({ rowId: DRAFT_ID, col: target.name, draft: next[target.name] ?? '' });
}
return;
}
const missing = requiredColumns.filter((c) => (next[c.name] ?? '').trim() === '');
if (missing.length === 0) {
setDraft(null);
takeKeysBack();
// Put it back if the server refuses, rather than losing the typing.
void addRow(next).then((ok) => {
if (!ok) setDraft(next);
});
return;
}
setDraft(next);
const target = missing[0]!;
setEditing({ rowId: DRAFT_ID, col: target.name, draft: next[target.name] ?? '' });
},
[draft, columns, requiredColumns, addRow, takeKeysBack],
);
const draftRow = useMemo<Row | null>(() => {
if (!draft) return null;
const values: Record<string, CellValue> = {};
const labels: Record<string, string | null> = {};
for (const col of columns) {
const raw = draft[col.name];
values[col.name] = raw === undefined || raw === '' ? null : raw;
if (col.type === 'link' && raw && col.target) {
const target = ws.tables.find((t) => t.name === col.target);
const hit = (linkOptions[col.target] ?? []).find((r) => r.id === raw);
labels[col.name] =
hit && target?.display ? String(hit.values[target.display] ?? hit.id) : null;
}
}
return { id: DRAFT_ID, values, labels };
}, [draft, columns, linkOptions, ws.tables]);
const draftMissing = draft
? requiredColumns.filter((c) => (draft[c.name] ?? '').trim() === '')
: [];
const deleteRow = useCallback(
async (row: Row) => {
setBusy(true);
try {
const result = await api.deleteRow(ws.tableName, row.id);
ws.undo.record({
kind: 'delete',
table: ws.tableName,
rowId: row.id,
restore: result.restore,
label: t('grid.undoDeleteRow', result.label),
});
if (ws.selection?.rowId === row.id) ws.setSelection(null);
await ws.refresh();
toasts.push(t('grid.deletedRow', result.label), 'success', {
label: t('common.undo'),
run: () => void ws.undo.undo(),
});
} catch (error) {
toasts.fail(error);
} finally {
setBusy(false);
}
},
[ws, toasts, t],
);
/* ---- block selection --------------------------------------------------- */
/** The data cell under an event, in loaded-row and column indexes. */
const cellUnder = useCallback(
(target: EventTarget | null): { row: number; col: number } | null => {
const el = target instanceof Element ? target : null;
const td = el?.closest('td[data-col]');
const tr = td?.closest('tr[aria-rowindex]');
if (!td || !tr) return null;
const row = Number(tr.getAttribute('aria-rowindex')) - 2;
const col = columns.findIndex((c) => c.name === td.getAttribute('data-col'));
if (!Number.isInteger(row) || row < 0 || row >= rows.length || col < 0) return null;
return { row, col };
},
[columns, rows.length],
);
const onGridMouseDown = useCallback(
(event: React.MouseEvent) => {
if (event.button !== 0 || editing) return;
const cell = cellUnder(event.target);
if (!cell) return;
// A click on a cell is where a drag may start — and where any standing
// block ends: the single selection takes over again.
dragFrom.current = cell;
setBlock(null);
},
[editing, cellUnder],
);
const onGridMouseMove = useCallback(
(event: React.MouseEvent) => {
const from = dragFrom.current;
if (!from) return;
if (!(event.buttons & 1)) {
dragFrom.current = null;
return;
}
const cell = cellUnder(event.target);
if (!cell) return;
if (!dragSelecting) {
// Still on the starting cell: a click, until proven a drag.
if (cell.row === from.row && cell.col === from.col) return;
setDragSelecting(true);
// The drag is choosing cells now, not the text inside them.
window.getSelection()?.removeAllRanges();
}
const rowIds = rows
.slice(Math.min(from.row, cell.row), Math.max(from.row, cell.row) + 1)
.map((r) => r.id);
const cols = columns
.slice(Math.min(from.col, cell.col), Math.max(from.col, cell.col) + 1)
.map((c) => c.name);
setBlock({ rowIds, cols });
},
[cellUnder, dragSelecting, rows, columns],
);
useEffect(() => {
function onUp() {
dragFrom.current = null;
setDragSelecting(false);
}
window.addEventListener('mouseup', onUp);
return () => window.removeEventListener('mouseup', onUp);
}, []);
/** Take one row in or out of the block; the last one out takes the block with it. */
const toggleBlockRow = useCallback((rowId: string) => {
setBlock((b) => {
if (!b) return b;
const rowIds = b.rowIds.includes(rowId)
? b.rowIds.filter((id) => id !== rowId)
: [...b.rowIds, rowId];
return rowIds.length ? { ...b, rowIds } : null;
});
}, []);
const toggleBlockCol = useCallback((name: string) => {
setBlock((b) => {
if (!b) return b;
const cols = b.cols.includes(name) ? b.cols.filter((c) => c !== name) : [...b.cols, name];
return cols.length ? { ...b, cols } : null;
});
}, []);
/* ---- clipboard -------------------------------------------------------- */
/** A cell as text, the way it reads on screen — what the clipboard gets. */
const cellText = useCallback(
(row: Row, col: ColumnMeta): string =>
col.type === 'link'
? (row.labels[col.name] ?? '')
: formatCell(row.values[col.name] ?? null, col, {
label: row.labels[col.name],
currency,
locale,
}),
[currency, locale],
);
const copySelection = useCallback(async () => {
let text: string;
let cells = 1;
if (block) {
// Grid order, not click order: the block reads the way the grid does.
const selRows = rows.filter((r) => blockRows.has(r.id));
const selCols = columns.filter((c) => blockCols.has(c.name));
if (!selRows.length || !selCols.length) return;
cells = selRows.length * selCols.length;
text = selRows.map((r) => selCols.map((c) => cellText(r, c)).join('\t')).join('\n');
} else {
if (!selection) return;
const row = rows.find((r) => r.id === selection.rowId);
const col = columns.find((c) => c.name === selection.col);
if (!row || !col) return;
text = cellText(row, col);
}
try {
await navigator.clipboard.writeText(text);
toasts.push(cells > 1 ? t('grid.copiedCells', cells, locale) : t('grid.copied'), 'success');
} catch {
toasts.push(t('grid.clipboardRefused'), 'error');
}
}, [block, blockRows, blockCols, selection, rows, columns, cellText, toasts, t, locale]);
/**
* Paste a rectangle of tab-separated text starting at the selected cell,
* the way a spreadsheet does. Computed columns in the path are skipped
* rather than silently shifting the remaining values sideways.
*/
const pasteAtSelection = useCallback(async () => {
if (!selection || !canEdit) return;
let text: string;
try {
text = await navigator.clipboard.readText();
} catch {
toasts.push(t('grid.clipboardRefused'), 'error');
return;
}
if (!text) return;
const startRow = rows.findIndex((r) => r.id === selection.rowId);
const startCol = columns.findIndex((c) => c.name === selection.col);
if (startRow < 0 || startCol < 0) return;
const grid = text
.replace(/\r\n?/g, '\n')
.replace(/\n$/, '')
.split('\n')
.map((line) => line.split('\t'));
const cellCount = grid.reduce((n, line) => n + line.length, 0);
if (cellCount > MAX_PASTE_CELLS) {
toasts.push(t('grid.pasteTooBig', cellCount, MAX_PASTE_CELLS, locale), 'error');
return;
}
setBusy(true);
let written = 0;
let skipped = 0;
try {
for (let r = 0; r < grid.length; r++) {
const target = rows[startRow + r];
if (!target) break;
const patch: Record<string, unknown> = {};
grid[r]!.forEach((value, c) => {
const col = columns[startCol + c];
if (!col) return;
if (isComputed(col.type)) {
skipped++;
return;
}
patch[col.name] = value;
});
if (!Object.keys(patch).length) continue;
const before: Record<string, CellValue> = {};
for (const name of storedColumnNames) before[name] = target.values[name] ?? null;
const result = await api.updateRow(ws.tableName, target.id, patch);
const delta = diffValues(before, result.row.values, storedColumnNames);
if (Object.keys(delta.after).length) {
ws.undo.record({
kind: 'update',
table: ws.tableName,
rowId: target.id,
before: delta.before,
after: delta.after,
label: t('grid.undoPaste', describeRow(target, table?.display)),
});
written++;
}
}
await ws.refresh();
const note = skipped ? ` ${t('grid.pastedSkipped', skipped, locale)}` : '';
toasts.push(`${t('grid.pasted', written, locale)}${note}`, 'success');
} catch (error) {
toasts.fail(error);
await ws.refresh();
} finally {
setBusy(false);
}
}, [selection, canEdit, rows, columns, storedColumnNames, ws, table, toasts, t, locale]);
/* ---- keyboard --------------------------------------------------------- */
const beginEdit = useCallback(
(seed?: string) => {
if (!selection) return;
if (!canEdit) {
refuseEdit();
return;
}
const row = rows.find((r) => r.id === selection.rowId);
const col = columns.find((c) => c.name === selection.col);
if (!row || !col || isComputed(col.type)) return;
// A checkbox has nothing to type into: opening it is flipping it.
if (col.type === 'checkbox') {
commitCell(row.id, col.name, row.values[col.name] ? 'false' : 'true');
return;
}
// A link cell edits through its picker: what was typed becomes the
// search, while the value draft stays the row the cell points at.
if (col.type === 'link') {
setEditing({
rowId: row.id,
col: col.name,
draft: editableValue(row.values[col.name] ?? null),
search: seed,
});
return;
}
setEditing({
rowId: row.id,
col: col.name,
draft: seed ?? editableValue(row.values[col.name] ?? null),
seeded: seed !== undefined,
});
},
[selection, canEdit, refuseEdit, rows, columns, commitCell],
);
const onKeyDown = useCallback(
(event: React.KeyboardEvent) => {
if (editing) return;
const mod = event.ctrlKey || event.metaKey;
if (mod && event.key.toLowerCase() === 'z') {
event.preventDefault();
void (event.shiftKey ? ws.undo.redo() : ws.undo.undo()).catch((e) => toasts.fail(e));
return;
}
if (mod && event.key.toLowerCase() === 'y') {
event.preventDefault();
void ws.undo.redo().catch((e) => toasts.fail(e));
return;
}
if (mod && event.key.toLowerCase() === 'c') {
event.preventDefault();
void copySelection();
return;
}
if (mod && event.key.toLowerCase() === 'v') {
event.preventDefault();
void pasteAtSelection();
return;
}
switch (event.key) {
case 'ArrowUp':
event.preventDefault();
moveSelection(-1, 0);
return;
case 'ArrowDown':
event.preventDefault();
moveSelection(1, 0);
return;
case 'ArrowLeft':
event.preventDefault();
moveSelection(0, -1);
return;
case 'ArrowRight':
event.preventDefault();
moveSelection(0, 1);
return;
case 'Tab':
event.preventDefault();
moveSelection(0, event.shiftKey ? -1 : 1, true);
return;
case 'PageDown':
event.preventDefault();
moveSelection(20, 0);
return;
case 'PageUp':
event.preventDefault();
moveSelection(-20, 0);
return;
case 'Home':
event.preventDefault();
if (mod) moveSelection(-rows.length, -columns.length);
else moveSelection(0, -columns.length);
return;
case 'End':
event.preventDefault();
if (mod) moveSelection(rows.length, columns.length);
else moveSelection(0, columns.length);
return;
case 'Enter':
case 'F2':
event.preventDefault();
// Ctrl+Enter starts a row, so that adding one is not a thing you
// have to reach for the mouse to do.
if (mod && canEdit && !busy) {
if (requiredColumns.length) beginDraft();
else void addRow();
return;
}
beginEdit();
return;
case 'Escape':
// Escape belongs to whatever is open — an editor, a menu, a panel.
// With nothing open it drops the block but not the cursor: losing
// your place meant the next arrow key started from the first row.
if (block) setBlock(null);
return;
case 'Delete':
case 'Backspace': {
if (!selection) return;
const col = columns.find((c) => c.name === selection.col);
if (!col || isComputed(col.type)) return;
if (!canEdit) {
refuseEdit();
return;
}
event.preventDefault();
commitCell(selection.rowId, selection.col, '');
return;
}
default:
break;
}
// Typing a printable character starts an edit seeded with it, like Excel.
// A checkbox takes no text, so there the space bar flips it instead — and
// a link cell opens its picker already searching for what was typed.
if (!mod && !event.altKey && event.key.length === 1) {
const col = columns.find((c) => c.name === selection?.col);
if (!col || isComputed(col.type) || col.type === 'select') return;
if (!canEdit) {
refuseEdit();
return;
}
if (col.type === 'checkbox') {
if (event.key === ' ') {
event.preventDefault();
beginEdit();
}
return;
}
event.preventDefault();
beginEdit(event.key);
}
},
[
editing,
ws,
toasts,
copySelection,
pasteAtSelection,
moveSelection,
beginEdit,
refuseEdit,
selection,
canEdit,
columns,
commitCell,
rows.length,
block,
busy,
requiredColumns,
beginDraft,
addRow,
],
);
/* ---- column resize + reorder ------------------------------------------ */
const [dragCol, setDragCol] = useState<string | null>(null);
const [dropCol, setDropCol] = useState<string | null>(null);
const resizing = useRef<{ col: ColumnMeta; startX: number; startWidth: number } | null>(null);
useEffect(() => {
function onMove(event: PointerEvent) {
const state = resizing.current;
if (!state) return;
setWidth(state.col, state.startWidth + (event.clientX - state.startX));
}
function onUp() {
resizing.current = null;
}
window.addEventListener('pointermove', onMove);
window.addEventListener('pointerup', onUp);
return () => {
window.removeEventListener('pointermove', onMove);
window.removeEventListener('pointerup', onUp);
};
}, [setWidth]);
const applyReorder = useCallback(
async (from: string, to: string) => {
if (from === to) return;
const order = columns.map((c) => c.name);
const fromIdx = order.indexOf(from);
const toIdx = order.indexOf(to);
if (fromIdx < 0 || toIdx < 0) return;
order.splice(toIdx, 0, ...order.splice(fromIdx, 1));
try {
await api.reorderColumns(ws.tableName, order);
await ws.refresh();
} catch (error) {
toasts.fail(error);
}
},
[columns, ws, toasts],
);
/* ---- render ----------------------------------------------------------- */
if (!table) {
return <div className="gridstate">{t('grid.noTable')}</div>;
}
if (query.isError) {
return (
<div className="gridstate error">
{query.error instanceof Error ? query.error.message : t('grid.rowsFailed')}
</div>
);
}
if (query.isLoading) {
return <div className="gridstate">{t('grid.loadingTable', table.label.toLowerCase())}</div>;
}
const colGroup: CSSProperties[] = columns.map((c) => ({ width: widthOf(c) }));
const linkRecordTable = linkRecord
? ws.tables.find((t) => t.name === linkRecord.table)
: undefined;
const linkRecordRow = linkRecord
? (linkOptions[linkRecord.table] ?? []).find((r) => r.id === linkRecord.rowId)
: undefined;
return (
<div className="gridarea">
{/* Above the grid, not below it: at the end of the table the button sat
under every row there is, and on a long table reaching it meant
scrolling — and loading — all of them. */}
{draft ? (
<div className="addrow drafting">
{t('grid.draftFillIn', draftMissing.map((c) => c.label).join(', '))}
<button onClick={() => discardDraft()}>{t('grid.draftDiscard')}</button>
</div>
) : (
<button
className="addrow"
onClick={() => (requiredColumns.length ? beginDraft() : void addRow())}
disabled={!canEdit || busy}
>
＋ {t('grid.newRow')}
</button>
)}
<div
className={['gridwrap', block ? 'blocksel' : '', dragSelecting ? 'dragsel' : '']
.filter(Boolean)
.join(' ')}
ref={scrollRef}
tabIndex={0}
role="grid"
aria-label={t('grid.gridLabel', table.label)}
aria-rowcount={query.total}
aria-colcount={columns.length + 1}
aria-busy={busy}
onKeyDown={onKeyDown}
onMouseDown={onGridMouseDown}
onMouseMove={onGridMouseMove}
>
{/* min-width, not width: the table stretches to the wrapper when the
columns do not fill it, and the trailing filler column swallows the
slack so real column widths are never rewritten to do it. */}
<table className="grid" style={{ minWidth: totalWidth }}>
<colgroup>
<col style={{ width: 44 }} />
{colGroup.map((style, i) => (
<col key={columns[i]!.name} style={style} />
))}
<col />
</colgroup>
<thead>
<tr>
<th className="rownum" scope="col">
#
</th>
{columns.map((col, index) => {
const sorted = ws.sort?.col === col.name;
return (
<th
key={col.name}
scope="col"
className={[
index === 0 ? 'frozen' : '',
dragCol === col.name ? 'dragging' : '',
dropCol === col.name ? 'drop-target' : '',
blockCols.has(col.name) ? 'selcol' : '',
]
.filter(Boolean)
.join(' ')}
aria-sort={
sorted ? (ws.sort!.dir === 'asc' ? 'ascending' : 'descending') : 'none'
}
draggable={canEdit}
onDragStart={(e) => {
setDragCol(col.name);
e.dataTransfer.effectAllowed = 'move';
e.dataTransfer.setData('text/plain', col.name);
}}
onDragOver={(e) => {
if (!dragCol) return;
e.preventDefault();
setDropCol(col.name);
}}
onDragLeave={() => setDropCol((d) => (d === col.name ? null : d))}
onDrop={(e) => {
e.preventDefault();
const from = e.dataTransfer.getData('text/plain') || dragCol;
setDragCol(null);
setDropCol(null);
if (from) void applyReorder(from, col.name);
}}
onDragEnd={() => {
setDragCol(null);
setDropCol(null);
}}
onContextMenu={(e) => {
e.preventDefault();
headerMenu.open(e, col);
}}
>
<button
className="hbtn"
// Ctrl+click takes the column in or out of the block, the
// way Excel extends a selection; a plain click still sorts.
onClick={(e) =>
block && (e.ctrlKey || e.metaKey)
? toggleBlockCol(col.name)
: ws.toggleSort(col.name)
}
onDoubleClick={() => resetWidth(col)}
>
{col.type === 'link' && <span style={{ color: 'var(--rel)' }}>⚿</span>}
<span className="label">{col.label}</span>
<span className="ty">{typeMark(col.type, currency)}</span>
{/* The constraints the column carries, in the two marks SQL
has always used for them. */}
{col.required && (
<span className="cons" title={t('grid.colRequired', col.label)}>
*
</span>
)}
{col.unique && (
<span className="cons" title={t('grid.colUnique', col.label)}>
≠
</span>
)}
{columnErrors[col.name] && (
<span className="broken" title={columnErrors[col.name]}>
!
</span>
)}
{sorted && (
<span className="sortmark">{ws.sort!.dir === 'asc' ? '↑' : '↓'}</span>
)}
</button>
<span
className="resizer"
role="separator"
aria-label={t('grid.resizeCol', col.label)}
onPointerDown={(e) => {
e.preventDefault();
resizing.current = {
col,
startX: e.clientX,
startWidth: widthOf(col),
};
}}
onDoubleClick={() => resetWidth(col)}
/>
</th>
);
})}
<th className="filler" aria-hidden="true" />
</tr>
</thead>
<tbody>
{/* Composed at the top, right under the headers where the button
is, rather than below rows that may not even be loaded yet. */}
{draftRow && (
<tr className="draftrow">
<td className="rownum" title={t('grid.notSavedYet')}>
＋
</td>
{columns.map((col, index) => {
const editable = !isComputed(col.type) && canEdit;
return (
<GridCell
key={col.name}
row={draftRow}
col={col}
frozen={index === 0}
selected={false}
inBlock={false}
editing={editing}
linkOptions={col.target ? linkOptions[col.target] : undefined}
linkTable={
col.target ? ws.tables.find((t) => t.name === col.target) : undefined
}
currency={currency}
error={undefined}
readOnly={!editable}
// A row being composed has no selection to make: a click on
// one of its cells means "fill this in". A checkbox is the
// exception — the box takes the click itself.
onSelect={() => {
if (editable && col.type !== 'checkbox') {
setEditing({
rowId: DRAFT_ID,
col: col.name,
draft: draft?.[col.name] ?? '',
});
}
}}
onBeginEdit={() => {
if (editable && col.type !== 'checkbox') {
setEditing({
rowId: DRAFT_ID,
col: col.name,
draft: draft?.[col.name] ?? '',
});
}
}}
onDraft={(value) => setEditing((e) => (e ? { ...e, draft: value } : e))}
onCommit={(value, move) => commitDraft(col.name, value, move)}
onCancel={(byKey) => discardDraft(byKey)}
onContextMenu={(e) => e.preventDefault()}
onOpenLink={() => undefined}
/>
);
})}
<td className="filler" aria-hidden="true" />
</tr>
)}
{paddingTop > 0 && (
<tr aria-hidden="true" style={{ height: paddingTop }}>
<td colSpan={columns.length + 2} />
</tr>
)}
{virtualRows.map((virtual) => {
const row = rows[virtual.index];
if (!row) return null;
const isRowSelected = selection?.rowId === row.id;
return (
<tr
key={row.id}
className={isRowSelected ? 'row-selected' : undefined}
aria-rowindex={virtual.index + 2}
>
<td
className={blockRows.has(row.id) ? 'rownum selrow' : 'rownum'}
// Ctrl+click takes the row in or out of the block, the same
// gesture the column headers answer to.
onClick={(e) => {
if (block && (e.ctrlKey || e.metaKey)) toggleBlockRow(row.id);
}}
onContextMenu={(e) => {
e.preventDefault();
rowMenu.open(e, row);
}}
>
{virtual.index + 1}
</td>
{columns.map((col, index) => (
<GridCell
key={col.name}
row={row}
col={col}
frozen={index === 0}
selected={isRowSelected && selection?.col === col.name}
inBlock={blockRows.has(row.id) && blockCols.has(col.name)}
editing={editing}
linkOptions={col.target ? linkOptions[col.target] : undefined}
linkTable={
col.target ? ws.tables.find((t) => t.name === col.target) : undefined
}
currency={currency}
error={columnErrors[col.name]}
readOnly={!canEdit}
onSelect={() => ws.setSelection({ rowId: row.id, col: col.name })}
onBeginEdit={() =>
setEditing({
rowId: row.id,
col: col.name,
draft: editableValue(row.values[col.name] ?? null),
})
}
onRefused={refuseEdit}
onDraft={(draft) => setEditing((e) => (e ? { ...e, draft } : e))}
onCommit={(value, move) => commitCell(row.id, col.name, value, move)}
onCancel={(byKey) => {
setEditing(null);
if (byKey) takeKeysBack();
}}
onContextMenu={(e) => {
e.preventDefault();
// On the block, the menu is about the block.
if (block && blockRows.has(row.id) && blockCols.has(col.name)) {
selectionMenu.open(e, null);
return;
}
ws.setSelection({ rowId: row.id, col: col.name });
rowMenu.open(e, row);
}}
onOpenLink={(rowId) => {
if (col.target) setLinkRecord({ table: col.target, rowId });
}}
/>
))}
<td className="filler" aria-hidden="true" />
</tr>
);
})}
{paddingBottom > 0 && (
<tr aria-hidden="true" style={{ height: paddingBottom }}>
<td colSpan={columns.length + 2} />
</tr>
)}
</tbody>
{/* The aggregate row. Pinned to the bottom of the scroller, aligned to
the columns because it is part of the same table — and empty until
somebody asks a column a question. */}
<tfoot>
<tr>
{/* Marked, not numbered. A count here read as a row number — and
on a six-row table it literally was one. The row count already
stands in the SQL bar, permanently. */}
<td className="rownum" title={t('grid.rowCountTitle', query.total, locale)}>
Σ
</td>
{columns.map((col, index) => {
const fn = ws.summaries[col.name];
const error = summary.errors[col.name];
const text = fn
? summaryText(col, fn, summary.values[col.name] ?? null, currency, locale)
: '';
return (
<td key={col.name} className={index === 0 ? 'sumcell frozen' : 'sumcell'}>
<button
className="sumbtn"
// Named for the function first, so it never collides with
// the column header button of the same name — and so a
// screen reader hears the answer, not just the column.
aria-label={
fn
? t('grid.summaryOf', aggName(t, fn, col), col.label, text)
: t('grid.summarise', col.label)
}
title={
error ??
(fn ? `${aggName(t, fn, col)}: ${text}` : t('grid.summariseThis'))
}
onClick={(e) => summaryMenu.open(e, col)}
>
{error ? (
<span className="broken">!</span>
) : fn ? (
<>
<span className="fn">{aggName(t, fn, col)}</span>
<span className="val">{text}</span>
</>
) : (
<span className="pick">Σ</span>
)}
</button>
</td>
);
})}
<td className="filler" aria-hidden="true" />
</tr>
</tfoot>
</table>
{rows.length === 0 && (
<div className="gridstate">
{ws.search || ws.allFilters.length
? t('grid.noRowsMatch')
: t('grid.noRowsYet', table.label.toLowerCase())}
</div>
)}
{isFetchingNextPage && <div className="gridstate">{t('grid.loadingMore')}</div>}
{headerMenu.state && (
<Menu
x={headerMenu.state.x}
y={headerMenu.state.y}
label={t('grid.columnMenu', headerMenu.state.payload.label)}
onClose={headerMenu.close}
items={headerMenuItems(headerMenu.state.payload)}
/>
)}
{summaryMenu.state && (
<Menu
x={summaryMenu.state.x}
y={summaryMenu.state.y}
label={t('grid.summarise', summaryMenu.state.payload.label)}
onClose={summaryMenu.close}
items={summaryMenuItems(summaryMenu.state.payload)}
/>
)}
{rowMenu.state && (
<Menu
x={rowMenu.state.x}
y={rowMenu.state.y}
label={t('grid.rowActions')}
onClose={rowMenu.close}
items={rowMenuItems(rowMenu.state.payload)}
/>
)}
{selectionMenu.state && (
<Menu
x={selectionMenu.state.x}
y={selectionMenu.state.y}
label={t('grid.selectionMenu')}
onClose={selectionMenu.close}
items={[
{
label: t('grid.copySelection'),
onSelect: () => void copySelection(),
hint: 'Ctrl+C',
},
'separator',
{ label: t('grid.clearSelection'), onSelect: () => setBlock(null) },
]}
/>
)}
{linkRecord && linkRecordTable && (
<RecordPanel
table={linkRecordTable}
row={linkRecordRow}
onClose={() => setLinkRecord(null)}
onOpenTable={() => {
setLinkRecord(null);
ws.setTable(linkRecordTable.name);
}}
/>
)}
</div>
</div>
);
function headerMenuItems(col: ColumnMeta): MenuEntry[] {
return [
{ label: t('grid.sortAsc'), onSelect: () => ws.setSort({ col: col.name, dir: 'asc' }) },
{ label: t('grid.sortDesc'), onSelect: () => ws.setSort({ col: col.name, dir: 'desc' }) },
{ label: t('grid.clearSort'), onSelect: () => ws.setSort(null), disabled: !ws.sort },
'separator',
{
label: t('grid.filterByColumn'),
onSelect: () => ws.addFilter({ col: col.name, op: 'is not empty', val: '' }),
},
{ label: t('grid.resetWidth'), onSelect: () => resetWidth(col) },
{
label: t('grid.hideColumn'),
onSelect: () => ws.hideColumn(col.name),
// The last column stays: a grid of nothing has no menu to bring one back.
disabled: columns.length <= 1,
},
...(ws.hiddenCols.length
? [
{
label: t('grid.showHiddenColumns', ws.hiddenCols.length),
onSelect: () => ws.revealColumns(),
} satisfies MenuEntry,
]
: []),
'separator',
{
label: t('grid.useAsRowLabel'),
onSelect: () => {
void api
.setDisplayColumn(ws.tableName, col.name)
.then(() => ws.refresh())
.catch((e: unknown) => toasts.fail(e));
},
disabled: !canAlter || col.type === 'link' || table?.display === col.name,
},
{ label: t('grid.editColumn'), onSelect: () => onEditColumn(col), disabled: !canAlter },
{
label: t('grid.tightenColumn'),
onSelect: () => onTightenColumn(col),
disabled: !canAlter || isComputed(col.type),
},
{ label: t('grid.addColumn'), onSelect: onAddColumn, disabled: !canAlter },
'separator',
{
label: t('grid.deleteColumn'),
onSelect: () => onDeleteColumn(col),
disabled: !canAlter,
danger: true,
},
];
}
/** Only what the column's type can answer, so the footer never asks for the impossible. */
function summaryMenuItems(col: ColumnMeta): MenuEntry[] {
const current = ws.summaries[col.name];
const items: MenuEntry[] = aggsFor(col).map((fn) => ({
label: aggName(t, fn, col),
hint: current === fn ? '✓' : undefined,
onSelect: () => ws.setSummary(col.name, fn),
}));
if (current) {
items.push('separator', {
label: t('grid.noSummary'),
onSelect: () => ws.setSummary(col.name, null),
});
}
return items;
}
function rowMenuItems(row: Row): MenuEntry[] {
return [
{
label: t('grid.showHistory'),
onSelect: () => {
ws.setAuditRowId(row.id);
ws.setShowAudit(true);
},
},
{
label: t('grid.copyRowId'),
onSelect: () => void navigator.clipboard.writeText(row.id).catch(() => undefined),
},
'separator',
{
label: t('grid.deleteRow'),
onSelect: () => void deleteRow(row),
disabled: !canEdit || busy,
danger: true,
},
];
}
}
/**
* What the menu and the footer call an aggregate, in the reader's language.
* The date and checkbox special cases mirror `aggLabel` in @wellformed/shared.
*/
function aggName(t: TranslateFn, fn: AggFn, col?: ColumnMeta): string {
if (col?.type === 'date') {
if (fn === 'min') return t('grid.agg.earliest');
if (fn === 'max') return t('grid.agg.latest');
}
if (col?.type === 'checkbox' && fn === 'empty') return t('grid.agg.unchecked');
return t(`grid.agg.${fn}`);
}
/**
* How an aggregate reads.
*
* A sum of money is money and formats like the cells above it; a count of the
* filled ones is a number of rows and must not inherit the currency sign. Null
* means the database had nothing to work with — an average of no rows — and
* says so rather than showing a confident zero.
*/
function summaryText(
col: ColumnMeta,
fn: AggFn,
value: CellValue,
currency: string,
locale: Locale,
): string {
if (value === null || value === '') return '—';
if (aggKeepsUnits(fn)) return formatCell(value, col, { currency, locale });
return formatCount(Number(value), locale);
}
function describeRow(row: Row, displayColumn: string | undefined): string {
if (!displayColumn) return row.id;
const value = row.values[displayColumn];
return value === null || value === undefined || value === '' ? row.id : String(value);
}