Combine Multiple Excel Files into One using Java

CombineMultipleExcelFiles_Java.md

Learn how to combine multiple Excel files into one using Java: https://blog.conholdate.com/2021/09/02/combine-multiple-excel-files-into-one-using-java/

The following topics are covered here:

1. Combine Multiple Excel Files into One using Java
2. Combine Specific Worksheets of Multiple Excel Files into One using Java
3. Merge Multiple Worksheets into One Worksheet using Java
4. Consolidate Columns of Multiple Worksheets into One using Java

CombineMultipleExcelFiles_Java_CombineSpecificWorksheets.java

String sourceFile1 = "Quarter_1.xlsx";
String sourceFile2 = "Quarter_2.xlsx";

// Open the first Excel file.
Workbook excelA = new Workbook("C:\\Files\\" + sourceFile1);

// Open the second Excel file.
Workbook excelB = new Workbook("C:\\Files\\" + sourceFile2);

// Create destination Workbook.
Workbook destWorkbook = new Workbook();

// First worksheet is added by default to the Workbook. Add the second worksheet.
destWorkbook.getWorksheets().add();

// Copy the Jan worksheet of first Excel file to destination file.
destWorkbook.getWorksheets().get(0).copy(excelA.getWorksheets().get("Jan"));

// Copy the Jul worksheet of second Excel file to destination file.
destWorkbook.getWorksheets().get(1).copy(excelB.getWorksheets().get("Jul"));

// By default, the worksheet names are "Sheet1" and "Sheet2" respectively.
// Lets give them meaningful names.
destWorkbook.getWorksheets().get(0).setName(sourceFile1 + " - Jan");
destWorkbook.getWorksheets().get(1).setName(sourceFile2 + " - Jul");

// Save the destination file.
destWorkbook.save("C:\\Files\\CombinedSpecificSheetsInFile.xlsx");

CombineMultipleExcelFiles_Java_CombineWorkbooks.java

// Open the first excel file.
Workbook SourceBook1 = new Workbook("C:\\Files\\Quarter_1.xlsx");

// Open the second excel file.
Workbook SourceBook2 = new Workbook("C:\\Files\\Quarter_2.xlsx");

// Open the third excel file.
Workbook SourceBook3 = new Workbook("C:\\Files\\Quarter_3.xlsx");

// Copy worksheets of second Excel file to the first workbook.
SourceBook1.combine(SourceBook2);

// Copy worksheets of third Excel file to the first workbook.
SourceBook1.combine(SourceBook3);

// Save the updated first excel file as a new file.
SourceBook1.save("C:\\Files\\CombinedFile.xlsx");

CombineMultipleExcelFiles_Java_MergeColumnsOfMultipleWorksheets.java

// Open a Workbook.
Workbook workbook = new Workbook("C:\\Files\\sample.xlsx");

// Add a worksheet named Summary_sheet
Worksheet summarySheet = workbook.getWorksheets().add("Summary_sheet");

// Iterate over worksheets to copy columns to the
// summary worksheet
String[] nameOfSourceWorksheets = { "Products", "Sales", "Customers" };
int totalCol = 0; 

for (String sheetName : nameOfSourceWorksheets) {
  Worksheet sourceSheet = workbook.getWorksheets().get(sheetName);

  if (sheetName.equals("Products")) {
    // Get worksheet columns collection
    ColumnCollection columns = sourceSheet.getCells().getColumns();

    // copy column to summaySheet
    for (Column column : (Iterable<Column>) columns)
    {
      summarySheet.getCells().copyColumn(sourceSheet.getCells(), column.getIndex(), totalCol);
      totalCol = totalCol + 1;
    }
    }
  else {
    // Get worksheet columns collection
    ColumnCollection columns = sourceSheet.getCells().getColumns();

    // copy column to summaySheet
    for (Column column : (Iterable<Column>) columns)
    {
      summarySheet.getCells().copyColumn(sourceSheet.getCells(), column.getIndex(), totalCol);
      totalCol = totalCol + 1;
    }
  }
}

// Save the excel file.
workbook.save("C:\\Files\\CopyingColumns_out.xlsx");

CombineMultipleExcelFiles_Java_MergeMultipleWorksheets.java

// Open an Excel file that contains the worksheets:
// Jan, Feb, Mar, and Apr
Workbook workbook = new Workbook("C:\\Files\\Quarter_1.xlsx");

// Add a worksheet named Summary_sheet
Worksheet summarySheet = workbook.getWorksheets().add("Summary_sheet");

// Iterate over source worksheets to copy data to the
// summary worksheet
String[] nameOfSourceWorksheets = { "Jan", "Feb", "Mar", "Apr" };
int totalRowCount = 0;

for (String sheetName : nameOfSourceWorksheets)
{
  // Get worksheet
    Worksheet sourceSheet = workbook.getWorksheets().get(sheetName);

    Range sourceRange = null;
    Range destRange = null;

    // In case of Jan worksheet, include all rows and columns.
    if (sheetName.equals("Jan"))
    {
        sourceRange = sourceSheet.getCells().getMaxDisplayRange();

        destRange = summarySheet.getCells().createRange(
                sourceRange.getFirstRow() + totalRowCount,
                sourceRange.getFirstColumn(),
                sourceRange.getRowCount(),
                sourceRange.getColumnCount());
    }
    // In case of other worksheets,
    // exclude the first row (which contains headings).
    else
    {
        int mdatarow = sourceSheet.getCells().getMaxDataRow(); // Zero-based
        int mdatacol = sourceSheet.getCells().getMaxDataColumn(); // Zero-based
        sourceRange = sourceSheet.getCells().createRange(0 + 1, 0, mdatarow, mdatacol + 1);

        destRange = summarySheet.getCells().createRange(
                sourceRange.getFirstRow() + totalRowCount -1,
                sourceRange.getFirstColumn(),
                sourceRange.getRowCount(),
                sourceRange.getColumnCount());
    }

    // Copies data, formatting, drawing objects etc. from a
    // source range to destination range.
    destRange.copy(sourceRange);
    totalRowCount = sourceRange.getRowCount() + totalRowCount;
}

// Save the workbook 
workbook.save("C:\\Files\\Summarized.xlsx");

there is no imports where is it come from "Workbook" ***

@cinawar
Please find the imports given below:
import com.aspose.cells.Range;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;