Merge Excel Files in Java

#java #mergeexcelfiles #mergeworksheets

In this article, we will introduce the following two possibilities to merge Excel files in Java:

Merge multiple Excel worksheets into a single worksheet
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, 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.

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.

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.

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);
    }
}