How to Merge Excel Files with Java

Combining data from multiple Excel workbooks is a common requirement in reporting, data analysis, and document management workflows. Whether you are consolidating monthly reports, aggregating team submissions, or compiling financial records, programmatic merging is more efficient and less error-prone than manual copy-and-paste operations.

This article demonstrates two practical approaches for merging Excel files using Spire.XLS for Java: combining entire workbooks sheet by sheet, and consolidating data from multiple worksheets into a single summary sheet.

Adding the Library to Your Project

To use Spire.XLS for Java, add it as a dependency in your build configuration. For Maven projects, include the following in your 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>16.4.1</version>
    </dependency>
</dependencies>

Refresh your project to download the required JAR files. The library requires Java 8 or later. Note that a free version is available with limitations on the number of worksheets per workbook; a paid license removes these restrictions.

Approach 1: Merging Multiple Workbooks into One

This method creates a new workbook and copies every worksheet from each source file into it. The addCopy() method preserves data, cell formatting, formulas, and charts from the original sheets.

The following example merges three Excel files into a single output workbook:

import com.spire.xls.*;

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

        // Create a new workbook and remove default empty sheets
        Workbook newBook = new Workbook();
        newBook.getWorksheets().clear();

        // Temporary workbook to load each source file
        Workbook tempBook = new Workbook();

        // Loop through each file and copy all worksheets
        for (String file : inputFiles) {
            tempBook.loadFromFile(file);
            for (Worksheet sheet : (Iterable<Worksheet>) tempBook.getWorksheets()) {
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        // Save the merged result
        newBook.saveToFile("MergedWorkbook.xlsx", ExcelVersion.Version2016);
        newBook.dispose();
        tempBook.dispose();
    }
}

How this code works:

• getWorksheets().clear() removes the default blank sheet that a new workbook creates, giving you a clean starting point.
• loadFromFile() opens each source workbook into a temporary Workbook instance.
• addCopy() with WorksheetCopyType.CopyAll duplicates a worksheet while preserving all content, styles, and embedded objects.

Handling duplicate sheet names: If multiple source files contain sheets with identical names, the library automatically appends a numeric suffix to prevent naming conflicts. For example, a second "Sheet1" becomes "Sheet1(1)".

Approach 2: Consolidating Worksheets into a Single Sheet

In some scenarios, you may want to combine data from several worksheets into one consolidated sheet rather than keeping them as separate tabs. This approach copies data ranges and appends them sequentially below existing content.

The example below merges two worksheets from the same workbook into the first sheet:

import com.spire.xls.*;

public class MergeWorksheetsIntoSingle {
    public static void main(String[] args) {
        // Load the source workbook
        Workbook workbook = new Workbook();
        workbook.loadFromFile("input.xlsx");

        // Access the two worksheets to merge
        Worksheet sheet1 = workbook.getWorksheets().get(0);
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        // Identify the data range in the second sheet
        CellRange sourceRange = sheet2.getAllocatedRange();

        // Determine the destination: one row below the last row of sheet1
        CellRange destRange = sheet1.getCellRange(
            sheet1.getLastRow() + 1, 1
        );

        // Copy data from source range to destination
        sourceRange.copy(destRange);

        // Remove the now-empty second sheet
        sheet2.remove();

        // Save the result
        workbook.saveToFile("MergedSheets.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Key details:

• getAllocatedRange() automatically detects the area containing actual data, so you do not need to hard-code row and column boundaries.
• The destination row is calculated as sheet1.getLastRow() + 1, which places the copied data immediately below the existing content.
• Removing the source sheet after copying is optional. Skip the remove() call if you prefer to retain the original worksheets.

Excluding duplicate headers: When appending data from multiple sheets, each source sheet may have its own header row. To avoid repeating headers, adjust the source range to exclude the first row, or offset the copied data so only the header from the first sheet is retained.

Batch Processing Files from a Directory

When dealing with many files, you can extend the first approach to process all Excel files in a given folder automatically:

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

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

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

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

        newBook.saveToFile("MergedDirectory.xlsx", ExcelVersion.Version2016);
        newBook.dispose();
        tempBook.dispose();
    }
}

This batch approach is useful for automated report generation, monthly data aggregation, or any workflow where files accumulate over time.

Alternative Libraries for Excel Merging

Several other Java libraries can also handle Excel file merging, each with different design philosophies and trade-offs:

Library	License	Characteristics
Apache POI	Apache 2.0 (open source)	Mature and widely adopted; requires more manual code to preserve formatting during copy operations
EasyExcel	Apache 2.0 (open source)	Designed for memory efficiency with large files; uses a streaming approach
Aspose.Cells	Commercial	Offers a broad feature set including advanced chart and formatting support

Apache POI can merge files but typically requires explicit handling of styles and formatting to replicate them accurately. EasyExcel prioritizes low memory consumption and is well-suited for processing large datasets. Aspose.Cells provides functionality comparable to Spire.XLS under a commercial licensing model. The right choice depends on your project's licensing budget, file size requirements, and desired level of API abstraction.

Conclusion

Merging Excel files in Java with Spire.XLS for Java involves two primary strategies: copying entire worksheets between workbooks using the addCopy() method, which preserves all formatting, formulas, and data structures, or appending data ranges to consolidate information into a single sheet. Both approaches handle common challenges such as duplicate sheet names and header row management, and they can be extended with directory-based batch processing for larger volumes of files. The library's API abstracts away many low-level details of worksheet copying, though developers should be aware of the free version's worksheet limits when evaluating it for production use. For further exploration, the library's documentation covers additional merge-related scenarios including handling inconsistent headers across files, converting between formats like CSV to Excel before merging, and applying post-merge formatting adjustments.