Skip to content

Row pre-populating

Populate newly-added rows with predefined template values, using cell renderers.

Example

The example below shows how cell renderers can be used to populate the template values in empty rows. When a cell in the empty row is edited, the beforeChange callback fills the row with the template values.

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;

Hooks