Fork me on GitHub

Handsontable

This page has been moved to http://handsontable.com/. Please update your bookmarks.

Handsontable is a minimalistic approach to Excel-like table editor in HTML & jQuery.

Features:

Demos

Autoexpanding

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>

Legend

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>

Autocomplete

You can define rules for autocompletition. The below example shows 2 possible modes of operation:

  • First column (Cars) is configured in strict mode, which accepts only predefined vaules
  • Last 2 columns (Chassis color, Bumper color) are configured in non-strict mode, that allows any value for the cells

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 + '">&nbsp;&nbsp;&nbsp;</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>

Scroll

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>

Drag-down

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>

Context menu

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>

Validation

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>

Load & Save

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.

Click "Load" to load data from server

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 &gt; 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>

Advanced demos

Check out some advanced usage examples:

Browser compability

Tested with IE7, IE8, IE9, recent versions of Firefox, Chrome, Safari and Opera.

Not compatible with iOS and Android. Can you help with that?

Documentation

Available plugin methods and configuration options are listed in README.md on GitHub

Download

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

License

Licensed under the MIT license

Donate

If you like Handsontable and you would like to support the development of new features, please consider a donation. Thanks!

Authors

Marcin Warpechowski / Giraffe