多excel合并到一个excel的一个sheet中

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

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

import java.io.*;
import java.util.*;

/**
 * @description: 多个Excel合并Sheet
 */
public class ExcelUtil {


    public static void main(String[] args) throws Exception {
        // 开始时间
        long startMilliSec = 0;
        startMilliSec = System.currentTimeMillis();



        XSSFWorkbook newWorkbook = new XSSFWorkbook();
//        sheet名 ，H02
        Long startTs = System.currentTimeMillis();
//        newSheet 名称
        XSSFSheet newSheet = newWorkbook.createSheet("newSheet1"+ startTs);

        File f1 =  new File("D:\\test\\a.xlsx");
        File f2 =  new File("D:\\test\\b.xlsx");
        File f3 =  new File("D:\\test\\c.xlsx");

        FileInputStream fi1 = new FileInputStream(f1);
        FileInputStream fi2 = new FileInputStream(f2);
        FileInputStream fi3 = new FileInputStream(f3);
        Workbook oldWork = new XSSFWorkbook(f1);
        Workbook oldWork2 = new XSSFWorkbook(f2);
        Workbook oldWork3 = new XSSFWorkbook(f3);


        int removeTitleLength = 0;

//      a,b,c 三个excel 各取第一个sheet 合并到 同一个sheet内 
//      a 保留所以行，b、c 去掉前4行
        moveSourceSheetIntoTargetSheet(newWorkbook, oldWork.getSheetAt(0), newSheet, 0);
        moveSourceSheetIntoTargetSheet(newWorkbook, oldWork2.getSheetAt(0), newSheet, 4);
        moveSourceSheetIntoTargetSheet(newWorkbook, oldWork3.getSheetAt(0), newSheet, 4);


        FileOutputStream fileOut = new FileOutputStream("D:\\test\\abcd.xlsx");
        newWorkbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
        fi1.close();
        fi2.close();
        fi3.close();


        // 结束时间
        long endMilliSec = 0;
        endMilliSec = System.currentTimeMillis();


        f1.delete();
        f2.delete();
        f3.delete();



        System.out.println(endMilliSec - startMilliSec);
    }



    /*** sheet页合并
     *
     *@paramtargetWorkBook 目标workbook，该对象主要用来创建单元格格式
     *@paramsourceSheet 源sheet
     *@paramtargetSheet 目标sheet
     *@paramremoveTitleLength 源sheet页合并到目标sheet中需要去掉的表头长度，如果不去掉传递0*/

    private static void moveSourceSheetIntoTargetSheet(Workbook newWorkBook, Sheet sourceSheet, Sheet targetSheet, int removeTitleLength) {
        if (sourceSheet == null) {
            return;

        }
//获取目标sheet最后一行的下一行
        int targetRowNums = targetSheet.getLastRowNum();
        int physicalNumberOfRows = targetSheet.getPhysicalNumberOfRows();

        targetRowNums = physicalNumberOfRows == 0 ? 0 : targetRowNums + 1;//移动 源sheet页中的 合并单元格区域 到目标sheet页中

        moveSourceSheetAllMergedRegionToTargetSheet(sourceSheet, targetSheet, targetRowNums, removeTitleLength);

        int sourceRowNums = sourceSheet.getLastRowNum();
        //        CellStyle 集合
        Map<Integer,CellStyle> cellStyles = new HashMap<>();

        for (int i = removeTitleLength; i <= sourceRowNums; i++) {
            Row targetRow = targetSheet.createRow(targetRowNums++);

            Row sourceRow = sourceSheet.getRow(i);//复制行

            copySourceRowToTargetRow(newWorkBook, sourceRow, targetRow,cellStyles);

        }


    }

    /*** 将源行复制到目标行
     *@paramtargetWorkBook 目标workbook，主要用来创建单元格样式
     *@paramsourceRow 源行
     *@paramtargetRow 目标行*/

    private static void copySourceRowToTargetRow(Workbook targetWorkBook, Row sourceRow, Row targetRow,Map<Integer,CellStyle> cellStyles ) {
        if (sourceRow == null) {
            return;

        }//行高

        targetRow.setHeight(sourceRow.getHeight());
        int sourceCellNums = sourceRow.getLastCellNum();


        for (int i = 0; i < sourceCellNums; i++) {
            Cell targetCell = targetRow.createCell(i);

            Cell sourceCell = sourceRow.getCell(i);//复制单元格

            copySourceCellToTargetCell(targetWorkBook, targetCell, sourceCell, cellStyles);

        }

    }

    /*** 移动单元格
     *
     *@paramtargetWorkBook 目标workbook，用来在本方法中创建单元格样式
     *@paramtargetCell 目标单元格
     *@paramsourceCell 源单元格*/

    private static void copySourceCellToTargetCell(Workbook targetWorkBook, Cell targetCell, Cell sourceCell,Map<Integer,CellStyle> cellStyles) {

        if (sourceCell == null) {
            return;
        }
        //将源单元格的格式 赋值到 目标单元格中
        int stHashCode = sourceCell.getCellStyle().hashCode();

        CellStyle targetCellStyle = cellStyles.get(stHashCode);

        if (targetCellStyle == null) {
            targetCellStyle = targetWorkBook.createCellStyle();
            targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
            cellStyles.put(stHashCode, targetCellStyle);
        }

        targetCellStyle.cloneStyleFrom(cellStyles.get(stHashCode));

        targetCell.setCellStyle(targetCellStyle);

        CellType cellTypeEnum = sourceCell.getCellTypeEnum();
        switch (cellTypeEnum) {
            case STRING:

                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(sourceCell)) {//日期格式的值

                    targetCell.setCellValue(sourceCell.getDateCellValue());

                } else {
                    targetCell.setCellValue(sourceCell.getNumericCellValue());

                }
                break;
            case BOOLEAN:

                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
//***为公式的情况下获取的是单元格的数值

                targetCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case BLANK:
                break;
            case ERROR:

                targetCell.setCellValue(sourceCell.getErrorCellValue());
                break;
            case _NONE:
                break;
            default:

        }

    }

//合并单元格的 格式，TODO 有bug ，去头 合并单元格在头里，会出问题，合并单元格格式有问题
    private static void moveSourceSheetAllMergedRegionToTargetSheet(Sheet sourceSheet, Sheet targetSheet, int targetRowNums, int removeTitleLength) {
        int numMergedRegions = sourceSheet.getNumMergedRegions();
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();//去掉表头的 单元格合并

            int lastRow = mergedRegion.getLastRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();//合并单元格的行需要跟随当前单元格的行数下移

            firstRow = firstRow + targetRowNums;

            lastRow = lastRow + targetRowNums;

            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);

            targetSheet.addMergedRegion(cellRangeAddress);

        }

    }


}