nuxt-ui-tools Spreadsheet Import

Three layers, each usable without the next:

1. Schema (defineSpreadsheetSchema): the data contract. Columns, how headers and values match, rules, row identity, output. No UI.
2. Runtime (useSpreadsheetImport): headless state and commands, one reactive model per concern: file, context, layout, columns, values, rows, review, submit, readiness. Tests, scripts, and fully custom screens stop here.
3. UI: UiSpreadsheetImport* parts placed wherever you want (one page, a drawer, a wizard), optional steps (useSpreadsheetSteps), and one ready composition (UiSpreadsheetImport).

Matching is exact: a header or a value matches a declared name, ignoring case, accents, line breaks, repeated spaces, and a trailing *. Anything else goes to the column's policy or to the user. The engine never guesses.

Files are read with SheetJS (xlsx), an optional peer that must be 0.20.2 or later. npm only serves 0.18.5, which has known vulnerabilities, so install it from the SheetJS CDN: bun add https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz.

Schema

Declare the context type first when columns depend on data the page provides. Every callback receives it as ctx. Columns are a chain: each method adds a column, and the callbacks of a column (options, default, rules, parse) receive row, typed with the columns declared above it. Write columns before the callbacks that read whole rows (rows, validate, output): they are typed from it.

interface AssessmentsContext {
  center: TestCenter
  products: readonly Product[] // each with its own `scale` and `skills`
}

const productOf = (ctx: AssessmentsContext, id: string) =>
  ctx.products.find((product) => product.id === id)

export const assessmentsImport = defineSpreadsheetSchema<AssessmentsContext>()({
  key: 'assessments',
  file: { accept: ['.xlsx', '.csv'], maxRows: 5000 },
  columns: (c) =>
    c
      .select('testCenterId', {
        label: 'Test center',
        headers: 'Test center ID',
        options: ({ ctx }) => [ctx.center.vtestId],
        default: ({ ctx }) => ctx.center.vtestId,
        unknown: 'error',
      })
      .text('secureCode', { label: 'Secure code', required: true })
      .text('examName', { label: 'Exam name', required: true })
      .select('productId', {
        label: 'Product',
        from: 'examName',
        options: ({ ctx }) =>
          ctx.products.map((product) => ({
            label: product.name,
            value: product.id,
            aliases: product.examNames,
          })),
        required: true,
      })
      .date('completedAt', {
        label: 'Completed at',
        formats: ['MMMM d, yyyy h:mm a'],
        rules: (v) => [v.notFuture()],
      })
      .number('score', {
        // `row.productId` is typed: the product column is declared above.
        rules: (v, { row, ctx }) => [v.max(productOf(ctx, row.productId)?.maxScore ?? 100)],
      })
      .group('levels', { label: 'CEFR levels' }, (g) =>
        g
          .select('general', {
            label: 'General',
            headers: 'General level',
            // Each product has its own scale: the options follow the row's product.
            options: ({ row, ctx }) => productOf(ctx, row.productId)?.scale ?? CEFR,
          })
          .select('listening', { label: 'Listening', headers: 'Listening level', options: CEFR }),
      )
      .dynamic('affiliations', {
        label: 'Affiliations',
        items: ({ ctx }) => ctx.center.affiliationGroups,
        column: (group, c) =>
          c.select(group.slug, {
            label: group.name,
            headers: `${group.name}: PRÉREQUIS CECR`,
            options: {
              source: toOptions(group.items),
              create: { handler: ({ label }) => api.affiliations.create(label) },
            },
            multiple: true,
            unknown: 'create',
          }),
      }),
  rows: {
    key: (row) => row.secureCode,
    duplicates: 'error',
    existing: {
      lookup: ({ keys, ctx }) => assessmentsByCodeQuery({ centerId: ctx.center.id, codes: keys }),
      action: ({ row, existing }) =>
        row.levels.general === existing.levels.general ? 'skip' : 'update',
    },
  },
  validate: ({ row, issue }) => [
    !row.levels.general &&
      row.status === 'Done' &&
      issue('levels.general', 'Required when the test is done'),
  ],
  output: ({ row, mode, existing, ctx }) => ({
    ...row,
    centerId: ctx.center.id,
    assessmentId: existing?.id,
  }),
})

A schema without context skips the type argument: defineSpreadsheetSchema({ ... }).

Column builders

Method	Value	Own options
.text	string	—
.number	number	decimal: '.' | ',' (thousands separators are ignored)
.date	ISO string, with the time if present	formats, like dd/MM/yyyy or MMMM d, yyyy h:mm a
.boolean	boolean	true / false: texts read as each (yes, oui, x, 1…)
.select	the option values, literal when possible	options, from, unknown
.group	a nested object: (g) => g.text(…)…	label, description, when
.dynamic	a record, one column per context item	items: ({ ctx }) => list, column: (item, c) => c.…

Options every column takes: label, headers (strings, regexes, or ({ header, ctx }) => boolean; the label and the key always match too), required, default (a value or ({ ctx, row }) => value, used when the column is missing or the cell is empty), when (({ ctx }) => boolean, leaves the column out), description, example, parse (({ cell, ctx, row }) => value, its return type becomes the value type), rules, multiple (true or { separator }), editable.

Value types follow the options: optional and without a default → T | null; required or default → T; multiple → T[]; when → the key is optional in the row. Declaring a key twice is a type error, and so is reading a column declared below.

Columns depending on columns above

row in a column's callbacks holds the columns declared above, parsed. Use it when the accepted values, the default, or the rules of a column depend on another one:

c.select('product', { options: products, required: true })
  .select('category', { options: ({ row, ctx }) => categoriesOf(ctx, row.product) })
  .select('level', { options: ({ row, ctx }) => scaleOf(ctx, row.product) })
  .number('score', { rules: (v, { row, ctx }) => [v.max(maxScoreOf(ctx, row.product))] })
  .text('note', { default: ({ row }) => `Imported for ${row.product}` })

Options reading row return a list; each row is checked against its own list, and the cell editor offers it. A value matching none is asked once per list: a question carries scope (the fields the options read, with their values in the question's rows: "For Product: English") and choices (that list). Editing the product re-checks the row.

Options

A select takes the option sources of form selects:

c.select('status', { options: ['Done', 'Absent'] }) // a list
c.select('productId', { options: ({ ctx }) => ctx.products.map(toOption) }) // from the context
c.select('level', { options: ({ row, ctx }) => scaleOf(ctx, row.productId) }) // from the row
c.select('countryId', { options: countriesQuery() }) // a TanStack query
c.select('candidateId', { options: candidates }) // a defineRemoteOptions loader
c.select('site', { options: { source: sites, create: { handler } }, unknown: 'create' })

Items are values or { value, label, aliases }; aliases are the other exact names a value has in files. A remote loader is matched exactly with its optional resolveLabels query (one request for every distinct value of the file); without it, each value is searched. Its load feeds the picker.

from: 'examName' reads the cell of a column declared above: the file's exam name becomes a product. A from that names no column above is a type error.

Unknown values

unknown	A value that is not an option…
ask (default)	becomes a question in Values, once per distinct value; it blocks until answered
error	is a blocking issue on the row
skip-rows	leaves its rows out (reason value); the user can answer otherwise
leave-empty	is dropped
create	is created with options.create.handler when the user imports

Answers apply to every row using the value (in the question's scope when options depend on the row). Choices are listed in declared order, never pre-filled.

Rules and levels

rules: (v) => [...], or (v, { row, ctx }) => [...] to read the columns above, with required, validate, email, pattern, minLength, maxLength, min, max, between, notFuture. Each takes { message, level }; error (default) blocks the row, warning and info only inform. Rules run on non-empty values, on each item with multiple, and are typed by the column: v.min on a text column is a type error. Cross-field rules go in validate, which returns issues or falsy values.

Row identity

rows.key is a function of the row (a tuple for several fields). duplicates: error (both rows blocked, "Same key as row 34"), keep-first, or keep-last. existing.lookup({ keys, ctx }) returns the stored records (a query definition or a promise); action is update (default), skip, error, or a function of { row, existing, ctx }. Stored records in the row's shape let review show the cells that change. Each row gets a mode: create, update, or skip.

Sheet and header row

file.sheet: 'auto' (default) picks the sheet whose rows name the most columns; file.headerRow: 'auto' (default) picks the row, among the first 20, naming the most columns. Title rows above are skipped. Fix them with a sheet name and a 1-based row for your own templates.

Runtime

const importer = useSpreadsheetImport(assessmentsImport, {
  context: {
    center: () => center.value, // a value, a ref, or a getter
    products: productsQuery(), // or a TanStack query, loaded by the runtime
  },
  onSubmit: async ({ create, update, rows, ctx, reportProgress, signal }) => {
    const result = await api.assessments.import({ create, update })
    return {
      rejected: result.errors.map((error) => ({
        index: rows[error.position].index,
        message: error.message,
      })),
    }
  },
  submit: { batchSize: 250 },
})

importer.file.load(file) // or importer.file.paste(text)
await importer.ready()
importer.readiness // { missingColumns, openValues, invalidRows, importable, loading, canSubmit }
await importer.submit.run()

context is required when the schema declares one. Every model is reactive and reads the same in templates and scripts (importer.rows.invalid.length):

Model	State	Commands
file	name, loaded, reading, error, sheets	load(source, name?), paste(text), clear()
context	ctx, loading, ready, error	—
layout	sheet, headerRow, detected, ambiguous, candidates, preview	setSheet(), setHeaderRow(), redetect()
columns	fields (status matched / default / missing / unmatched, suggestion), missing, unusedHeaders	assign(field, header), ignore(field), reset(field)
values	questions (with scope and choices), open, recognized, loading	answer(question, option value or action), clear(), choices(field, row?), search()
rows	all, valid, invalid, discarded, importable, byMode, keyFields	cell(), edit(), revert(), discard(), restore(), distinct(), issues(), exportInvalid()
review	tab, level, mode, issue, search, visible, selection, inspected, position	setters, select(), inspect(), next(), previous()
submit	status, progress, imported, rejected, error	run(), retryRejected(), reset()

Field paths (levels.general, affiliations.${string}) and row data are typed from the schema. Edits are text: they go through the same parsing, options, answers, and rules as the file. Suggestions offer, for a missing required field, an unused column whose values fit it; they are never applied without the user. Rows the server refuses come back with a server issue.

UI

Parts read the importer from UiSpreadsheetImportRoot, or from their own importer prop. They render no step headings: titles and KPIs belong to your page.

Part	Slots and props
UiSpreadsheetImportRoot	importer, steps, locale. Renders nothing.
…Dropzone	default slot { load, paste, reading, error, accept }; accepts drops, browsing, and ⌘V.
…FileCard, …SourceSettings	file name and Replace; sheet and header row with a preview (always, no-preview).
…ExpectedColumns, …TemplateButton	columns the file should have (#column); the template built from the schema.
…ColumnMapping	only="missing", suggestions, group-by-status; #field, default slot with assign.
…ValueMapping	only="open", no-recognized; #question.
…Table, …TableToolbar, …RowInspector	editable virtualized grid (fields, height, editable, selectable, #cell), tabs and filters, one row with its issues.
…Stats, …Stat, …ExportButton	KPI tiles (#extra for yours), one tile, the invalid rows as xlsx.
…SubmitButton, …Summary, …Progress	import with readiness; what will be imported (#rows); progress, rejections, retry.
…Stepper, …Step, …StepNav	wizard parts: orientation="vertical" and compact; a slot per step key; Back, Continue, Import, cancel-to.

A page without steps:

<UiSpreadsheetImportRoot :importer="importer">
  <UiSpreadsheetImportDropzone v-if="!importer.file.loaded" />
  <template v-else>
    <UiSpreadsheetImportColumnMapping only="missing" />
    <UiSpreadsheetImportValueMapping only="open" />
    <UiSpreadsheetImportTable />
    <UiSpreadsheetImportSubmitButton />
  </template>
</UiSpreadsheetImportRoot>

A wizard declares its steps. Built-in steps know their readiness and default parts; yours are the same object:

const steps = useSpreadsheetSteps(importer, [
  { key: 'center', label: 'Center', ready: () => Boolean(centerId.value) },
  spreadsheetSteps.file(),
  spreadsheetSteps.columns({ show: 'when-needed' }),
  spreadsheetSteps.values({ label: 'Matches', show: 'when-needed' }),
  spreadsheetSteps.review(),
  spreadsheetSteps.submit(),
])

<UiSpreadsheetImportRoot :importer="importer" :steps="steps">
  <UiSpreadsheetImportStepper orientation="vertical" :compact="steps.current === 'review'" />
  <UiSpreadsheetImportStep>
    <template #center><CenterPicker v-model="centerId" /></template>
  </UiSpreadsheetImportStep>
  <UiSpreadsheetImportStepNav cancel-to="/assessments" />
</UiSpreadsheetImportRoot>

when-needed steps are skipped while they have nothing to do, and stay done once visited.

<UiSpreadsheetImport :importer="importer" :steps="…" title="…" closable> is the ready composition: stepper, current step with headings, navigation. A slot named after a step key replaces its content.

Copy lives under spreadsheet.* in the locale; override it with extendUiToolsLocale and pass it to the root, like { spreadsheet: { nav: { import: 'Import {count} assessments' } } }. The current step marker uses --nut-sheet-accent-fg for its text on the primary color.