Schema Builder

Data Validation

Add native Excel dropdowns, prompts, blocking errors, and formula-based validation rules.

Use validation when a column should emit native Excel data-validation rules.

Unlike JavaScript-side transforms, validation stays live after the workbook opens. That means Excel can still:

show dropdown lists
show input prompts when a cell is selected
block invalid edits with an error alert
reevaluate custom rules after manual edits

Validation builder

The recommended API is the callback builder form:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

type Row
 = {
  amount
: number;
  startDate
: Date
;
  status
: "draft" | "active" | "archived";
};

const schema
 = createExcelSchema
<Row
>()
  .column
("status", {
    header
: () => "Status",
    accessor
: "status",
    validation
: (validation
) =>
      validation
        .list
(["draft", "active", "archived"])
        .prompt
({
          title
: () => "Allowed values",
          message
: () => "Choose draft, active, or archived",
        })
        .error
({
          title
: () => "Invalid status",
          message
: () => "Use one of the allowed workflow states",
        }),
  })
  .column
("amount", {
    accessor
: "amount",
    validation
: (validation
) => validation
.integer
().between
(100, 100000).allowBlank
(),
  })
  .column
("startDate", {
    accessor
: "startDate",
    validation
: (validation
) => validation
.date
().gte
(new Date
(Date
.UTC
(2025, 0, 1))),
  })
  .build
();

Supported validation kinds

list([...])
integer()
decimal()
date()
textLength()
custom(({ row, fx }) => ...)

integer() is the public API name even though Excel serializes this kind as whole internally.

Comparison helpers

These apply to numeric, date, text-length, and custom rules where appropriate:

between(min, max)
notBetween(min, max)
eq(value)
neq(value)
gt(value)
gte(value)
lt(value)
lte(value)

Prompts and error alerts

Prompt and error text support lazy callbacks, which is useful when labels come from your own i18n layer:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

declare function t
(key
: string): string;

const schema
 = createExcelSchema
<{ status
: string }>()
  .column
("status", {
    header
: () => t
("columns.status"),
    accessor
: "status",
    validation
: (validation
) =>
      validation
.list
(["draft", "active", "archived"]).error
({
        title
: () => t
("validation.status.title"),
        message
: () => t
("validation.status.message"),
      }),
  })
  .build
();

The library only accepts lazy callbacks. It does not know about any i18n framework directly.

Custom validation rules

Custom rules reuse the same formula DSL used by formula columns and conditional styles.

That means you can compare the current column against earlier columns in the same row:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

const schema
 = createExcelSchema
<{ amount
: number; score
: number }>()
  .column
("amount", { accessor
: "amount" })
  .column
("score", {
    accessor
: "score",
    validation
: (validation
) =>
      validation
.custom
(({ row
 }) => row
.ref
("score").gte
(row
.ref
("amount"))),
  })
  .build
();

Like formulas, validation references are predecessor-only. A column can validate against columns declared before it.

Excel-table mode

validation also works in createExcelSchema({ mode: "excel-table" }).

The rule authoring API stays the same. Only the emitted reference strategy changes under the hood so Excel-table rows still validate correctly.

Kitchen sink example

The kitchen sink workbook now includes a dedicated Validation sheet with:

a dropdown-backed Status column
an integer range on Amount
a minimum date rule on Start Date

This is the easiest place to test the feature in Excel manually.

Conditional Styles

Apply native Excel conditional formatting with the same formula DSL used by formula columns.

Formula Columns

Declare Excel formulas with a type-safe DSL that works in both report and excel-table modes.