In everyday Excel spreadsheet handling, grouping rows or columns lets you collapse detail data and show only summary information, making large tables cleaner and easier to read. Spire.XLS for JavaScript performs grouping and ungrouping directly in the browser based on WebAssembly, and manages input/output files through a virtual file system (VFS), with no backend service required.

This article covers two core features:

• Group Rows or Columns
• Ungroup Rows or Columns

For installation and project configuration, refer to Integrate Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module is initialized.

Group Rows or Columns

After grouping rows or columns, you can collapse the detail data inside a group and keep only the summary rows or columns you need, making the worksheet tidier. Spire.XLS for JavaScript groups rows with the GroupByRows() method and columns with the GroupByColumns() method. The main steps are as follows:

1. Create a Workbook object and use the LoadFromFile() method to load the Excel document.
2. Use the Workbook.Worksheets.get() method to get a specific worksheet.
3. Use the Worksheet.GroupByRows() method to group rows.
4. Use the Worksheet.GroupByColumns() method to group columns.
5. Use the Workbook.SaveToFile() method to save the document to a specified path.

Here is a complete code example showing how to group rows or columns in React:

function App() {
  const groupRowsAndColumns = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'GroupRowsAndColumns.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Group rows
    sheet.GroupByRows(6, 10, false);
    sheet.GroupByRows(14, 16, false);

    // Group columns
    sheet.GroupByColumns(2, 7, false);

    // Save the document
    const outputFileName = 'GroupRowsAndColumns_output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release resources
    workbook.Dispose();

    // Read the converted file from the VFS and trigger a download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Group Rows And Columns</h1>
      <button onClick={groupRowsAndColumns}>
        Start
      </button>
    </div>
  );
}

export default App;

After grouping, group markers appear on the left side of the grouped rows or above the grouped columns. Click a marker to collapse or expand the detail data.

Ungroup Rows or Columns

When the grouping structure is no longer needed, you can ungroup the existing groups so that all rows and columns return to their normal display. Spire.XLS for JavaScript ungroups rows with the UngroupByRows() method and columns with the UngroupByColumns() method. The main steps are as follows:

1. Create a Workbook object and use the LoadFromFile() method to load the Excel document that contains groups.
2. Use the Workbook.Worksheets.get() method to get a specific worksheet.
3. Use the Worksheet.UngroupByRows() method to ungroup rows.
4. Use the Worksheet.UngroupByColumns() method to ungroup columns.
5. Use the Workbook.SaveToFile() method to save the document to a specified path.

Here is a complete code example showing how to ungroup rows or columns in React:

function App() {
  const ungroupRowsAndColumns = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'GroupRowsAndColumns.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Ungroup rows
    sheet.UngroupByRows(6, 10);
    sheet.UngroupByRows(14, 16);

    // Ungroup columns
    sheet.UngroupByColumns(2, 7);

    // Save the document
    const outputFileName = 'UngroupRowsAndColumns_output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release resources
    workbook.Dispose();

    // Read the converted file from the VFS and trigger a download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Ungroup Rows And Columns</h1>
      <button onClick={ungroupRowsAndColumns}>
        Start
      </button>
    </div>
  );
}

export default App;

After ungrouping, the group markers on the rows or columns disappear and the data returns to the normal ungrouped display.

FAQ

Cannot collapse or expand detail data after grouping

Cause: The third parameter isCollapsed of the GroupByRows() and GroupByColumns() methods is set to false, so the groups are displayed expanded by default.

Solution: Set this parameter to true, and the groups will be displayed collapsed after saving:

sheet.GroupByRows(6, 10, true);

Some rows or columns still show group symbols after ungrouping

Cause: The UngroupByRows() and UngroupByColumns() methods only ungroup the rows or columns within the specified range. If these rows or columns also belong to a higher-level group, the higher-level group symbols are still retained.

Solution: Make sure the range passed when ungrouping matches the range used when grouping. If nested groups exist, call the ungroup methods repeatedly to ungroup level by level:

sheet.UngroupByRows(6, 10);
sheet.UngroupByRows(14, 16);
sheet.UngroupByColumns(2, 7);

Obtain a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.