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/