Merging Two excel files as two sheets in one workbook in java

Question

I have two xlsx files at folder C:\DemoFilesExcel\demo1.xlsx and C:\DemoFilesExcel\demo2.xlsx.

I want to create a new xlsx C:\DemoFilesExcel\merged.xlsx that will have these two files as two sheets in merged.xlsx workbook.

This is to be done using apache POI in java. Any idea how to do

Answer 1

This below piece of code can be used to merge two excel files as two sheets in one workbook in java. This will copy both the excel files with the same structure.

It has worked for me.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class MergeMultipleXlsFilesInDifferentSheet{
 public static void mergeExcelFiles(File file) throws IOException {
    HSSFWorkbook book = new HSSFWorkbook();
    System.out.println(file.getName());
    String directoryName = "C:\\MergingXlsDirectory";
    File directory = new File(directoryName);
    //get all the files from a directory
    File[] fList = directory.listFiles();
    for (File file1 : fList){
        if (file1.isFile()){
            String ParticularFile = file1.getName();
       FileInputStream fin = new FileInputStream(new File(directoryName+"\\"+ParticularFile));
      HSSFWorkbook b = new HSSFWorkbook(fin);
      for (int i = 0; i < b.getNumberOfSheets(); i++) {
          HSSFSheet sheet = book.createSheet(b.getSheetName(i));
        copySheets(book, sheet, b.getSheetAt(i));
        System.out.println("Copying..");
      }
    }
    try {
      writeFile(book, file);
    }catch(Exception e) {
        e.printStackTrace();
    }
   }
  }
  protected static void writeFile(HSSFWorkbook book, File file) throws Exception {
    FileOutputStream out = new FileOutputStream(file);
    book.write(out);
    out.close();
  }
  private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet){     
    copySheets(newWorkbook, newSheet, sheet, true);
  }     

  private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){     
    int newRownumber = newSheet.getLastRowNum();
    int maxColumnNum = 0;     
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;    

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {     
      HSSFRow srcRow = sheet.getRow(i);     
      HSSFRow destRow = newSheet.createRow(i + newRownumber);     
      if (srcRow != null) {     
        copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);     
        if (srcRow.getLastCellNum() > maxColumnNum) {     
            maxColumnNum = srcRow.getLastCellNum();     
        }     
      }     
    }     
    for (int i = 0; i <= maxColumnNum; i++) {     
      newSheet.setColumnWidth(i, sheet.getColumnWidth(i));     
    }     
  }     

  public static void copyRow(HSSFWorkbook newWorkbook, HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {     
    destRow.setHeight(srcRow.getHeight());
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {     
      HSSFCell oldCell = srcRow.getCell(j);
      HSSFCell newCell = destRow.getCell(j);
      if (oldCell != null) {     
        if (newCell == null) {     
          newCell = destRow.createCell(j);     
        }     
        copyCell(newWorkbook, oldCell, newCell, styleMap);
      }     
    }                
  }

  public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {      
    if(styleMap != null) {     
      int stHashCode = oldCell.getCellStyle().hashCode();     
      HSSFCellStyle newCellStyle = styleMap.get(stHashCode);     
      if(newCellStyle == null){     
        newCellStyle = newWorkbook.createCellStyle();     
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());     
        styleMap.put(stHashCode, newCellStyle);     
      }     
      newCell.setCellStyle(newCellStyle);   
    }     
    switch(oldCell.getCellType()) {     
      case HSSFCell.CELL_TYPE_STRING:     
        newCell.setCellValue(oldCell.getRichStringCellValue());     
        break;     
      case HSSFCell.CELL_TYPE_NUMERIC:     
        newCell.setCellValue(oldCell.getNumericCellValue());     
        break;     
      case HSSFCell.CELL_TYPE_BLANK:     
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);     
        break;     
      case HSSFCell.CELL_TYPE_BOOLEAN:     
        newCell.setCellValue(oldCell.getBooleanCellValue());     
        break;     
      case HSSFCell.CELL_TYPE_ERROR:     
        newCell.setCellErrorValue(oldCell.getErrorCellValue());     
        break;     
      case HSSFCell.CELL_TYPE_FORMULA:     
        newCell.setCellFormula(oldCell.getCellFormula());     
        break;     
      default:     
        break;     
    }
  }
  public static void main(String[] args) {
      try {
        mergeExcelFiles(new File("C:\\NewXlsfileWhereDataWillBeMerged.xls"));
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}
}