SpreadJS V12 SP1 Data Validation

Data validation has been enhanced with our new SpreadJS Service Pack. Visual updates have been made to data validation to enhance SpreadJS customization abilities. In previous iterations, we only had one type of highlighting for data validation. Now, we've created a separate class that allows the addition of two new types: Dog Ear and Icon. With these icon types, developers can further customize the user experience by displaying specific images (depending on the data that they are showcasing).

Read the Full Release

We've also added the ability to use escape characters within validation lists, allowing for more detailed options that fit user needs more effectively, whether that be specially formatted strings that include commas, or formatted numbers. This article discusses data validation and gives a quick tutorial on how to use these new features.

Highlighting Enhancement

In older versions, data validation highlighting was restricted to the circle type, but we have separated it out to add other types of highlighting. Now, the three types of data validation highlighting styles that can be used are Circle, Dog ear, and Icon. For all of these to work of course, the highlightInvalidData option will need to be set to true:

spread.options.highlightInvalidData = true;

Keep in mind, these data validation types cannot be exported to Excel.

Data Validation Highlighting - Circle

This type of highlighting can be found in the DataValidation.HighlightType.circle enumeration, and only contains a type and color attribute:

var dv1 = new GC.Spread.Sheets.DataValidation.createListValidator('Fruit,Vegetable,Food');
dv1.highlightStyle({
    type: GC.Spread.Sheets.DataValidation.HighlightType.circle,
    color: 'red'
});
sheet.setDataValidator(1, 1, dv1);

Data Validation Highlighting - Dog Ear

This highlighting style is found in the DataValidation.HighlightType.dogEar enumeration, and contains attributes for type, color, and position, the latter of which can only be "topLeft," "topRight," "bottomRight," and "bottomLeft":

var dv1 = new GC.Spread.Sheets.DataValidation.createListValidator('Fruit,Vegetable,Food');
dv1.highlightStyle({
    type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
    color: 'red',
    position: GC.Spread.Sheets.DataValidation.HighlightPosition.topLeft
});
sheet.setDataValidator(1, 1, dv1);

Data Validation Highlighting - Icon

The last added highlighting style is found in the DataValidation.HighlightType.icon enumeration, and has four attributes: type, color, position, and image:

var imageData = 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAC5ElEQVRYR8WXPUxTURTH//8nbYE6YIKSCH70lcbEVOOgMTGokw7E2cVVcDQmCrzi0IFQICbGTcXVTRcDiXEiDBojmmifRCgtjYKDYsAQ0g+gx7xXadry0dfyQt/U5J57zu+c23vu/xAWvx8td+r+OuvbhXKdEJ+AjYQcEkEGxB8KFwB8g8hLt7hGPPFg0oprljKKtGoHU8J+EblBsq6UvbEuIgmSz12UgG8m9HunPdsCCII1uifdDYpG0m0lcLGNiKxAGPLPOgeJ4NpWPrYE+HK05wAdygiBC5UE3gQCvJXVzLXT3wcWi9c2AUyq930ZrL8BedyO4DkfInEF+66ejPVF8v0WAIQ9gSYo+Eig2dbgG85E4o6k69yJn0HjD2t+OYDPTXfditvxjuSpksEFSaG8L8hEeB5Ebam9IphoSCcuHZl7mCgA0FVtEGRXKQfmukjcHwt58m11VZu1fGyCQX+svycHMNXS27zqlBkrGdgEkHSl1ny++aE58wh0b2AYwE1L2dtRgWygZ/5ofwe/IujMqKklq03Glgr8b1ZKzNXASVVrz5CjlrO3rwLGDWhnWNUek7xVDQARecKwVxsjeLkaAICMUVcD0yB8VQEQmWZYDayQqK8KAGSZujewDGB/FQG0CMDWqgAIItRVbRzkxXIARLAE4FHRntskGsr0M17ZNSwnyg625jWsqBHZBGA2omwrTi+WcxPsOAJDN5qtOPsYaU8BdlhObLfPcfZBGfZHQ50mgKGESMT38jmuZfpYa/TBr5wi0r2BIQD3LFVhtxUQGfLHQt05QWL8MAaPJWfdOImzJSF2I8mATw2pRNsmSWYEnTocbFytTX2wLK1KkhYaCDDvTDjPbClKN0wNWb7O9dcE1TL972wuEgflij86MJNvuO1gotQor0C02QEh5QwmGwEn0OlweRq7QOktS67lEVc8muVnbV5RBX17PpwWl3678RxCEcpCbjwHX7gzjlGr4/k/MVGWHUHnf3sAAAAASUVORK5CYII='
var dv1 = new GC.Spread.Sheets.DataValidation.createListValidator('Fruit,Vegetable,Food');
dv1.highlightStyle({
    type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
    color: 'orange',
    position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideRight,
    image: imageData
});
sheet.setDataValidator(1, 1, dv1);

Comma Support in Validation Lists

Commas can now be used in validation lists that give users options to pick from. This can simply be used like an escape character when creating a list validator:

var dv1 = new GC.Spread.Sheets.DataValidation.createListValidator("123\\,456,234\\,567,789\\,564");

dv1.inputTitle("Please choose a number:");

dv1.inputMessage("Number of money");

sheet.setDataValidator(1,1, dv1);

Data validation is a useful tool for both users and developers, and the newest service pack for SpreadJS has enhanced customization offerings. This enhancement includes added highlighting types as well as comma support in validation lists.

Have a question or comment about the data validation tool? Please feel free to comment below.

Happy coding!