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.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import React, { useState, useEffect } from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
function _getElementById(id) {
return document.getElementById(id);
}
export function AppFunc() {
const [highlightInvalidData, sethighlightInvalidData] = useState(true);
const [spread, setspread] = useState(null);
let initSpread = function(value) {
setspread(value);
value.suspendPaint();
loadData(value);
setValidator(value);
value.resumePaint();
}
let loadData = function(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.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);
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");
}
let setValidator = function(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);
}
let changeHighlightInvalidData = function(e) {
let highlightInvalidData = e.target.checked;
spread.options.highlightInvalidData = highlightInvalidData;
sethighlightInvalidData(highlightInvalidData);
}
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<div className="options-container">
<div className="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={highlightInvalidData} onChange={e => changeHighlightInvalidData(e)} />
<label htmlFor="highlightInvalidData">Highlight invalid data</label>
</div>
</div>
</div>);
}
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
function _getElementById(id) {
return document.getElementById(id);
}
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.state = {highlightInvalidData: true};
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<div className="options-container">
<div className="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={this.state.highlightInvalidData} onChange={e => this.changeHighlightInvalidData(e)} />
<label htmlFor="highlightInvalidData">Highlight invalid data</label>
</div>
</div>
</div>;
}
initSpread(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.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);
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) {
let highlightInvalidData = e.target.checked;
this.spread.options.highlightInvalidData = highlightInvalidData;
this.setState({highlightInvalidData: highlightInvalidData});
}
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</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;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
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-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.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: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
