Handsontable is a minimalistic approach to Excel-like table editor in HTML & jQuery.
Features:
Type in any cell and see how the grid adds new rows and cols automatically. You can try CTRL+Z and CTRL+Y to undo/redo.
Also, why not try CTRL+C, CTRL+V between this and Excel, Google Spreadsheet or LibreOffice!
Code:
<div id="example1grid" class="dataTable"></div> <script> $("#example1grid").handsontable({ rows: 5, cols: 5, minSpareCols: 1, //always keep at least 1 spare col at the right minSpareRows: 1, //always keep at least 1 spare row at the bottom contextMenu: true }); var data = [ ["", "Kia", "Nissan", "Toyota", "Honda"], ["2008", 10, 11, 12, 13], ["2009", 20, 11, 14, 13], ["2010", 30, 15, 12, 13] ]; $("#example1grid").handsontable("loadData", data); </script>
The legend feature, which makes the first row have an green font and an icon.
In the below example, the column (A, B, C) and row (1, 2, 3) headers are on.
Code:
<div id="example2grid" class="dataTable"></div> <script> $("#example2grid").handsontable({ rows: 5, cols: 5, rowHeaders: true, colHeaders: true, fillHandle: false, //fillHandle can be turned off contextMenu: ["row_above", "row_below", "remove_row"], //contextMenu will only allow inserting and removing rows legend: [ { match: function (row, col, data) { return (row === 0); //if it is first row }, style: { color: 'green', //make the text green and bold fontWeight: 'bold' }, title: 'Heading' //make some tooltip }, { match: function (row, col, data) { //if first row in this column contains word "Nissan" return (row > 0 && data()[0][col].indexOf('Nissan') > -1); }, style: { fontStyle: 'italic' //make cells text in this column written in italic } }, { match: function (row, col, data) { return (row === 0 && data()[row][col]); //if it is first row with data }, icon: { src: "http://upload.wikimedia.org/wikipedia/commons/7/75/Information-silk.png", click: function (row, col, data, icon) { alert("The Icon in row " + row + " and column " + col + " was clicked."); } } } ] }); var data = [ ["", "Kia", "Nissan", "Toyota", "Honda"], ["2008", 10, 11, 12, 13], ["2009", 20, 11, 14, 13], ["2010", 30, 15, 12, 13] ]; $("#example2grid").handsontable("loadData", data); </script>
You can define rules for autocompletition. The below example shows 2 possible modes of operation:
Note: Make sure you include the dependency (bootstrap-typeahead.js) as presented in README.md. This is a modified file from Bootstrap library.
Code:
<div id="example3grid" class="dataTable"></div> <script> $("#example3grid").handsontable({ rows: 7, cols: 4, rowHeaders: false, //turn off 1, 2, 3, ... colHeaders: ["Car", "Year", "Chassis color", "Bumper color"], legend: [ { match: function (row, col, data) { if (col == 0 || col == 2 || col == 3) { return true; } return false; }, style: { fontStyle: 'italic' //make the text italic }, title: "Type to show the list of options" } ], autoComplete: [ { match: function (row, col, data) { if (col == 2 || col == 3) { return true; } return false; }, highlighter: function (item) { var query = this.query.replace(/[\-\[\]{}()*+?.,\\\^$|#\s]/g, '\\$&'); var label = item.replace(new RegExp('(' + query + ')', 'ig'), function ($1, match) { return '<strong>' + match + '</strong>'; }); return '<span style="margin-right: 10px; background-color: ' + item + '"> </span>' + label; }, source: function () { return ["yellow", "red", "orange", "green", "blue", "gray", "black", "white"] }, strict: false //allows other values that defined in array above }, { match: function (row, col, data) { return (col === 0); //if it is first column }, source: function () { return ["BMW", "Chrysler", "Nissan", "Suzuki", "Toyota", "Volvo"] }, strict: true //only accept predefined values (from array above) } ] }); var data = [ ["Nissan", 2009, "black", "black"], ["Nissan", 2006, "blue", "blue"], ["Chrysler", 2004, "yellow", "black"], ["Volvo", 2012, "white", "gray"] ]; $("#example3grid").handsontable("loadData", data); </script>
If you want scrollbars, just set it in the container CSS:
Code:
<div id="example4grid" class="dataTable" style="width: 680px; height: 300px; overflow: scroll"></div> <script> $("#example4grid").handsontable({ rows: 40, cols: 40, rowHeaders: true, colHeaders: true, minSpareCols: 1, //always keep at least 1 spare col at the right minSpareRows: 1 //always keep at least 1 spare row at the bottom }); var data = [ ["", "Kia", "Nissan", "Toyota", "Honda"], ["2008", 10, 11, 12, 13], ["2009", 20, 11, 14, 13], ["2010", 30, 15, 12, 13] ]; $("#example4grid").handsontable("loadData", data); </script>
Notice the little square (fill handle) in the corner of the selected cell. You can drag it (drag-down) to repeat the values from the cell.
Double click the fill handle in cell B4 (value "30") to fill the selection down to the last value in neighbouring column, just like it would in LibreOffice or Google Docs.
Code:
<div id="example5grid" class="dataTable"></div> <script> $("#example5grid").handsontable({ rows: 8, cols: 8, rowHeaders: true, colHeaders: true, minSpareCols: 1, minSpareRows: 1, fillHandle: true //possible values: true, false, "horizontal", "vertical" }); var data = [ ["", "Kia", "Nissan", "Toyota", "Honda"], ["2008", 10, 11, 12, 13], ["2009", 20, 11, 14, 13], ["2010", 30, 15, 12, 13], ["2011", "", "", "", ""], ["2012", "", "", "", ""] ]; $("#example5grid").handsontable("loadData", data); </script>
Right click to see the context menu.
Note: Make sure you include the dependencies (jquery.contextMenu.js, jquery.contextMenu.css, jquery.ui.position.js) as presented in README.md.
Code:
<div id="example7grid" class="dataTable"></div> <script> $("#example7grid").handsontable({ rows: 5, cols: 5, rowHeaders: true, colHeaders: true, minSpareCols: 1, minSpareRows: 1, contextMenu: true }); var data = [ ["", "Kia", "Nissan", "Toyota", "Honda"], ["2008", 10, 11, 12, 13], ["2009", 20, 11, 14, 13], ["2010", 30, 15, 12, 13] ]; $("#example7grid").handsontable("loadData", data); </script>
Use the onBeforeChange callback to validate changes before they are applied to the table.
Callback console: [[row, col, oldValue, newValue], ...]
Edit the above grid to see callback
Code:
<div id="example6grid" class="dataTable"></div> <pre id="example6console" class="console"> Edit the above grid to see callback </pre> <script> $("#example6grid").handsontable({ rows: 8, cols: 8, rowHeaders: true, colHeaders: true, minSpareCols: 1, minSpareRows: 1, contextMenu: true, onBeforeChange: function (data) { for (var i = 0, ilen = data.length; i < ilen; i++) { if (data[i][3] === "foo") { //gently don't accept the word "foo" data[i][3] = false; } else if (data[i][3] === "bar") { //if the word bar is given, add a ! at the end of it data[i][3] = data[i][3] + '!'; } else if (data[i][3] === "nuke") { //if any of pasted cells contains the word "nuke", reject the whole paste return false; } } }, onChange: function (data, source) { if (source === 'loadData') { return; //don't show this change in console } $("#example6console").text(JSON.stringify(data)); } }); var data = [ ["", "Kia", "Nissan", "Toyota", "Honda"], ["2008", 10, 11, 12, 13], ["2009", 20, 11, 14, 13], ["2010", 30, 15, 12, 13] ]; $("#example6grid").handsontable("loadData", data); </script>
Use the onChange callback to track changes made in the table. In the example below, $.ajax is used to load and save grid data.
Note: this is a mockup. Nothing is acually saved. You have to implement that part server-side.
Code:
<p> <button name="load">Load</button> <button name="save">Save</button> <label><input type="checkbox" name="autosave" checked="checked" autocomplete="off"> Autosave</label> </p> <div id="example9console" class="console">Click "Load" to load data from server</div> <div id="example9grid" class="dataTable"></div> <script> var $container = $("#example9grid"); var $console = $("#example9console"); var $parent = $container.parent(); var autosaveNotification; $container.handsontable({ rows: 8, cols: 8, rowHeaders: true, colHeaders: true, minSpareCols: 1, minSpareRows: 1, contextMenu: true, onChange: function (change, source) { if (source === 'loadData') { return; //don't save this change } if ($parent.find('input[name=autosave]').is(':checked')) { clearTimeout(autosaveNotification); $.ajax({ url: "demo/json/save.json", dataType: "json", type: "POST", data: change, //contains changed cells' data success: function (data) { $console.text('Autosaved (' + change.length + ' cell' + (change.length > 1 ? 's' : '') + ')'); autosaveNotification = setTimeout(function(){ $console.text('Changes will be autosaved'); }, 1000); } }); } } }); var handsontable = $container.data('handsontable'); $parent.find('button[name=load]').click(function () { $.ajax({ url: "demo/json/load.json", dataType: 'json', type: 'GET', success: function (res) { handsontable.loadData(res.data); $console.text('Data loaded'); } }); }); $parent.find('button[name=save]').click(function () { $.ajax({ url: "demo/json/save.json", data: {"data": handsontable.getData()}, //returns all cells' data dataType: 'json', type: 'POST', success: function (res) { if(res.result === 'ok') { $console.text('Data saved'); } else { $console.text('Save error'); } }, error: function () { $console.text('Save error'); } }); }); $parent.find('input[name=autosave]').click(function () { if($(this).is(':checked')) { $console.text('Changes will be autosaved'); } else{ $console.text('Changes will not be autosaved'); } }); </script>
Check out some advanced usage examples:
Tested with IE7, IE8, IE9, recent versions of Firefox, Chrome, Safari and Opera.
Not compatible with iOS and Android. Can you help with that?
Available plugin methods and configuration options are listed in README.md on GitHub
You can download this project in either zip or tar formats.
You can also clone the project with Git by running:
$ git clone git://github.com/warpech/jquery-handsontable
Licensed under the MIT license
If you like Handsontable and you would like to support the development of new features, please consider a donation. Thanks!
Marcin Warpechowski / Giraffe