多个excel合并成一个excel的sheet

1.方案一：使用POI的API

缺点是：数据量稍微大一点生成特别耗时。数据量在3000条记录的样子

优点：不依赖第三方应用，使用POI自己的API完成

建议：小数据量可以满足要求，

public class ExcelMergerUtils {
    public static void main(String[] args) {
        List<String> list = Arrays.asList(
                new File("D:\\test\\a.xlsx").toString(),
                new File("D:\\test\\b.xlsx").toString(),
                new File("D:\\test\\c.xlsx").toString()
        );

        mergexcel(list,"杨洪-家庭贷-20190908(报告).xlsx");
        System.out.println("oK");
    }

    /**
     * * 合并多个ExcelSheet
     *
     * @param files 文件字符串(file.toString)集合,按顺序进行合并，合并的Excel中Sheet名称不可重复
     * @param excelName 合并后Excel名称(不写后缀默认xslx)
     * @return
     * @Date: 2020/9/18 15:31
     */
    public static AjaxResult mergexcel(List<String> files, String excelName) {
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        // 遍历每个源excel文件，TmpList为源文件的名称集合
        for (String fromExcelName : files) {
            try (InputStream in = new FileInputStream( RuoYiConfig.getDownloadPath()+fromExcelName)) {
                XSSFWorkbook fromExcel = new XSSFWorkbook(in);
                int length = fromExcel.getNumberOfSheets();
                if (length <= 1) {       //长度为1时
                    XSSFSheet oldSheet = fromExcel.getSheetAt(0);
                    XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                    copySheet(newExcelCreat, oldSheet, newSheet);
                } else {
                    for (int i = 0; i < length; i++) {// 遍历每个sheet
                        XSSFSheet oldSheet = fromExcel.getSheetAt(i);
                        XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                        copySheet(newExcelCreat, oldSheet, newSheet);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        // 定义新生成的xlxs表格文件

        String filename =encodingFilename(excelName);
        String allFileName =  RuoYiConfig.getDownloadPath()+filename ;
        try (FileOutputStream fileOut = new FileOutputStream(allFileName)) {
            newExcelCreat.write(fileOut);
            fileOut.flush();

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                newExcelCreat.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return AjaxResult.success(filename);
    }
    /**
     * 编码文件名
     */
    public static String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }
    /**
     * 合并单元格
     *
     * @param fromSheet
     * @param toSheet
     */
    private static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    /**
     * 复制单元格
     *
     * @param wb
     * @param fromCell
     * @param toCell
     */
    private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        // 复制单元格样式
        newstyle.cloneStyleFrom(fromCell.getCellStyle());
        // 样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == CellType.STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == CellType.BLANK) {
            // nothing21
        } else if (fromCellType == CellType.BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == CellType.ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == CellType.FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else {
            // nothing29
        }
    }

    /**
     * 行复制功能
     *
     * @param wb
     * @param oldRow
     * @param toRow
     */
    private 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);
        }
    }

    /**
     * Sheet复制
     *
     * @param wb
     * @param fromSheet
     * @param toSheet
     */
    private static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        // 设置列宽
        int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
        for (int i = 0; i <= length; 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);
        }
    }

方案二：

使用easyExcel：

pom：

 <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

public class EasyExcelMergUtils {
    public static AjaxResult mergerExcel(String path, List<String> fileNames, String newFileName) {


        ArrayList<NoModelDataListener> noModelDataListeners = new ArrayList<>();
        // 这里 只要，然后读取第一个sheet 同步读取会自动finish
        for (int i = 0; i < fileNames.size(); i++) {
            String filename = fileNames.get(i);
            String sheetName = filename.substring(filename.indexOf("_") + 1);
            NoModelDataListener one = new NoModelDataListener(i, sheetName);
            EasyExcel.read(path + filename, one).sheet().doRead();
            noModelDataListeners.add(one);
        }
        // 写法1   String fileName =  "D:" + File.separator + "指标汇总.xlsx";
        ExcelWriter excelWriter = null;
        String filename = encodingFilename(newFileName);
        try {
            // 这里 指定文件
            excelWriter = EasyExcel.write(path + filename)
                    //自定义表格样式
                    .registerWriteHandler(getCustomHorizontalCellStyleStrategy())
                    //自定义单元格宽度
                    .registerWriteHandler(new CustomCellWriteHandler())
                    .build();
            for (int i = 0; i < noModelDataListeners.size(); i++) {
                NoModelDataListener noModelDataListener = noModelDataListeners.get(i);
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
                WriteSheet writeSheet = EasyExcel.writerSheet(noModelDataListener.getSheetId(), noModelDataListener.getSheetName()).head(noModelDataListener.getLists()).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                excelWriter.write(noModelDataListener.getCachedDataList(), writeSheet);
            }

        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return AjaxResult.success(filename);
    }

    private static HorizontalCellStyleStrategy getCustomHorizontalCellStyleStrategy() {

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteFont.setBold(true);
        headWriteFont.setFontName("Arial");
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现

        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontName("Arial");
        contentWriteFont.setFontHeightInPoints((short) 10);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        return horizontalCellStyleStrategy;
    }

    /**
     * 编码文件名
     */
    public static String encodingFilename(String filename) {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }

    }
}