Editing Nested FlexSheet Group Rows in React

Background

FlexSheet can display hierarchical data by creating a Wijmo FlexGrid with childItemsPath and then using that grid as the source for a FlexSheet Sheet.

In this scenario, parent rows are rendered as tree/group rows. These rows are read-only by default, so users can edit child rows but not the main parent rows. To allow editing on those parent rows, you can clear the row-level read-only setting after the rows are loaded.

You can also preserve formula display behavior by customizing the FlexSheet cell factory so formulas show their calculated results when the cell is not being edited.

Steps to Complete

1. Install and import the required Wijmo React packages.
2. Create nested hierarchical data.
3. Initialize the FlexSheet and enable editing for tree/group rows.
4. Create a FlexGrid-backed sheet with childItemsPath.
5. Display formula results when cells are not being edited.
6. Optionally cascade parent row edits to child rows.
7. Add row insertion helpers.
8. Add buttons for inserting rows.

Getting Started

Install and import the required Wijmo React packages

npm install @mescius/wijmo.react.grid.sheet @mescius/wijmo.grid @mescius/wijmo.grid.sheet @mescius/wijmo.styles

import React, { useCallback, useMemo, useRef } from 'react';
import '@mescius/wijmo.styles/wijmo.css';

import { FlexSheet } from '@mescius/wijmo.react.grid.sheet';
import * as wjGrid from '@mescius/wijmo.grid';
import * as wjSheet from '@mescius/wijmo.grid.sheet';

import './styles.css';

• The wijmo.react.grid.sheet package provides the React FlexSheet component. The wijmo.grid and wijmo.grid.sheet packages are used to create the backing FlexGrid and attach it to the FlexSheet as a sheet.

Create nested hierarchical data

function getSampleData() {
  return [
    {
      name: 'Jack Smith',
      checks: [
        {
          name: 'check1',
          earnings: [
            { name: 'hourly', hours: 30, rate: 15 },
            { name: 'overtime', hours: 10, rate: 20 },
            { name: 'bonus', hours: 5, rate: 30 }
          ]
        },
        {
          name: 'check2',
          earnings: [
            { name: 'hourly', hours: 20, rate: 18 },
            { name: 'overtime', hours: 20, rate: 24 }
          ]
        }
      ]
    }
  ];
}

• Each parent item contains child arrays. In this example, workers contain checks, and each check contains earnings.

Initialize the FlexSheet and enable editing for tree/group rows

const childItemsPath = ['checks', 'earnings', 'earnings'];

function App() {
  const flexSheetRef = useRef(null);
  const data = useMemo(() => getSampleData(), []);

  const initialized = useCallback((flexSheet) => {
    flexSheetRef.current = flexSheet;
    flexSheet.autoGenerateColumns = false;

    updateFactoryFnToShowFormulaResult(flexSheet);

    flexSheet.loadedRows.addHandler((s) => {
      const activeSheet = s.selectedSheet;

      if (activeSheet && activeSheet.grid && activeSheet.grid.childItemsPath) {
        s.rows.forEach((row) => {
          row.isReadOnly = false;
        });
      }
    });

    flexSheet.selectedSheetChanged.addHandler((s) => {
      const activeSheet = s.selectedSheet;

      if (activeSheet && activeSheet.grid) {
        s.childItemsPath = activeSheet.grid.childItemsPath;
      }

      if (activeSheet && activeSheet._collapseInfo) {
        const info = activeSheet._collapseInfo;

        s.rows.forEach((row) => {
          row.isCollapsed = !info.has(row.index);
        });
      }
    });

    flexSheet.groupCollapsedChanged.addHandler((s, e) => {
      let info = s.selectedSheet._collapseInfo;

      if (!info) {
        info = s.selectedSheet._collapseInfo = new Set();
      }

      if (s.rows[e.row].isCollapsed) {
        info.delete(e.row);
      } else {
        info.add(e.row);
      }
    });

    createTreeGridSheet(flexSheet, data);
    flexSheet.collapseGroupsToLevel(0);
  }, [data]);

  return (
    <div className="container-fluid">
      <FlexSheet initialized={initialized} />
    </div>
  );
}

• The loadedRows handler makes the tree/group rows editable. The selectedSheetChanged and groupCollapsedChanged handlers preserve the nested row configuration and collapsed state when switching sheets.

Create a FlexGrid-backed sheet with childItemsPath

function createTreeGridSheet(flexSheet, data) {
  const grid = new wjGrid.FlexGrid(document.createElement('div'), {
    childItemsPath,
    autoGenerateColumns: false,
    columns: [
      { binding: 'name' },
      { binding: 'hours', dataType: 'Number', format: 'n2' },
      { binding: 'rate', dataType: 'Number', format: 'n2' }
    ],
    itemsSource: data
  });

  const sheet = new wjSheet.Sheet(flexSheet, grid, 'TreeGridSheet');

  flexSheet.sheets.push(sheet);
}

• Instead of using addBoundSheet, create a FlexGrid with the nested itemsSource and childItemsPath, then create a Sheet from that grid. This lets FlexSheet display the nested structure while still supporting spreadsheet behavior.

Display formula results when cells are not being edited

function updateFactoryFnToShowFormulaResult(flexSheet) {
  const oldFn = flexSheet.cellFactory.updateCell;

  flexSheet.cellFactory.updateCell = function(panel, r, c, cell, rng) {
    oldFn.call(this, panel, r, c, cell, rng);

    if (panel.cellType !== wjGrid.CellType.Cell) {
      return;
    }

    const grid = panel.grid;

    if (rng && !rng.isSingleCell) {
      r = rng.row;
      c = rng.col;
    }

    if (
      grid.editRange &&
      grid.editRange.containsRow(r) &&
      grid.editRange.containsColumn(c)
    ) {
      return;
    }

    const data = grid.getCellData(r, c, true);

    if (typeof data === 'string' && data.startsWith('=')) {
      cell.innerText = grid.getCellValue(r, c, true);
    }
  };
}

• This keeps formulas editable while showing the calculated value when the cell is not in edit mode.

Optionally cascade parent row edits to child rows

function addCascadeEditHandler(flexSheet) {
  const bindingsToCascade = new Set(['hours', 'rate']);

  flexSheet.cellEditEnded.addHandler((s, e) => {
    const row = s.rows[e.row];
    const col = s.columns[e.col];
    const binding = col.binding;

    if (!row || !row.hasChildren || !bindingsToCascade.has(binding)) {
      return;
    }

    const value = row.dataItem[binding];

    updateChildren(row.dataItem, row.level, s.childItemsPath, binding, value);
    s.collectionView.refresh();
  });
}

function updateChildren(item, level, childItemsPath, binding, value) {
  const childPath = childItemsPath[Math.min(level, childItemsPath.length - 1)];
  const children = item[childPath];

  if (!Array.isArray(children)) {
    return;
  }

  children.forEach((child) => {
    child[binding] = value;
    updateChildren(child, level + 1, childItemsPath, binding, value);
  });
}

• Use this step only when editing a parent row should update its child rows. In this example, edits to hours and rate cascade to descendants.
• Call this helper from the initialized callback

Add row insertion helpers

function insertItemAtIndex(grid, index, mode, newItem) {
  const row = grid.rows[index];

  if (!row) {
    return;
  }

  if (mode === 'child') {
    const item = row.dataItem;
    const binding = grid.childItemsPath[
      Math.min(row.level, grid.childItemsPath.length - 1)
    ];

    if (!item[binding]) {
      item[binding] = [];
    }

    item[binding].unshift(newItem);
  } else {
    const parentCollection = getParentCollection(grid, row.index);
    const rowIndex = parentCollection.indexOf(row.dataItem);
    const delta = mode === 'above' ? 0 : 1;

    parentCollection.splice(rowIndex + delta, 0, newItem);
  }

  grid.collectionView.refresh();
}

function getParentCollection(grid, rowIndex) {
  const row = grid.rows[rowIndex];

  if (row.level === 0) {
    return grid.collectionView.sourceCollection;
  }

  for (let r = rowIndex - 1; r >= 0; r--) {
    const parentRow = grid.rows[r];

    if (parentRow.hasChildren && parentRow.level < row.level) {
      return parentRow.dataItem[grid.childItemsPath[parentRow.level]];
    }
  }

  return grid.collectionView.sourceCollection;
}

• These helpers allow users to add rows above, below, or as children of the selected row while keeping the nested source data in sync.

Add buttons for inserting rows

function App() {
  const flexSheetRef = useRef(null);
  const data = useMemo(() => getSampleData(), []);

  const insertItem = useCallback((mode) => {
    const flexSheet = flexSheetRef.current;

    if (!flexSheet) {
      return;
    }

    insertItemAtIndex(
      flexSheet,
      flexSheet.selection.row,
      mode,
      { name: 'newItem', hours: 5, rate: 5 }
    );
  }, []);

  return (
    <div className="container-fluid">
      <div>
        <button onClick={() => insertItem('above')}>Add above</button>
        <button onClick={() => insertItem('below')}>Add below</button>
        <button onClick={() => insertItem('child')}>Add child</button>
      </div>

      <FlexSheet initialized={initialized} />
    </div>
  );
}

• The buttons call the insertion helper using the currently selected FlexSheet row.

With this setup, FlexSheet can display nested hierarchical rows, allow editing on parent/group rows, preserve formula display behavior, and optionally cascade parent row edits to child rows.

Happy coding!