In my current project, I have users who want to regularly copy and paste stuff from excel and save it into a database via a web app. At first I tried an elaborate system where they would download a spreadsheet template, paste their data into it, upload it and get back a new spreadsheet with validation errors. It started getting quite complicated, and I was very happy to find the handsontable library (an awesome lightweight spreadsheet library) to use instead. The problem is that there aren’t many examples on how to add validation on the web. So I hope this helps some other poor soul like me.
First thing is to stop trying to use handsontable’s methods via jquery. I spent ages trying to get it working and getting terribly confused because examples on the internet were all using jquery but the documentation on the site was all just pure js. Definitely easiest to go the pure js way, don’t forget you can include pure js code in your jquery $(document).ready(function(){}) – it will work just fine. JQuery is just javascript after all!
So, here is an example on a few different types of validation methods:
HTML:
<div id="exampleGrid" class="dataTable"></div>
<button id="submitData" type="button" class="btn btn-primary">Upload</button>
JS: (remember to include jquery for your bits and bobs, as well as handsontable's js and css)
var myData = [
["cat", '10/11/2015', ''],
["invalidvalue", '10/11/2015', ''],
["dog", '10/11/2015', ''],
["", '10/11/2015', '']
];
// Empty validator
emptyValidator = function(value, callback) {
if (!value || 0 === value.length) {
console.log('false');
callback(false);
} else {
console.log('true');
callback(true);
}
};
// Handsontable options
hotOptions = {
colHeaders: ['autocomplete','date', 'string'],
minSpareRows: 0, // This must be 0 or else it tries to add a blank row as soon as you remove it for validation
rowHeaders: true,
contextMenu: true,
colWidths: [240, 100, 100],
columns: [
{ // Note, whenever you put "type: something", handsontable does its own validation and if you add a "validator: yourfunction" it seems to go haywire.
type: 'autocomplete',
source: ['cat', 'dog'],
strict: true
},
{ // It is more obvious with the date that some kind of validation is going on and it will get confused with your own custom validator. I don't know how you would add custom date validation, I haven't had to do that yet thank goodness
type: 'date',
dateFormat: 'DD/MM/YYYY',
correctFormat: true,
defaultDate: '01/01/2000'
},
{ // Here is the empty column with your validator forcing text in there
validator: emptyValidator
}
],
data: myData,
};
// Instantiate
var ht = new Handsontable(document.getElementById('exampleGrid'), hotOptions);
// Trigger more validation with the click
$('#submitData').click(function() {
// Get the data in the cells
var myTableData = ht.getData();
// If the last row is empty, remove it before validation
if(myTableData.length > 1 && ht.isEmptyRow(myTableData.length - 1)) {
// Remove the last row if it's empty
ht.alter('remove_row', parseInt(myTableData.length - 1), keepEmptyRows = false);
}
// Validate the cells and submit the form via ajax or whatever
ht.validateCells(function(result, obj) {
if(result == true) {
console.log('submitted');
}
else {
console.log('bad form data')
}
});
});
And here is a link to a jsFiddle: https://jsfiddle.net/sysb6wvd/2/
