In daily Excel document processing, textboxes are often used to add explanatory text, annotations, or tips to data — whether adding comments to reports or extracting annotation content from existing documents, the add/remove/modify operations on textboxes are essential. Spire.XLS for JavaScript completes these operations directly in the browser based on WebAssembly, managing input and output files through a virtual file system (VFS), with no backend service required.

This article covers three core features:

• Add TextBox
• Extract Text and Image from TextBox
• Remove TextBox

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

Add TextBox

Adding textboxes to a worksheet provides supplementary explanations for data, such as operation guidance or notes. Spire.XLS for JavaScript inserts a textbox at a specified position with the Worksheet.TextBoxes.AddTextBox() method, after which you can set the text, alignment, font, and background color of the textbox, or fill it with a picture. 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.TextBoxes.AddTextBox() method to add the first textbox, and set its text, horizontal/vertical center alignment, font, and background color.
4. Use the Worksheet.TextBoxes.AddTextBox() method to add a second textbox and fill it with a picture.
5. Use the Workbook.SaveToFile() method to save the document to a specified path.

Here is a complete code example showing how to add two textboxes to a worksheet in React — one containing text and one filled with a picture:

function App() {
  const addTextBox = 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, Excel file and picture into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'TextBox.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
    await window.spire.FetchFileToVFS('logo.png', '', `${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);

    // Add the first textbox and set its position and size
    const textBox = sheet.TextBoxes.AddTextBox(3, 2, 50, 196);

    // Set the text in the textbox
    textBox.Text = 'Insert Excel TextBox';

    // Set the text to be centered horizontally and vertically
    textBox.HAlignment = xlsModule.CommentHAlignType.Center;
    textBox.VAlignment = xlsModule.CommentVAlignType.Center;

    // Set the font of the textbox (bold, white, 12pt)
    const font = workbook.CreateFont();
    font.FontName = 'Arial';
    font.Size = 12;
    font.IsBold = true;
    font.Color = xlsModule.Color.get_White();
    const rt = xlsModule.RichTextShape.Convert(textBox.RichText);
    rt.SetFont(0, textBox.Text.length - 1, font);

    // Set the background color of the textbox to blue-gray
    textBox.Fill.FillType = xlsModule.ShapeFillType.SolidColor;
    textBox.Fill.ForeKnownColor = xlsModule.ExcelColors.BlueGray;

    // Add the second textbox and set its position and size
    const textBox2 = sheet.TextBoxes.AddTextBox(6, 5, 90, 90);

    // Load a picture and fill the textbox with it
    textBox2.Fill.CustomPicture('logo.png');
    textBox2.Fill.FillType = xlsModule.ShapeFillType.Picture;

    // Set the border of the second textbox to 0
    textBox2.Line.Weight = 0;

    // Save the document
    const outputFileName = 'AddTextBox_output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Release resources
    workbook.Dispose();

    // Read the converted file from the VFS and trigger the 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>Add TextBox</h1>
      <button onClick={addTextBox}>
        Start
      </button>
    </div>
  );
}

export default App;

The result of adding the textboxes

Extract Text and Image from TextBox

When you need to aggregate or reuse annotation information in existing documents, you can iterate through the textboxes and extract their text content and fill images. Spire.XLS for JavaScript gets the number of textboxes with Worksheet.TextBoxes.Count and iterates over each textbox with the Worksheet.TextBoxes.get() method: it reads the Text property to obtain the text content, checks the fill type through Fill.FillType, and extracts the fill image through the Fill.Picture property, finally saving the results as a txt file and a png image file respectively. 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. Iterate over each textbox in the TextBoxes collection with Worksheet.TextBoxes.Count and Worksheet.TextBoxes.get().
4. Read the Text property of each textbox to collect the text content.
5. For a textbox filled with a picture, get its fill image through the Fill.Picture property and save it as a png file.
6. Write the collected text into a txt file.

Here is a complete code example showing how to extract text and images from a textbox in React:

function App() {
  const extractTextAndImage = 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 = 'TextBox.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);

    // Iterate over all textboxes in the worksheet and extract text and pictures
    const textLines = [];
    const pictureFiles = [];
    for (let i = sheet.TextBoxes.Count - 1; i >= 0; i--) {
      const shape = sheet.TextBoxes.get(i);

      // Extract the text in the textbox
      if (shape.Text) {
        textLines.push(shape.Text);
      }

      // Extract the fill picture of the textbox
      if (shape.Fill.FillType === xlsModule.ShapeFillType.Picture) {
        const picture = shape.Fill.Picture;
        const imageFile = 'ExtractedImage' + i + '.png';
        picture.Save(imageFile);
        pictureFiles.push(imageFile);
      }
    }

    // Save the extracted text as a txt file
    const textFile = 'ExtractedText.txt';
    window.dotnetRuntime.Module.FS.writeFile(textFile, textLines.join('\r\n'));

    // Release resources
    workbook.Dispose();

    // Read the extracted txt file from the VFS and trigger the download
    const txtArray = window.dotnetRuntime.Module.FS.readFile(textFile);
    const txtBlob = new Blob([txtArray], { type: 'text/plain' });
    const txtUrl = URL.createObjectURL(txtBlob);
    const txtAnchor = document.createElement('a');
    txtAnchor.href = txtUrl;
    txtAnchor.download = textFile;
    txtAnchor.click();
    URL.revokeObjectURL(txtUrl);

    // Read the extracted picture files from the VFS and trigger the downloads
    for (const imageFile of pictureFiles) {
      const imageArray = window.dotnetRuntime.Module.FS.readFile(imageFile);
      const imageBlob = new Blob([imageArray], { type: 'application/png' });
      const imageUrl = URL.createObjectURL(imageBlob);
      const imageAnchor = document.createElement('a');
      imageAnchor.href = imageUrl;
      imageAnchor.download = imageFile;
      imageAnchor.click();
      URL.revokeObjectURL(imageUrl);
    }
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Extract Text And Image From TextBox</h1>
      <button onClick={extractTextAndImage}>
        Start
      </button>
    </div>
  );
}

export default App;

The result of extracting the text and image from the textbox

Remove TextBox

When annotation information in a document is no longer needed, you can delete it to keep the worksheet tidy. Spire.XLS for JavaScript deletes a specified textbox by index with the Worksheet.TextBoxes.RemoveAt() 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.TextBoxes.RemoveAt() method to delete the textbox at a specified index.
4. Use the Workbook.SaveToFile() method to save the document to a specified path.

Here is a complete code example showing how to remove a textbox from a worksheet in React:

function App() {
  const removeTextBox = 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 = 'TextBox.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);

    // Remove the first textbox
    sheet.TextBoxes.RemoveAt(0);

    // Save the document
    const outputFileName = 'RemoveTextBox_output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Release resources
    workbook.Dispose();

    // Read the converted file from the VFS and trigger the 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>Remove TextBox</h1>
      <button onClick={removeTextBox}>
        Start
      </button>
    </div>
  );
}

export default App;

The result of removing the textbox

Frequently Asked Questions

The added textbox does not display in the result document

Cause: The row and column coordinates specified in the AddTextBox() method are out of range, or the font file was not loaded into the VFS, so the text in the textbox cannot be rendered properly.

Solution: Make sure the row and column coordinates are within the worksheet range, and ensure the required font has been loaded via FetchFileToVFS() before use, for example:

await window.spire.FetchFileToVFS(
  'ARIAL.TTF', '/Library/Fonts/', '/'
);

An error occurs when extracting an image due to the fill type

Cause: Accessing the Fill.Picture property directly only works for textboxes filled with a picture. If no image fill is set on the textbox (for example, a solid-color fill), accessing this property throws an exception.

Solution: Check whether the Fill.FillType of the textbox is Picture before accessing Fill.Picture; only then get the picture and call the Save() method to save it.

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.

In Excel document processing, formulas and functions are among the most essential capabilities — whether summing, averaging, or performing date and trigonometric operations, formulas make data processing automated and efficient. Spire.XLS for JavaScript completes the insertion and reading of formulas and functions directly in the browser based on WebAssembly, and manages input and output files through a virtual file system (VFS), with no backend service required.

This article covers two core features:

• Insert Formulas and Functions into an Excel Worksheet
• Read Formulas and Functions from an Excel Worksheet

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

Insert Formulas and Functions into an Excel Worksheet

The Formula property of the cell Range object returned by the Worksheet.Range.get() method in Spire.XLS for JavaScript can be used to add formulas or functions to specified cells in an Excel worksheet. The main steps for adding formulas and functions to an Excel worksheet are as follows:

1. Create a Workbook object.
2. Use the Workbook.Worksheets.get() method to get a specific worksheet.
3. Write data into cells and set the cell formatting.
4. Use the Range.Formula property to add formulas and functions to the specified cells of the worksheet.
5. Use the Workbook.SaveToFile() method to save the workbook.

Here is a complete code example showing how to insert mathematical operations, date functions, trigonometric functions, average functions, and sum functions into an Excel worksheet in React:

function App() {
  const insertFormulasAndFunctions = 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 into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a Workbook object
    const workbook = new xlsModule.Workbook();

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

    // Declare two variables: currentRow and currentFormula
    let currentRow = 1;
    let currentFormula = "";

    // Set the column width
    sheet.SetColumnWidth(1, 32);
    sheet.SetColumnWidth(2, 16);

    // Write data into cells
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Test Data";
    sheet.Range.get({ row: currentRow, column: 2 }).NumberValue = 1;
    sheet.Range.get({ row: currentRow, column: 3 }).NumberValue = 2;
    sheet.Range.get({ row: currentRow, column: 4 }).NumberValue = 3;
    sheet.Range.get({ row: currentRow, column: 5 }).NumberValue = 4;
    sheet.Range.get({ row: currentRow, column: 6 }).NumberValue = 5;
    currentRow += 2;
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Formula or Function";
    sheet.Range.get({ row: currentRow, column: 2 }).Value = "Result";

    // Set the cell formatting
    let range = sheet.Range.get({ row: currentRow, column: 1, lastRow: currentRow, lastColumn: 2 });
    range.Style.Font.FontName = "Arial";
    range.Style.KnownColor = xlsModule.ExcelColors.LightGreen;
    range.Style.FillPattern = xlsModule.ExcelPatternType.Solid;
    range.Style.Borders.get(xlsModule.BordersLineType.EdgeBottom).LineStyle = xlsModule.LineStyleType.Medium;
    range.Style.Font.IsBold = true;

    // Mathematical operation
    currentFormula = "=1/2+3*4";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Date function
    currentFormula = "=TODAY()";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Style.NumberFormat = "YYYY/MM/DD";

    // Trigonometric function
    currentFormula = "=SIN(PI()/6)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Average function
    currentFormula = "=AVERAGE(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Sum function
    currentFormula = "=SUM(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Save the workbook
    const outputFileName = 'InsertFormulasAndFunctions_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>Insert Formulas and Functions</h1>
      <button onClick={insertFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Insert formulas and function results into Excel worksheets

Read Formulas and Functions from an Excel Worksheet

To read formulas and functions from an Excel worksheet, you need to loop through all the used cells in the worksheet, then use the HasFormula property of a cell to find the cells that contain formulas or functions, and finally use the Range.Formula property to get the formulas or functions in those cells. The detailed steps are as follows:

1. Create a Workbook object.
2. Use the Workbook.LoadFromFile() method to load an Excel workbook.
3. Use the Workbook.Worksheets.get() method to get the first worksheet.
4. Loop through the used cells in the worksheet.
5. Use the HasFormula property to detect whether a cell contains a formula or function. If so, use the Range.RangeAddressLocal property and the Range.Formula property to get the cell name and its formula or function, and output the retrieved content.

Here is a complete code example showing how to loop through a worksheet and read the formulas and functions in React:

function App() {
  const readFormulasAndFunctions = 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 = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Create a Workbook object
    const workbook = new xlsModule.Workbook();

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

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

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

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

    // Release resources
    output.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>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Read formulas and function results from Excel worksheets

Frequently Asked Questions

HasFormula cannot detect the formula, and the loop returns no results

Cause: The formula in the target cell was actually written as text (using the Text/Value property instead of the Formula property), and HasFormula only returns true for real formulas.

Solution: Make sure to use the Range.Formula property when inserting; otherwise, re-assign the text as a formula before reading.

Confusing the Formula and FormulaNumberValue properties

Cause: The Formula property returns the formula string in the cell, while the FormulaNumberValue property returns the numeric result after the formula is calculated. The two return different content.

Solution: Use cell.Formula when you need the formula string, and cell.FormulaNumberValue when you need the numeric result after calculation. Choose the appropriate property based on your actual needs.

Get 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.

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.