JavaScript Data GridSearching values

Search data across Handsontable, using the built-in API methods of the Search plugin, and implementing your own search UI.

Overview

The Search plugin provides an easy API to search data across Handsontable.

You should first enable the plugin by setting the search option to true. When enabled, the Search plugin exposes a new method query(queryStr), where queryStr is a string to find within the table. By default, the search is case insensitive.

query(queryStr, [callback], [queryMethod]) method does 2 things. First of all, it returns an array of search results. Every element is an objects containing 3 properties:

  • row – index of the row where the value has been found
  • col – index of the column where the value has been found
  • data – the value that has been found

The second thing the query() method does is set the isSearchResult property for each cell. If a cell is in search results, then its isSearchResult is set to true, otherwise the property is set to false.

All you have to do now, is use the query() method inside search input listener and you're done.

Search result class

By default, the Search plugin adds htSearchResult class to every cell which isSearchResult property is true. You can change this class using searchResultClass configuration option.

To change the result class, you use the var searchPlugin = hot.getPlugin('search'); searchPlugin.setSearchResultClass(className); method.

Custom queryMethod

The queryMethod() function is responsible for determining whether a queryStr matches the value stored in a cell. It takes 2 arguments: queryStr and cellData. The first is a string passed to query() method. The second is a value returned by getDataAtCell(). The queryMethod() function should return true if there is a match.

The default queryMethod function is dead simple:

const DEFAULT_QUERY_METHOD = function(query, value) {
  if (isUndefined(query) || query === null || !query.toLowerCase || query.length === 0) {
    return false;
  }
  if (isUndefined(value) || value === null) {
    return false;
  }

  return value.toString().toLowerCase().indexOf(query.toLowerCase()) !== -1;
};

If you want to change the queryMethod, use the queryMethod option. You can also pass the queryMethod as the third argument of query() method. To change the queryMethod, use var searchPlugin = hot.getPlugin('search'); searchPlugin.setQueryMethod(myNewQueryMethod);.

Custom result callback

After calling queryMethod the Search plugin calls callback(instance, rowIndex, colIndex, cellData, testResult) for every cell.

Just as the queryMethod, you can override this callback, using var searchPlugin = hot.getPlugin('search'); searchPlugin.setCallback(myNewCallbackFunction);, or passing your callback as the second argument of query() method.

The default callback is responsible for setting the isSearchResult property.

const DEFAULT_CALLBACK = function(instance, row, col, data, testResult) {
  instance.getCellMeta(row, col).isSearchResult = testResult;
};

Simplest use case

The example below:

  • Enables the Search plugin (by setting the search configuration option to true)
  • Adds a search input listener
  • Inside the search input listener, gets the Search plugin's instance
  • Uses the Search plugin's query() method

import Handsontable from 'handsontable';
import 'handsontable/dist/handsontable.full.min.css';

const data = [
  ['Tesla', 2017, 'black', 'black'],
  ['Nissan', 2018, 'blue', 'blue'],
  ['Chrysler', 2019, 'yellow', 'black'],
  ['Volvo', 2020, 'yellow', 'gray'],
];

const container = document.querySelector('#example1');
const hot = new Handsontable(container, {
  data,
  colHeaders: true,
  // enable the `Search` plugin
  search: true,
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

const searchField = document.querySelector('#search_field');

// add a search input listener
searchField.addEventListener('keyup', (event) => {
  // get the `Search` plugin's instance
  const search = hot.getPlugin('search');
  // use the `Search` plugin's `query()` method
  const queryResult = search.query(event.target.value);

  console.log(queryResult);
  hot.render();
});

Custom search result class

You can style your search results with a custom CSS class, using the Search plugin's searchResultClass option.

The example below highlights its search results in bold red. To do this, it:

  • Defines a custom CSS class called my-custom-search-result-class
  • Enables the Search plugin (by setting the search configuration option to an object)
  • Sets the Search plugin's searchResultClass option to 'my-custom-search-result-class'

import Handsontable from 'handsontable';
import 'handsontable/dist/handsontable.full.min.css';

const data = [
  ['Tesla', 2017, 'black', 'black'],
  ['Nissan', 2018, 'blue', 'blue'],
  ['Chrysler', 2019, 'yellow', 'black'],
  ['Volvo', 2020, 'yellow', 'gray'],
];

const container = document.querySelector('#example2');
const hot = new Handsontable(container, {
  data,
  colHeaders: true,
  // enable the `Search` plugin
  search: {
    // add your custom CSS class
    searchResultClass: 'my-custom-search-result-class',
  },
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

const searchField = document.querySelector('#search_field2');

searchField.addEventListener('keyup', (event) => {
  const search = hot.getPlugin('search');
  const queryResult = search.query(event.target.value);

  console.log(queryResult);
  hot.render();
});

Custom query method

You can add a custom query method, using the Search plugin's queryMethod.

The example below searches only for exact search query matches. To do this, it:

  • Defines a custom query method called onlyExactMatch
  • Enables the Search plugin (by setting the search configuration option to an object)
  • Sets the Search plugin's queryMethod option to onlyExactMatch

import Handsontable from 'handsontable';
import 'handsontable/dist/handsontable.full.min.css';

const data = [
  ['Tesla', 2017, 'black', 'black'],
  ['Nissan', 2018, 'blue', 'blue'],
  ['Chrysler', 2019, 'yellow', 'black'],
  ['Volvo', 2020, 'white', 'gray'],
];

// define your custom query method
function onlyExactMatch(queryStr, value) {
  return queryStr.toString() === value.toString();
}

const container = document.querySelector('#example3');
const hot = new Handsontable(container, {
  data,
  colHeaders: true,
  // enable the `Search` plugin
  search: {
    // add your custom query method
    queryMethod: onlyExactMatch,
  },
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

const searchField = document.querySelector('#search_field3');

searchField.addEventListener('keyup', (event) => {
  const search = hot.getPlugin('search');
  // use the `Search`'s `query()` method
  const queryResult = search.query(event.target.value);

  console.log(queryResult);
  hot.render();
});

Custom callback

You can add a custom callback function, using the Search plugin's callback option.

The example below displays the number of matching search results. To do this, it:

  • Defines a custom callback function called searchResultCounter
  • Enables the Search plugin (by setting the search configuration option to an object)
  • Sets the Search plugin's callback option to searchResultCounter

0 results
import Handsontable from 'handsontable';
import 'handsontable/dist/handsontable.full.min.css';

let searchResultCount = 0;
const data = [
  ['Tesla', 2017, 'black', 'black'],
  ['Nissan', 2018, 'blue', 'blue'],
  ['Chrysler', 2019, 'yellow', 'black'],
  ['Volvo', 2020, 'white', 'gray'],
];

// define your custom callback function
function searchResultCounter(instance, row, column, value, result) {
  const DEFAULT_CALLBACK = function (instance, row, col, _data, testResult) {
    instance.getCellMeta(row, col).isSearchResult = testResult;
  };

  DEFAULT_CALLBACK.apply(this, [instance, row, column, value, result]);

  if (result) {
    searchResultCount++;
  }
}

const container = document.querySelector('#example4');
const hot = new Handsontable(container, {
  data,
  colHeaders: true,
  // enable the `Search` plugin
  search: {
    // add your custom callback function
    callback: searchResultCounter,
  },
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

const searchField = document.querySelector('#search_field4');
const output = document.querySelector('#output');

searchField.addEventListener('keyup', (event) => {
  searchResultCount = 0;

  const search = hot.getPlugin('search');
  const queryResult = search.query(event.target.value);

  console.log(queryResult);
  output.innerText = `${searchResultCount} results`;
  hot.render();
});

There is a newer version of Handsontable available. Switch to the latest version ⟶