Data validations

Validations are an array in ws["!validations"].

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.aoa_to_sheet([[1], [2], [3], [4], [5]]);
ws["!validations"] = [
  { ref: "A1:A5", t: "List", l: ["a", "b", "c"], input: { title: "Letter", message: "Pick one" } },
  { ref: "B1:B5", t: "Whole", op: "IN", blank: false, min: 0, max: 10,
    error: { title: "Range", message: "Enter 0-10", style: "stop" } },
];
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }));
const v = back.Sheets.S["!validations"];
console.log(v.length, v[0].t, v[0].l, v[1].op, v[1].min, v[1].max, v[1].blank);

Output

2 List [ 'a', 'b', 'c' ] IN 0 10 false

Key	Meaning
ref	cell or range: "A2", "A2:C4", { r, c } or { s, e }
t	type (see below)
l	array of strings for a fixed drop-down list
f	formula or range (for Custom, or for a List read from cells)
op	operator (see below)
min, max, v	bounds or the single value, depending on the operator
blank	“ignore blank”; false turns it off
input	input message { title, message }; false turns the message off
error	error alert { title, message, style } with style "stop", "warning" or "info"; false turns the alert off

t	Excel “Allow”	Parameters
"Any"	Any value	none
"Whole"	Whole number	an operator with min and max, or v
"Decimal"	Decimal	same
"List"	List	l or f
"Date"	Date	same as Whole
"Time"	Time	same as Whole
"Length"	Text length	same as Whole
"Custom"	Custom	f

op	Excel name	Uses
"IN"	between	min, max
"OT"	not between	min, max
"EQ" "NE" "GT" "LT" "GE" "LE"	equal, not equal, greater, less, greater or equal, less or equal	v

Use the matching operator fields: min and max for IN / OT, or v for a single-value comparison. The XLSX reader and writer preserve OT, error-alert styles, comparison values in v, and the Length type. An error alert containing only style, without a title or message, is omitted; see Known differences.

const XLSX = require("@agent-sheet/wasm");
const assert = require("node:assert/strict");
const ws = XLSX.utils.aoa_to_sheet([["Code"]]);
ws["!validations"] = [{ ref: "A1", t: "Length", op: "OT", min: 1, max: 3,
  error: { title: "Length", message: "Choose another length", style: "warning" } }];
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "S");
const rule = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" })).Sheets.S["!validations"][0];
assert.equal(rule.t, "Length");
assert.equal(rule.op, "OT");
assert.equal(rule.min, 1);
assert.equal(rule.max, 3);

Long lists

Keep inline lists within Excel’s 255-character limit. The writer accepts longer lists, but Excel may reject them. Put longer lists on a hidden sheet and point to them with f:

const XLSX = require("@agent-sheet/wasm");
const values = ["abc", "acb", "bac", "bca", "cab", "cba"];
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([[], []]);
XLSX.utils.book_append_sheet(wb, ws, "Main");
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[""]].concat(values.map((v) => [v]))), "Lookup");
wb.Workbook = { Sheets: [{ Hidden: 0 }, { Hidden: 2 }] };            // 2 = very hidden
ws["!validations"] = [{ t: "List", ref: "B1:B2", f: "'Lookup'!A2:A" + (values.length + 1) }];
const back = XLSX.read(XLSX.write(wb, { type: "buffer", bookType: "xlsx" }));
console.log(back.Workbook.Sheets[1].Hidden, back.Sheets.Main["!validations"][0].f);

Output

2 'Lookup'!A2:A7

Validations in template mode

You can add and remove validations in an existing file without losing anything else. See Template: data validations.