Merge Excel Files in Java
Tags: java, mergeexcelfiles, mergeworksheets
Published: 2020-01-20 06:14:03

---
title: Merge Excel Files in Java
published: true
description: In this article, we will introduce how to merge Excel files in Java
tags: java, merge excel files, merge worksheets
---


In this article, we will introduce the following two possibilities to merge Excel files in Java:
1. Merge multiple Excel worksheets into a single worksheet
2. Merge multiple Excel files into a single file

**The library we used**:

Free Spire.XLS for Java

Before getting started, please download Free Spire.XLS for Java package through [this link](https://www.e-iceblue.com/Download/xls-for-java-free.html), unzip the package and then import Spire.Xls.jar from lib folder into our application.

1.Merge multiple Excel worksheets into a single worksheet

A quick way to merge data in multiple worksheets into a single worksheet is using DataTable. Free Spire.XLS for Java provides **_insertDataTable()_** and **_exportDataTable()_** methods that allow us to quickly export data from a worksheet into a data table and then insert the data table into another worksheet. However, this method **_will not maintain formatting_**.
```java
import com.spire.data.table.DataTable;
import com.spire.xls.*;

public class MergeWorksheets {
    public static void main(String[] args){
        //Create a workbook
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Get the second worksheet
        Worksheet sheet2 = workbook.getWorksheets().get(1);
        //Export data in the second worksheet into a data table
        DataTable dt2 = sheet2.exportDataTable();

        //Insert the data table into the first worksheet 
        sheet1.insertDataTable(dt2, true, sheet1.getLastRow() + 1,1);

        //Save the resultant file
        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}
```
If we want to keep formatting in worksheets, we can use **_CellRange.copy()_** method as shown in the below example.
```java
import com.spire.xls.*;

public class MergeWorksheets {
    public static void main(String[] args){
        //Create a workbook
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

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

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

        //Copy data along with formatting from sheet2 into sheet1
        sheet2.getAllocatedRange().copy(sheet1.getRange().get(sheet1.getLastRow() +1, 1));

        //Save the resultant file
        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}
```
2.Merge multiple Excel files into a single file

Free Spire.XLS for Java offers an **_addCopy()_** method for us to copy one worksheet in an Excel file to another Excel file.
```java
import com.spire.xls.*;

public class MergeExcels {
    public static void main(String[] args){
        //Input Excel files
        String[] inputFiles = new String[]{"file1.xlsx","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 Excel files, 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 resultant file
        newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
    }
}
```