自定义数据验证

当你使用数据验证对象时，你还可以设置它的属性。例如：当你输入无效的数据时，数据验证模块将会触发 ValidationError 事件。例如：

var spreadNS = GC.Spread.Sheets;
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2});
initSpread(spread);
};
function initSpread(spread) {
var self = this;
var sheet = spread.getSheet(0);
spread.options.highlightInvalidData = true;
var validatorTypes = "DateValidator";
_getElementById("validatorTab2").style.display = 'none';
_getElementById("validatorTab3").style.display = 'none';
_getElementById("isIntTab").style.display = 'none';
_getElementById("dogear-position").style.display = 'none';
_getElementById("icon-position").style.display = 'none';
_getElementById("iconFile").style.display = 'none';
var updateValidatorTab = function (validatorTypes) {
switch (validatorTypes) {
case "DateValidator":
_getElementById("validatorTab1").style.display = 'block';
_getElementById("isIntTab").style.display = 'none';
_getElementById("validatorTab2").style.display = 'none';
_getElementById("validatorTab3").style.display = 'none';
break;
case "ListValidator":
_getElementById("validatorTab3").style.display = 'block';
_getElementById("validatorTab2").style.display = 'none';
_getElementById("validatorTab1").style.display = 'none';
break;
case "FormulaListValidator":
case "FormulaValidator":
_getElementById("validatorTab2").style.display = 'block';
_getElementById("validatorTab1").style.display = 'none';
_getElementById("validatorTab3").style.display = 'none';
break;
case "NumberValidator":
_getElementById("validatorTab1").style.display = 'block';
_getElementById("isIntTab").style.display = 'block';
_getElementById("validatorTab2").style.display = 'none';
_getElementById("validatorTab3").style.display = 'none';
break;
case "TextLengthValidator":
_getElementById("validatorTab1").style.display = 'block';
_getElementById("isIntTab").style.display = 'none';
_getElementById("validatorTab2").style.display = 'none';
_getElementById("validatorTab3").style.display = 'none';
break;
}
};
_getElementById("highlighticon").addEventListener("change", function () {
var file = this.files[0];
var reader = new FileReader();
if(file){
reader.readAsDataURL(file);
reader.onloadend = function (e) {
self.imageBase64 = this.result;
};
}
});
_getElementById("highlightType").addEventListener('change',function () {
switch(this.value){
case 'circle':{
_getElementById("dogear-position").style.display = 'none';
_getElementById("icon-position").style.display = 'none';
_getElementById("iconFile").style.display = 'none';
break;
}
case 'dogear':{
_getElementById("dogear-position").style.display = '';
_getElementById("icon-position").style.display = 'none';
_getElementById("iconFile").style.display = 'none';
break;
}
case 'icon':{
_getElementById("dogear-position").style.display = 'none';
_getElementById("icon-position").style.display = '';
_getElementById("iconFile").style.display = '';
break;
}
}
});
_getElementById("validatorTypes").addEventListener('change',function () {
validatorTypes = this.value;
updateValidatorTab(validatorTypes);
});
_getElementById("validatorComparisonOperator").addEventListener('change',function () {
var operatorType = this.value;
switch (operatorType) {
case '6':
case '7':
_getElementById("txtValidatorValue2").style.display = 'block';
break;
default:
_getElementById("txtValidatorValue2").style.display = 'none';
break;
}
});
_getElementById("btnSetValidator").addEventListener('click',function () {
var gcdv = spreadNS.DataValidation;
var ddv = null;
var v1 = _getElementById("txtValidatorValue1").value;
var v2 = _getElementById("txtValidatorValue2").value;
switch (validatorTypes) {
case "DateValidator":
ddv = gcdv.createDateValidator(parseInt(_getElementById("validatorComparisonOperator").value), new Date(v1), new Date(v2));
break;
case "FormulaListValidator":
ddv = gcdv.createFormulaListValidator(_getElementById("txtValidatorValue").value);
break;
case "FormulaValidator":
ddv = gcdv.createFormulaValidator(_getElementById("txtValidatorValue").value);
break;
case "ListValidator":
ddv = gcdv.createListValidator(_getElementById("txtListValidatorValue").value);
ddv.inCellDropdown(_getElementById("ckbIncellDropDown").checked);
break;
case "NumberValidator":
if (_getElementById("chkIsInteger").checked) {
ddv = gcdv.createNumberValidator(parseInt(_getElementById("validatorComparisonOperator").value),
isNaN(v1) ? v1 : parseInt(v1),
isNaN(v2) ? v2 : parseInt(v2),
true);
} else {
ddv = gcdv.createNumberValidator(parseInt(_getElementById("validatorComparisonOperator").value),
isNaN(v1) ? v1 : parseFloat(v1),
isNaN(v2) ? v2 : parseFloat(v2),
false);
}
break;
case "TextLengthValidator":
ddv = gcdv.createTextLengthValidator(parseInt(_getElementById("validatorComparisonOperator").value),
isNaN(v1) ? v1 : parseInt(v1),
isNaN(v2) ? v2 : parseInt(v2));
break;
}
if (ddv != null) {
ddv.errorMessage(_getElementById("txtErrorMessage").value);
ddv.errorStyle(parseInt(_getElementById("validatorErrorStyles").value));
ddv.errorTitle(_getElementById("txtErrorTitle").value);
ddv.showErrorMessage(_getElementById("chkShowError").checked);
ddv.ignoreBlank(_getElementById("chkValidatorIgnoreBlank").checked);
ddv.showInputMessage(_getElementById("ckbShowInputMessage").checked);
ddv.inputTitle(_getElementById("txtInputTitle").value);
ddv.inputMessage(_getElementById("txtInputMessage").value);
var highLightStyle = _getElementById("highlightType").value;
var dogearPosition = _getElementById("dogearPositionOption").value;
var iconPosition = _getElementById("iconPositionOption").value;
var highlightStyleColor = _getElementById("highlightColor").value;
if (highLightStyle === "circle") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.circle,
color: highlightStyleColor
});
} else if (highLightStyle === "dogear" && dogearPosition === "Top Left") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: highlightStyleColor,
position: GC.Spread.Sheets.DataValidation.HighlightPosition.topLeft
});
} else if (highLightStyle === "dogear" && dogearPosition === "Top Right") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: highlightStyleColor,
position: GC.Spread.Sheets.DataValidation.HighlightPosition.topRight
});
} else if (highLightStyle === "dogear" && dogearPosition === "Bottom Left") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: highlightStyleColor,
position: GC.Spread.Sheets.DataValidation.HighlightPosition.bottomLeft
});
} else if (highLightStyle === "dogear" && dogearPosition === "Bottom Right") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: highlightStyleColor,
position: GC.Spread.Sheets.DataValidation.HighlightPosition.bottomRight
});
} else if (highLightStyle === "icon" && iconPosition === "Outside Left") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: highlightStyleColor,
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideLeft,
image: self.imageBase64
});
} else if (highLightStyle === "icon" && iconPosition === "Outside Right") {
ddv.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: highlightStyleColor,
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideRight,
image: self.imageBase64
});
}
var ss = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = ss.getActiveSheet();
sheet.suspendPaint();
var sels = sheet.getSelections();
for (var i = 0; i < sels.length; i++) {
var sel = getActualRange(sheet, sels[i]);
sheet.setDataValidator(sel.row, sel.col, sel.rowCount, sel.colCount, ddv);
}
sheet.resumePaint();
}
});
_getElementById("chkValidatorIgnoreBlank").addEventListener('change',function () {
var ss = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = ss.getActiveSheet();
var sels = sheet.getSelections();
for (var i = 0; i < sels.length; i++) {
var sel = getActualRange(sheet, sels[i]);
for (var r = 0; r < sel.rowCount; r++) {
for (var c = 0; c < sel.colCount; c++) {
var dv = sheet.getDataValidator(sel.row + r, sel.col + c);
if (dv) {
dv.ignoreBlank(this.checked);
}
}
}
}
});
_getElementById("chkShowError").addEventListener('change',function () {
var ss = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = ss.getActiveSheet();
var checked = _getElementById("chkShowError").checked;
if (checked) {
ss.bind(spreadNS.Events.ValidationError, function (event, data) {
var dv = data.validator;
if (dv) {
alert(dv.errorMessage());
}
});
} else {
ss.unbind(spreadNS.Events.ValidationError);
}
});
_getElementById("btnClearValidator").addEventListener('click',function () {
var sheet = spread.getActiveSheet();
var sels = sheet.getSelections();
for (var i = 0; i < sels.length; i++) {
var sel = getActualRange(sheet, sels[i]);
sheet.setDataValidator(sel.row, sel.col, sel.rowCount, sel.colCount, null);
}
});
spread.bind(GC.Spread.Sheets.Events.EnterCell, function (e, args) {
var sheet = spread.getActiveSheet();
var activeRow = sheet.getActiveRowIndex();
var activeCol = sheet.getActiveColumnIndex();
var dataValidator = sheet.getDataValidator(activeRow, activeCol);
var validatorTypes = _getElementById("validatorTypes");
if (dataValidator) {
var type = dataValidator.type();
switch (type) {
case 1:
case 2:
validatorTypes = "NumberValidator";
break;
case 3:
var condition = dataValidator.condition();
if (condition && condition.formula()) {
validatorTypes = "FormulaListValidator";
} else {
validatorTypes = "ListValidator";
}
break;
case 4:
validatorTypes = "DateValidator";
break;
case 6:
validatorTypes = "TextLengthValidator";
break;
case 7:
validatorTypes = "FormulaValidator";
break;
}
validatorTypes.value = validatorTypes;
updateValidatorTab(validatorTypes);
_getElementById("validatorComparisonOperator").value = dataValidator.comparisonOperator();
_getElementById("txtValidatorValue1").value = dataValidator.value1();
_getElementById("txtValidatorValue2").value = dataValidator.value2();
_getElementById("txtValidatorValue").value = dataValidator.value1();
_getElementById("txtListValidatorValue").value = dataValidator.value1();
_getElementById("chkIsInteger").setAttribute('checked', type === 1);
_getElementById("ckbShowInputMessage").setAttribute('checked', dataValidator.showInputMessage());
_getElementById("txtInputTitle").value = dataValidator.inputTitle();
_getElementById("txtInputMessage").value = dataValidator.inputMessage();
_getElementById("chkShowError").setAttribute('checked', dataValidator.showErrorMessage());
_getElementById("chkValidatorIgnoreBlank").setAttribute('checked', dataValidator.ignoreBlank());
_getElementById("txtErrorTitle").value = dataValidator.errorTitle();
_getElementById("txtErrorMessage").value = dataValidator.errorMessage();
_getElementById("validatorErrorStyles").value = dataValidator.errorStyle();
}
});
}
function getActualRange(sheet, range) {
var row = range.row, rowCount = range.rowCount;
if (row === -1) {
row = 0;
rowCount = sheet.getRowCount();
}
var col = range.col, colCount = range.colCount;
if (col === -1) {
col = 0;
colCount = sheet.getColumnCount();
}
return new GC.Spread.Sheets.Range(row, col, rowCount, colCount);
}
function _getElementById(id){
return document.getElementById(id);
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p>
在工作表中选择一个单元格或单元格区域，然后使用以下选项为该单元格（或单元格区域）创建数据验证器。您还可以设置用户尝试编辑单元格（或单元格区域）时显示的输入消息，以及输入不符合条件时显示的错误消息。
</p>
<div class="option-row">
<select id="validatorTypes">
<option value="DateValidator" selected>日期验证器</option>
<option value="FormulaListValidator">公式列表验证器</option>
<option value="FormulaValidator">公式验证器</option>
<option value="ListValidator">列表验证器</option>
<option value="NumberValidator">数字验证器</option>
<option value="TextLengthValidator">文本长度验证器</option>
</select>
</div>
<div id="validatorTab1">
<div class="option-row">
<select id="validatorComparisonOperator">
<option value="6" selected>介于</option>
<option value="7">不介于</option>
<option value="0">等于</option>
<option value="1">不等于</option>
<option value="2">大于</option>
<option value="4">小于</option>
<option value="3">大于或等于</option>
<option value="5">小于或等于</option>
</select>
</div>
<div class="option-row">
<input class="normal-input" id="txtValidatorValue1" type="text" placeholder="值1" />
</div>
<div class="option-row">
<input class="normal-input" id="txtValidatorValue2" type="text" placeholder="值2" />
</div>
<div class="option-row" id="isIntTab">
<input class="normal-input" id="chkIsInteger" type="checkbox" /><label for="chkIsInteger">是否为整数</label>
</div>
</div>
<div class="option-row" id="validatorTab2">
<input class="normal-input" type="text" id="txtValidatorValue" placeholder="值1" />
</div>
<div class="option-row" id="validatorTab3">
<input class="normal-input" type="text" id="txtListValidatorValue" placeholder="(例如:1,2,3,4,5)" />
<input class="normal-input" type="checkbox" id="ckbIncellDropDown" checked="checked" />
<label for="ckbIncellDropDown">显示单元格内下拉列表</label>
</div>
<div class="option-row">
<input class="normal-input" type="checkbox" checked="checked" id="ckbShowInputMessage" />
<label for="ckbShowInputMessage">显示输入消息</label>
</div>
<div class="option-row">
<label>标题:</label>
<input class="normal-input" type="text" id="txtInputTitle" placeholder="标题" />
</div>
<div class="option-row">
<label>输入消息:</label>
<input class="normal-input" type="text" id="txtInputMessage" placeholder="输入消息" />
</div>
<div class="option-row">
<input class="normal-input" id="chkShowError" type="checkbox" />
<label for="chkShowError">显示错误消息</label>
</div>
<div class="option-row">
<input class="normal-input" id="chkValidatorIgnoreBlank" type="checkbox" />
<label for="chkValidatorIgnoreBlank">忽略空白</label>
</div>
<div class="option-row">
<input class="normal-input" id="txtErrorTitle" type="text" placeholder="错误标题" />
</div>
<div class="option-row">
<input class="normal-input" id="txtErrorMessage" type="text" placeholder="错误消息" />
</div>
<div class="option-row">
<select id="validatorErrorStyles">
<option value="0" selected>停止</option>
<option value="1">警告</option>
<option value="2">信息</option>
</select>
</div>
<div class="option-row">
<label>自定义高亮样式:</label>
<select id="highlightType">
<option selected="selected" value="circle" data-bind="text: res.dataValidationDialog.circle">
圆圈
</option>
<option value="dogear" data-bind="text: res.dataValidationDialog.dogear">
折角
</option>
<option value="icon" data-bind="text: res.dataValidationDialog.icon">
图标
</option>
</select>
</div>
<div class="option-row">
<input class="normal-input" id="highlightColor" type="text" placeholder="高亮颜色" />
</div>
<div id="dogear-position" class="option-row">
<label>折角位置:</label>
<select id="dogearPositionOption">
<option value="Top Left" data-bind="text: res.dataValidationDialog.topLeft">
左上
</option>
<option value="Top Right" data-bind="text: res.dataValidationDialog.topRight">
右上
</option>
<option value="Bottom Right" data-bind="text: res.dataValidationDialog.bottomRight">
右下
</option>
<option value="Bottom Left" data-bind="text: res.dataValidationDialog.bottomLeft">
左下
</option>
</select>
</div>
<div id="icon-position" class="option-row">
<label>图标位置:</label>
<select id="iconPositionOption">
<option value="Outside Left" data-bind="text: res.dataValidationDialog.outsideLeft">
外部左侧
</option>
<option value="Outside Right" data-bind="text: res.dataValidationDialog.outsideRight">
外部右侧
</option>
</select>
</div>
<div id="iconFile" class="option-row">
<input id="highlighticon" type="file" accept="image/*">
</div>
<div class="option-row">
<input class="normal-input" id="btnSetValidator" type="button" value="设置验证器" />
</div>
<div class="option-row">
<input class="normal-input" id="btnClearValidator" type="button" value="清除验证器" />
</div>
</div>
</div>
</body>
</html>

.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 5px;
}
.normal-input {
display: block;
padding: 4px 6px;
box-sizing: border-box;
width: 100%;
}
select {
display: block;
padding: 4px 6px;
box-sizing: border-box;
width: 100%;
}
input[type = checkbox] {
display: inline-block;
width: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}

SpreadJS 在线示例