제목, 입력 메시지, 오류 메시지 등과 같은 유효성 검사기를 사용자 정의할 수 있습니다. 이러한 설정을 사용하면 셀에 입력할 수 있는 데이터와 입력해야 하는 데이터를 알 수 있으며 데이터가 잘못된 경우 메시지가 표시됩니다.
validator 개체를 사용할 경우 해당 속성을 설정할 수 있습니다. 예:
잘못된 데이터를 입력하면 컨트롤이 ValidationError 이벤트를 트리거합니다. 예:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<p>
Select a cell or range of cells in the sheet then use the options below to create a data validator for that cell(s). You can also set the input message that displays when the user tries to edit the cell(s) as well as the error message that shows if the input does not fit the conditions.
</p>
<div class="option-row">
<select id="validatorTypes" @change="changeValidatorTypes">
<option value="DateValidator" selected>DateValidator</option>
<option value="FormulaListValidator">FormulaListValidator</option>
<option value="FormulaValidator">FormulaValidator</option>
<option value="ListValidator">ListValidator</option>
<option value="NumberValidator">NumberValidator</option>
<option value="TextLengthValidator">TextLengthValidator</option>
</select>
</div>
<div id="validatorTab1">
<div class="option-row">
<select id="validatorComparisonOperator" @change="changeValidatorComparisonOperator">
<option value="6" selected>Between</option>
<option value="7">NotBetween</option>
<option value="0">EqualTo</option>
<option value="1">NotEqualTo</option>
<option value="2">GreaterThan</option>
<option value="4">LessThan</option>
<option value="3">GreaterThanOrEqualTo</option>
<option value="5">LessThanOrEqualTo</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">Is Integer</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">Show In-Cell DropDown</label>
</div>
<div class="option-row">
<input class="normal-input" type="checkbox" checked="checked" id="ckbShowInputMessage" />
<label for="ckbShowInputMessage">Show InputMessage</label>
</div>
<div class="option-row">
<label>Title:</label>
<input class="normal-input" type="text" id="txtInputTitle" placeholder="Title" />
</div>
<div class="option-row">
<label>Input Message:</label>
<input class="normal-input" type="text" id="txtInputMessage" placeholder="Input Message" />
</div>
<div class="option-row">
<input class="normal-input" id="chkShowError" type="checkbox" @change="changeShowError" />
<label for="chkShowError">ShowErrorMessage</label>
</div>
<div class="option-row">
<input class="normal-input" id="chkValidatorIgnoreBlank" type="checkbox" @change="changeValidatorIgnoreBlank" />
<label for="chkValidatorIgnoreBlank">IgnoreBlank</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>Stop</option>
<option value="1">Warning</option>
<option value="2">Information</option>
</select>
</div>
<div class="option-row">
<label>Custom HighlightStyle:</label>
<select id="highlightType" @change="changeHighlightType">
<option selected="selected" value="circle" data-bind="text: res.dataValidationDialog.circle">
Circle
</option>
<option value="dogear" data-bind="text: res.dataValidationDialog.dogear">
Dogear
</option>
<option value="icon" data-bind="text: res.dataValidationDialog.icon">
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>Dogear Position:</label>
<select id="dogearPositionOption">
<option value="Top Left" data-bind="text: res.dataValidationDialog.topLeft">
Top Left
</option>
<option value="Top Right" data-bind="text: res.dataValidationDialog.topRight">
Top Right
</option>
<option value="Bottom Right" data-bind="text: res.dataValidationDialog.bottomRight">
Bottom Right
</option>
<option value="Bottom Left" data-bind="text: res.dataValidationDialog.bottomLeft">
Bottom Left
</option>
</select>
</div>
<div id="icon-position" class="option-row">
<label>Icon Position:</label>
<select id="iconPositionOption">
<option value="Outside Left" data-bind="text: res.dataValidationDialog.outsideLeft">
Outside Left
</option>
<option value="Outside Right" data-bind="text: res.dataValidationDialog.outsideRight">
Outside Right
</option>
</select>
</div>
<div id="iconFile" class="option-row">
<input id="highlighticon" type="file" accept="image/*" @change="changeHighlighticon">
</div>
<div class="option-row">
<input class="normal-input" id="btnSetValidator" type="button" value="Set Validator" @click="setValidator" />
</div>
<div class="option-row">
<input class="normal-input" id="btnClearValidator" type="button" value="Clear Validator" @click="clearValidator" />
</div>
</div>
</div>
</template>
<script>
import Vue from "vue";
import '@mescius/spread-sheets-resources-ko';
GC.Spread.Common.CultureManager.culture("ko-kr");
import "@mescius/spread-sheets-vue";
import GC from "@mescius/spread-sheets";
import "./styles.css";
let App = Vue.extend({
name: "app",
data: function () {
return {
spread: null,
imageBase64: ""
};
},
methods: {
initSpread: function (spread) {
this.spread = spread;
spread.suspendPaint();
spread.options.highlightInvalidData = true;
_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';
let self = this;
spread.bind(GC.Spread.Sheets.Events.EnterCell, function (e, args) {
let sheet = spread.getActiveSheet();
let activeRow = sheet.getActiveRowIndex();
let activeCol = sheet.getActiveColumnIndex();
let dataValidator = sheet.getDataValidator(activeRow, activeCol);
let validatorTypesElement = _getElementById("validatorTypes"),
validatorTypes;
if (dataValidator) {
let type = dataValidator.type();
switch (type) {
case 1:
case 2:
validatorTypes = "NumberValidator";
break;
case 3:
let 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;
}
validatorTypesElement.value = validatorTypes;
self.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();
}
});
spread.resumePaint();
},
changeHighlighticon(e) {
let self = this;
let file = e.target.files[0];
let reader = new FileReader();
if (file) {
reader.readAsDataURL(file);
reader.onloadend = function (e) {
self.imageBase64 = this.result;
};
}
},
changeHighlightType(e) {
switch (e.target.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;
}
}
},
changeValidatorTypes(e) {
let validatorTypes = e.target.value;
this.updateValidatorTab(validatorTypes);
},
updateValidatorTab(validatorTypes) {
switch (validatorTypes) {
case "DateValidator":
case "TextLengthValidator":
_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;
}
},
changeValidatorComparisonOperator(e) {
let operatorType = e.target.value;
switch (operatorType) {
case '6':
case '7':
_getElementById("txtValidatorValue2").style.display = 'block';
break;
default:
_getElementById("txtValidatorValue2").style.display = 'none';
break;
}
},
setValidator(e) {
let self = this;
let gcdv = GC.Spread.Sheets.DataValidation;
let ddv = null;
let v1 = _getElementById("txtValidatorValue1").value;
let v2 = _getElementById("txtValidatorValue2").value;
let validatorTypes = _getElementById("validatorTypes").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);
let highLightStyle = _getElementById("highlightType").value;
let dogearPosition = _getElementById("dogearPositionOption").value;
let iconPosition = _getElementById("iconPositionOption").value;
let 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
});
}
let ss = this.spread;
let sheet = ss.getActiveSheet();
sheet.suspendPaint();
let sels = sheet.getSelections();
for (let i = 0; i < sels.length; i++) {
let sel = this.getActualRange(sheet, sels[i]);
sheet.setDataValidator(sel.row, sel.col, sel.rowCount, sel.colCount, ddv);
}
sheet.resumePaint();
}
},
changeValidatorIgnoreBlank(e) {
let ss = this.spread;
let sheet = ss.getActiveSheet();
let sels = sheet.getSelections();
for (let i = 0; i < sels.length; i++) {
let sel = this.getActualRange(sheet, sels[i]);
for (let r = 0; r < sel.rowCount; r++) {
for (let c = 0; c < sel.colCount; c++) {
let dv = sheet.getDataValidator(sel.row + r, sel.col + c);
if (dv) {
dv.ignoreBlank(e.target.checked);
}
}
}
}
},
getActualRange(sheet, range) {
let row = range.row,
rowCount = range.rowCount;
if (row === -1) {
row = 0;
rowCount = sheet.getRowCount();
}
let col = range.col,
colCount = range.colCount;
if (col === -1) {
col = 0;
colCount = sheet.getColumnCount();
}
return new GC.Spread.Sheets.Range(row, col, rowCount, colCount);
},
clearValidator(e) {
let sheet = this.spread.getActiveSheet();
let sels = sheet.getSelections();
for (let i = 0; i < sels.length; i++) {
let sel = this.getActualRange(sheet, sels[i]);
sheet.setDataValidator(sel.row, sel.col, sel.rowCount, sel.colCount, null);
}
},
changeShowError(e) {
let ss = this.spread;
let checked = e.target.checked;
if (checked) {
ss.bind(GC.Spread.Sheets.Events.ValidationError, function (event, data) {
let dv = data.validator;
if (dv) {
alert(dv.errorMessage());
}
});
} else {
ss.unbind(GC.Spread.Sheets.Events.ValidationError);
}
}
}
});
function _getElementById(id) {
return document.getElementById(id);
}
new Vue({
render: h => h(App)
}).$mount("#app");
</script>
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/ko/vue/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app.vue');
System.import('$DEMOROOT$/ko/lib/vue/license.js');
</script>
</head>
<body>
<div id="app"></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;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
meta: {
'*.css': { loader: 'css' },
'*.vue': { loader: 'vue-loader' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-ko': 'npm:@mescius/spread-sheets-resources-ko/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'jszip': 'npm:jszip/dist/jszip.js',
'css': 'npm:systemjs-plugin-css/css.js',
'vue': 'npm:vue/dist/vue.min.js',
'vue-loader': 'npm:systemjs-vue-browser/index.js',
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'js'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
}
}
});
})(this);
