カスタム検証

SpreadJSでは、ワークシートでカスタムデータ検証を使用することが可能です。

<p>データ検証機能は、タイトル、入力メッセージ、エラーメッセージなど、さまざまな要素をカスタマイズできます。無効なデータを示す外観を変更することもできます。このような設定により、各セルにどのようなデータを入力すべきなのかが分かります。また、入力したデータが無効な場合にメッセージを表示させることもできます。</p> <p>データ検証機能オブジェクトを使用する場合は、さまざまなプロパティを設定できます。次に、例を示します。</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> dv1 = sheet.getDataValidator(<span class="hljs-number">1</span>, <span class="hljs-number">1</span>); dv1.inputTitle(<span class="hljs-string">'Please choose a category:'</span>); dv1.inputMessage(<span class="hljs-string">'Fruit, Vegetable, Food'</span>); dv1.showInputMessage(<span class="hljs-literal">true</span>); <span class="hljs-comment">// タイトル、入力メッセージの表示設定。</span> dv1.showErrorMessage(<span class="hljs-literal">true</span>); <span class="hljs-comment">// エラーメッセージの表示設定。</span> dv1.inCellDropdown(<span class="hljs-literal">true</span>); <span class="hljs-comment">// ドロップダウンボタンの表示設定。.</span> dv1.ignoreBlank(<span class="hljs-literal">false</span>); <span class="hljs-comment">// 空白値を無視するかどうか。</span> dv1.errorStyle(GC.Spread.Sheets.DataValidation.ErrorStyle.stop); </code></pre> <p>無効なデータが入力された場合、コントロールは<strong>ValidationError</strong>イベントをトリガします。次に、例を示します。</p> <pre><code class="hljs js language-js"> sheet.bind(GC.Spread.Sheets.Events.ValidationError, <span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">e, args</span>) </span>{ <span class="hljs-comment">// 処理を追加します。</span> }); </code></pre>
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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/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; }