How to merge Excel files using a free Java API

In the process of manipulating Excel files, it is a lot easier to process data in a single file instead of switching between numerous sources. In this article, I’m going to introduce how to copy data from one worksheet into another worksheet in the same Excel file, and how to copy sheets from multiple Excel workbooks into one workbook.

Dependency

I used a free Java API called Free Spire.XLS for Java in this tutorial. Before running codes, we need to insert the Jar file in the library into IDEA. You can download it from the website or directly reference it using the following Maven configurations.

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

How to copy data from one worksheet to another worksheet
I have an Excel file which includes two worksheets as below. Now I want to copy the data and its format 
in the second worksheet into the first worksheet. 

Use the following codes to achieve the effect I mentioned above.
import com.spire.xls.*;

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

        //Load a Excel file including two worksheets
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

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

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

        //Copy the data and format in the second worksheet into the first worksheet
        sheet2.getAllocatedRange().copy(sheet1.getRange().get(sheet1.getLastRow() +1, 1));

        //Save the resulting document
        workbook.saveToFile("output/MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}
Output

How to copy sheets from multiple Excel workbooks into one workbook
In addition to merging worksheets, Free Spire.XLS for Java supports merging multiple Excel files into 
a single file. The screenshot of the original files is shown as below.

Use the following codes to merge several Excel files into a single file.
import com.spire.xls.*;
public class MergeFiles {
    public static void main(String[] args) {
        //Input the two Excel files which are used to merge into a file
        String[] inputFiles = new String[]{"C:\\Users\\Test1\\Desktop\\file1.xlsx","C:\\Users\\Test1\\Desktop\\file2.xlsx"};

        //Create a new workbook
        Workbook newBook = new Workbook();

        //Clear all worksheets
        newBook.getWorksheets().clear();

        //Create another workbook
        Workbook tempBook = new Workbook();

        //Loop through the two Excel files, and copy worksheets in each Excel file into the new workbook
        for (String file : inputFiles)
        {
            tempBook.loadFromFile(file);
            for (Worksheet sheet : (Iterable<Worksheet>)tempBook.getWorksheets())
            {
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        //Save the resulting document
        newBook.saveToFile("output/MergeFiles.xlsx", ExcelVersion.Version2013);

    }
}
Output