Demos
Features
Cells
Data Validation
Custom Validation

Custom Validation

With SpreadJS you can use custom data validation in a worksheet.

JavaScript
Angular
Vue3
React
Description
app.jsx
app-func.jsx
app-class.jsx
index.html
styles.css
Copy to CodeMine

You can customize the validator’s title, input message, error message, and so on. With these settings you will know what data can or should be entered in a cell, and when the data is invalid you will get a message.

When you use the validator object, you can set its properties. For example:

    var dv1 = sheet.getDataValidator(1, 1);
    dv1.inputTitle('Please choose a category:');
    dv1.inputMessage('Fruit, Vegetable, Food');
    dv1.showInputMessage(true); //whether to display the input title and input message.
    dv1.showErrorMessage(true); //whether to display an error message.
    dv1.inCellDropdown(true); //whether to display a drop-down button.
    dv1.ignoreBlank(false); //whether to ignore an empty value.
    dv1.errorStyle(GC.Spread.Sheets.DataValidation.ErrorStyle.stop);

When you input invalid data, the control will trigger the ValidationError event. For example:

    sheet.bind(GC.Spread.Sheets.Events.ValidationError, function(e, args) {
       // do some thing.
    });
You can customize the validator’s title, input message, error message, and so on. With these settings you will know what data can or should be entered in a cell, and when the data is invalid you will get a message. When you use the validator object, you can set its properties. For example: When you input invalid data, the control will trigger the ValidationError event. For example:
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 * as React from 'react'; import GC from '@mescius/spread-sheets'; import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react'; function _getElementById(id) { return document.getElementById(id); } export function AppFunc() { let spread = null; let imageBase64 = ""; const initSpread = (currSpread) => { spread = currSpread; 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'; 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; 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(); } const changeHighlighticon = (e) => { let file = e.target.files[0]; let reader = new FileReader(); if (file) { reader.readAsDataURL(file); reader.onloadend = function (e) { imageBase64 = this.result; }; } } const 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; } } } const changeValidatorTypes = (e) => { let validatorTypes = e.target.value; updateValidatorTab(validatorTypes); } const 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; } } const 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; } } const setValidator = (e) => { 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: 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: imageBase64 }); } let sheet = spread.getActiveSheet(); sheet.suspendPaint(); let sels = sheet.getSelections(); for (let i = 0; i < sels.length; i++) { let sel = getActualRange(sheet, sels[i]); sheet.setDataValidator(sel.row, sel.col, sel.rowCount, sel.colCount, ddv); } sheet.resumePaint(); } } const changeValidatorIgnoreBlank = (e) => { let ss = spread; let sheet = ss.getActiveSheet(); let sels = sheet.getSelections(); for (let i = 0; i < sels.length; i++) { let sel = 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); } } } } } const 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); } const clearValidator = (e) => { let sheet = spread.getActiveSheet(); let sels = sheet.getSelections(); for (let i = 0; i < sels.length; i++) { let sel = getActualRange(sheet, sels[i]); sheet.setDataValidator(sel.row, sel.col, sel.rowCount, sel.colCount, null); } } const changeShowError = (e) => { let ss = 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); } } return <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={initSpread}> <Worksheet> </Worksheet> </SpreadSheets> </div> <div className="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 className="option-row"> <select id="validatorTypes" onChange={e => changeValidatorTypes(e)}> <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 className="option-row"> <select id="validatorComparisonOperator" onChange={e => changeValidatorComparisonOperator(e)}> <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 className="option-row"> <input className="normal-input" id="txtValidatorValue1" type="text" placeholder="Value1" /> </div> <div className="option-row"> <input className="normal-input" id="txtValidatorValue2" type="text" placeholder="Value2" /> </div> <div className="option-row" id="isIntTab"> <input className="normal-input" id="chkIsInteger" type="checkbox" /><label htmlFor="chkIsInteger">Is Integer</label> </div> </div> <div className="option-row" id="validatorTab2"> <input className="normal-input" type="text" id="txtValidatorValue" placeholder="Value1" /> </div> <div className="option-row" id="validatorTab3"> <input className="normal-input" type="text" id="txtListValidatorValue" placeholder="(eg:1,2,3,4,5)" /> <input className="normal-input" type="checkbox" id="ckbIncellDropDown" defaultChecked="checked" /> <label htmlFor="ckbIncellDropDown">Show In-Cell DropDown</label> </div> <div className="option-row"> <input className="normal-input" type="checkbox" defaultChecked="checked" id="ckbShowInputMessage" /> <label htmlFor="ckbShowInputMessage">Show InputMessage</label> </div> <div className="option-row"> <label>Title:</label> <input className="normal-input" type="text" id="txtInputTitle" placeholder="Title" /> </div> <div className="option-row"> <label>Input Message:</label> <input className="normal-input" type="text" id="txtInputMessage" placeholder="Input Message" /> </div> <div className="option-row"> <input className="normal-input" id="chkShowError" type="checkbox" onChange={e => changeShowError(e)} /> <label htmlFor="chkShowError">ShowErrorMessage</label> </div> <div className="option-row"> <input className="normal-input" id="chkValidatorIgnoreBlank" type="checkbox" onChange={e => changeValidatorIgnoreBlank(e)} /> <label htmlFor="chkValidatorIgnoreBlank">IgnoreBlank</label> </div> <div className="option-row"> <input className="normal-input" id="txtErrorTitle" type="text" placeholder="ErrorTitle" /> </div> <div className="option-row"> <input className="normal-input" id="txtErrorMessage" type="text" placeholder="ErrorMessage" /> </div> <div className="option-row"> <select id="validatorErrorStyles"> <option value={0} selected>Stop</option> <option value={1}>Warning</option> <option value={2}>Information</option> </select> </div> <div className="option-row"> <label>Custom HighlightStyle:</label> <select id="highlightType" onChange={e => changeHighlightType(e)}> <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 className="option-row"> <input className="normal-input" id="highlightColor" type="text" placeholder="HighlightColor" /> </div> <div id="dogear-position" className="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" className="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" className="option-row"> <input id="highlighticon" type="file" accept="image/*" onChange={e => changeHighlighticon(e)} /> </div> <div className="option-row"> <input className="normal-input" id="btnSetValidator" type="button" defaultValue="Set Validator" onClick={e=>setValidator(e)} /> </div> <div className="option-row"> <input className="normal-input" id="btnClearValidator" type="button" defaultValue="Clear Validator" onClick={e => clearValidator(e)} /> </div> </div> </div>; }
import * as React from 'react'; import GC from '@mescius/spread-sheets'; import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react'; const Component = React.Component; function _getElementById(id) { return document.getElementById(id); } export class App extends Component { constructor(props) { super(props); this.spread = null; this.imageBase64 = ""; } render() { return <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread => this.initSpread(spread)}> <Worksheet> </Worksheet> </SpreadSheets> </div> <div className="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 className="option-row"> <select id="validatorTypes" onChange={e => this.changeValidatorTypes(e)}> <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 className="option-row"> <select id="validatorComparisonOperator" onChange={e => this.changeValidatorComparisonOperator(e)}> <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 className="option-row"> <input className="normal-input" id="txtValidatorValue1" type="text" placeholder="Value1" /> </div> <div className="option-row"> <input className="normal-input" id="txtValidatorValue2" type="text" placeholder="Value2" /> </div> <div className="option-row" id="isIntTab"> <input className="normal-input" id="chkIsInteger" type="checkbox" /><label htmlFor="chkIsInteger">Is Integer</label> </div> </div> <div className="option-row" id="validatorTab2"> <input className="normal-input" type="text" id="txtValidatorValue" placeholder="Value1" /> </div> <div className="option-row" id="validatorTab3"> <input className="normal-input" type="text" id="txtListValidatorValue" placeholder="(eg:1,2,3,4,5)" /> <input className="normal-input" type="checkbox" id="ckbIncellDropDown" defaultChecked="checked" /> <label htmlFor="ckbIncellDropDown">Show In-Cell DropDown</label> </div> <div className="option-row"> <input className="normal-input" type="checkbox" defaultChecked="checked" id="ckbShowInputMessage" /> <label htmlFor="ckbShowInputMessage">Show InputMessage</label> </div> <div className="option-row"> <label>Title:</label> <input className="normal-input" type="text" id="txtInputTitle" placeholder="Title" /> </div> <div className="option-row"> <label>Input Message:</label> <input className="normal-input" type="text" id="txtInputMessage" placeholder="Input Message" /> </div> <div className="option-row"> <input className="normal-input" id="chkShowError" type="checkbox" onChange={e => this.changeShowError(e)} /> <label htmlFor="chkShowError">ShowErrorMessage</label> </div> <div className="option-row"> <input className="normal-input" id="chkValidatorIgnoreBlank" type="checkbox" onChange={e => this.changeValidatorIgnoreBlank(e)} /> <label htmlFor="chkValidatorIgnoreBlank">IgnoreBlank</label> </div> <div className="option-row"> <input className="normal-input" id="txtErrorTitle" type="text" placeholder="ErrorTitle" /> </div> <div className="option-row"> <input className="normal-input" id="txtErrorMessage" type="text" placeholder="ErrorMessage" /> </div> <div className="option-row"> <select id="validatorErrorStyles"> <option value={0} selected>Stop</option> <option value={1}>Warning</option> <option value={2}>Information</option> </select> </div> <div className="option-row"> <label>Custom HighlightStyle:</label> <select id="highlightType" onChange={e => this.changeHighlightType(e)}> <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 className="option-row"> <input className="normal-input" id="highlightColor" type="text" placeholder="HighlightColor" /> </div> <div id="dogear-position" className="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" className="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" className="option-row"> <input id="highlighticon" type="file" accept="image/*" onChange={e => this.changeHighlighticon(e)} /> </div> <div className="option-row"> <input className="normal-input" id="btnSetValidator" type="button" defaultValue="Set Validator" onClick={e=>this.setValidator(e)} /> </div> <div className="option-row"> <input className="normal-input" id="btnClearValidator" type="button" defaultValue="Clear Validator" onClick={e => this.clearValidator(e)} /> </div> </div> </div>; } initSpread(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); } } }
<!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: 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; } #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);