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 { HotTable } from '@handsontable/react-wrapper';
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 ExampleComponent = () => {
14
  return (
15
    <HotTable
16
      data={data}
17
      minSpareRows={1}
18
      height="auto"
19
      autoWrapRow={true}
20
      autoWrapCol={true}
21
      licenseKey="non-commercial-and-evaluation"
22
    />
23
  );
24
};
25

26
export default ExampleComponent;

1
import { HotTable } from '@handsontable/react-wrapper';
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 ExampleComponent = () => {
14
  return (
15
    <HotTable
16
      data={data}
17
      minSpareRows={1}
18
      height="auto"
19
      autoWrapRow={true}
20
      autoWrapCol={true}
21
      licenseKey="non-commercial-and-evaluation"
22
    />
23
  );
24
};
25

26
export default ExampleComponent;

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 { HotTable } from '@handsontable/react-wrapper';
2
import { registerAllModules } from 'handsontable/registry';
3
import { textRenderer } from 'handsontable/renderers/textRenderer';
4

5
registerAllModules();
6

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

15
const ExampleComponent = () => {
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
  function defaultValueRenderer(instance, td, row, col) {
29
    const args = arguments;
30

31
    if (args[5] === null && isEmptyRow(instance, row)) {
32
      args[5] = templateValues[col];
33
      td.style.color = '#999';
34
    } else {
35
      td.style.color = '';
36
    }
37

38
    textRenderer.apply(this, args);
39
  }
40

41
  return (
42
    <HotTable
43
      data={data}
44
      minSpareRows={1}
45
      height="auto"
46
      autoWrapRow={true}
47
      autoWrapCol={true}
48
      licenseKey="non-commercial-and-evaluation"
49
      cells={() => ({ renderer: defaultValueRenderer })}
50
    />
51
  );
52
};
53

54
export default ExampleComponent;

1
import { HotTable } from '@handsontable/react-wrapper';
2
import { registerAllModules } from 'handsontable/registry';
3
import { textRenderer } from 'handsontable/renderers/textRenderer';
4
import Handsontable from 'handsontable/base';
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
const ExampleComponent = () => {
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
  function defaultValueRenderer(
30
    this: Handsontable,
31
    instance: Handsontable,
32
    td: HTMLTableCellElement,
33
    row: number,
34
    col: number
35
  ) {
36
    const args = arguments;
37

38
    if (args[5] === null && isEmptyRow(instance, row)) {
39
      args[5] = templateValues[col];
40
      td.style.color = '#999';
41
    } else {
42
      td.style.color = '';
43
    }
44

45
    textRenderer.apply(this, args as any);
46
  }
47

48
  return (
49
    <HotTable
50
      data={data}
51
      minSpareRows={1}
52
      height="auto"
53
      autoWrapRow={true}
54
      autoWrapCol={true}
55
      licenseKey="non-commercial-and-evaluation"
56
      cells={() => ({ renderer: defaultValueRenderer })}
57
    />
58
  );
59
};
60

61
export default ExampleComponent;

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 { useRef } from 'react';
2
import { HotTable } from '@handsontable/react-wrapper';
3
import { registerAllModules } from 'handsontable/registry';
4
import { textRenderer } from 'handsontable/renderers/textRenderer';
5

6
// register Handsontable's modules
7
registerAllModules();
8

9
const ExampleComponent = () => {
10
  const hotRef = useRef(null);
11
  const templateValues = ['one', 'two', 'three'];
12
  const data = [
13
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
14
    ['2017', 10, 11, 12, 13],
15
    ['2018', 20, 11, 14, 13],
16
    ['2019', 30, 15, 12, 13],
17
  ];
18

19
  function isEmptyRow(instance, row) {
20
    const rowData = instance.getDataAtRow(row);
21

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

28
    return true;
29
  }
30

31
  function defaultValueRenderer(instance, td, row, col) {
32
    const args = arguments;
33

34
    if (args[5] === null && isEmptyRow(instance, row)) {
35
      args[5] = templateValues[col];
36
      td.style.color = '#999';
37
    } else {
38
      td.style.color = '';
39
    }
40

41
    textRenderer.apply(this, args);
42
  }
43

44
  return (
45
    <>
46
      <HotTable
47
        ref={hotRef}
48
        data={data}
49
        startRows={8}
50
        startCols={5}
51
        minSpareRows={1}
52
        contextMenu={true}
53
        height="auto"
54
        autoWrapRow={true}
55
        autoWrapCol={true}
56
        licenseKey="non-commercial-and-evaluation"
57
        cells={function (row, col, prop) {
58
          const cellProperties = {};
59

60
          cellProperties.renderer = defaultValueRenderer;
61

62
          return cellProperties;
63
        }}
64
        beforeChange={function (changes) {
65
          const instance = hotRef.current?.hotInstance;
66
          const columns = instance?.countCols() || 0;
67
          const rowColumnSeen = {};
68
          const rowsToFill = {};
69

70
          for (let i = 0; i < changes.length; i++) {
71
            const cellChanges = changes;
72

73
            // if oldVal is empty
74
            if (cellChanges[i][2] === null && cellChanges[i][3] !== null) {
75
              if (isEmptyRow(instance, cellChanges[i][0])) {
76
                // add this row/col combination to the cache so it will not be overwritten by the template
77
                rowColumnSeen[`${cellChanges[i][0]}/${cellChanges[i][1]}`] = true;
78
                rowsToFill[cellChanges[i][0]] = true;
79
              }
80
            }
81
          }
82

83
          for (const r in rowsToFill) {
84
            if (rowsToFill.hasOwnProperty(r)) {
85
              for (let c = 0; c < columns; c++) {
86
                // if it is not provided by user in this change set, take the value from the template
87
                if (!rowColumnSeen[`${r}/${c}`]) {
88
                  changes.push([r, c, null, templateValues[c]]);
89
                }
90
              }
91
            }
92
          }
93
        }}
94
      />
95
    </>
96
  );
97
};
98

99
export default ExampleComponent;

1
import { useRef } from 'react';
2
import { HotTable, HotTableRef } from '@handsontable/react-wrapper';
3
import { registerAllModules } from 'handsontable/registry';
4
import { textRenderer } from 'handsontable/renderers/textRenderer';
5
import Handsontable from 'handsontable/base';
6
import { CellChange } from 'handsontable/common';
7

8
// register Handsontable's modules
9
registerAllModules();
10

11
const ExampleComponent = () => {
12
  const hotRef = useRef<HotTableRef>(null);
13

14
  const templateValues = ['one', 'two', 'three'];
15
  const data = [
16
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
17
    ['2017', 10, 11, 12, 13],
18
    ['2018', 20, 11, 14, 13],
19
    ['2019', 30, 15, 12, 13],
20
  ];
21

22
  function isEmptyRow(instance: Handsontable, row: number) {
23
    const rowData = instance.getDataAtRow(row);
24

25
    for (let i = 0, ilen = rowData.length; i < ilen; i++) {
26
      if (rowData[i] !== null) {
27
        return false;
28
      }
29
    }
30

31
    return true;
32
  }
33

34
  function defaultValueRenderer(
35
    this: Handsontable,
36
    instance: Handsontable,
37
    td: HTMLTableCellElement,
38
    row: number,
39
    col: number
40
  ) {
41
    const args = arguments;
42

43
    if (args[5] === null && isEmptyRow(instance, row)) {
44
      args[5] = templateValues[col];
45
      td.style.color = '#999';
46
    } else {
47
      td.style.color = '';
48
    }
49

50
    textRenderer.apply(this, args as any);
51
  }
52

53
  return (
54
    <>
55
      <HotTable
56
        ref={hotRef}
57
        data={data}
58
        startRows={8}
59
        startCols={5}
60
        minSpareRows={1}
61
        contextMenu={true}
62
        height="auto"
63
        autoWrapRow={true}
64
        autoWrapCol={true}
65
        licenseKey="non-commercial-and-evaluation"
66
        cells={function (row, col, prop) {
67
          const cellProperties: Handsontable.CellMeta = {};
68

69
          cellProperties.renderer = defaultValueRenderer;
70

71
          return cellProperties;
72
        }}
73
        beforeChange={function (changes) {
74
          const instance = hotRef.current?.hotInstance;
75
          const columns = instance?.countCols() || 0;
76
          const rowColumnSeen = {};
77
          const rowsToFill = {};
78

79
          for (let i = 0; i < changes.length; i++) {
80
            const cellChanges = changes as CellChange;
81

82
            // if oldVal is empty
83
            if (cellChanges[i][2] === null && cellChanges[i][3] !== null) {
84
              if (isEmptyRow(instance!, cellChanges[i][0])) {
85
                // add this row/col combination to the cache so it will not be overwritten by the template
86
                rowColumnSeen[`${cellChanges[i][0]}/${cellChanges[i][1]}`] = true;
87
                rowsToFill[cellChanges[i][0]] = true;
88
              }
89
            }
90
          }
91

92
          for (const r in rowsToFill) {
93
            if (rowsToFill.hasOwnProperty(r)) {
94
              for (let c = 0; c < columns; c++) {
95
                // if it is not provided by user in this change set, take the value from the template
96
                if (!rowColumnSeen[`${r}/${c}`]) {
97
                  changes.push([r, c, null, templateValues[c]]);
98
                }
99
              }
100
            }
101
          }
102
        }}
103
      />
104
    </>
105
  );
106
};
107

108
export default ExampleComponent;

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