To add a data validator to a sheet, create a validator and then add it to the sheet. For example:
You can create six kinds of validators, as follows.
createNumberValidator: Creates a validator based on numbers.
createDateValidator: Creates a validator based on the data.
createTextLengthValidator: Creates a validator based on text length.
createFormulaValidator: Creates a validator based on a formula.
createFormulaListValidator: Creates a validator based on a formula list.
createListValidator: Creates a validator based on a list.
After you set a validator to a cell, you can use the getDataValidator method to get the cell data validator. And you can use the isValid method to determine whether the cell value is valid. For example:
You can use the highlightInvalidData option to get and set whether to highlight invalid data. If you set it to true, then if your value is not valid there will be a red circle on the cell.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<div class="option-row">
<p>Try clicking on the checkbox for ‘Highlight invalid data’ to see how you can highlight data in Spread that isn’t valid in different ways.</p>
<input type="checkbox" id="highlightInvalidData" checked="checked" @change="changeHighlightInvalidData" />
<label for="highlightInvalidData">Highlight invalid data</label>
</div>
</div>
</div>
</template>
<script>
import Vue from "vue";
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
};
},
methods: {
initSpread: function (spread) {
this.spread = spread;
spread.suspendPaint();
this.loadData(spread);
this.setValidator(spread);
spread.resumePaint();
},
loadData(spread) {
let spreadNS = GC.Spread.Sheets;
let sheet = spread.getActiveSheet();
sheet.setRowHeight(3, 40);
sheet.setValue(3, 0, "Shopping Place");
let title = sheet.getCell(3, 0);
title.font("bold 20px arial");
title.vAlign(spreadNS.VerticalAlign.center);
title.backColor("#D1CBC5");
sheet.setColumnWidth(0, 160);
sheet.setColumnWidth(1, 35);
sheet
.getRange(3, 0, 3, 1)
.setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), {
all: true
});
sheet.setValue(4, 0, "Food Shop");
sheet.setValue(5, 0, "Other");
sheet.getCell(4, 0).font("bold 15px arial");
sheet.getCell(5, 0).font("bold 15px arial");
let startRow = 3;
let startCol = 1;
sheet.addSpan(startRow + 0, startCol + 0, 1, 4);
sheet.setRowHeight(startRow + 0, 40);
sheet.setValue(startRow + 0, startCol + 0, "Goods List");
title = sheet.getCell(startRow + 0, startCol + 0);
title.font("bold 30px arial");
title.vAlign(spreadNS.VerticalAlign.center);
title.backColor("#D1CBC5");
sheet.setColumnWidth(startCol + 0, 100);
sheet.setColumnWidth(startCol + 1, 100);
sheet.setColumnWidth(startCol + 2, 100);
sheet.setColumnWidth(startCol + 3, 120);
sheet
.getRange(startRow + 0, startCol + 0, 8, 4)
.setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), {
all: true
});
sheet.setValue(startRow + 1, startCol + 0, "Name");
sheet.setValue(startRow + 1, startCol + 1, "Category");
sheet.setValue(startRow + 1, startCol + 2, "Price");
sheet.setValue(startRow + 1, startCol + 3, "Shopping Place");
for (let i = 0; i < 4; i++) {
sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial");
}
sheet.setValue(startRow + 2, startCol + 0, "Apple");
sheet.setValue(startRow + 3, startCol + 0, "Potato");
sheet.setValue(startRow + 4, startCol + 0, "Tomato");
sheet.setValue(startRow + 5, startCol + 0, "Sandwich");
sheet.setValue(startRow + 6, startCol + 0, "Hamburger");
sheet.setValue(startRow + 7, startCol + 0, "Grape");
sheet.setValue(startRow + 2, startCol + 1, "Fruit");
sheet.setValue(startRow + 3, startCol + 1, "Vegetable");
sheet.setValue(startRow + 4, startCol + 1, "Vegetable");
sheet.setValue(startRow + 5, startCol + 1, "Food");
sheet.setValue(startRow + 6, startCol + 1, "Food");
sheet.setValue(startRow + 7, startCol + 1, "Fruit");
sheet.setValue(startRow + 2, startCol + 2, 1.0);
sheet.setValue(startRow + 3, startCol + 2, 2.01);
sheet.setValue(startRow + 4, startCol + 2, 3.21);
sheet.setValue(startRow + 5, startCol + 2, 2);
sheet.setValue(startRow + 6, startCol + 2, 2);
sheet.setValue(startRow + 7, startCol + 2, 4);
let myFormatter = new GC.Spread.Formatter.GeneralFormatter(
"$#,##0.00;[Red] $#,##0.00"
);
for (let i = 2; i < 8; i++) {
sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter);
}
sheet.setValue(startRow + 2, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 3, startCol + 3, "Other");
sheet.setValue(startRow + 4, startCol + 3, "Other");
sheet.setValue(startRow + 5, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 6, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 7, startCol + 3, "Other");
//set invalid data
sheet.setValue(6, 2, "sss");
sheet.setValue(10, 4, "Sun Store");
},
setValidator(spread) {
let spreadNS = GC.Spread.Sheets;
let sheet = spread.getActiveSheet();
spread.options.highlightInvalidData = true;
//ListValidator
let dv1 = new spreadNS.DataValidation.createListValidator(
"Fruit,Vegetable,Food"
);
dv1.inputTitle("Please choose a category:");
dv1.inputMessage("Fruit, Vegetable, Food");
dv1.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: "gold",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideRight
});
for (let i = 5; i < 11; i++) {
sheet.setDataValidator(i, 2, dv1);
}
//FormulaListValidator
let dv2 = new spreadNS.DataValidation.createFormulaListValidator(
"$A$5:$A$6"
);
for (let i = 5; i < 11; i++) {
sheet.setDataValidator(i, 4, dv2);
}
sheet.setValue(14, 0, "ValidationList Comma Support");
//Validation List Support Comma
sheet.setValue(14, 2, "Amount of money");
let dv3 = new GC.Spread.Sheets.DataValidation.createListValidator(
"123\\,456,234\\,567,789\\,564"
);
dv3.inputTitle("Please choose a number:");
dv3.inputMessage("Amount of money");
dv3.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: "green",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.topRight
});
sheet.setDataValidator(14, 2, dv3);
sheet.setValue(14, 4, "Calculation operators");
let dv4 = new GC.Spread.Sheets.DataValidation.createListValidator(
"\\,,>,<,*,/"
);
dv4.inputTitle("Please choose a operator:");
dv4.inputMessage("Calculation operators");
dv4.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: "yellow",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideLeft,
image: "$DEMOROOT$/spread/source/images/apple.jpg"
});
sheet.setDataValidator(14, 4, dv4);
},
changeHighlightInvalidData(e) {
this.spread.options.highlightInvalidData = e.target.checked;
}
}
});
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$/en/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app.vue');
System.import('$DEMOROOT$/en/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: 4px;
margin-top: 4px;
}
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-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);
