PDF has a fixed layout and is easy to distribute, but once its content has been generated, it is difficult to modify within the body text. For documents such as contracts, reports, and notices, you often need to indicate the confidentiality level, copyright ownership, or usage states such as "Draft" and "Sample" without affecting the reading of the body content. Besides the text watermarks mentioned above, image watermarks made from a company logo, seal, or warning image are also quite common: they float over the content as semi-transparent images, conveying the brand and status information clearly without harming the readability of the original.

Spire.PDF for JavaScript loads, draws, and saves PDFs directly in the browser via WebAssembly, managing input and output files through a virtual file system (VFS) without requiring a backend server. Image watermarking usually takes two forms: one places a single image watermark at a specified position on the page (such as the center of the page), which can be achieved directly by loading the image with PdfImage.FromFile and combining the transparency settings of the page canvas with the DrawImage method; the other repeats the image across the whole page, which can be done with the PdfTilingBrush tiling brush.

This article covers two core features:

• Add a Single Image Watermark to PDF
• Add a Tiled Image Watermark to PDF

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

Add a Single Image Watermark to PDF

A single image watermark places a semi-transparent image at a specified position on the page (in this example, the center of each page), suitable for placing a company logo or warning sign in a prominent position of the document. The approach is as follows: load the image from a file with PdfImage.FromFile; then, on the canvas of each page, save the state with Save, set the transparency and blend mode with SetTransparency, draw the image at the centered position with DrawImage, and finally restore the canvas state with Restore.

function App() {
  const addSingleImageWatermark = async () => {
    // Get the Spire.PDF WASM module
    const pdfModule = window.wasmModule?.spirepdf;

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

    // Load the PDF file to be watermarked into VFS
    const inputFileName = 'Lease_Agreement_EN.pdf';
    await window.spire.FetchFileToVFS(inputFileName, "", `${process.env.PUBLIC_URL}/data/`);

    // Load the watermark image into VFS
    const inputImageName = 'logo.png';
    await window.spire.FetchFileToVFS(inputImageName, "", `${process.env.PUBLIC_URL}/data/`);

    // Create a PdfDocument object and load the PDF document
    let doc = new pdfModule.PdfDocument();
    doc.LoadFromFile(inputFileName);

    // Load the watermark image from a file
    let image = pdfModule.PdfImage.FromFile(inputImageName);

    // Loop through all the pages in the document
    for (let i = 0; i < doc.Pages.Count; i++) {
      // Get the specified page
      let page = doc.Pages.get_Item(i);

      // Save the canvas state, and set the semi-transparency (alpha 0.5) with the Multiply blend mode
      page.Canvas.Save();
      page.Canvas.SetTransparency({ alphaPen: 0.5, alphaBrush: 0.5, blendMode: pdfModule.PdfBlendMode.Multiply });

      // Compute the centered drawing position: subtract the image size from the page size and halve the result
      let position = new pdfModule.PointF(
        (page.Canvas.Size.Width - image.Width) / 2,
        (page.Canvas.Size.Height - image.Height) / 2
      );

      // Draw the watermark image at the center of the page
      page.Canvas.DrawImage({ image: image, point: position });

      // Restore the previous state of the canvas
      page.Canvas.Restore();
    }

    // Define the output file name and save the document
    const outputFileName = 'SingleImageWatermark.pdf';
    doc.SaveToFile(outputFileName);
    doc.Close();

    // Read the generated file from VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/pdf' });
    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 Single Image Watermark To PDF</h1>
      <button onClick={addSingleImageWatermark}>
        Generate
      </button>
    </div>
  );
}

export default App;

PDF document after adding the single image watermark

Add a Tiled Image Watermark to PDF

When you need the watermark to fill the whole page and form a faint background texture, use a tiled image watermark. The approach is as follows: load the image with PdfImage.FromFile; divide the page into tiling cells according to the page size with PdfTilingBrush; inside the graphics context of the brush, lower the transparency with SetTransparency and draw the image within the cell with DrawImage; finally fill the whole page with the brush using DrawRectangle so that the image repeats row by row and column by column, covering the entire page.

function App() {
  const addTiledImageWatermark = async () => {
    // Get the Spire.PDF WASM module
    const pdfModule = window.wasmModule?.spirepdf;

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

    // Load the PDF file to be watermarked into VFS
    const inputFileName = 'Lease_Agreement_EN.pdf';
    await window.spire.FetchFileToVFS(inputFileName, "", `${process.env.PUBLIC_URL}/data/`);

    // Load the watermark image into VFS
    const inputImageName = 'logo.png';
    await window.spire.FetchFileToVFS(inputImageName, "", `${process.env.PUBLIC_URL}/data/`);

    // Create a PdfDocument object and load the PDF document
    let doc = new pdfModule.PdfDocument();
    doc.LoadFromFile(inputFileName);

    // Load the watermark image from a file
    let image = pdfModule.PdfImage.FromFile(inputImageName);

    // Loop through all the pages in the document
    for (let i = 0; i < doc.Pages.Count; i++) {
      // Get the specified page
      let page = doc.Pages.get_Item(i);

      // Create a tiling brush: use one third of the page width and one fifth of the page height as the tiling cell
      let size = new pdfModule.SizeF({
        width: page.Canvas.Size.Width / 3,
        height: page.Canvas.Size.Height / 5
      });
      let brush = new pdfModule.PdfTilingBrush({ size: size });

      // Set the watermark transparency to 30%
      brush.Graphics.SetTransparency({ alpha: 0.3 });

      // Draw the watermark image at the center of the tiling cell
      let point = new pdfModule.PointF(
        (brush.Size.Width - image.Width) / 2,
        (brush.Size.Height - image.Height) / 2
      );
      brush.Graphics.DrawImage({ image: image, point: point });

      // Fill a whole-page rectangle with the tiling brush so that the image watermark tiles across the entire page
      let rect = new pdfModule.RectangleF({
        location: new pdfModule.PointF(0, 0),
        size: page.Canvas.Size
      });
      page.Canvas.DrawRectangle({ brush: brush, rectangle: rect });
    }

    // Define the output file name and save the document
    const outputFileName = 'TiledImageWatermark.pdf';
    doc.SaveToFile(outputFileName);
    doc.Close();

    // Read the generated file from VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/pdf' });
    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 Tiled Image Watermark To PDF</h1>
      <button onClick={addTiledImageWatermark}>
        Generate
      </button>
    </div>
  );
}

export default App;

PDF document after adding the tiled image watermark

FAQ

How to control the transparency of an image watermark

Reason: An image watermark is layered over the body content as an image; if its transparency is not lowered, it will obscure the content underneath.

Solution: Before drawing, save the canvas state with Save, and then set the transparency with SetTransparency, whose value is specified by alphaPen and alphaBrush ranging from 0 (fully transparent) to 1 (opaque). To make the watermark blend naturally with the background, you can also specify a blend mode through blendMode, such as PdfBlendMode.Multiply. After drawing, restore the canvas state with Restore to avoid affecting subsequent drawing:

// Save the canvas state, set the semi-transparency and the Multiply blend mode, then draw the watermark image
page.Canvas.Save();
page.Canvas.SetTransparency({ alphaPen: 0.5, alphaBrush: 0.5, blendMode: pdfModule.PdfBlendMode.Multiply });
page.Canvas.DrawImage({ image: image, point: position });
page.Canvas.Restore();

How to specify the position of an image watermark

Reason: If no position is passed, DrawImage draws the image at the default coordinates, making it impossible to precisely control where the watermark lands.

Solution: DrawImage supports passing a PointF position or x, y coordinates. To center the image, subtract the image size from the page size and halve the result to get the centering coordinates:

// Compute the centered position: subtract the image size from the page size and halve the result
let point = new pdfModule.PointF(
  (page.Canvas.Size.Width - image.Width) / 2,
  (page.Canvas.Size.Height - image.Height) / 2
);

// Draw the watermark image at the specified position
page.Canvas.DrawImage({ image: image, point: point });

How to adjust the density of a tiled image watermark

Reason: The density of a tiled image watermark is determined by the size (tiling cell size) of the PdfTilingBrush tiling brush.

Solution: The smaller the tiling cell, the denser the repeated images; the larger the cell, the sparser they are. Set size to a certain ratio of the page width and height to tile the images row by row and column by column — for example, using one third of the page width and one fifth of the page height as one cell yields a moderately spaced watermark texture. To make it sparser, increase the divisor (such as Width / 4); to make it denser, decrease the divisor:

// Create a tiling brush: use one third of the page width and one fifth of the page height as the tiling cell
let size = new pdfModule.SizeF({
  width: page.Canvas.Size.Width / 3,
  height: page.Canvas.Size.Height / 5
});
let brush = new pdfModule.PdfTilingBrush({ size: size });

Get a Free License

If you want to remove the evaluation messages in the resulting documents or get rid of functional limitations, contact sales to obtain a 30-day temporary license.

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.