@onparallel/write-excel-file-data-validation

Data validation custom feature for write-excel-file.

Adds support for Excel's "data validation" rules — dropdown lists, numeric/date/time ranges, text length limits, and custom formulas — without requiring a fork of the base package.

Install

npm install write-excel-file @onparallel/write-excel-file-data-validation

write-excel-file is a peer dependency (^4.0.0). The package is published publicly under the @onparallel scope — no authentication required to install.

The package is ESM. You can load it with import or, on Node.js 22.12 or later, with require() from CommonJS.

Usage

Register the feature when calling writeXlsxFile(). write-excel-file/node's built-in SheetOptions does not know about dataValidation, so intersect it with the DataValidationSheetOptions type exported by this package:

import writeXlsxFile, { type SheetOptions } from "write-excel-file/node";
import dataValidation, {
  type DataValidationSheetOptions,
} from "@onparallel/write-excel-file-data-validation";

const sheetOptions: SheetOptions<any> & DataValidationSheetOptions = {
  sheet: "Sheet1",
  dataValidation: [
    {
      cellRange: {
        from: { row: 2, column: 1 },
        to: { row: 10, column: 1 },
      },
      validation: {
        type: "list",
        values: ["open", "closed", "pending"],
        error: "Pick one",
      },
    },
  ],
};

await writeXlsxFile(data, sheetOptions, { features: [dataValidation] }).toFile("out.xlsx");

If you use the intersection in many places, alias it locally: type MySheetOptions = SheetOptions<any> & DataValidationSheetOptions.

Why not module augmentation? write-excel-file/node re-exports its types with export type { ... }, which TypeScript does not allow downstream packages to augment.

Supported validation types

| type | OOXML type | Required fields | | -------------- | ------------ | --------------------------------------------------------------------------------- | | 'list' | list | values: string[] or valuesRange: string (e.g. '$E$4:$G$4') | | 'integer' | whole | operator, value (and value2 for '...' / '!...') | | 'decimal' | decimal | same as integer | | 'date' | date | operator, value (and value2 for between operators); values: Date/number | | 'time' | time | same as date; Date values are reduced to fractional time-of-day | | 'textLength' | textLength | same as integer | | 'custom' | custom | formula: string | | 'any' | (no type) | Only metadata (used to attach a tooltip/error message without restricting input) |

Operators (mirror the symbols used by conditionalFormatting):

| Symbol | OOXML | | ------ | -------------------- | | < | lessThan | | > | greaterThan | | <= | lessThanOrEqual | | >= | greaterThanOrEqual | | = | equal | | != | notEqual | | ... | between | | !... | notBetween |

Common options (any type)

| Field | Default | OOXML attribute | | ------------------ | -------- | -------------------------------------------------------------------------------- | | error | — | error | | errorTitle | — | errorTitle | | errorStyle | 'stop' | errorStyle | | input | — | prompt | | inputTitle | — | promptTitle | | allowBlank | true | allowBlank | | showErrorMessage | true | showErrorMessage | | showInputMessage | true | showInputMessage | | showDropdown | true | showDropDown (inverted: OOXML "1" means hide) — only meaningful for 'list' |

Maximum lengths enforced (Excel limits): titles ≤ 32 characters, messages ≤ 255 characters, inline list formulas ≤ 255 characters.

Limitations

• Sheet-level only. This feature reads dataValidation from sheetOptions. Per-cell validation (attaching a validation property to a cell object) is not supported because write-excel-file's feature API does not expose sheet data to the transform hook. If you need per-cell validation, encode it as a sheet-level rule with a single-cell range:

  { cellRange: { from: { row: 5, column: 3 }, to: { row: 5, column: 3 } }, validation: { ... } }

• Inline list commas and quotes. Excel uses , as the list separator inside <formula1>"…"</formula1> and " as the surrounding delimiter — neither has a working escape. Values containing , or " are rejected; use valuesRange to reference a range of cells instead.

Examples

The examples/ folder contains runnable TypeScript scripts — one per validation type — that emit .xlsx files next to themselves. Open the generated files in Excel/Numbers/LibreOffice to confirm each rule works.

pnpm install
pnpm exec tsx examples/list-inline.ts # run a single example
pnpm run examples                     # run them all

See examples/README.md for the full list.

Development

pnpm install
pnpm test          # vitest
pnpm run typecheck # tsc --noEmit
pnpm run build     # emit dist/ via tsc

License

MIT