@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.

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.

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

See examples/README.md for the full list.

Development

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

License

MIT