Merge a List of Excel Files with POI Copies all Sheets, Fields of a List of Excel Files into a new one

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;     
    }
  }

Worked nicely, I just changed all HSSF to XSSF

It worked. thanks

can't get it to compile :|

C:\Users\user\Desktop\java>"C:\Program Files\Java\jdk1.8.0_40\bin\javac.exe" mergeExcelFiles.java
mergeExcelFiles.java:1: error: class, interface, or enum expected
  public static void mergeExcelFiles(File file, List<FileInputStream> list) throws IOException {
                ^
mergeExcelFiles.java:3: error: class, interface, or enum expected
    XSSFSheet sheet = book.createSheet(file.getName());
    ^
mergeExcelFiles.java:5: error: class, interface, or enum expected
    for (FileInputStream fin : list) {
    ^
mergeExcelFiles.java:7: error: class, interface, or enum expected
      for (int i = 0; i < b.getNumberOfSheets(); i++) {
      ^
mergeExcelFiles.java:7: error: class, interface, or enum expected
      for (int i = 0; i < b.getNumberOfSheets(); i++) {
                      ^
mergeExcelFiles.java:7: error: class, interface, or enum expected
      for (int i = 0; i < b.getNumberOfSheets(); i++) {
                                                 ^
mergeExcelFiles.java:9: error: class, interface, or enum expected
      }
      ^
mergeExcelFiles.java:14: error: class, interface, or enum expected
    } catch (Exception e) {
    ^
mergeExcelFiles.java:17: error: class, interface, or enum expected
    }
    ^
mergeExcelFiles.java:22: error: class, interface, or enum expected
    book.write(out);
    ^
mergeExcelFiles.java:23: error: class, interface, or enum expected
    out.close();
    ^
mergeExcelFiles.java:24: error: class, interface, or enum expected
  }
  ^
mergeExcelFiles.java:28: error: class, interface, or enum expected
  }
  ^
mergeExcelFiles.java:32: error: class, interface, or enum expected
    int maxColumnNum = 0;
    ^
mergeExcelFiles.java:33: error: class, interface, or enum expected
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    ^
mergeExcelFiles.java:35: error: class, interface, or enum expected

how did you guys compile it ?
im trying on windows 10 64bit with jdk1.8.0_40

I used this a long time ago with JAVA 1.6, thats your Problem, and i used an older iText Library(2.4.1) so you probably need to update some class names from HSSF to XSSF.

Hola buen trabajo, me ayudo mucho.

Use POI 3.16

public void copySheets(Workbook newWorkbook, Sheet newSheet, Sheet sheet){
copySheets(newWorkbook, newSheet, sheet, true);
}

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

for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {     
  Row srcRow = sheet.getRow(i);     
  Row 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));     
}     

}

private void copyRow(Workbook newWorkbook, Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow, Map<Integer, CellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
Cell oldCell = srcRow.getCell(j);
Cell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(newWorkbook, oldCell, newCell, styleMap);
}
}
}

private void copyCell(Workbook newWorkbook, Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) {
if(styleMap != null) {
int stHashCode = oldCell.getCellStyle().hashCode();
CellStyle newCellStyle = styleMap.get(stHashCode);
if(newCellStyle == null){
newCellStyle = newWorkbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
switch(oldCell.getCellTypeEnum()) {
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;
}
}

can I know how to merge this files on column wise

when i use the above code, the output excel file is getting corrupted, unable to open the file

This code is taking so much time to merge.

This code is taking so much time to merge.

can you share your code

This code is taking so much time to merge.

can you share your code
Hi @aashu45
Here is the code:

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

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

@service("ExcelMergeService")
public class ExcelMergeService{
private static final Logger log = LogManager.getLogger(ExcelMergeService.class);

public void mergeExcelFiles(File file,String path, List mergeReporsList) throws IOException {
log.info("file path:{}",path);
XSSFWorkbook book = new XSSFWorkbook();
String directoryName = path;
File directory = new File(directoryName);
//get all the files from a directory
File[] fList = directory.listFiles();
int count = 0; int startOfRow = 0;
XSSFSheet sheet = null;
Calendar cl = Calendar. getInstance();
cl.add(Calendar.DATE, -7);
cl.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
for (String filename : mergeReporsList){
File file1 = new File(path + file.separator + filename);
if (file1.isFile()){
String ParticularFile = file1.getName();
if(mergeReporsList.contains(ParticularFile)){
log.info("{} file started merging",file1.getName());
FileInputStream fin = new FileInputStream(new File(directoryName+file.separator+ParticularFile));
XSSFWorkbook b = new XSSFWorkbook(fin);
String fileName = ParticularFile.replaceFirst("[.][^.]+$", "");
int index = 0;
if(fileName.contains("-")){
index = fileName.indexOf("-");
fileName = fileName.substring(index + 1).trim();
if(fileName.contains("")){
index = fileName.indexOf("");
fileName = fileName.substring(0, index).trim();
}
}
for (int i = 0; i < b.getNumberOfSheets(); i++) {
if(fileName.contains("Summary"))
{
if(count == 0 )
{
sheet = book.createSheet("Week of "+(cl.get(Calendar.MONTH)+1)+"-"+cl.get(Calendar.DAY_OF_MONTH));
}
if(fileName.contains("Summary_4"))
{
sheet = book.createSheet("Week of "+(cl.get(Calendar.MONTH)+1)+"-"+cl.get(Calendar.DAY_OF_MONTH)+" Details");
startOfRow = 0;
}
}
else{
if(index == 0){
sheet = book.createSheet(count + "_" + fileName);
startOfRow = 0;
}else{
if(i == 0){
sheet = book.createSheet(fileName);
startOfRow = 0;
}
else{
sheet = book.createSheet(fileName + "-" + i);
startOfRow = 0;
}
}
}
count = count + 1;
copySheets(book, sheet, b.getSheetAt(i), startOfRow);
startOfRow = startOfRow + 1;
}
}
}
try {
writeFile(book, file);
//deleteFiles(path, mergeReporsList);

    }catch(Exception e) {
    	log.error("Excelption at Excel Merge: {}",e);
    }
   }
    log.info("copying is done");
    deleteFiles(path, mergeReporsList);

}
protected void writeFile(XSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
}
private void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, int startOfRow){
copySheets(newWorkbook, newSheet, sheet, true, startOfRow);
}

private void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle, int startOfRow){
int newRownumber = newSheet.getLastRowNum();
if(startOfRow != 0){
log.info("row:nul:{}",startOfRow);
newRownumber = newSheet.getLastRowNum() + 2;
}

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 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 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(XSSFCell.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;
}
}
public void buildMerger(String path, List mergeReporsList, String outputFile) {
try {
mergeExcelFiles(new File(path + File.separator + outputFile), path, mergeReporsList);
} catch (IOException e) {
// TODO Auto-generated catch block
log.error("Excelption at Excel Merge: {}",e);
}
}
public void deleteFiles(String path, List mergeReporsList){
File dir = new File(path);
File[] files = dir.listFiles();
for (int i = 0; i < files.length; i++) {
if(!files[i].isDirectory() && !files[i].getName().endsWith(".zip") && mergeReporsList.contains(files[i].getName())) {
File deleteFile = new File(files[i].toString());

			if (deleteFile.delete()) {
				log.info("File: {} deleted successfully.",
						files[i].toString());
			} else {
				log.info("File: {} not deleted.", files[i].toString());
			}
		}
	}

}
}

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;     
    }
  } 

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

can I know how to merge this files on column wise

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

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

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;
        }
    }
}