Merge Excel Files into One with JavaScript in React

Merging Excel files is a common task that many people encounter when working with data. As projects expand or teams collaborate, you may find yourself with multiple spreadsheets that need to be combined into one cohesive document. This process not only helps in organizing information but also makes it easier to analyze and draw insights from your data. Whether you're dealing with financial records, project updates, or any other type of data, knowing how to merge Excel files effectively can save you time and effort. In this guide, we'll explain how to programmatically merge Excel files into one in React using Spire.XLS for JavaScript.

Install Spire.XLS for JavaScript

To get started with merging Excel files into one in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:

npm i spire.xls

After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project. Additionally, include the required font files to ensure accurate and consistent text rendering.

For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project

Merge Multiple Excel Workbooks into One

Combining multiple Excel workbooks allows you to merge distinct files into a single workbook, which simplifies the management and analysis of diverse datasets for comprehensive reporting.

With Spire.XLS for JavaScript, developers can efficiently merge multiple workbooks by copying worksheets from the source workbooks into a newly created workbook using the XlsWorksheetsCollection.AddCopy() method. The key steps are as follows.

  • Put the file paths of the workbooks to be merged into a list.
  • Initialize a Workbook object to create a new workbook and clear its default worksheets.
  • Initialize a temporary Workbook object.
  • Loop through the list of file paths.
  • Load each workbook specified by the file path in the list into the temporary Workbook object using Workbook.LoadFromFile() method.
  • Loop through the worksheets in the temporary workbook, then copy each worksheet from the temporary workbook to the newly created workbook using XlsWorksheetsCollection.AddCopy() method.
  • Save the resulting workbook using Workbook.SaveToFile() method.
  • JavaScript
import React, { useState, useEffect } from 'react';

function App() {

  // State to hold the loaded WASM module
  const [wasmModule, setWasmModule] = useState(null);

  // useEffect hook to load the WASM module when the component mounts
  useEffect(() => {
    const loadWasm = async () => {
      try {

        // Access the Module and spirexls from the global window object
        const { Module, spirexls } = window;

        // Set the wasmModule state when the runtime is initialized
        Module.onRuntimeInitialized = () => {
          setWasmModule(spirexls);
        };
      } catch (err) {

        // Log any errors that occur during loading
        console.error('Failed to load WASM module:', err);
      }
    };

    // Create a script element to load the WASM JavaScript file

    const script = document.createElement('script');
    script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
    script.onload = loadWasm;

    // Append the script to the document body
    document.body.appendChild(script);

    // Cleanup function to remove the script when the component unmounts
    return () => {
      document.body.removeChild(script);
    };
  }, []); 

  // Function to merge Excel workbooks into one
  const MergeExcelWorkbooks = async () => {
    if (wasmModule) {
      // Load the ARIALUNI.TTF font file into the virtual file system (VFS)
      await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
      
      // Load the Excel files into the virtual file system (VFS)
      const files = [
        "File1.xlsx",
        "File2.xlsx",
        "File3.xlsx",
      ];
      for (const file of files) {
        await wasmModule.FetchFileToVFS(file, "", `${process.env.PUBLIC_URL}/`);
      }
      
      // Create a new workbook
      let newbook = wasmModule.Workbook.Create();
        newbook.Version = wasmModule.ExcelVersion.Version2013;
        // Clear the default worksheets
        newbook.Worksheets.Clear();
        
        // Create a temp workbook
        let tempbook = wasmModule.Workbook.Create();

        for (const file of files) {
          // Load the current file
          tempbook.LoadFromFile(file.split("/").pop());

          for (let i = 0; i < tempbook.Worksheets.Count; i++) {
            let sheet = tempbook.Worksheets.get(i);
            // Copy every sheet in the current file to the new workbook
            wasmModule.XlsWorksheetsCollection.Convert(
              newbook.Worksheets
            ).AddCopy({
              sheet: sheet,
              flags: wasmModule.WorksheetCopyType.CopyAll,
            });
          }
        }

        let outputFileName = "MergeExcelWorkbooks.xlsx";
        // Save the resulting file
        newbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
      
      // Read the saved file and convert it to a Blob object
      const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
      const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      
      // Create a URL for the Blob and initiate the download
      const url = URL.createObjectURL(modifiedFile);
      const a = document.createElement('a');
      a.href = url;
      a.download = outputFileName;
      document.body.appendChild(a);
      a.click(); 
      document.body.removeChild(a); 
      URL.revokeObjectURL(url); 

      // Clean up resources used by the workbooks
      newbook.Dispose();
      tempbook.Dispose();
    }
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Merge Multiple Excel Workbooks into One Using JavaScript in React</h1>
      <button onClick={MergeExcelWorkbooks} disabled={!wasmModule}>
        Merge
      </button>
    </div>
  );
}

export default App;

Run the code to launch the React app at localhost:3000. Once it's running, click on the "Merge" button to merge multiple Excel workbooks into one:

Run the code to launch the React app

The screenshot below showcases the input workbooks and the output workbook:

Merge Multiple Excel Workbooks into One

Merge Multiple Excel Worksheets into One

Consolidating multiple worksheets into a single sheet enhances clarity and provides a comprehensive overview of related information.

Using Spire.XLS for JavaScript, developers can merge multiple worksheets by copying the used data ranges in these worksheets into a single worksheet using the CellRange.Copy() method. The key steps are as follows.

  • Initialize a Workbook object and load an Excel workbook using Workbook.LoadFromFile() method.
  • Get the two worksheets to be merged using Workbook.Worksheets.get() method.
  • Get the used data range of the second worksheet using Worksheet.AllocatedRange property.
  • Specify the destination range in the first worksheet using Worksheet.Range.get() method.
  • Copy the used data range from the second worksheet to the specified destination range in the first worksheet using CellRange.Copy() method.
  • Remove the second worksheet from the workbook.
  • Save the resulting workbook using Workbook.SaveToFile() method.
  • JavaScript
import React, { useState, useEffect } from 'react';

function App() {

  // State to hold the loaded WASM module
  const [wasmModule, setWasmModule] = useState(null);

  // useEffect hook to load the WASM module when the component mounts
  useEffect(() => {
    const loadWasm = async () => {
      try {

        // Access the Module and spirexls from the global window object
        const { Module, spirexls } = window;

        // Set the wasmModule state when the runtime is initialized
        Module.onRuntimeInitialized = () => {
          setWasmModule(spirexls);
        };
      } catch (err) {

        // Log any errors that occur during loading
        console.error('Failed to load WASM module:', err);
      }
    };

    // Create a script element to load the WASM JavaScript file
    const script = document.createElement('script');
    script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
    script.onload = loadWasm;

    // Append the script to the document body
    document.body.appendChild(script);

    // Cleanup function to remove the script when the component unmounts
    return () => {
      document.body.removeChild(script);
    };
  }, []); 

  // Function to merge worksheets in an Excel workbook into one
  const MergeWorksheets = async () => {
    if (wasmModule) {     
      // Load the sample Excel file into the virtual file system (VFS)
      let excelFileName = 'Sample.xlsx';
      await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);

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

      // Load the Excel file from the virtual file system
      workbook.LoadFromFile(excelFileName);

      // Get the first worksheet
      let sheet1 = workbook.Worksheets.get(0);
      // Get the second worksheet
      let sheet2 = workbook.Worksheets.get(1);

      // Get the used range in the second worksheet
      let fromRange = sheet2.AllocatedRange;
      // Specify the destination range in the first worksheet
      let toRange = sheet1.Range.get({row: sheet1.LastRow + 1, column: 1});

      // Copy the used range from the second worksheet to the destination range in the first worksheet
      fromRange.Copy({ destRange: toRange });

      // Remove the second worksheet
      sheet2.Remove();

      // Define the output file name
      const outputFileName = "MergeWorksheets.xlsx";

      // Save the workbook to the specified path
      workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });

      // Read the saved file and convert it to a Blob object
      const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
      const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      
      // Create a URL for the Blob and initiate the download
      const url = URL.createObjectURL(modifiedFile);
      const a = document.createElement('a');
      a.href = url;
      a.download = outputFileName;
      document.body.appendChild(a);
      a.click(); 
      document.body.removeChild(a); 
      URL.revokeObjectURL(url); 

      // Clean up resources used by the workbook
      workbook.Dispose();
    }
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Merge Multiple Excel Worksheets into One Using JavaScript in React</h1>
      <button onClick={MergeWorksheets} disabled={!wasmModule}>
        Merge
      </button>
    </div>
  );
}

export default App;

Merge Multiple Excel Worksheets into One

Get a Free License

To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.