Overview

Data validation is a SpreadJS feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. This can be particularly useful if you have a form connected to a database that you want to allow users to enter data in, but you want to restrict the input to fit the restrictions in your database.

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; }