Skip to content

Instantly share code, notes, and snippets.

@davidsommer

davidsommer/mergeExcel.java

Created December 5, 2013 14:01
Show Gist options
  • Save davidsommer/7805574 to your computer and use it in GitHub Desktop.
Save davidsommer/7805574 to your computer and use it in GitHub Desktop.
Download ZIP
Merge a List of Excel Files with POI Copies all Sheets, Fields of a List of Excel Files into a new one
Raw
mergeExcel.java
public static void mergeExcelFiles(File file, List<FileInputStream> list) throws IOException {
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet(file.getName());
for (FileInputStream fin : list) {
HSSFWorkbook b = new HSSFWorkbook(fin);
for (int i = 0; i < b.getNumberOfSheets(); i++) {
copySheets(book, sheet, b.getSheetAt(i));
}
}
try {
writeFile(book, file);
} catch (Exception e) {
// TODO Auto-generated catch block
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() + 1;
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;
}
}
@PriyankDiwakar
Copy link

For xssf i used that code:

public static void mergeExcelFiles(File file, List list) throws IOException {

    XSSFWorkbook book = new XSSFWorkbook();
    XSSFSheet sheet = book.createSheet(file.getName());
   
    
    
    for (FileInputStream fin : list) {
      XSSFWorkbook b = new XSSFWorkbook(fin);
      for (int i = 0; i < b.getNumberOfSheets(); i++) {
        copySheets(book, sheet, b.getSheetAt(i));
      }
    }
    
    try {
      writeFile(book, file);
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
  
  protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
    FileOutputStream out = new FileOutputStream(file);
    book.write(out);
    out.close();
  }
  
  private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet){     
    copySheets(newWorkbook, newSheet, sheet, true);
  }     

  private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle){     
    int newRownumber = newSheet.getLastRowNum() + 1;
    int maxColumnNum = 0;     
    Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;    
    
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {     
      XSSFRow srcRow = sheet.getRow(i);     
      XSSFRow 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(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {     
    destRow.setHeight(srcRow.getHeight());
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {     
      XSSFCell oldCell = srcRow.getCell(j);
      XSSFCell newCell = destRow.getCell(j);
      if (oldCell != null) {     
        if (newCell == null) {     
          newCell = destRow.createCell(j);     
        }     
        copyCell(newWorkbook, oldCell, newCell, styleMap);
      }     
    }                
  }
  
  public static void copyCell(XSSFWorkbook newWorkbook, XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {      
    if(styleMap != null) {     
      int stHashCode = oldCell.getCellStyle().hashCode();     
      XSSFCellStyle 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 XSSFCell.CELL_TYPE_STRING:     
        newCell.setCellValue(oldCell.getRichStringCellValue());     
        break;     
      case XSSFCell.CELL_TYPE_NUMERIC:     
        newCell.setCellValue(oldCell.getNumericCellValue());     
        break;     
      case XSSFCell.CELL_TYPE_BLANK:     
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);     
        break;     
      case XSSFCell.CELL_TYPE_BOOLEAN:     
        newCell.setCellValue(oldCell.getBooleanCellValue());     
        break;     
      case XSSFCell.CELL_TYPE_ERROR:     
        newCell.setCellErrorValue(oldCell.getErrorCellValue());     
        break;     
      case XSSFCell.CELL_TYPE_FORMULA:     
        newCell.setCellFormula(oldCell.getCellFormula());     
        break;     
      default:     
        break;     
    }
  }

@viswa7878
Copy link

can you please provide code for combining two excel sheets from left to right instead of top to bottom.
Note: Just two files no need directories of files. Combine two excel and store it in third excel file

@viswa7878
Copy link

can I know how to merge this files on column wise

did you got how to merge column wise . Can you please give reply

@davidsommer
Copy link
Author

Its not like i do this as a free service :-) Try yourself

@falberto
Copy link

update for implementation("org.apache.poi:poi-ooxml:5.2.5")

package org.example;

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

import java.io.*;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args) throws IOException {

        FileInputStream[] filePaths = {new FileInputStream("C:\\temp\\rel\\file1.xlsx"), new FileInputStream("C:\\temp\\rel\\file2.xlsx")};

        mergeExcelFiles(new File("C:\\temp\\rel\\all.xlsx"), Arrays.asList(filePaths));

        System.out.println("Done!");
    }

    public static void mergeExcelFiles(File file, List<FileInputStream> list) throws IOException {
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet(file.getName());


        for (FileInputStream fin : list) {
            XSSFWorkbook b = new XSSFWorkbook(fin);
            for (int i = 0; i < b.getNumberOfSheets(); i++) {
                copySheets(book, sheet, b.getSheetAt(i));
            }
        }

        try {
            writeFile(book, file);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
        FileOutputStream out = new FileOutputStream(file);
        book.write(out);
        out.close();
    }

    private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet) {
        copySheets(newWorkbook, newSheet, sheet, true);
    }

    private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
        int newRownumber = newSheet.getLastRowNum() + 1;
        int maxColumnNum = 0;
        Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;

        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow srcRow = sheet.getRow(i);
            XSSFRow 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(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
        destRow.setHeight(srcRow.getHeight());
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
            XSSFCell oldCell = srcRow.getCell(j);
            XSSFCell newCell = destRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = destRow.createCell(j);
                }
                copyCell(newWorkbook, oldCell, newCell, styleMap);
            }
        }
    }

    public static void copyCell(XSSFWorkbook newWorkbook, XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
        if (styleMap != null) {
            int stHashCode = oldCell.getCellStyle().hashCode();
            XSSFCellStyle 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 STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case BLANK:
                newCell.setCellType(CellType.BLANK);
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            default:
                break;
        }
    }
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment