Handsontable

a minimalistic Excel-like data grid editor for HTML, JavaScript & jQuery

Autocomplete cell type

This page shows how to configure Handsontable with Autocomplete cell type:

Autocomplete lazy mode
Autocomplete strict mode
Autocomplete strict mode (Ajax)

Autocomplete lazy mode

This example shows the usage of the Autocomplete feature.

Car	Year	Chassis color	Bumper color
Nissan ▼	2009	black ▼	black ▼
Nissan ▼	2006	blue ▼	blue ▼
Chrysler ▼	2004	yellow ▼	black ▼
Volvo ▼	2012	white ▼	gray ▼

Edit in jsFiddle

function myAutocompleteRenderer(instance, td, row, col, prop, value, cellProperties) {
  Handsontable.AutocompleteCell.renderer.apply(this, arguments);
  td.style.fontStyle = 'italic';
  td.title = 'Type to show the list of options';
}

function colorHighlighter(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;
}

$("#example1").handsontable({
  data: getCarData(),
  startRows: 7,
  startCols: 4,
  colHeaders: ["Car", "Year", "Chassis color", "Bumper color"],
  columns: [
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      source: ["BMW", "Chrysler", "Nissan", "Suzuki", "Toyota", "Volvo"],
      strict: false
    },
    {type: 'numeric'},
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      source: ["yellow", "red", "orange", "green", "blue", "gray", "black", "white"],
      highlighter: colorHighlighter,
      strict: false
    },
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      source: ["yellow", "red", "orange", "green", "blue", "gray", "black", "white"],
      highlighter: colorHighlighter,
      strict: false
    }
  ]
});

Autocomplete strict mode

This is the same example as above with a difference that autocomplete now runs in strict mode.

In this mode, the autocomplete cells will only accept values that are defined in the source array.

Car	Year	Chassis color	Bumper color
Nissan ▼	2009	black ▼	black ▼
Nissan ▼	2006	blue ▼	blue ▼
Chrysler ▼	2004	yellow ▼	black ▼
Volvo ▼	2012	white ▼	gray ▼

Edit in jsFiddle

$("#example2").handsontable({
  data: getCarData(),
  startRows: 7,
  startCols: 4,
  colHeaders: ["Car", "Year", "Chassis color", "Bumper color"],
  columns: [
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      source: ["BMW", "Chrysler", "Nissan", "Suzuki", "Toyota", "Volvo"],
      strict: true
    },
    {},
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      source: ["yellow", "red", "orange", "green", "blue", "gray", "black", "white"],
      highlighter: colorHighlighter,
      strict: true
    },
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      source: ["yellow", "red", "orange", "green", "blue", "gray", "black", "white"],
      highlighter: colorHighlighter,
      strict: true
    }
  ]
});

Autocomplete strict mode (Ajax)

Autocomplete can be also used with Ajax data source. In the below example, suggestions for the "Car" column are loaded from server.

To load data from remote (asynchrous) source, assign a function to the 'source' property. Function should perform the server side request and call the callback function when the result is available.

The property options overrides `defaults` defined in Bootstrap Typeahead. It may be useful to configure the number of displayed suggestions.

Car	Year	Chassis color	Bumper color
Nissan ▼	2009	black	black
Nissan ▼	2006	blue	blue
Chrysler ▼	2004	yellow	black
Volvo ▼	2012	white	gray

$("#example3").handsontable({
  data: getCarData(),
  startRows: 7,
  startCols: 4,
  colHeaders: ["Car", "Year", "Chassis color", "Bumper color"],
  columns: [
    {
      type: {renderer: myAutocompleteRenderer, editor: Handsontable.AutocompleteEditor},
      options: {items: 10}, //`options` overrides `defaults` defined in bootstrap typeahead
      source: function (query, process) {
        $.ajax({
          url: 'php/cars.php',
          data: {
            query: query
          },
          success: function (response) {
            console.log("response", response);
            process(response);
          }
        });
      },
      strict: true
    },
    {} /*Year is a default text column*/,
    {} /*Chassis color is a default text column*/,
    {} /*Bumper color is a default text column*/
  ]
});

For more examples, head back to the main page.