DEV Community

lu liu
lu liu

Posted on

How to Merge Excel Files and Worksheets in Java

#automation #java #tutorial

Manually combining data from multiple Excel files or consolidating information across various worksheets can be a tedious and error-prone task. This often leads to inefficiencies and potential data integrity issues. Fortunately, Java, coupled with powerful libraries like Spire.XLS for Java, offers a robust solution for automating these merging operations, saving time and ensuring accuracy. This tutorial will guide you through the process of how to merge Excel in Java.

Introduction to Spire.XLS for Java & Installation

Spire.XLS for Java is a professional Excel API that enables developers to create, read, write, convert, and print Excel files in Java applications without needing Microsoft Office installed. It supports a wide range of Excel features, making it an ideal choice for complex Excel manipulation tasks, including merging.

To begin using Spire.XLS for Java, you need to add its dependency to your Java project. If you are using Maven, add the following dependency to 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>15.10.5</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

Merging Multiple Excel Files into One

A common scenario involves combining several separate Excel files, each potentially containing different data or reports, into a single consolidated workbook. This section demonstrates how to merge Excel files in Java by appending all worksheets from multiple source files into a new destination file.

Let's assume you have a list of Excel file paths (file1.xlsx, file2.xlsx, etc.) that you want to merge. The following steps outline the process:

  • Initialize a new Workbook: Create an empty Workbook object that will serve as your merged output file.
  • Iterate through source files: Loop through each Excel file you wish to merge.
  • Load each source file: For each source file, load it into a temporary Workbook object.
  • Copy worksheets: Iterate through all worksheets in the temporary workbook and copy them to your new, consolidated workbook.

Here's the Java code that accomplishes this:

import com.spire.xls.*;

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

        //Initialize a new Workbook object
        Workbook newBook = new Workbook();

        //Clear the default worksheets
        newBook.getWorksheets().clear();

        //Initialize another temporary Workbook object
        Workbook tempBook = new Workbook();

        //Loop through all input Excel files
        for (String file : inputFiles)
        {
            //Load the current workbook
            tempBook.loadFromFile(file);
            //Loop through the worksheets in the current workbook
            for (Worksheet sheet : (Iterable) tempBook.getWorksheets())
            {
                //Copy each worksheet from the current workbook to the new workbook
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        //Save the result file
        newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
    }
}
Enter fullscreen mode Exit fullscreen mode

This code snippet effectively merges all worksheets from Budget Summary.xlsx, Income.xlsx, and Expenses.xlsx into a single file named MergeFiles.xlsx. Each original worksheet will become a separate sheet in the merged output.

Merging Multiple Worksheets into One Worksheet

Beyond merging entire files, you might need to consolidate data from multiple worksheets (either within the same file or from different files) into a single worksheet. This is particularly useful when you want to create a summary sheet or combine related data for analysis. This section focuses on how to merge worksheets in Excel into one worksheet using Spire.XLS for Java.

The process typically involves:

  • Identifying source worksheets: Pinpoint the worksheets whose data you want to merge.
  • Creating a destination worksheet: Designate or create a new worksheet where the consolidated data will reside.
  • Copying data ranges: Iterate through the source worksheets, copy their data ranges, and paste them sequentially into the destination worksheet. You'll need to manage the starting row for pasting to avoid overwriting previous data.

Let's illustrate with an example where we merge data from the first worksheet and the second one of input.xlsx into a new sheet.

import com.spire.xls.*;

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

        //Load an Excel file
        workbook.loadFromFile("input.xlsx");

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

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

        //Get the used range in the second worksheet
        CellRange sourceRange = sheet2.getAllocatedRange();

        //Specify the destination range in the first worksheet
        CellRange destRange = sheet1.getCellRange(sheet1.getLastRow() + 1, 1);

        //Copy the used range of the second worksheet to the destination range in the first worksheet
        sourceRange.copy(destRange);

        //Remove the second worksheet
        sheet2.remove();

        //Save the result file
        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}
Enter fullscreen mode Exit fullscreen mode

Spire.XLS Cell Merging

Spire.XLS also provides direct methods for merging cells within a single worksheet, which is often needed for formatting or creating combined headers.

import com.spire.xls.*;

public class MergeCells {
    public static void main(String[] args){

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

        //Load a sample Excel document
        workbook.loadFromFile("input.xlsx");

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

        //Merge cells by range
        sheet.getRange().get("A2:A4").merge();
        sheet.getRange().get("A5:A7").merge();

        //Set the horizontal alignment of merged cells to Center
        sheet.getRange().get("A2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getRange().get("A5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        //Set the vertical alignment of merged cells to Center
        sheet.getRange().get("A2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getRange().get("A5").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);


        //Save the result document
        workbook.saveToFile("MergeCells.xlsx", FileFormat.Version2013);
    }
}
Enter fullscreen mode Exit fullscreen mode

Conclusion

Automating Excel merging tasks with Java and Spire.XLS for Java significantly enhances efficiency and accuracy, transforming tedious manual processes into streamlined operations. This tutorial has demonstrated how to merge Excel files in Java by combining entire workbooks and how to merge worksheets in Excel into a single consolidated sheet. By leveraging the power of Spire.XLS, developers can easily handle complex Excel manipulation, ensuring data integrity and boosting productivity. Embrace these programmatic solutions to automate your Excel workflows and focus on more strategic tasks.

Top comments (0)