Spreadsheet-UX op een echte relationele engine
Deze repository wordt als momentopname gepubliceerd: een clone bevat de volledige inhoud, maar geen commitgeschiedenis.
import { useState, type ReactNode } from 'react';
import {
IMPORT_COLUMN_TYPES,
slugifyTableName,
type ImportColumnOverride,
type ImportColumnType,
type ImportGroupOverride,
type ImportNotes,
type ImportPreview,
type Locale,
} from '@wellformed/shared';
import { api, type ImportRequest } from '../api/client';
import { useI18n, type TranslateFn } from '../i18n';
import { useSession } from '../state/session';
import { useWorkspace } from '../state/workspace';
import { useToasts } from '../state/toasts';
import { Modal } from './Modal';
/**
* Bring a spreadsheet in.
*
* Two steps, always: read it, then write it. The preview is not politeness —
* the sheet, the delimiter, the column names and the inferred types are all
* guesses the server made, and this is the only moment anyone can catch a
* wrong one before it becomes a table with 4,000 rows in it. So every guess
* shown here can be corrected here: the sheet and header row by picking
* another, the names by typing over them, the types by choosing — and a type
* choice round-trips through the preview, so the sample column shows what the
* values become before anything is written.
*
* Whatever they have is what they bring: Excel, OpenDocument, CSV, or a block
* of text out of the clipboard. The file is sent as bytes rather than read
* into the textarea, because a workbook is not text — and because the
* spreadsheet the operator is looking at is the one that should be imported,
* not the CSV they were going to be asked to make from it.
*/
export function ImportDialog({
table,
onClose,
onTidy,
}: {
/** Opened from a table's own menu, the target starts on that table. */
table?: string;
onClose(): void;
/** Open the tidy-up walk on a table — the offer made when an import lands messy. */
onTidy(table: string): void;
}): ReactNode {
const ws = useWorkspace();
const session = useSession();
const toasts = useToasts();
const { t, locale } = useI18n();
const [csv, setCsv] = useState('');
const [file, setFile] = useState<{ name: string; data: string } | null>(null);
const [preview, setPreview] = useState<ImportPreview | null>(null);
// Left alone, the server picks the header row and the sheet. Set once
// someone disagrees, and from then on it is their choice that is previewed
// and imported.
const [headerRow, setHeaderRow] = useState<number | undefined>(undefined);
// Which row groups the columns — the file's merged "Q1"/"Q2" headings.
// Undefined lets the server decide, null turns the fold off, a number
// names the row. Reset with everything else that re-reads the document.
const [groupRow, setGroupRow] = useState<number | null | undefined>(undefined);
// Corrections to the group headings, by position. One edit renames every
// column in the group — a messy merged cell is one correction, not five.
const [groupEdits, setGroupEdits] = useState<Record<number, string>>({});
// Trailing totals rows: undefined lets the server decide, 0 keeps them all,
// a number drops that many. Reset with everything that re-reads the file.
const [footerRows, setFooterRows] = useState<number | undefined>(undefined);
const [sheet, setSheet] = useState<string | undefined>(undefined);
const [target, setTarget] = useState<string>(table ?? '');
// The one name a person gives: their own words. The table's identifier is
// derived from it — see slugifyTableName — and the server steps it past
// collisions, so nobody is ever asked to spell an identifier.
const [label, setLabel] = useState('');
// Corrections to the guessed columns, by position — labels typed over the
// guess, types chosen over it. They belong to one reading of one document,
// so another sheet or another header row starts them over.
const [overrides, setOverrides] = useState<Record<number, ImportColumnOverride>>({});
const [busy, setBusy] = useState(false);
const [error, setError] = useState<string | null>(null);
const creating = target === '';
function overrideList(from: Record<number, ImportColumnOverride> = overrides): ImportColumnOverride[] {
return Object.values(from).filter((o) => o.label !== undefined || o.type !== undefined);
}
function groupEditList(from: Record<number, string> = groupEdits): ImportGroupOverride[] {
return Object.entries(from).map(([index, name]) => ({ index: Number(index), label: name }));
}
/**
* Creating a table makes the first column its row label, and a label is
* text — pinned into every request so the preview, the sample values and
* the table that lands all say the same thing about it.
*/
function sentColumns(into: string, cols: ImportColumnOverride[]): ImportColumnOverride[] {
if (into) return cols;
return [
{ ...cols.find((o) => o.index === 0), index: 0, type: 'text' as const },
...cols.filter((o) => o.index !== 0),
];
}
async function readFile(chosen: File) {
const data = base64Of(new Uint8Array(await chosen.arrayBuffer()));
setFile({ name: chosen.name, data });
// Reading a file is the end of whatever was pasted before it, and the old
// header row, sheet and column corrections all named another document.
setCsv('');
setHeaderRow(undefined);
setGroupRow(undefined);
setGroupEdits({});
setFooterRows(undefined);
setSheet(undefined);
setOverrides({});
// The file name is the best guess anyone has at what the table is called.
const stem = chosen.name.replace(/\.[^.]+$/, '').trim();
if (!label && stem) setLabel(stem);
// Handed on rather than read back: `setLabel` above has not landed in
// `label` yet, and the preview would otherwise think nothing is named and
// overwrite the file's own word with one taken off a column heading.
await look({ file: { name: chosen.name, data } }, undefined, undefined, label || stem, [], target, undefined, [], undefined);
}
/** What to send: the uploaded file if there is one, otherwise the textarea. */
function source(): ImportRequest {
return file ? { file } : { csv };
}
async function look(
what: ImportRequest = source(),
row = headerRow,
tab = sheet,
named = label,
cols = overrideList(),
into = target,
grouping: number | null | undefined = groupRow,
headings: ImportGroupOverride[] = groupEditList(),
footer: number | undefined = footerRows,
) {
setBusy(true);
setError(null);
try {
const sent = sentColumns(into, cols);
const result = await api.previewImport({
...what,
headerRow: row,
...(grouping !== undefined ? { groupRow: grouping } : {}),
...(footer !== undefined ? { footerRows: footer } : {}),
sheet: tab,
...(sent.length ? { columns: sent } : {}),
...(headings.length ? { groups: headings } : {}),
...(into ? { table: into } : {}),
});
setPreview(result);
// Pasted text brings no file name, and neither does a file called
// `export (3).xlsx`. An empty name is the only thing standing between
// the preview and the Import button, so fill one in — from the sheet's
// own name where there is one — rather than leaving a disabled button
// and a placeholder that reads like a value.
if (!named) setLabel(suggestLabel(result.sheet ?? undefined, result.columns[0]?.label));
} catch (err) {
// The last good preview stays on screen: the correction table in it is
// the way out of most refusals, and wiping it would leave the error
// with nothing underneath to fix it with.
setError(err instanceof Error ? err.message : t('import.couldNotRead'));
} finally {
setBusy(false);
}
}
async function run() {
setBusy(true);
setError(null);
try {
const cols = sentColumns(target, overrideList());
const headings = groupEditList();
const what = {
...source(),
headerRow,
...(groupRow !== undefined ? { groupRow } : {}),
...(footerRows !== undefined ? { footerRows } : {}),
sheet,
...(cols.length ? { columns: cols } : {}),
...(headings.length ? { groups: headings } : {}),
};
const result = await api.importSheet(
creating ? { ...what, label: label.trim() } : { ...what, table: target },
);
await ws.refresh();
ws.setTable(result.table);
toasts.push(landed(result, t, locale), result.overLimit ? 'info' : 'success');
// The proactive part of tightening: nobody goes looking for a stray
// lowercase c or a date column left as text on their own, so the
// product walks the new table the moment the rows land and offers the
// guided walk when it finds anything. Silent when the walk finds
// nothing — and when it fails, because an offer is not a duty.
void api
.walkTighten(result.table)
.then(({ plan }) => {
const steps = plan.steps.filter((s) => s.clusters.length > 0 || session.can('admin'));
if (!steps.length) return;
toasts.push(t('import.tidyOffer', steps.length, locale), 'info', {
label: t('import.tidyNow'),
run: () => onTidy(result.table),
});
})
.catch(() => undefined);
onClose();
} catch (err) {
setError(err instanceof Error ? err.message : t('import.couldNotImport'));
} finally {
setBusy(false);
}
}
// A correction is a label — any words at all, as long as there are words.
const namesOk = Object.values(overrides).every(
(o) => o.label === undefined || (o.label.trim().length > 0 && o.label.length <= 60),
);
// A column somebody typed a wrong type onto blocks the import — the count
// sits red under that type until they pick another or fix the file.
const readsClean = !preview?.columns.some((c) => c.unreadable > 0);
const ready = Boolean(preview) && readsClean && namesOk && (creating ? label.trim().length > 0 : true);
return (
<Modal
title={t('import.title')}
wide
onClose={onClose}
footer={
<>
<div className="grow" />
<button className="btn" onClick={onClose} disabled={busy}>
{t('common.cancel')}
</button>
<button className="btn primary" onClick={() => void run()} disabled={busy || !ready}>
{busy
? t('common.working')
: preview
? t('import.importRows', preview.rowCount, locale)
: t('import.import')}
</button>
</>
}
>
<div className="mb">
{error && <div className="formerror">{error}</div>}
<label>
{t('import.chooseFile')}
<input
type="file"
accept={ACCEPTS}
onChange={(e) => {
const chosen = e.target.files?.[0];
if (chosen) void readFile(chosen);
}}
/>
<span className="hint">{t('import.fileHint')}</span>
</label>
<label>
{t('import.orPaste')}
<textarea
className="mono"
rows={5}
value={csv}
disabled={Boolean(file)}
onChange={(e) => setCsv(e.target.value)}
onBlur={() => csv.trim() && void look()}
placeholder={t('import.pastePlaceholder')}
/>
{file && (
<span className="hint">
{t('import.readingFile', file.name)}{' '}
<button
className="linkbtn"
type="button"
onClick={() => {
setFile(null);
setPreview(null);
setSheet(undefined);
setHeaderRow(undefined);
}}
>
{t('import.pasteInstead')}
</button>
</span>
)}
</label>
<label>
{t('import.into')}
<select
value={target}
onChange={(e) => {
const into = e.target.value;
setTarget(into);
// The preview reads the file the way the target stores it, so
// another target is another reading of the same columns.
if (preview) void look(source(), headerRow, sheet, label, overrideList(), into);
}}
>
<option value="">{t('import.newTable')}</option>
{ws.tables.map((table) => (
<option key={table.name} value={table.name}>
{table.label}
</option>
))}
</select>
{!creating && <span className="hint">{t('import.matchHint')}</span>}
</label>
{creating && (
<label>
{t('import.tableName')}
<input
value={label}
onChange={(e) => setLabel(e.target.value)}
placeholder={t('import.tableNamePlaceholder')}
/>
{label.trim() && (
<span className="hint mono">
{t('table.inDatabase', slugifyTableName(label.trim()))}
</span>
)}
</label>
)}
{preview && (
<div className="notice">
<div className="hint">
{t(
'import.previewSummary',
preview.rowCount,
preview.columns.length,
describeSource(preview, t),
locale,
)}
{preview.skipped > 0 && ` ${t('import.pastLimit', preview.skipped, locale)}`}
</div>
{/* Only worth asking when there is more than one answer. The sheet
that was opened is the first with anything in it, which is
right often enough to be a default and wrong often enough to
be shown. */}
{preview.sheets.length > 1 && (
<label>
{t('import.sheet')}
<select
value={preview.sheet ?? ''}
disabled={busy}
onChange={(e) => {
const picked = e.target.value;
setSheet(picked);
// Another sheet is another document: its header row, its
// group row and its column corrections are its own, and
// carrying the last ones over would be nonsense.
setHeaderRow(undefined);
setGroupRow(undefined);
setGroupEdits({});
setFooterRows(undefined);
setOverrides({});
void look(source(), undefined, picked, label, [], target, undefined, [], undefined);
}}
>
{preview.sheets.map((tab) => (
<option key={tab} value={tab}>
{tab}
</option>
))}
</select>
</label>
)}
{/* The header was found rather than assumed, so say where — the
column names below are the proof, and this is the sentence that
sends someone to check them. */}
{describeIgnored(preview.ignored, t, locale) && (
<div className="hint">{describeIgnored(preview.ignored, t, locale)}</div>
)}
{/* Where the header is was worked out, not known. The person who
can see the file is the one who can settle it, so the guess is
offered as the current answer rather than stated as a fact. */}
<label>
{t('import.headerFrom')}
<select
value={preview.headerRow}
disabled={busy}
onChange={(e) => {
const row = Number(e.target.value);
setHeaderRow(row);
// Another header row renames every column, so corrections
// to the old names would land on the wrong ones — and the
// group row belonged to the old header too.
setGroupRow(undefined);
setGroupEdits({});
setFooterRows(undefined);
setOverrides({});
void look(source(), row, sheet, label, [], target, undefined, [], undefined);
}}
>
{preview.firstRows.map((line, index) => (
<option key={index} value={index}>
{t('import.rowOption', index + 1, line ? line.slice(0, 70) : t('import.blank'))}
</option>
))}
</select>
</label>
{/* Grouped columns: the row above the header carries merged
headings — "Q1" over Omzet and Kosten — and folding it in is
what keeps two Omzet columns apart. Offered whenever such a
row is there, applied by itself when the header repeats a
name, and always the operator's to overrule. */}
{(preview.groupRow !== null || preview.groupCandidate !== null) && (
<label className="row">
<input
type="checkbox"
checked={preview.groupRow !== null}
disabled={busy}
onChange={(e) => {
const row = preview.groupRow ?? preview.groupCandidate!;
const wanted = e.target.checked ? row : null;
setGroupRow(wanted);
// The fold renames every column, so corrections to the
// old names would land on the wrong ones.
setGroupEdits({});
setOverrides({});
void look(source(), headerRow, sheet, label, [], target, wanted, []);
}}
/>
{t(
'import.groupRowToggle',
(preview.groupRow ?? preview.groupCandidate)! + 1,
(preview.firstRows[(preview.groupRow ?? preview.groupCandidate)!] ?? '').slice(0, 50),
)}
</label>
)}
{/* The headings themselves, editable as one word each. A merged
cell that arrived as "Q1 2026 (definitief)" is cleaned here in
one correction instead of once per column beneath it, and
emptied it stops prefixing its columns at all. */}
{preview.groupRow !== null && preview.groups.length > 0 && (
<div style={{ paddingLeft: 22 }}>
<div style={{ display: 'flex', gap: 8, flexWrap: 'wrap', alignItems: 'center' }}>
{preview.groups.map((group, ordinal) => (
<span
key={ordinal}
style={{ display: 'inline-flex', alignItems: 'center', gap: 4 }}
>
<input
style={{ width: 140 }}
value={groupEdits[ordinal] ?? group.label}
disabled={busy}
aria-label={t('import.groupN', ordinal + 1)}
onChange={(e) =>
setGroupEdits((current) => ({ ...current, [ordinal]: e.target.value }))
}
onBlur={() => {
if ((groupEdits[ordinal] ?? group.label) !== group.label) void look();
}}
onKeyDown={(e) => {
if (e.key === 'Enter') (e.target as HTMLInputElement).blur();
}}
/>
<span className="hint">×{group.count}</span>
</span>
))}
</div>
<span className="hint">{t('import.groupsHint')}</span>
</div>
)}
{/* The totals rows at the bottom — arithmetic about the table, not
rows of it. Left out the way title rows above the header are,
and just as visibly: the rows are quoted, and the checkbox
keeps them after all. */}
{(preview.footerRows > 0 || preview.footerCandidate > 0) && (
<label className="row">
<input
type="checkbox"
checked={preview.footerRows > 0}
disabled={busy}
onChange={(e) => {
const count = preview.footerRows > 0 ? preview.footerRows : preview.footerCandidate;
const wanted = e.target.checked ? count : 0;
setFooterRows(wanted);
void look(source(), headerRow, sheet, label, overrideList(), target, groupRow, groupEditList(), wanted);
}}
/>
{t(
'import.footerToggle',
Math.max(preview.footerRows, preview.footerCandidate),
preview.footerText.join(' — ').slice(0, 80),
)}
</label>
)}
{/* Every guess in this table can be corrected in this table. A
type choice goes back through the preview, so the first-value
column shows what the values become; a name is only sent when
the import runs. Appending is the exception: there the target
table owns the types, so they are shown rather than offered. */}
<table className="plain">
<thead>
<tr>
<th>{t('import.colColumn')}</th>
<th>{t('import.colType')}</th>
<th>{t('import.colFirstValue')}</th>
</tr>
</thead>
<tbody>
{preview.columns.map((column, index) => {
const typed = overrides[index]?.label;
const shown = typed ?? column.label;
const labelLocked = creating && index === 0;
return (
<tr key={index}>
<td style={{ fontSize: 11.5 }}>
<input
style={{ width: 150, fontSize: 11.5 }}
value={shown}
disabled={busy}
aria-label={t('import.colColumn')}
onChange={(e) =>
setOverrides((current) => ({
...current,
[index]: { ...current[index], index, label: e.target.value },
}))
}
/>
{typed !== undefined && !typed.trim() ? (
<span className="hint bad" style={{ display: 'inline', marginLeft: 6 }}>
{t('import.nameRuleShort')}
</span>
) : (
column.header !== '' &&
column.header !== shown && (
<span className="hint" style={{ display: 'inline', marginLeft: 6 }}>
{t('import.was', column.header)}
</span>
)
)}
</td>
<td>
{creating ? (
<select
value={column.type}
disabled={busy || labelLocked}
title={labelLocked ? t('import.labelStaysText') : undefined}
aria-label={t('import.colType')}
onChange={(e) => {
const type = e.target.value as ImportColumnType;
const next = {
...overrides,
[index]: { ...overrides[index], index, type },
};
setOverrides(next);
void look(source(), headerRow, sheet, label, overrideList(next));
}}
>
{IMPORT_COLUMN_TYPES.map((type) => (
<option key={type} value={type}>
{typeLabel(type, t)}
</option>
))}
</select>
) : (
typeLabel(column.type, t)
)}
{column.unreadable > 0 && (
<span className="hint bad" style={{ display: 'block' }}>
{t('import.wontRead', column.unreadable, column.problem ?? '', locale)}
</span>
)}
</td>
<td className="hint" style={{ fontWeight: 400 }}>
{String(preview.sample[0]?.[column.name] ?? '')}
</td>
</tr>
);
})}
</tbody>
</table>
</div>
)}
</div>
</Modal>
);
}
/** What the file picker offers, by extension and by the types browsers report. */
const ACCEPTS = [
'.csv',
'.tsv',
'.txt',
'.xlsx',
'.xlsm',
'.ods',
'text/csv',
'text/plain',
'text/tab-separated-values',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'application/vnd.oasis.opendocument.spreadsheet',
].join(',');
/**
* A file's bytes as base64, in chunks.
*
* `String.fromCharCode(...bytes)` on a whole spreadsheet blows the argument
* limit and throws, and it does it at exactly the sizes worth importing.
*/
function base64Of(bytes: Uint8Array): string {
let binary = '';
for (let at = 0; at < bytes.length; at += 8192) {
binary += String.fromCharCode(...bytes.subarray(at, at + 8192));
}
return btoa(binary);
}
/** What just happened, including the part someone would rather not hear later. */
function landed(
result: {
imported: number;
table: string;
skipped: number;
limit: number;
overLimit: boolean;
},
t: TranslateFn,
locale: Locale,
): string {
const parts = [t('import.landed', result.imported, result.table, locale)];
if (result.skipped > 0) {
parts.push(t('import.landedSkipped', result.skipped, result.limit, locale));
}
// The import was let through whole even though it did not fit. Saying so now
// is the difference between an upgrade prompt and a mystery.
if (result.overLimit) {
parts.push(t('import.landedOverLimit'));
}
return parts.join(' ');
}
/** The type, named the way the column dialog names it — not as an identifier. */
function typeLabel(type: string, t: TranslateFn): string {
switch (type) {
case 'text':
return t('column.typeText');
case 'number':
return t('column.typeNumber');
case 'currency':
return t('column.typeCurrency');
case 'date':
return t('column.typeDate');
case 'checkbox':
return t('column.typeCheckbox');
case 'select':
return t('column.typeSelect');
case 'link':
return t('column.typeLink');
default:
return type;
}
}
/** A legal table name for pasted text, which brings no file name with it. */
function suggestLabel(sheet: string | undefined, firstColumn: string | undefined): string {
return sheet ?? (firstColumn ? `${firstColumn} import` : 'Import');
}
/** What the parser walked past, as a sentence, or nothing when it was tidy. */
function describeIgnored(notes: ImportNotes, t: TranslateFn, locale: Locale): string | null {
const parts: string[] = [];
if (notes.preamble > 0) parts.push(t('import.skippedPreamble', notes.preamble, locale));
if (notes.blankRows > 0) parts.push(t('import.skippedBlank', notes.blankRows, locale));
if (notes.emptyColumns > 0) parts.push(t('import.skippedEmptyCols', notes.emptyColumns, locale));
if (notes.footer > 0) parts.push(t('import.skippedFooter', notes.footer, locale));
if (parts.length === 0) return null;
const listed =
parts.length > 1
? `${parts.slice(0, -1).join(', ')}${t('import.and')}${parts.at(-1)}`
: parts.join('');
return t('import.skippedSentence', listed);
}
/** Where the grid came from, in the words of whichever format it was. */
function describeSource(preview: ImportPreview, t: TranslateFn): string {
if (preview.format === 'xlsx') return t('import.sourceXlsx', preview.sheet ?? '');
if (preview.format === 'ods') return t('import.sourceOds', preview.sheet ?? '');
return t('import.sourceDelimited', describeDelimiter(preview.delimiter, t));
}
function describeDelimiter(delimiter: string | null, t: TranslateFn): string {
if (delimiter === '\t') return t('import.delimTabs');
if (delimiter === ';') return t('import.delimSemicolons');
if (delimiter === '|') return t('import.delimPipes');
return t('import.delimCommas');
}