Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Combine 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. Here, you will learn how to combine multiple Excel files into one using Java.
The following code examples are added 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
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");
// 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");
// 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");
// 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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment