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.
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 sheet = spread.getSheet(0);
sheet.suspendPaint();
loadData(spread);
setValidator(spread);
sheet.resumePaint();
document.getElementById("highlightInvalidData").checked = spread.options.highlightInvalidData;
document.getElementById("highlightInvalidData").addEventListener('click', function() {
spread.options.highlightInvalidData = document.getElementById("highlightInvalidData").checked;
});
};
function loadData(spread) {
var sheet = spread.getActiveSheet();
sheet.setRowHeight(3, 40);
sheet.setValue(3, 0, "Shopping Place");
var 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");
var startRow = 3;
var startCol = 1;
sheet.addSpan(startRow + 0, startCol + 0, 1, 4);
sheet.setRowHeight(startRow + 0, 40);
sheet.setValue(startRow + 0, startCol + 0, "Goods List");
var 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 (var 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.00);
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);
var myFormatter = new GC.Spread.Formatter.GeneralFormatter("$#,##0.00;[Red] $#,##0.00");
for (var 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");
}
function setValidator(spread) {
var sheet = spread.getActiveSheet();
spread.options.highlightInvalidData = true;
//ListValidator
var 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 (var i = 5; i < 11; i++) {
sheet.setDataValidator(i, 2, dv1);
}
//FormulaListValidator
var dv2 = new spreadNS.DataValidation.createFormulaListValidator("$A$5:$A$6")
for (var 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");
var 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");
var 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);
}
<!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/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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">
<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"/>
<label for="highlightInvalidData">Highlight invalid data</label>
</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: 4px;
margin-top: 4px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
