Searching values

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

Overview

The Search plugin lets you scan all cells in the grid and get back a list of matches. Enable it by setting the search option to true or to a configuration object.

Once enabled, the plugin exposes the query(queryStr) method. Call it with a search string whenever the user types. By default, the search is case-insensitive and matches partial cell values.

How `query()` works

Calling query(queryStr, [callback], [queryMethod]) does two things:

Iterates over every cell in the grid and tests each one using the queryMethod.
After each test, calls the callback to update cell metadata (isSearchResult).

It returns an array of result objects - one for each matching cell:

Property	Type	Description
`row`	`number`	Visual row index of the matching cell
`col`	`number`	Visual column index of the matching cell
`data`	`string\|number\|null`	Value of the matching cell

After calling query(), call hot.render() to refresh visual highlighting.

Search result class

After query() runs, every cell where isSearchResult === true automatically receives the CSS class htSearchResult. You can replace this class in two ways:

At initialization: set search: { searchResultClass: 'my-class' }
Programmatically: call hot.getPlugin('search').setSearchResultClass('my-class')

Custom `queryMethod`

The queryMethod function determines whether the query string matches a cell value. It is called once per cell during every query() call.

Signature:

1
function queryMethod(query, value, cellProperties) {
2
  // return true for a match, false otherwise
3
}

Parameter	Type	Description
`query`	`string`	The search string passed to `query()`
`value`	`string\|number\|null`	The cell value (from `getDataAtCell()`)
`cellProperties`	`object`	The cell’s metadata object (includes `locale`, `type`, and other cell options)

The built-in default performs a case-insensitive, locale-aware substring match:

1
function defaultQueryMethod(query, value, cellProperties) {
2
  if (query === undefined || query === null || query.length === 0) {
3
    return false;
4
  }
5
  if (value === undefined || value === null) {
6
    return false;
7
  }
8

9
  return value.toString().toLocaleLowerCase(cellProperties.locale)
10
    .indexOf(query.toLocaleLowerCase(cellProperties.locale)) !== -1;
11
}

You can set a custom query method in three ways:

At initialization: search: { queryMethod: myQueryMethod }
Programmatically: hot.getPlugin('search').setQueryMethod(myQueryMethod)
Per query() call: searchPlugin.query(queryStr, callback, myQueryMethod) (applies to that call only)

Custom result callback

The callback function is called for every cell during a query() run, whether or not the cell matches. It is responsible for updating cell metadata so the renderer knows which cells to highlight.

Signature:

1
function callback(instance, row, col, data, testResult) {
2
  // update cell metadata based on testResult
3
}

Parameter	Type	Description
`instance`	`Handsontable`	The Handsontable instance
`row`	`number`	Visual row index
`col`	`number`	Visual column index
`data`	`string\|number\|null`	The cell value
`testResult`	`boolean`	`true` if the cell matches the query, `false` otherwise

The built-in default sets the isSearchResult flag on each cell’s metadata:

1
function defaultCallback(instance, row, col, data, testResult) {
2
  instance.getCellMeta(row, col).isSearchResult = testResult;
3
}

If you override the callback to add custom logic (for example, to count results), call the default behavior manually so that cell highlighting still works.

You can set a custom callback in three ways:

At initialization: search: { callback: myCallback }
Programmatically: hot.getPlugin('search').setCallback(myCallback)
Per query() call: searchPlugin.query(queryStr, myCallback) (applies to that call only)

Per-cell `queryMethod` and `callback`

Both queryMethod and callback can be overridden for individual cells, columns, or rows using Handsontable’s cascading configuration model. Set a search object directly in a cell, columns, or rows entry:

1
handsontable({
2
  data: myData,
3
  search: true,
4
  columns: [
5
    {},
6
    // Column 1: exact match only, everything else uses the global queryMethod
7
    {
8
      search: {
9
        queryMethod(queryStr, value) {
10
          return queryStr.toString() === value.toString();
11
        }
12
      }
13
    }
14
  ]
15
});

You can also set it programmatically on a specific cell using setCellMeta():

1
hot.setCellMeta(row, col, 'search', {
2
  queryMethod(queryStr, value) {
3
    return queryStr.toString() === value.toString();
4
  }
5
});

Per-cell settings take precedence over the plugin-level queryMethod and callback. Only queryMethod and callback support per-cell overrides - searchResultClass does not.

Simplest use case

The example below:

Enables the Search plugin by setting search to true
Listens for keyup events on a search input
Calls query() on each keystroke and re-renders the grid to apply highlighting

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3

4
// Register all Handsontable's modules.
5
registerAllModules();
6

7
const data = [
8
  ['Tesla', 2017, 'black', 'black'],
9
  ['Nissan', 2018, 'blue', 'blue'],
10
  ['Chrysler', 2019, 'yellow', 'black'],
11
  ['Volvo', 2020, 'yellow', 'gray'],
12
];
13

14
const container = document.querySelector('#example1');
15
const hot = new Handsontable(container, {
16
  data,
17
  colHeaders: true,
18
  // enable the `Search` plugin
19
  search: true,
20
  height: 'auto',
21
  autoWrapRow: true,
22
  autoWrapCol: true,
23
  licenseKey: 'non-commercial-and-evaluation',
24
});
25

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

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

35
  console.log(queryResult);
36
  hot.render();
37
});

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { Search } from 'handsontable/plugins';
4

5
// Register all Handsontable's modules.
6
registerAllModules();
7

8
const data: (string | number)[][] = [
9
  ['Tesla', 2017, 'black', 'black'],
10
  ['Nissan', 2018, 'blue', 'blue'],
11
  ['Chrysler', 2019, 'yellow', 'black'],
12
  ['Volvo', 2020, 'yellow', 'gray'],
13
];
14

15
const container = document.querySelector('#example1')!;
16

17
const hot = new Handsontable(container, {
18
  data,
19
  colHeaders: true,
20
  // enable the `Search` plugin
21
  search: true,
22
  height: 'auto',
23
  autoWrapRow: true,
24
  autoWrapCol: true,
25
  licenseKey: 'non-commercial-and-evaluation',
26
});
27

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

30
// add a search input listener
31
searchField.addEventListener('keyup', (event) => {
32
  // get the `Search` plugin's instance
33
  const search: Search = hot.getPlugin('search');
34
  // use the `Search` plugin's `query()` method
35
  const queryResult = search.query((event.target as HTMLInputElement).value);
36

37
  console.log(queryResult);
38

39
  hot.render();
40
});

1
<div class="example-controls-container">
2
  <div class="controls">
3
    <input id="search_field" type="search" placeholder="Search">
4
  </div>
5
</div>
6
<div id="example1"></div>

Custom search result class

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

The example below highlights search results with a pink background and red text. To do this, it:

Defines a custom CSS class called my-class, scoped to .ht-theme-main .handsontable
Enables the Search plugin with a configuration object
Sets searchResultClass to 'my-class'

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3

4
// Register all Handsontable's modules.
5
registerAllModules();
6

7
const data = [
8
  ['Tesla', 2017, 'black', 'black'],
9
  ['Nissan', 2018, 'blue', 'blue'],
10
  ['Chrysler', 2019, 'yellow', 'black'],
11
  ['Volvo', 2020, 'yellow', 'gray'],
12
];
13

14
const container = document.querySelector('#example2');
15
const hot = new Handsontable(container, {
16
  data,
17
  colHeaders: true,
18
  // enable the `Search` plugin
19
  search: {
20
    // add your custom CSS class
21
    searchResultClass: 'my-class',
22
  },
23
  height: 'auto',
24
  autoWrapRow: true,
25
  autoWrapCol: true,
26
  licenseKey: 'non-commercial-and-evaluation',
27
});
28

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

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

35
  console.log(queryResult);
36
  hot.render();
37
});

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { Search } from 'handsontable/plugins';
4

5
// Register all Handsontable's modules.
6
registerAllModules();
7

8
const data: (string | number)[][] = [
9
  ['Tesla', 2017, 'black', 'black'],
10
  ['Nissan', 2018, 'blue', 'blue'],
11
  ['Chrysler', 2019, 'yellow', 'black'],
12
  ['Volvo', 2020, 'yellow', 'gray'],
13
];
14

15
const container = document.querySelector('#example2')!;
16

17
const hot = new Handsontable(container, {
18
  data,
19
  colHeaders: true,
20
  // enable the `Search` plugin
21
  search: {
22
    // add your custom CSS class
23
    searchResultClass: 'my-class',
24
  },
25
  height: 'auto',
26
  autoWrapRow: true,
27
  autoWrapCol: true,
28
  licenseKey: 'non-commercial-and-evaluation',
29
});
30

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

33
searchField.addEventListener('keyup', (event) => {
34
  const search: Search = hot.getPlugin('search');
35
  const queryResult = search.query((event.target as HTMLInputElement).value);
36

37
  console.log(queryResult);
38
  hot.render();
39
});

1
.ht-theme-main .handsontable .my-class {
2
  background: pink;
3
  color: red;
4
}

1
<div class="example-controls-container">
2
  <div class="controls">
3
    <input id="search_field2" type="search" placeholder="Search">
4
  </div>
5
</div>
6
<div id="example2"></div>

Custom query method

You can replace the built-in substring search with a custom query method, using the queryMethod option.

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

Defines a custom query method called onlyExactMatch that uses strict equality (===)
Enables the Search plugin with a configuration object
Sets queryMethod to onlyExactMatch

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3

4
// Register all Handsontable's modules.
5
registerAllModules();
6

7
const data = [
8
  ['Tesla', 2017, 'black', 'black'],
9
  ['Nissan', 2018, 'blue', 'blue'],
10
  ['Chrysler', 2019, 'yellow', 'black'],
11
  ['Volvo', 2020, 'white', 'gray'],
12
];
13

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

19
const container = document.querySelector('#example3');
20
const hot = new Handsontable(container, {
21
  data,
22
  colHeaders: true,
23
  // enable the `Search` plugin
24
  search: {
25
    // add your custom query method
26
    queryMethod: onlyExactMatch,
27
  },
28
  height: 'auto',
29
  autoWrapRow: true,
30
  autoWrapCol: true,
31
  licenseKey: 'non-commercial-and-evaluation',
32
});
33

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

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

41
  console.log(queryResult);
42
  hot.render();
43
});

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { Search } from 'handsontable/plugins';
4

5
// Register all Handsontable's modules.
6
registerAllModules();
7

8
const data: (string | number)[][] = [
9
  ['Tesla', 2017, 'black', 'black'],
10
  ['Nissan', 2018, 'blue', 'blue'],
11
  ['Chrysler', 2019, 'yellow', 'black'],
12
  ['Volvo', 2020, 'white', 'gray'],
13
];
14

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

20
const container = document.querySelector('#example3')!;
21

22
const hot = new Handsontable(container, {
23
  data,
24
  colHeaders: true,
25
  // enable the `Search` plugin
26
  search: {
27
    // add your custom query method
28
    queryMethod: onlyExactMatch,
29
  },
30
  height: 'auto',
31
  autoWrapRow: true,
32
  autoWrapCol: true,
33
  licenseKey: 'non-commercial-and-evaluation',
34
});
35

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

38
searchField.addEventListener('keyup', (event) => {
39
  const search: Search = hot.getPlugin('search');
40
  // use the `Search`'s `query()` method
41
  const queryResult = search.query((event.target as HTMLInputElement).value);
42

43
  console.log(queryResult);
44

45
  hot.render();
46
});

1
<div class="example-controls-container">
2
  <div class="controls">
3
    <input id="search_field3" type="search" placeholder="Search">
4
  </div>
5
</div>
6
<div id="example3"></div>

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 that counts matches and calls the default callback to preserve highlighting
Enables the Search plugin with a configuration object
Sets callback to searchResultCounter

0 results

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3

4
// Register all Handsontable's modules.
5
registerAllModules();
6

7
let searchResultCount = 0;
8
const data = [
9
  ['Tesla', 2017, 'black', 'black'],
10
  ['Nissan', 2018, 'blue', 'blue'],
11
  ['Chrysler', 2019, 'yellow', 'black'],
12
  ['Volvo', 2020, 'white', 'gray'],
13
];
14

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

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

23
  if (result) {
24
    searchResultCount++;
25
  }
26
}
27

28
const container = document.querySelector('#example4');
29
const hot = new Handsontable(container, {
30
  data,
31
  colHeaders: true,
32
  // enable the `Search` plugin
33
  search: {
34
    // add your custom callback function
35
    callback: searchResultCounter,
36
  },
37
  height: 'auto',
38
  autoWrapRow: true,
39
  autoWrapCol: true,
40
  licenseKey: 'non-commercial-and-evaluation',
41
});
42

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

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

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

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

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { Search } from 'handsontable/plugins';
4

5
// Register all Handsontable's modules.
6
registerAllModules();
7

8
let searchResultCount = 0;
9

10
const data: (string | number)[][] = [
11
  ['Tesla', 2017, 'black', 'black'],
12
  ['Nissan', 2018, 'blue', 'blue'],
13
  ['Chrysler', 2019, 'yellow', 'black'],
14
  ['Volvo', 2020, 'white', 'gray'],
15
];
16

17
// define your custom callback function
18
function searchResultCounter(
19
  instance: Handsontable.Core,
20
  row: number,
21
  column: number,
22
  value: Handsontable.CellValue,
23
  result: boolean
24
): void {
25
  const DEFAULT_CALLBACK = function (instance, row, col, _data, testResult) {
26
    instance.getCellMeta(row, col).isSearchResult = testResult;
27
  };
28

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

31
  if (result) {
32
    searchResultCount++;
33
  }
34
}
35

36
const container = document.querySelector('#example4')!;
37

38
const hot = new Handsontable(container, {
39
  data,
40
  colHeaders: true,
41
  // enable the `Search` plugin
42
  search: {
43
    // add your custom callback function
44
    callback: searchResultCounter,
45
  },
46
  height: 'auto',
47
  autoWrapRow: true,
48
  autoWrapCol: true,
49
  licenseKey: 'non-commercial-and-evaluation',
50
});
51

52
const searchField = document.querySelector('#search_field4')!;
53
const output = document.querySelector('#output')!;
54

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

58
  const search: Search = hot.getPlugin('search');
59
  const queryResult = search.query((event.target as HTMLInputElement).value);
60

61
  console.log(queryResult);
62
  (output as HTMLElement).innerText = `${searchResultCount} results`;
63
  hot.render();
64
});

1
<div class="example-controls-container">
2
    <div class="controls">
3
    <input id="search_field4" type="search" placeholder="Search">
4
  </div>
5
  <output class="console" id="output">0 results</output>
6
</div>
7
<div id="example4"></div>

Configuration options

Plugins

Searching values

Searching values

Overview

How query() works

Search result class

Custom queryMethod

Custom result callback

Per-cell queryMethod and callback

Simplest use case

Custom search result class

Custom query method

Custom callback

Related API reference

How `query()` works

Custom `queryMethod`

Per-cell `queryMethod` and `callback`