XLSX
数据验证
列表、数字、日期和自定义数据验证
列表验证
为单元格创建下拉列表：
{
"worksheets": [
{
"name": "Sheet1",
"dataValidations": [
{
"sqref": "B2:B5",
"type": "list",
"formula1": "\"Yes,No,Maybe\""
}
],
"children": [
{ "cells": [{ "value": "Question" }, { "value": "Answer" }] },
{ "cells": [{ "value": "Do you agree?" }, { "value": null }] }
]
}
]
}
{
name: "Sheet1",
dataValidations: [
{
type: "list",
sqref: "B2:B5",
formula1: '"Yes,No,Maybe"',
},
],
children: [
{ cells: [{ value: "Question" }, { value: "Answer" }] },
{ cells: [{ value: "Do you agree?" }, { value: null }] },
],
}
数字验证
{
"worksheets": [
{
"name": "Sheet1",
"dataValidations": [
{
"sqref": "A2:A100",
"type": "whole",
"operator": "between",
"formula1": "0",
"formula2": "100",
"showErrorMessage": true,
"errorTitle": "Invalid",
"error": "Must be between 0 and 100"
}
],
"children": [{ "cells": [{ "value": "Score" }] }, { "cells": [{ "value": 85 }] }]
}
]
}
dataValidations: [
{
type: "whole",
operator: "between",
sqref: "A2:A100",
formula1: "0",
formula2: "100",
showErrorMessage: true,
errorTitle: "Invalid",
error: "Must be between 0 and 100",
},
],
验证类型
| 类型 | type 值 | 说明 |
|---|---|---|
| 列表 | "list" | 值的下拉列表 |
| 整数 | "whole" | 整数验证 |
| 小数 | "decimal" | 小数验证 |
| 日期 | "date" | 日期验证 |
| 时间 | "time" | 时间验证 |
| 文本长度 | "textLength" | 文本长度验证 |
| 自定义 | "custom" | 自定义公式验证 |
运算符可选值：between、notBetween、equal、notEqual、greaterThan、lessThan、greaterThanOrEqual、lessThanOrEqual。
条件格式
基于单元格值应用格式规则：
{
"worksheets": [
{
"name": "Sheet1",
"conditionalFormats": [
{
"sqref": "B2:B5",
"rules": [{ "type": "cellIs", "operator": "greaterThan", "formulas": ["100"] }]
}
],
"children": [
{ "cells": [{ "value": "Name" }, { "value": "Score" }] },
{ "cells": [{ "value": "Alice" }, { "value": 150 }] },
{ "cells": [{ "value": "Bob" }, { "value": 42 }] }
]
}
]
}
conditionalFormats: [
{
sqref: "B2:B5",
rules: [
{ type: "cellIs", operator: "greaterThan", formulas: ["100"] },
],
},
],
数据验证选项参考
| 选项 | 类型 | 说明 |
|---|---|---|
sqref | string | 单元格区域，如 "A1:A10" |
type | string | 验证类型（见上表） |
operator | string | 比较运算符 |
formula1 | string | 第一个公式或值 |
formula2 | string | 第二个公式（用于 between） |
allowBlank | boolean | 允许空单元格 |
showErrorMessage | boolean | 显示错误提示 |
errorTitle | string | 错误对话框标题 |
error | string | 错误消息 |
showInputMessage | boolean | 显示输入提示 |
promptTitle | string | 输入提示标题 |
prompt | string | 输入提示文本 |