カスタム検証 | 機能紹介 | SpreadJS（スプレッドJS）チュートリアルデモアプリケーション

データ検証機能は、タイトル、入力メッセージ、エラーメッセージなど、さまざまな要素をカスタマイズできます。無効なデータを示す外観を変更することもできます。このような設定により、各セルにどのようなデータを入力すべきなのかが分かります。また、入力したデータが無効な場合にメッセージを表示させることもできます。データ検証機能オブジェクトを使用する場合は、さまざまなプロパティを設定できます。次に、例を示します。無効なデータが入力された場合、コントロールは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="ja-jp" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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="Value1"/>
</div>
<div class="option-row">
<input class="normal-input" id="txtValidatorValue2" type="text" placeholder="Value2"/>
</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="Value1" />
</div>
<div class="option-row" id="validatorTab3">
<input class="normal-input" type="text" id="txtListValidatorValue" placeholder="(eg: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="ErrorTitle"/>
</div>
<div class="option-row">
<input class="normal-input" id="txtErrorMessage" type="text" placeholder="ErrorMessage"/>
</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="HighlightColor"/>
</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;
}