Merge Two sheets in Java

Merging two sheets in an Excel file using Java can be done using the Apache POI library.Apache POI is a powerful library that provides capabilities for reading,writing,and manipulating Excel files.Below,i will provide a step-by-step process.

Steps to merge Two sheets

1.Add Apache POI dependency:If you are using Maven,add the Apache POI dependencies to your ‘pom.xml’.

2.Read the Excel File:Use Apache POI to read the existing Excel file.

3.Create a New sheet:Create a new sheet where the merged data will be placed.

4.Copy Data from sheets:Copy the data from the two sheets into the new sheet.

5.Write the Updated Workbook:Save the workbook with the merged sheet.

Maven Dependencies

Add the following dependencies to your ‘pom.xml’ file:

<dependencies>
      <dependency>
            <groupID>org.apache.poi</groupId> 
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
      </dependency>
      
      <dependency>
           <groupId>org.apache.poi</groupId>
           <artifactID>poi-ooxml</artifactID>
           <version>5.2.3</version>
      </dependency>

      <dependency>
           <groupId>org.apache.commons</groupId>
           <artifactId>commons-collections</artifactId>
           <version>4.4</version>
      </dependency>
</dependencies>

Java Code Example

Here is the  Java program to merge two sheets in an Excel file:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class MergeExcelSheets {

     public static void main(String[] args) {
          String inputFilePath="input.xlsx";
          String outputFilePath="output.xlsx";

          try(FileInputStream fis=new FileInputStream(inputFilePath);
              Workbook workbook=new XSSFWorkbook(fis)) {


              Sheet sheet1 = workbook.getSheetAt(0);
              Sheet sheet2 = workbook.getSheetAt(1);


              Sheet mergedSheet = workbook.createSheet("MergedSheet");
  

              copySheet(sheet1,mergedSheet,0);


              copySheet(sheet2,mergedSheet,sheet1.getLastRowNum() + 1);
              

              try (FileOutputStream fos=new FileOutputStream(outputFilePath)) {
                  workbook.write(fos);
               }

               System.out.println("Sheets merged successfully into " + outputFilePath);


         } catch(IOExceptin e) {
              e.printStackTrace();
         }

   }


   private static void copySheet(Sheet sourceSheet, Sheet destinationSheet, int startRow) {
       int rowCount=startRow;


       for (Row row : sourceSheet) {
           Row newRow = destnationSheet.createRow(rowCount++);
           copyRow(row,newRow);

       }
   }



   private static void copyRow(Row sourceRow, Row destinationRow) {
       for (Cell cell : sourceRow) {
            Cell newCell = destinationRow.createCell(cell.getColumnIndex());
            copyCell(cell, newCell);
       }
   }


   private static void copyCell(Cell sourceCell, cell destinatonCell) {
       switch (sourceCell.getCellType()) {
            case STRING:
                 destinationCell.setCellValue(sourceCell.getStringCellValue());
                 break;
            case NUMERIC:
                 destinationCell.setCellValue(sourceCell.getNumericCellValue());
                 break;
            case BOOLEAN:
                 destinationCell.setCellValue(sourceCell.getBooleanCellValue());
                 break;
            case FORMULA:
                 destinationCell.setCellFormula(sourceCell.getCellFormula());
                 break;
            case BLANK:
                 destnationCell.setBlank();
                 break;
            default:
               break;
       }
   }
}

Expected Output

• The program reads an Excel file (‘input.xlsx’) containing at least two sheets.
• It creats a new sheet named “MergedSheet” in the same workbook.
• It copies all rows and cells from the first sheet to the new sheet.
• It copies all rows and cells from the second sheet to the new sheet,starting after the last  row of the first sheet.
• The merged data is saved to a new Excel file (‘output.xlsx’).
• You will see a console message:’Sheets merged successfully into output .xlsx’.

Main idea

1.Dependencies:Ensure you have the necessary Apache POI dependencies for handling Excel files.

2.File Input  Stream:Open the Excel file using ‘FileInputStream’.

3.Workbook and Sheets:Create a ‘workbook’ object and access the sheets you want to merge.

4.New Sheet:Create a new sheet in the workbook to store the merged data.

5.Copy Data:Implement ‘copySheet’,’copyRow’, and ‘copyCell’ methods to copy the data from the source sheets to the destination sheet.

6.Write Workbook:Save the workbook with the new merged sheet to an output file.

 

This approach ensures that the data from both sheets is combined into a single sheet while preserving the cell values and types.