How to Merge Excel Files in Java

In enterprise applications, working with Excel files is a common requirement. Whether it’s financial reporting, sales analysis, or data consolidation, merging Excel files is an essential task. Different business scenarios require different merging strategies. Here are some typical Excel merging scenarios:

• File-level merging: Combine multiple Excel files into a single workbook.

• Worksheet-level merging: Consolidate multiple worksheets into a single sheet.

• Batch merging: Merge all Excel files within a folder.

• Header-aligned merging: Merge data from files with inconsistent headers into a unified format.

Below, we’ll introduce several practical ways to merge Excel files in Java according to these scenarios.

Environment Setup and Dependencies

Before you start, prepare the following environment:

Add the Spire.XLS dependency to your project:

<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>16.3.2</version>
    </dependency>
</dependencies>

Example 1: Merge Multiple Excel Files into One

To merge multiple Excel files into a new workbook, copy each worksheet from the source files using Workbook.getWorksheets().addCopy(). This preserves all content, including data, formatting, formulas, charts, and conditional formatting. For large files, load files one by one to avoid high memory usage.

import com.spire.xls.*;

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

        Workbook newBook = new Workbook();
        newBook.getWorksheets().clear();
        Workbook tempBook = new Workbook();

        for (String file : inputFiles) {
            tempBook.loadFromFile(file);
            for (Worksheet sheet : (Iterable) tempBook.getWorksheets()) {
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
        System.out.println("Merge completed!");
    }
}

Example 2: Merge Multiple Worksheets into a Single Sheet

When consolidating data into a single summary sheet, select a master worksheet and append data from other sheets. Use CellRange.copy() to precisely copy ranges while maintaining cell styles and formats. Be careful to skip headers to avoid duplication.

import com.spire.xls.*;

public class MergeExcelWorksheets {
    public static void main(String[] args){
        Workbook workbook = new Workbook();
        workbook.loadFromFile("input.xlsx");

        Worksheet sheet1 = workbook.getWorksheets().get(0);
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        CellRange sourceRange = sheet2.getAllocatedRange();
        CellRange destRange = sheet1.getCellRange(sheet1.getLastRow() + 1, 1);

        sourceRange.copy(destRange);
        sheet2.remove();

        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
        System.out.println("Worksheet data merge completed!");
    }
}

Example 3: Batch Merge Excel Files from a Directory

To merge all Excel files in a folder, iterate through each file and copy worksheets in order. The addCopy() method works here too, making it efficient to handle large volumes of reports. For very large directories, load files in batches to reduce memory usage.

import com.spire.xls.*;
import java.io.File;

public class MergeExcelDirectory {
    public static void main(String[] args){
        File folder = new File("excel_folder");
        File[] files = folder.listFiles((dir, name) -> name.endsWith(".xlsx"));

        Workbook newBook = new Workbook();
        newBook.getWorksheets().clear();
        Workbook tempBook = new Workbook();

        for (File file : files) {
            tempBook.loadFromFile(file.getAbsolutePath());
            for (Worksheet sheet : (Iterable) tempBook.getWorksheets()) {
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        newBook.saveToFile("MergedDirectory.xlsx", ExcelVersion.Version2013);
        System.out.println("Batch merge completed!");
    }
}

Example 4: Merge Excel Files with Unified Headers

When source files have inconsistent or missing headers, define a unified header in the target worksheet, then copy each file’s data into the corresponding columns. CellRange.copy() ensures correct alignment, while incrementing row numbers prevents overwriting.

import com.spire.xls.*;

public class MergeWithHeader {
    public static void main(String[] args){
        String[] files = {"Jan.xlsx", "Feb.xlsx", "Mar.xlsx"};
        Workbook workbook = new Workbook();
        workbook.getWorksheets().clear();
        Worksheet masterSheet = workbook.getWorksheets().add("Summary");

        masterSheet.getCellRange("A1").setText("Date");
        masterSheet.getCellRange("B1").setText("Sales");
        masterSheet.getCellRange("C1").setText("Cost");

        int currentRow = 2;
        Workbook temp = new Workbook();

        for (String file : files) {
            temp.loadFromFile(file);
            Worksheet sheet = temp.getWorksheets().get(0);
            CellRange dataRange = sheet.getAllocatedRange();
            CellRange dest = masterSheet.getCellRange(currentRow, 1);
            dataRange.copy(dest);
            currentRow += dataRange.getRowCount();
        }

        workbook.saveToFile("MergedWithHeader.xlsx", ExcelVersion.Version2013);
        System.out.println("Data merge with unified headers completed!");
    }
}

Tips for Merging Excel Files in Java

• Always load large files one by one to manage memory efficiently.

• Ensure headers are consistent when consolidating data.

• Use addCopy() to preserve all worksheet elements including charts and formatting.

• Skip header rows when appending data to avoid duplication.

Conclusion

This article introduced four practical methods to merge Excel files in Java:

1. Merging multiple files into one workbook.

2. Consolidating multiple worksheets into a single sheet.

3. Batch merging all files in a directory.

4. Merging files with unified headers for consistent data structure.

Choosing the right method improves both the efficiency and accuracy of handling Excel data in enterprise applications.