poi实现多个excel合并成一个excel

目前，网上实现多个excel合并成一个excel的方法，使用版本比较老，问题较多，所以特此写一个方法，仅供参考。

导入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

utils工具类

public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
        toStyle.cloneStyleFrom(fromStyle);
    }

    public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR ;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        copyCellStyle(fromCell.getCellStyle(), newstyle);
        //样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }

        // 不同数据类型处理
        CellType cellType = fromCell.getCellType();
        toCell.setCellType(cellType);

        if (fromCell == null || fromCell.equals(null) || fromCell.getCellType() == CellType.BLANK) {
            toCell.setCellValue("");
        } else {
            //判断数据类型
            switch (cellType) {
                case FORMULA:
                    toCell.setCellValue(fromCell.getCellFormula());
                    break;
                case NUMERIC:
                    toCell.setCellValue(fromCell.getNumericCellValue());
                    break;
                case STRING:
                    toCell.setCellValue(fromCell.getStringCellValue());
                    break;
                default:
                    break;
            }
        }

    }

    public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }

    public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        //设置列宽
        for (int i = 0; i <= fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum(); i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }

实现方法

    public String hc(List<Workbook> workbooks, String uuid) {
        ByteArrayOutputStream bos = null;
        InputStream is = null;
        FileOutputStream fileOut=null;
        String filepath;
        try {
            XSSFWorkbook newExcelCreat = new XSSFWorkbook();
            for (Workbook workbook : workbooks) {
                bos = new ByteArrayOutputStream();
                workbook.write(bos);
                byte[] barray = bos.toByteArray();
                is = new ByteArrayInputStream(barray);
                XSSFWorkbook fromExcel = new XSSFWorkbook(is);

                XSSFSheet oldSheet = fromExcel.getSheetAt(0);
                XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                PoiUtils.copySheet(newExcelCreat, oldSheet, newSheet);

            }
            String allFileName = "d:/excel/0.xlsx";
            fileOut = new FileOutputStream(allFileName);
            newExcelCreat.write(fileOut);
            fileOut.flush();

           /* bos = new ByteArrayOutputStream();
            newExcelCreat.write(bos);
            byte[] barray = bos.toByteArray();
            is = new ByteArrayInputStream(barray);
            filepath = hdfsPath + "twentyTwo/" + uuid + ".xls";
            HdfsUtil.createFile(is, filepath);
            log.info("文件路径为{}", filepath);*/
        } catch (IOException i) {
            log.info("导出异常{}", i.getMessage());
            return null;
        } finally {
            try {
                fileOut.close();
                is.close();
                bos.close();
            } catch (IOException i) {
                i.printStackTrace();
            }

        }

        return null;
    }