Row pre-populating

Pre-populate new rows with default values when users add rows to the grid.

Basic spare rows

To keep one empty row at the bottom of the grid, set minSpareRows to 1.

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
registerAllModules();
4
const data = [
5
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
6
    ['2017', 10, 11, 12, 13],
7
    ['2018', 20, 11, 14, 13],
8
    ['2019', 30, 15, 12, 13],
9
];
10
const container = document.querySelector('#example1');
11
const hot = new Handsontable(container, {
12
    data,
13
    minSpareRows: 1,
14
    height: 'auto',
15
    licenseKey: 'non-commercial-and-evaluation',
16
    autoWrapRow: true,
17
    autoWrapCol: true,
18
});

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

4
registerAllModules();
5

6
const data = [
7
  ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
8
  ['2017', 10, 11, 12, 13],
9
  ['2018', 20, 11, 14, 13],
10
  ['2019', 30, 15, 12, 13],
11
];
12

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

15
const hot = new Handsontable(container, {
16
  data,
17
  minSpareRows: 1,
18
  height: 'auto',
19
  licenseKey: 'non-commercial-and-evaluation',
20
  autoWrapRow: true,
21
  autoWrapCol: true,
22
});

Spare rows with placeholder styling

To hint what to enter in the spare row, add a custom cell renderer that displays greyed-out placeholder text in empty cells. The renderer checks whether the whole row is empty, then shows a template value in a lighter color.

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { textRenderer } from 'handsontable/renderers/textRenderer';
4
registerAllModules();
5
const templateValues = ['one', 'two', 'three'];
6
const data = [
7
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
8
    ['2017', 10, 11, 12, 13],
9
    ['2018', 20, 11, 14, 13],
10
    ['2019', 30, 15, 12, 13],
11
];
12
function isEmptyRow(instance, row) {
13
    const rowData = instance.getDataAtRow(row);
14
    for (let i = 0, ilen = rowData.length; i < ilen; i++) {
15
        if (rowData[i] !== null) {
16
            return false;
17
        }
18
    }
19
    return true;
20
}
21
const defaultValueRenderer = (instance, td, row, col, prop, value, cellProperties) => {
22
    if (value === null && isEmptyRow(instance, row)) {
23
        value = templateValues[col];
24
        td.style.color = '#999';
25
    }
26
    else {
27
        td.style.color = '';
28
    }
29
    textRenderer(instance, td, row, col, prop, value, cellProperties);
30
};
31
const container = document.querySelector('#example2');
32
const hot = new Handsontable(container, {
33
    data,
34
    minSpareRows: 1,
35
    height: 'auto',
36
    licenseKey: 'non-commercial-and-evaluation',
37
    cells() {
38
        return { renderer: defaultValueRenderer };
39
    },
40
    autoWrapRow: true,
41
    autoWrapCol: true,
42
});

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { BaseRenderer } from 'handsontable/renderers';
4
import { textRenderer } from 'handsontable/renderers/textRenderer';
5

6
registerAllModules();
7

8
const templateValues = ['one', 'two', 'three'];
9
const data = [
10
  ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
11
  ['2017', 10, 11, 12, 13],
12
  ['2018', 20, 11, 14, 13],
13
  ['2019', 30, 15, 12, 13],
14
];
15

16
function isEmptyRow(instance: Handsontable, row: number) {
17
  const rowData = instance.getDataAtRow(row);
18

19
  for (let i = 0, ilen = rowData.length; i < ilen; i++) {
20
    if (rowData[i] !== null) {
21
      return false;
22
    }
23
  }
24

25
  return true;
26
}
27

28
const defaultValueRenderer: BaseRenderer = (instance, td, row, col, prop, value, cellProperties) => {
29
  if (value === null && isEmptyRow(instance, row)) {
30
    value = templateValues[col];
31
    td.style.color = '#999';
32
  } else {
33
    td.style.color = '';
34
  }
35

36
  textRenderer(instance, td, row, col, prop, value, cellProperties);
37
};
38

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

41
const hot = new Handsontable(container, {
42
  data,
43
  minSpareRows: 1,
44
  height: 'auto',
45
  licenseKey: 'non-commercial-and-evaluation',
46
  cells() {
47
    return { renderer: defaultValueRenderer };
48
  },
49
  autoWrapRow: true,
50
  autoWrapCol: true,
51
});

Auto-populating with template values

For full pre-population, use the beforeChange hook to fill all cells in a spare row with template values the moment the user starts editing. The isEmptyRow() helper detects whether the row is untouched, and the hook pushes changes for every column except the one the user is editing.

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

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

8
const templateValues = ['one', 'two', 'three'];
9
const data = [
10
  ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
11
  ['2017', 10, 11, 12, 13],
12
  ['2018', 20, 11, 14, 13],
13
  ['2019', 30, 15, 12, 13],
14
];
15

16
function isEmptyRow(instance, row) {
17
  const rowData = instance.getDataAtRow(row);
18

19
  for (let i = 0, ilen = rowData.length; i < ilen; i++) {
20
    if (rowData[i] !== null) {
21
      return false;
22
    }
23
  }
24

25
  return true;
26
}
27

28
const defaultValueRenderer = (instance, td, row, col, prop, value, cellProperties) => {
29
  if (value === null && isEmptyRow(instance, row)) {
30
    value = templateValues[col];
31
    td.style.color = '#999';
32
  } else {
33
    td.style.color = '';
34
  }
35

36
  textRenderer(instance, td, row, col, prop, value, cellProperties);
37
};
38

39
const container = document.querySelector('#example3');
40
const hot = new Handsontable(container, {
41
  startRows: 8,
42
  startCols: 5,
43
  minSpareRows: 1,
44
  contextMenu: true,
45
  height: 'auto',
46
  licenseKey: 'non-commercial-and-evaluation',
47
  cells() {
48
    return { renderer: defaultValueRenderer };
49
  },
50
  beforeChange(changes) {
51
    const instance = hot;
52
    const columns = instance.countCols();
53
    const rowColumnSeen = {};
54
    const rowsToFill = {};
55
    const ch = changes === null ? [] : changes;
56

57
    for (let i = 0; i < changes.length; i++) {
58
      // if oldVal is empty
59
      if (ch[i][2] === null && ch[i][3] !== null) {
60
        if (isEmptyRow(instance, ch[i][0])) {
61
          // add this row/col combination to the cache so it will not be overwritten by the template
62
          rowColumnSeen[`${ch[i][0]}/${ch[i][1]}`] = true;
63
          rowsToFill[ch[i][0]] = true;
64
        }
65
      }
66
    }
67

68
    for (const r in rowsToFill) {
69
      if (rowsToFill.hasOwnProperty(r)) {
70
        for (let c = 0; c < columns; c++) {
71
          // if it is not provided by user in this change set, take the value from the template
72
          if (!rowColumnSeen[`${r}/${c}`]) {
73
            changes.push([Number(r), c, null, templateValues[c]]);
74
          }
75
        }
76
      }
77
    }
78
  },
79
  autoWrapRow: true,
80
  autoWrapCol: true,
81
});
82

83
// or, use `updateData()` to replace `data` without resetting states
84
hot.loadData(data);

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import { BaseRenderer } from 'handsontable/renderers';
4
import { textRenderer } from 'handsontable/renderers/textRenderer';
5

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

9
const templateValues: string[] = ['one', 'two', 'three'];
10
const data: (string | number)[][] = [
11
  ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
12
  ['2017', 10, 11, 12, 13],
13
  ['2018', 20, 11, 14, 13],
14
  ['2019', 30, 15, 12, 13],
15
];
16

17
function isEmptyRow(instance: Handsontable, row: number) {
18
  const rowData = instance.getDataAtRow(row);
19

20
  for (let i = 0, ilen = rowData.length; i < ilen; i++) {
21
    if (rowData[i] !== null) {
22
      return false;
23
    }
24
  }
25

26
  return true;
27
}
28

29
const defaultValueRenderer: BaseRenderer = (instance, td, row, col, prop, value, cellProperties) => {
30
  if (value === null && isEmptyRow(instance, row)) {
31
    value = templateValues[col];
32
    td.style.color = '#999';
33
  } else {
34
    td.style.color = '';
35
  }
36

37
  textRenderer(instance, td, row, col, prop, value, cellProperties);
38
};
39

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

42
const hot = new Handsontable(container, {
43
  startRows: 8,
44
  startCols: 5,
45
  minSpareRows: 1,
46
  contextMenu: true,
47
  height: 'auto',
48
  licenseKey: 'non-commercial-and-evaluation',
49
  cells() {
50
    return { renderer: defaultValueRenderer };
51
  },
52
  beforeChange(changes) {
53
    const instance = hot;
54
    const columns = instance.countCols();
55
    const rowColumnSeen = {};
56
    const rowsToFill = {};
57
    const ch = changes === null ? [] : (changes as Handsontable.CellChange);
58

59
    for (let i = 0; i < changes.length; i++) {
60
      // if oldVal is empty
61
      if (ch[i][2] === null && ch[i][3] !== null) {
62
        if (isEmptyRow(instance, ch[i][0])) {
63
          // add this row/col combination to the cache so it will not be overwritten by the template
64
          rowColumnSeen[`${ch[i][0]}/${ch[i][1]}`] = true;
65
          rowsToFill[ch[i][0]] = true;
66
        }
67
      }
68
    }
69

70
    for (const r in rowsToFill) {
71
      if (rowsToFill.hasOwnProperty(r)) {
72
        for (let c = 0; c < columns; c++) {
73
          // if it is not provided by user in this change set, take the value from the template
74
          if (!rowColumnSeen[`${r}/${c}`]) {
75
            changes.push([Number(r), c, null, templateValues[c]]);
76
          }
77
        }
78
      }
79
    }
80
  },
81
  autoWrapRow: true,
82
  autoWrapCol: true,
83
});
84

85
// or, use `updateData()` to replace `data` without resetting states
86
hot.loadData(data);

Pre-populate from an adjacent row

You can copy values from the row above when the user inserts a new row. Use the afterCreateRow hook to read the source row’s data and write it to the newly created row.

1
const hot = new Handsontable(container, {
2
  data: [
3
    ['Product A', 10, 'active'],
4
    ['Product B', 20, 'inactive'],
5
  ],
6
  colHeaders: ['Name', 'Quantity', 'Status'],
7
  afterCreateRow(index) {
8
    if (index > 0) {
9
      const sourceRow = hot.getSourceDataAtRow(index - 1);
10

11
      sourceRow.forEach((value, col) => {
12
        hot.setDataAtCell(index, col, value);
13
      });
14
    }
15
  },
16
  licenseKey: 'non-commercial-and-evaluation',
17
});

Pre-populate from a server-fetched default

You can fetch default row values from a server and apply them when the user adds a new row. Use the afterCreateRow hook to trigger the request and populate the row once the response arrives.

1
const hot = new Handsontable(container, {
2
  data: [],
3
  colHeaders: ['Name', 'Quantity', 'Status'],
4
  afterCreateRow(index) {
5
    fetch('/api/row-defaults')
6
      .then((response) => response.json())
7
      .then((defaults) => {
8
        hot.setDataAtRow(index, [defaults.name, defaults.quantity, defaults.status]);
9
      });
10
  },
11
  licenseKey: 'non-commercial-and-evaluation',
12
});

Result

Your grid keeps one or more empty rows at the bottom. Depending on the approach, spare rows show greyed-out placeholder text or auto-fill all cells with template values when the user starts editing.

Hooks

afterCreateRow

beforeChange