Skip to content

Instantly share code, notes, and snippets.

@optilude

optilude/plugins.js

Last active March 27, 2017 19:58
Show Gist options
  • Save optilude/5051129 to your computer and use it in GitHub Desktop.
Save optilude/5051129 to your computer and use it in GitHub Desktop.
Download ZIP
Simple validation framework for Handsontable
Raw
plugins.js
define([
'jquery',
'underscore',
'app/app',
'jquery.handsontable',
'moment',
'jquery.tools'
], function($, _, app, Handsontable, moment) {
function _validateCell(instance, row, prop, value) {
var col = instance.propToCol(prop);
var validator = instance.getCellMeta(row, col).validator;
return validator? validator(instance, row, prop, value) : true;
}
return {
/**
* Validators that can be set against a column under the 'validator'
* property, e.g.:
* foo.handsontable({
* ...
* columns: [{
* data: "name",
* validator: TablePlugins.validators.requiredText
* }]
* }
*/
validators: {
requiredText: function(instance, row, prop, value) {
return Boolean(value);
},
uniqueText: function(instance, row, prop, value) {
for(var i = 0; i < (instance.countRows() - instance.getSettings().minSpareRows); ++i) {
var otherValue = instance.getDataAtCell(i, instance.propToCol(prop));
if(i != row && value == otherValue) {
return false;
}
}
return true;
},
requiredUniqueText: function(instance, row, prop, value) {
if(!Boolean(value)) {
return false;
}
for(var i = 0; i < (instance.countRows() - instance.getSettings().minSpareRows); ++i) {
var otherValue = instance.getDataAtCell(i, instance.propToCol(prop));
if(i != row && value == otherValue) {
return false;
}
}
return true;
},
number: function(instance, row, prop, value) {
return _.isNull(value) || _.isFinite(value);
},
requiredNumber: function(instance, row, prop, value) {
return _.isFinite(value);
},
percentage: function(instance, row, prop, value) {
return _.isNull(value) || (_.isFinite(value) && value >= 0 && value <= 100);
},
requiredPercentage: function(instance, row, prop, value) {
return _.isFinite(value) && value >= 0 && value <= 100;
},
date: function(instance, row, prop, value) {
return _.isNull(value) || _.isDate(value);
},
requiredDate: function(instance, row, prop, value) {
return _.isDate(value);
},
// A list that will be managed as a ``DetailsListCell``.
// The column definition must have a property ``detailsColumns``
// that is a list like ``columns``, describing the columns of
// the child list.
detailsList: function(instance, row, prop, value) {
var col = instance.propToCol(prop),
cellMeta = instance.getCellMeta(row, col),
detailsColumns = cellMeta.detailsColumns;
if(!detailsColumns) {
console.error("Expected to find detailsColumns property for " + prop);
return true;
}
if(_.isNull(value))
return true;
var data = {};
function validateCell(col) {
var validator = col.validator;
// XXX: Almost, but not quite; instance is not available, so some validators won't work
return validator? validator(null, data, col.data, data[col.data]) : true;
}
for(var i = 0; i < value.length; ++i) {
data = value[i];
if(!_.every(detailsColumns, validateCell))
return false;
}
return true;
}
},
/**
* Validate a single cell, returning true or false
*/
validateCell: _validateCell,
/**
* Validate a change in a way that is suitable as an onBeforeChange
* handler, rejecting changes that would result in an invalid cell
*/
validateBeforeChange: function(data, source) {
var instance = $(this).data('handsontable');
_.each(data, function(change) {
var row = change[0],
prop = change[1],
oldValue = change[2],
newValue = change[3];
if(!_validateCell(instance, row, prop, newValue)) {
change[3] = oldValue;
}
});
},
/**
* Validate an entire table. Returns a promise. Success callbacks are
* called with the table instance as an argument. Failure callbacks are
* called with the instance and a list of [row, prop] pairs where there
* were validation errors.
*/
validateTable: function(instance) {
var dfd = $.Deferred(),
invalid = [],
data = instance.getData(),
spareRows = instance.getSettings().minSpareRows,
dataLength = data.length - spareRows;
_.each(data, function(element, index) {
if(index >= dataLength) return;
_.each(element, function(value, key) {
if(!_validateCell(instance, index, key, value)) {
invalid.push([index, key]);
}
});
});
if(invalid.length > 0) {
dfd.reject(instance, invalid);
} else {
dfd.resolve(instance);
}
return dfd.promise();
},
/**
* Cell types with inline validation
*/
// Plain text or a number
TextCell: {
renderer: function(instance, td, row, col, prop, value, cellProperties) {
Handsontable.TextCell.renderer.apply(this, arguments);
if(row < (instance.countRows() - instance.getSettings().minSpareRows)) {
td.className = _validateCell(instance, row, prop, value)? '': 'error';
}
},
editor: Handsontable.TextEditor
},
// A date, using a date picker
DateCell: {
renderer: function(instance, td, row, col, prop, value, cellProperties) {
Handsontable.TextCell.renderer.apply(this, arguments);
if(row < (instance.countRows() - instance.getSettings().minSpareRows)) {
td.className = _validateCell(instance, row, prop, value)? '': 'error';
if(value) {
var dateValue = moment(value);
td.innerHTML = dateValue.format(app.momentDateFormat);
}
}
},
editor: function (instance, td, row, col, prop, keyboardProxy, cellProperties) {
if(_.isUndefined(cellProperties))
cellProperties = {};
$(td).dateinput({
format: app.datePickerDateFormat,
firstDay: 1,
onShow: function() {
var tdPosition = $(td).offset();
$("#calroot").offset(tdPosition);
},
change: function() {
instance.setDataAtCell(row, prop, this.getValue());
}
});
keyboardProxy.on("keydown.editor", function(event) {
//catch CTRL but not right ALT (which in some systems triggers ALT+CTRL)
var ctrlDown = (event.ctrlKey || event.metaKey) && !event.altKey;
if(!ctrlDown && Handsontable.helper.isPrintableChar(event.keyCode)) {
$(td).data('dateinput').show();
event.stopPropagation();
}
});
instance.view.wt.update('onCellDblClick', function() {
$(td).data('dateinput').show();
});
return function() {
keyboardProxy.off(".editor");
instance.view.wt.update('onCellDblClick', null);
};
}
},
// A currency value, stored as a number, but rendered with a £ prefix
CurrencyCell: {
renderer: function(instance, td, row, col, prop, value, cellProperties) {
Handsontable.TextCell.renderer.apply(this, arguments);
if(!_.isNull(value) && value !== "") {
td.innerHTML = "£" + td.innerHTML;
}
if(row < (instance.countRows() - instance.getSettings().minSpareRows)) {
td.className = _validateCell(instance, row, prop, value)? '': 'error';
}
},
editor: Handsontable.TextEditor
},
// A percentage, stored as a number, but rendered with a % suffix
PercentageCell: {
renderer: function(instance, td, row, col, prop, value, cellProperties) {
Handsontable.TextCell.renderer.apply(this, arguments);
if(!_.isNull(value) && value !== "") {
td.innerHTML = td.innerHTML + "%";
}
if(row < (instance.countRows() - instance.getSettings().minSpareRows)) {
td.className = _validateCell(instance, row, prop, value)? '': 'error';
}
},
editor: Handsontable.TextEditor
},
// Autocomplete with validation
AutocompleteCell: {
renderer: function(instance, td, row, col, prop, value, cellProperties) {
Handsontable.AutocompleteCell.renderer.apply(this, arguments);
if(row < (instance.countRows() - instance.getSettings().minSpareRows)) {
td.className = _validateCell(instance, row, prop, value)? '': 'error';
}
},
editor: Handsontable.AutocompleteEditor
},
// A list of values. Renders the number of items in the list. On edit,
// calls a callback function with the list, which can be edited inplace.
// For example:
//
// columns: [
// {
// data: "allocations",
// type: TablePlugins.ChildListCell,
// showEditor: function(instance, td, row, col, prop, cellProperties, value) {
// ...
// }
// },
// ...
// ]
//
// If the current value is null, a new list will be created
DetailsListCell: {
renderer: function(instance, td, row, col, prop, value, cellProperties) {
td.innerHTML = value? value.length + "..." : "0...";
if(row < (instance.countRows() - instance.getSettings().minSpareRows)) {
td.className = _validateCell(instance, row, prop, value)? '': 'error';
}
},
editor: function (instance, td, row, col, prop, keyboardProxy, cellProperties) {
if(_.isUndefined(cellProperties))
cellProperties = {};
var value = instance.getDataAtCell(row, col);
if(_.isNull(value)) { // turn null into a list
var rowData = instance.getData()[row];
value = rowData[prop] = [];
}
keyboardProxy.on("keydown.editor", function(event) {
//catch CTRL but not right ALT (which in some systems triggers ALT+CTRL)
var ctrlDown = (event.ctrlKey || event.metaKey) && !event.altKey;
if(!ctrlDown && Handsontable.helper.isPrintableChar(event.keyCode)) {
cellProperties.showEditor(instance, td, row, col, prop, cellProperties, value);
event.stopPropagation();
}
});
instance.view.wt.update('onCellDblClick', function() {
cellProperties.showEditor(instance, td, row, col, prop, cellProperties, value);
});
return function() {
keyboardProxy.off(".editor");
instance.view.wt.update('onCellDblClick', null);
};
}
}
};
});
Raw
tests.js
buster.spec.expose();
require.config({
baseUrl: 'public/'
});
describe('TablePlugins', function(run) {
require([
'jquery',
'app/utils/tableplugins'
], function($, TablePlugins) {
run(function() {
describe("Table validation", function() {
before(function() {
$("body").append("<div id='tbl' style='width: 600px' />");
});
after(function() {
$("body").empty();
});
it("can validate required text against null and empty string", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
nickname: null
},{
name: '',
nickname: 'Billy'
},{
name: 'James Smith',
nickname: 'Jim'
}
],
colHeaders: ["Name", "Nickname"],
dataSchema: {name: null, nickName: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredText
},{
data: "nickname",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredText
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[0, 'nickname'], [1, 'name']]);
});
it("can validate numbers", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
age: null
},{
name: 'Jim Smith',
age: "foo"
},{
name: 'Jane Smith',
age: 0
},{
name: 'James Smith',
age: 50
},{
name: 'Joan Smith',
age: "30"
}],
colHeaders: ["Name", "Age"],
dataSchema: {name: null, age: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "age",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.number
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[1, 'age']]);
});
it("can validate required numbers", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
age: null
},{
name: 'Jim Smith',
age: "foo"
},{
name: 'Jane Smith',
age: 0
},{
name: 'James Smith',
age: 50
},{
name: 'Joan Smith',
age: "30"
}],
colHeaders: ["Name", "Age"],
dataSchema: {name: null, age: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "age",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredNumber
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[0, 'age'], [1, 'age']]);
});
it("can validate percentages", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
success: null
},{
name: 'Jim Smith',
success: "foo"
},{
name: 'Jane Smith',
success: 0
},{
name: 'James Smith',
success: 50
},{
name: 'Joan Smith',
success: "30"
},{
name: 'Joan Smith',
success: 100
},{
name: 'Joan Smith',
success: 101
}],
colHeaders: ["Name", "Success"],
dataSchema: {name: null, success: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "success",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.percentage
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[1, 'success'], [6, 'success']]);
});
it("can validate required percentages", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
success: null
},{
name: 'Jim Smith',
success: "foo"
},{
name: 'Jane Smith',
success: 0
},{
name: 'James Smith',
success: 50
},{
name: 'Joan Smith',
success: "30"
},{
name: 'Joan Smith',
success: 100
},{
name: 'Joan Smith',
success: 101
}],
colHeaders: ["Name", "Success"],
dataSchema: {name: null, success: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "success",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredPercentage
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[0, 'success'], [1, 'success'], [6, 'success']]);
});
it("can validate dates", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
dob: null
},{
name: 'Jim Smith',
dob: "foo"
},{
name: 'Jane Smith',
dob: "2012-01-01"
},{
name: 'James Smith',
dob: new Date()
}],
colHeaders: ["Name", "Date of birth"],
dataSchema: {name: null, dob: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "dob",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.date
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[1, 'dob'], [2, 'dob']]);
});
it("can validate required dates", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
dob: null
},{
name: 'Jim Smith',
dob: "foo"
},{
name: 'Jane Smith',
dob: "2012-01-01"
},{
name: 'James Smith',
dob: new Date()
}],
colHeaders: ["Name", "Date of birth"],
dataSchema: {name: null, dob: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "dob",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredDate
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[0, 'dob'], [1, 'dob'], [2, 'dob']]);
});
it("can validate unique text", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
key: null
},{
name: 'Jim Smith',
key: "foo"
},{
name: 'Jane Smith',
key: "foo"
},{
name: 'James Smith',
key: "bar"
}],
colHeaders: ["Name", "Key"],
dataSchema: {name: null, key: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "key",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.uniqueText
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[1, 'key'], [2, 'key']]);
});
it("can validate unique required text", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
key: null
},{
name: 'Jim Smith',
key: "foo"
},{
name: 'Jane Smith',
key: "foo"
},{
name: 'James Smith',
key: "bar"
}],
colHeaders: ["Name", "Key"],
dataSchema: {name: null, key: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell
},{
data: "key",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredUniqueText
}]
});
var failed = null,
invalid = null;
TablePlugins.validateTable($("#tbl").data('handsontable'))
.done(function(table) {
failed = false;
})
.fail(function(table, invalid_) {
failed = true;
invalid = invalid_;
});
assert.isTrue(failed);
assert.equals(invalid, [[0, 'key'], [1, 'key'], [2, 'key']]);
});
it("can validate in-place during editing using validateBeforeChange as an onBeforeChange handler", function() {
$("#tbl").handsontable({
data: [{
name: 'John Smith',
nickname: "Johnny"
},{
name: 'William Smith',
nickname: 'Billy'
},{
name: 'James Smith',
nickname: 'Jim'
}
],
colHeaders: ["Name", "Nickname"],
dataSchema: {name: null, nickName: null},
minSpareRows: 1,
columns: [{
data: "name",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredText
},{
data: "nickname",
type: TablePlugins.TextCell,
validator: TablePlugins.validators.requiredText
}],
onBeforeChange: TablePlugins.validateBeforeChange
});
var table = $("#tbl").data('handsontable');
table.setDataAtCell(0, 'nickname', null);
assert.equals(table.getDataAtCell(0, table.propToCol('nickname')), "Johnny");
table.setDataAtCell(1, 'name', "");
assert.equals(table.getDataAtCell(1, table.propToCol('name')), "William Smith");
table.setDataAtCell(2, 'name', "Jamie Smith");
assert.equals(table.getDataAtCell(2, table.propToCol('name')), "Jamie Smith");
});
});
});
});
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment