sheet-schema-editor

A TypeScript/React library and demo UI for preparing tabular data for web applications: upload a CSV/XLS/XLSX file, inspect and edit it in a virtualized grid, define a target schema (names, types, required/nullable, defaults, source-column mapping, transforms), review validation feedback, and export the cleaned result as CSV or JSON.

Everything runs in the browser. The parsing, data model, schema, transforms, validation, and export logic are framework-free pure functions; the React components are a thin layer on top and can be used together (<SheetSchemaEditor />) or individually.

Features

• Import: CSV/TSV/TXT (Papa Parse) and XLS/XLSX/XLSM (SheetJS) with drag and drop, sheet selection for multi-sheet workbooks, file-size limits, and typed errors (UNSUPPORTED_FORMAT, EMPTY_FILE, PARSE_FAILED, FILE_TOO_LARGE).
• Grid editor: row virtualization (tested with 50k rows), inline cell editing with keyboard navigation (Enter/F2 to edit, Esc to cancel, Tab/arrows to move), sorting, debounced search across all cells, row selection and deletion, add/rename/delete columns, "clear empty values".
• Schema tools: infer a schema from the data, edit field names/types (string, number, boolean, date, datetime), nullable/required flags, default values, source-column mapping, ordered transform pipelines (trim, case, regex replace, default-if-empty, parse number/boolean/date, value mapping, custom function), and per-field issue counts.
• Validation & preview: applySchema produces normalized output rows plus a list of issues (REQUIRED_MISSING, TYPE_MISMATCH, NO_SOURCE, NULL_NOT_ALLOWED) with error/warning severity, a summary, and a paginated preview with invalid rows highlighted.
• Export: toCsv, toJson, and a browser downloadBlob helper; dates serialize as ISO strings (or epoch millis for JSON).
• Accessible, responsive UI: real tablist semantics, labelled controls, focus rings, aria-live status, role="alert" errors, and a layout that stacks below 900px.

Quick start

npm install
npm run dev        # demo at http://localhost:5173
npm test           # vitest (unit + component tests)
npm run lint       # eslint
npm run typecheck  # tsc --noEmit
npm run build      # demo bundle -> dist-demo/
npm run build:lib  # library (ESM + CJS + .d.ts) -> dist/
npm run format     # prettier

Requires Node 18+ (developed on Node 22).

Architecture

File ──parse──▶ Dataset ──edit──▶ Dataset ──applySchema(schema)──▶ { rows, issues, summary } ──toCsv/toJson──▶ export
                                                     ▲
                                  Schema (fields, types, mapping, transforms, defaults)

src/
  lib/
    index.ts            public API barrel
    model/              Dataset / Row / CellValue types and pure editing helpers
    parsing/            parseFile dispatcher, parseCsv (Papa Parse), parseWorkbook + listSheets (SheetJS)
    schema/             Schema / SchemaField types, inferSchema, field helpers, validateSchemaDefinition
    transforms/         Transform union, applyTransform(s), TRANSFORM_LABELS
    validation/         coerceToType, applySchema
    export/             toCsv, toJson, downloadBlob, exportDataset
    hooks/              useSheetEditor (reducer-based state for the whole flow)
    components/         FileDropzone, DataGrid, SchemaEditor, ValidationPanel, OutputPreview, SheetSchemaEditor, styles.css
  demo/                 Vite demo app (sample data + 50k-row generator)

Core data types:

type CellValue = string | number | boolean | Date | null;
type Dataset = {
  columns: { id: string; name: string }[];
  rows: { __id: string; cells: Record<string, CellValue> }[];
};

type FieldType = 'string' | 'number' | 'boolean' | 'date' | 'datetime';
type SchemaField = {
  id: string;
  name: string;
  type: FieldType;
  nullable: boolean;
  required: boolean;
  defaultValue?: CellValue;
  sourceColumnId: string | null;
  transforms: Transform[];
};
type Schema = { fields: SchemaField[] };

Per cell, applySchema runs: source value → transforms → default (if empty and a default is set) → coerce to the field type → null/required checks. Rows with any error-severity issue are counted as invalid; warning issues (e.g. a non-nullable optional field that ended up null) do not invalidate the row.

Library usage

Headless (no React)

import {
  parseFile,
  inferSchema,
  updateField,
  applySchema,
  toCsv,
  toJson,
} from 'sheet-schema-editor';

const { dataset, warnings } = await parseFile(file, { maxFileSizeBytes: 20 * 1024 * 1024 });

let schema = inferSchema(dataset);
const amount = schema.fields.find((f) => f.name === 'amount')!;
schema = updateField(schema, amount.id, {
  type: 'number',
  required: true,
  transforms: [{ kind: 'trim' }, { kind: 'parseNumber' }],
});

const result = applySchema(dataset, schema);
console.log(result.summary); // { rowCount, validRowCount, issueCount, issuesByField }
const csv = toCsv(result.rows);
const json = toJson(result.rows, { pretty: true, dateFormat: 'iso' });

Drop-in editor

import { SheetSchemaEditor } from 'sheet-schema-editor';
import 'sheet-schema-editor/styles.css';

<SheetSchemaEditor
  maxFileSizeBytes={50 * 1024 * 1024}
  onExport={(format, content) => upload(format, content)} // omit to trigger a browser download
/>;

Props: initialDataset?, initialSchema?, maxFileSizeBytes?, onExport?(format: 'csv' | 'json', content: string), className?.

Individual components with the hook

import {
  useSheetEditor,
  FileDropzone,
  DataGrid,
  SchemaEditor,
  OutputPreview,
} from 'sheet-schema-editor';

function MyImporter() {
  const editor = useSheetEditor();
  return (
    <>
      <FileDropzone status={editor.status} error={editor.error} onFile={editor.loadFile} />
      {editor.dataset && (
        <>
          <DataGrid
            dataset={editor.dataset}
            onChange={editor.setDataset}
            onAddRow={editor.addRow}
            onDeleteRows={editor.deleteRows}
            onRenameColumn={editor.renameColumn}
            onDeleteColumn={editor.deleteColumn}
          />
          <SchemaEditor
            dataset={editor.dataset}
            schema={editor.schema}
            onChange={editor.setSchema}
          />
          <OutputPreview result={editor.applySchema} onExport={(format) => /* ... */ null} />
        </>
      )}
    </>
  );
}

useSheetEditor(initialDataset?, initialSchema?) returns { dataset, schema, status, error, warnings, fileName, fileSize, sheetNames, applySchema, loadFile, setDataset, setSchema, addRow, deleteRows, addColumn, renameColumn, deleteColumn, setCell, clearEmptyValues, reset, ... }. DataGrid accepts focusCell={{ rowIndex, columnId }} so a validation issue can scroll the grid to its source cell.

Supported formats

Format	Extension	Parser	Notes
CSV / TSV	.csv .tsv .txt	Papa Parse	Delimiter auto-detected (TSV forced to tab); first row = header
Excel 2007+	.xlsx .xlsm	SheetJS	Dates read as Date objects; pick a sheet via sheetName
Excel 97-2003	.xls	SheetJS	Legacy format; formatting quirks possible

API overview

Area	Exports
Parsing	parseFile, parseCsv, parseWorkbook, listSheets, ParseError, ParseResult
Dataset	createDataset, addRow, deleteRows, addColumn, renameColumn, deleteColumn, setCell, clearEmptyValues, inferColumnType, filterRows, sortRows
Schema	inferSchema, addField, updateField, removeField, reorderField, validateSchemaDefinition
Transforms	applyTransform, applyTransforms, TRANSFORM_LABELS, Transform
Validation	coerceToType, applySchema, ValidationIssue, OutputRow
Export	toCsv, toJson, downloadBlob, exportDataset
React	useSheetEditor, SheetSchemaEditor, FileDropzone, DataGrid, SchemaEditor, ValidationPanel, OutputPreview

Limitations

• Everything is in-memory in the browser; there is no streaming parser, so very large files (hundreds of MB) are limited by available memory. The default upload cap is 50 MB.
• Date/boolean/number coercion uses pragmatic heuristics (ISO and MM/DD/YYYY dates, Excel serial numbers, yes/no/y/n/1/0/true/false). Ambiguous locale formats (e.g. DD/MM/YYYY) are not auto-detected; use a parseDate transform with an explicit format.
• Schema inference picks a type when at least 90% of non-empty values match; heavily mixed columns fall back to string.
• The xlsx dependency is pinned to the last npm release (0.18.5); newer SheetJS builds are distributed from the SheetJS CDN.
• Formulas, styles, merged cells, and multi-row headers are not preserved; the first row of a sheet is always treated as the header.
• custom transforms (arbitrary functions) cannot be serialized to JSON.

License

MIT © CommunityPoke