Combine Multiple Excel Files into One using Java

ombine Multiple Excel Files into One using Java

You have several Excel workbooks, and you want to combine them together into one file for reporting or to keep data in one place. As a Java developer, you can easily merge multiple Excel files into one file programmatically. In this article, you will learn how to combine multiple Excel files into one using Java.

The following topics are discussed/covered in this article:

Java API to Merge Excel Files

For merging multiple Excel files, I will be using Aspose.Cells for Java API. This API enables you to create, manipulate, convert, protect or print spreadsheets without relying on Microsoft Excel. It allows you to perform Excel automation features programmatically in your Java applications.

You can download the JAR of the API or just add the following pom.xml configuration in your Maven-based Java application to try the below-mentioned code examples.

<repository>
    <id>AsposeJavaAPI</id>
    <name>Aspose Java API</name>
    <url>https://repository.aspose.com/repo/</url>
</repository>
<dependency>
    <groupId>com.aspose</groupId>
    <artifactId>aspose-cells</artifactId>
    <version>21.8</version>
</dependency>

Combine Multiple Excel Files into One using Java

You can easily combine multiple Excel files into a single file programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with first source file
  • Create an instance of the Workbook class with second source file
  • Repeat above step for combining more than two files
  • Call the combine() method with second source file instance
  • Repeat above step for all source files one by one
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to combine multiple Excel files into one file using Java.

Combine Multiple Excel Files into One using Java
Combine Multiple Excel Files into One using Java

The Workbook class of the API is the main class used to create an Excel Spreadsheet. It enables you to open and save the native Excel files. It also provides several properties and methods to work with Excel Spreadsheet. The combine() method of this class combines the current workbook with another Workbook object. The save() method of the Workbook class saves the output file at the specified file path.

Combine Specific Worksheets of Multiple Excel Files into One using Java

You can easily combine specific worksheets from multiple Excel files into a single file programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class for source file 1
  • Create an instance of the Workbook class for source file 2
  • Repeat above step for combining worksheets from more than two files
  • Create an instance of the Workbook class for destination file
  • Add worksheet(s) using add() method of the WorksheetCollection class
  • Call the copy() method to copy specified worksheet from source file 1 to destination file
  • Call the copy() method to copy specified worksheet from source file 2 to destination file
  • Rename worksheets in destination file by using the setName() method
  • Save the destination file by calling the save() method of the Workbook class

The following code sample shows how to combine specific worksheets from multiple Excel files into one file using Java.

Combine Specific Worksheets of Multiple Excel Files into One using Java
Combine Specific Worksheets of Multiple Excel Files into One using Java

The getWorksheets() property method of the Workbook class returns a collection of all the worksheets in a Workbook. You can add a worksheet to the collection of worksheets using the add() method.

The Worksheet class of this API represents a single worksheet. It provides several properties and methods to work with a worksheet. The copy() method of this class copies content and formats from another worksheet. The Worksheet class also provides get() methods to get a specific worksheet by its index or by its name. The setName() property method sets the name of the worksheet.

Merge Multiple Worksheets into One Worksheet using Java

You can easily merge multiple worksheets of an Excel file into a single worksheet programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class for source file
  • Add a new worksheet using the add() method
  • Iterate over source worksheets and do the following:
    • create a range of cells and columns for one worksheet using the createRange() method
    • Copy data from a source range to the destination range using the copy() method
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to merge multiple worksheets into one worksheet using Java.

Merge Multiple Worksheets into One Worksheet using Java
Merge Multiple Worksheets into One Worksheet using Java

The getCells() property method of the Worksheet class provides the collection of the Cells available in the worksheet. The Cells class of the API represents a collection of objects relevant to a cell, such as a CellRow, etc. The getMaxDisplayRange() property method of the Cells class provides the max range which includes data, merged cells, and shapes. The Range class represents a range of cells within a spreadsheet.

The Cells class provides the following methods to create a range of cells:

  • createRange(int firstIndex, int number, boolean isVertical) method to create a Range object from rows of cells or columns of cells.
  • createRange(int firstRow, int firstColumn, int totalRows, int totalColumns) method to create a Range object from a range of cells.
  • The createRange(java.lang.String address) method creates a Range object from an address of the range.
  • The createRange(java.lang.String upperLeftCell, java.lang.String lowerRightCell) method creates a Range object from a range of cells.

The copy() method of the Range class copies all kinds of data (including formulas), formatting, drawing objects, etc. from a source range to the destination range.

Consolidate Columns of Multiple Worksheets into One using Java

You can easily merge columns of multiple worksheets into a single worksheet programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class for source file
  • Add a new worksheet using the add() method
  • Iterate over source worksheets and do the following:
    • Copy all columns one by one using the copyColumn() method with source worksheets cells and column index
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to consolidate columns of multiple worksheets into one worksheet using Java.

Consolidate Columns of Multiple Worksheets into One using Java
Consolidate Columns of Multiple Worksheets into One using Java

The getColumns() property method of the Cells class provides the collection of the columns available in the worksheet. The ColumnCollection class represents the collection of the individual columns in a worksheet, whereas the Column class represents a single column in a worksheet.

The copyColumn() method of the Cells class copies the data and formats of a whole column. The Cells class also provides overloaded copyColumn() methods to copy data with the PasteOptions, column number, source, and destination total columns, etc.

Get a Free License

You can try the API without evaluation limitations by requesting a free temporary license.

Conclusion

In this article, you have learned how to combine multiple Excel files into one file. You have also learned how to combine specific worksheets of multiple Excel files using Java. Moreover, you have learned how to merge multiple worksheets into a single worksheet programmatically. This article also explained how to consolidate columns of multiple worksheets into one using Java. You can learn more about Aspose.Cells for Java API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.

See Also