JavaScript Data Grid Autocomplete cell type

JavaScript Data GridAutocomplete cell type

Collect user input with a list of choices, by using the autocomplete cell type.

Overview
Autocomplete flexible mode
Autocomplete strict mode
Autocomplete strict mode (Ajax)
Related articles
- Related guides
- Related API reference

Overview

You can complete the autocomplete cell type in three different ways:

Flexible mode
Strict mode
Strict mode using Ajax

This example uses the autocomplete feature in the default flexible mode. In this mode, the user can choose one of the suggested options while typing or enter a custom value that is not included in the suggestions.

{
    "name": "handsontable",
    "version": "1.0.0",
    "description": "",
    "dependencies": {
      "hyperformula": "^2.4.0",
      "handsontable": "14.6"
    }
  }

<!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1.0" />
      <title>Handsontable</title>
    </head>
  
    <body>
      <div id="example1" class="hot "></div>
    </body>
  </html>

#example1 > .ht_master {
    height: 120px;
}

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

const colors = [
  'yellow',
  'red',
  'orange and another color',
  'green',
  'blue',
  'gray',
  'black',
  'white',
  'purple',
  'lime',
  'olive',
  'cyan',
];

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

new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    ['BMW', 2017, 'black', 'black'],
    ['Nissan', 2018, 'blue', 'blue'],
    ['Chrysler', 2019, 'yellow', 'black'],
    ['Volvo', 2020, 'white', 'gray'],
  ],
  colHeaders: ['Car', 'Year', 'Chassis color', 'Bumper color'],
  columns: [
    {
      type: 'autocomplete',
      source: ['BMW', 'Chrysler', 'Nissan', 'Suzuki', 'Toyota', 'Volvo'],
      strict: false,
    },
    { type: 'numeric' },
    {
      type: 'autocomplete',
      source: colors,
      strict: false,
      visibleRows: 4,
    },
    {
      type: 'autocomplete',
      source: colors,
      strict: false,
      trimDropdown: false,
    },
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

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

const colors = [
  'yellow',
  'red',
  'orange and another color',
  'green',
  'blue',
  'gray',
  'black',
  'white',
  'purple',
  'lime',
  'olive',
  'cyan',
];

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

new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    ['BMW', 2017, 'black', 'black'],
    ['Nissan', 2018, 'blue', 'blue'],
    ['Chrysler', 2019, 'yellow', 'black'],
    ['Volvo', 2020, 'white', 'gray'],
  ],
  colHeaders: ['Car', 'Year', 'Chassis color', 'Bumper color'],
  columns: [
    {
      type: 'autocomplete',
      source: ['BMW', 'Chrysler', 'Nissan', 'Suzuki', 'Toyota', 'Volvo'],
      strict: false,
    },
    { type: 'numeric' },
    {
      type: 'autocomplete',
      source: colors,
      strict: false,
      visibleRows: 4,
    },
    {
      type: 'autocomplete',
      source: colors,
      strict: false,
      trimDropdown: false,
    },
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

<script src="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.js"></script>
<link type="text/css" rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.css" /> 
<script src="https://handsontable.com/docs/scripts/fixer.js"></script>

<div id="example1" class="hot "></div>

#example1 > .ht_master {
    height: 120px;
}

Autocomplete strict mode

This is the same example as above, the difference being that autocomplete now runs in strict mode. In this mode, the autocomplete cells will only accept values that are defined in the source array. The mouse and keyboard bindings are identical to the Handsontable cell type but with the differences below:

If there is at least one option visible, there always is a selection in HOT-in-HOT
When the first row is selected, pressing Arrow Up does not deselect HOT-in-HOT. Instead, it behaves as the Enter key but moves the selection in the main HOT upwards

In strict mode, the allowInvalid option determines the behaviour in the case of manual user input:

allowInvalid: true optional - allows manual input of a value that does not exist in the source, the field background is highlighted in red, and the selection advances to the next cell
allowInvalid: false - does not allow manual input of a value that does not exist in the source, the Enter key is ignored, and the editor field remains open

{
    "name": "handsontable",
    "version": "1.0.0",
    "description": "",
    "dependencies": {
      "hyperformula": "^2.4.0",
      "handsontable": "14.6"
    }
  }

<!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1.0" />
      <title>Handsontable</title>
    </head>
  
    <body>
      <div id="example2" class="hot "></div>
    </body>
  </html>

#example2 > .ht_master {
    height: 130px;
}

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

const colors = [
  'yellow',
  'red',
  'orange',
  'green',
  'blue',
  'gray',
  'black',
  'white',
  'purple',
  'lime',
  'olive',
  'cyan',
];

const cars = ['BMW', 'Chrysler', 'Nissan', 'Suzuki', 'Toyota', 'Volvo'];

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

new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    ['BMW', 2017, 'black', 'black'],
    ['Nissan', 2018, 'blue', 'blue'],
    ['Chrysler', 2019, 'yellow', 'black'],
    ['Volvo', 2020, 'white', 'gray'],
  ],
  colHeaders: [
    'Car<br>(allowInvalid true)',
    'Year',
    'Chassis color<br>(allowInvalid false)',
    'Bumper color<br>(allowInvalid true)',
  ],
  columns: [
    {
      type: 'autocomplete',
      source: cars,
      strict: true,
    },
    {},
    {
      type: 'autocomplete',
      source: colors,
      strict: true,
      allowInvalid: false,
    },
    {
      type: 'autocomplete',
      source: colors,
      strict: true,
      allowInvalid: true,
    },
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

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

const colors = [
  'yellow',
  'red',
  'orange',
  'green',
  'blue',
  'gray',
  'black',
  'white',
  'purple',
  'lime',
  'olive',
  'cyan',
];

const cars = ['BMW', 'Chrysler', 'Nissan', 'Suzuki', 'Toyota', 'Volvo'];

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

new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    ['BMW', 2017, 'black', 'black'],
    ['Nissan', 2018, 'blue', 'blue'],
    ['Chrysler', 2019, 'yellow', 'black'],
    ['Volvo', 2020, 'white', 'gray'],
  ],
  colHeaders: [
    'Car<br>(allowInvalid true)',
    'Year',
    'Chassis color<br>(allowInvalid false)',
    'Bumper color<br>(allowInvalid true)',
  ],
  columns: [
    {
      type: 'autocomplete',
      source: cars,
      strict: true,
    },
    {},
    {
      type: 'autocomplete',
      source: colors,
      strict: true,
      allowInvalid: false,
    },
    {
      type: 'autocomplete',
      source: colors,
      strict: true,
      allowInvalid: true,
    },
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

<script src="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.js"></script>
<link type="text/css" rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.css" /> 
<script src="https://handsontable.com/docs/scripts/fixer.js"></script>

<div id="example2" class="hot "></div>

#example2 > .ht_master {
    height: 130px;
}

Autocomplete strict mode (Ajax)

Autocomplete can also be used with Ajax data sources. In the example below, suggestions for the "Car" column are loaded from the server. To load data from a remote asynchronous source, assign a function to the 'source' property. The function should perform the server-side request and call the callback function when the result is available.

{
    "name": "handsontable",
    "version": "1.0.0",
    "description": "",
    "dependencies": {
      "hyperformula": "^2.4.0",
      "handsontable": "14.6"
    }
  }

<!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1.0" />
      <title>Handsontable</title>
    </head>
  
    <body>
      <div id="example3" class="hot "></div>
    </body>
  </html>

#example3 > .ht_master {
    height: 120px;
}

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

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

new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    ['BMW', 2017, 'black', 'black'],
    ['Nissan', 2018, 'blue', 'blue'],
    ['Chrysler', 2019, 'yellow', 'black'],
    ['Volvo', 2020, 'white', 'gray'],
  ],
  colHeaders: ['Car', 'Year', 'Chassis color', 'Bumper color'],
  columns: [
    {
      type: 'autocomplete',
      source(_query, process) {
        fetch('https://handsontable.com/docs/scripts/json/autocomplete.json')
          .then((response) => response.json())
          .then((response) => process(response.data));
      },
      strict: true,
    },
    {},
    {},
    {},
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

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

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

new Handsontable(container, {
  licenseKey: 'non-commercial-and-evaluation',
  data: [
    ['BMW', 2017, 'black', 'black'],
    ['Nissan', 2018, 'blue', 'blue'],
    ['Chrysler', 2019, 'yellow', 'black'],
    ['Volvo', 2020, 'white', 'gray'],
  ],
  colHeaders: ['Car', 'Year', 'Chassis color', 'Bumper color'],
  columns: [
    {
      type: 'autocomplete',
      source(_query, process) {
        fetch('https://handsontable.com/docs/scripts/json/autocomplete.json')
          .then((response) => response.json())
          .then((response) => process(response.data));
      },
      strict: true,
    },
    {},
    {},
    {},
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

<script src="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.js"></script>
<link type="text/css" rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.css" /> 
<script src="https://handsontable.com/docs/scripts/fixer.js"></script>

<div id="example3" class="hot "></div>

#example3 > .ht_master {
    height: 120px;
}