Java: Merge Excel Files into One

When you are creating a report by referencing data from multiple Excel files, you may find that the process is quite time-consuming and may also cause confusion or lead to errors as you need to switch between different opened files. In such a case, combining these separate Excel files into a single Excel workbook is a great option to simplify your work. This article will demonstrate how to merge multiple Excel files into one using Spire.XLS for Java.

• Merge Multiple Excel Workbooks into One in Java
• Merge Multiple Excel Worksheets into One in Java

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.11.0</version>
    </dependency>
</dependencies>
    

Merge Multiple Excel Workbooks into One in Java

With Spire.XLS for Java, you can merge data from different Excel files into different worksheets of one Excel Workbook. The following are the steps to merge multiple Excel workbooks into one.

• Specify the input Excel files that need to be merged.
• Initialize a Workbook object to create a new Excel workbook, and then clear all default worksheets in the workbook using Workbook.getWorksheets().clear() method.
• Initialize another temporary Workbook object.
• Loop through all input Excel files, and load the current workbook into the temporary Workbook object using Workbook.loadFromFile() method.
• loop through the worksheets in the current workbook, and then copy each worksheet from the current workbook to the new workbook using Workbook.getWorksheets().addCopy() method.
• Save the new workbook to file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class MergeExcels {
    public static void main(String[] args){
        //Specify the input Excel files
        String[] inputFiles = new String[]{"Budget Summary.xlsx", "Income.xlsx", "Expenses.xlsx"};

        //Initialize a new Workbook object
        Workbook newBook = new Workbook();

        //Clear the default worksheets
        newBook.getWorksheets().clear();

        //Initialize another temporary Workbook object
        Workbook tempBook = new Workbook();

        //Loop through all input Excel files
        for (String file : inputFiles)
        {
            //Load the current workbook
            tempBook.loadFromFile(file);
            //Loop through the worksheets in the current workbook
            for (Worksheet sheet : (Iterable) tempBook.getWorksheets())
            {
                //Copy each worksheet from the current workbook to the new workbook
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        //Save the result file
        newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
    }
}

The input Excel files:

The merged Excel workbook:

Merge Multiple Excel Worksheets into One in Java

An Excel workbook can contain multiple worksheets, and there are times you may also need to merge these worksheets into a single worksheet. The following are the steps to merge multiple Excel worksheets in the same workbook into one worksheet.

• Initialize a Workbook object and load an Excel file using Workbook.loadFromFile() method.
• Get two worksheets that need to be merged using Workbook.getWorksheets().get(int Index) method. Note that the sheet index is zero-based.
• Get the used range of the second worksheet using Worksheet.getAllocatedRange() method.
• Specify the destination range in the first worksheet using Worksheet.getCellRange(int row, int column) method. Note that the row and column indexes are 1-based.
• Copy the used range of the second worksheet to the destination range in the first worksheet using CellRange.copy(CellRange destRange) method.
• Remove the second worksheet using Worksheet.remove() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class MergeExcelWorksheets {
    public static void main(String[] args){
        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("input.xlsx");

        //Get the first worksheet
        Worksheet sheet1 = workbook.getWorksheets().get(0);

        //Get the second worksheet
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        //Get the used range in the second worksheet
        CellRange sourceRange = sheet2.getAllocatedRange();

        //Specify the destination range in the first worksheet
        CellRange destRange = sheet1.getCellRange(sheet1.getLastRow() + 1, 1);

        //Copy the used range of the second worksheet to the destination range in the first worksheet
        sourceRange.copy(destRange);

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

        //Save the result file
        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}

The input Excel worksheets:

The merged Excel worksheets:

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.