Editing Nested FlexSheet Group Rows in JavaScript

Background

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

When childItemsPath is used, parent rows are rendered as tree/group rows. These rows are read-only by default, so users can edit child rows but not parent rows unless the row-level read-only setting is cleared.

This article shows how to display nested rows in a JavaScript FlexSheet, allow editing on parent/group rows, preserve formula display behavior, and optionally update child rows when a parent row value changes.

Steps to Complete

1. Install and import the required Wijmo packages.
2. Add the FlexSheet host element and row insertion buttons.
3. Initialize the FlexSheet.
4. Create a FlexGrid-backed sheet with childItemsPath.
5. Make tree/group rows editable.
6. Display formula results when cells are not being edited.
7. Optionally cascade parent row edits to child rows.
8. Add helper methods for inserting rows.
9. Add sample data and styles.

Getting Started

Install and import the required Wijmo packages

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

import '@mescius/wijmo.styles/wijmo.css';
import './styles.css';

import * as wjGrid from '@mescius/wijmo.grid';
import * as wjSheet from '@mescius/wijmo.grid.sheet';

• The wijmo.grid.sheet package provides the FlexSheet and Sheet classes. The wijmo.grid package is used to create the backing FlexGrid.

Add the FlexSheet host element and row insertion buttons

<div class="container-fluid">
  <div class="toolbar">
    <button id="addAbove" type="button">Add above</button>
    <button id="addBelow" type="button">Add below</button>
    <button id="addChild" type="button">Add child</button>
  </div>

  <div id="flexSheet"></div>
</div>

• The #flexSheet element is the host element where the JavaScript FlexSheet control will be created.

Initialize the FlexSheet

let flexSheet;
const data = getSampleData();
const childItemsPath = ['checks', 'earnings', 'earnings'];

document.readyState === 'complete' ? init() : window.onload = init;

function init() {
  flexSheet = new wjSheet.FlexSheet('#flexSheet');
  flexSheet.autoGenerateColumns = false;

  updateFactoryFnToShowFormulaResult(flexSheet);
  addCascadeEditHandler(flexSheet);
  addFlexSheetEventHandlers(flexSheet);
  createTreeGridSheet(flexSheet);

  flexSheet.collapseGroupsToLevel(0);

  document.getElementById('addAbove').addEventListener('click', () => {
    insertItem('above');
  });

  document.getElementById('addBelow').addEventListener('click', () => {
    insertItem('below');
  });

  document.getElementById('addChild').addEventListener('click', () => {
    insertItem('child');
  });
}

• This creates the FlexSheet directly in JavaScript and wires up the buttons for row insertion.

Create a FlexGrid-backed sheet with childItemsPath

function createTreeGridSheet(sheet) {
  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 treeSheet = new wjSheet.Sheet(sheet, grid, 'TreeGridSheet');
  sheet.sheets.push(treeSheet);
}

• Instead of using addBoundSheet, create a FlexGrid with hierarchical data and pass it to the Sheet constructor. This allows FlexSheet to display nested rows using childItemsPath.

Make tree/group rows editable

function addFlexSheetEventHandlers(sheet) {
  sheet.loadedRows.addHandler((s) => {
    const activeSheet = s.selectedSheet;

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

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

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

    if (activeSheet && activeSheet._collapseInfo) {
      s.rows.forEach((row) => {
        row.isCollapsed = !activeSheet._collapseInfo.has(row.index);
      });
    }
  });

  sheet.groupCollapsedChanged.addHandler((s, e) => {
    const activeSheet = s.selectedSheet;

    if (!activeSheet._collapseInfo) {
      activeSheet._collapseInfo = new Set();
    }

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

• The loadedRows handler is the key piece. It sets row.isReadOnly = false, allowing users to edit parent/group rows in the FlexSheet.
• The other handlers preserve the nested row configuration and collapsed state when sheets are changed.

Display formula results when cells are not being edited

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

  sheet.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 cellData = grid.getCellData(r, c, true);

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

• This keeps formulas editable while showing the calculated result when the cell is not actively being edited.

Optionally cascade parent row edits to child rows

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

  sheet.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, paths, binding, value) {
  const childPath = paths[Math.min(level, paths.length - 1)];
  const children = item[childPath];

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

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

• Use this when a parent row edit should push the edited value down to its descendants. In this example, edits to hours and rate are cascaded.

Add helper methods for inserting rows

function insertItem(mode) {
  if (!flexSheet) {
    return;
  }

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

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 (!Array.isArray(item[binding])) {
      item[binding] = [];
    }

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

    parentCollection.splice(itemIndex + 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 collection synchronized.

Add sample data and styles

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 }
          ]
        }
      ]
    }
  ];
}

.toolbar {
  margin-bottom: 8px;
}

.toolbar button {
  margin-right: 6px;
}

.wj-flexsheet {
  height: 500px;
}

.wj-grouppanel .wj-remove {
  display: none;
}

With this setup, a JavaScript FlexSheet can display nested data, allow edits on group rows, preserve formula display behavior, and update child rows from parent edits when needed.

Happy coding!