使用Easy Excel 多线程导出百万数据导出数据到excel的任务很快就完成了，使用EasyExcel导出数据节省了

背景

导出数据到excel的任务很快就完成了，使用EasyExcel导出数据节省了很多的时间。但是在自测时发现一个问题：如果数据量比较少，导出的excel文件不是很大的话，导出的速度还可以，当导出的数据量增加到千条以上时，导出的速度就要超过10s了，导出的字段有上百个，还是有点多的。现在的公司会限制导出数据的数量不超过3000条，速度也没要求要很快，因为是个异步导出，完事可以在下载记录页面下载，但是，作为一个程序员，当然要给自己增加工作量，看看使用网上的方法能优化到什么地步。

开始

在网上看了不少帖子，都是使用多线程优化，经过慎重筛选，终于找到一篇名为《EasyExcel 带格式多线程导出百万数据》的文章，看网上照抄的帖子还挺多的，就想尝试使用这个帖子中的方法试一下，看看效果能到什么样子，也看看是不是帖子中说的“亲测好用”，相关的文章会在文章末尾引用。

实验

1、优化原理：简单说就是利用多线程，每个文件对应一个线程，文件中的每个sheet对应一个线程，在将数据写入文件时利用锁锁住每个文件。下载完成后缓存起来，下次下载相同条件的文件先从缓存中找。

2、开始就遇到个问题，文章中代码都是不全的，而且没有开源地址，还需要自己补全。下面是利用AI补全的代码🙂，测试没有什么问题，但是要应用在生产中还有不少细节需要优化。我想说的是，这种利用多线程优化数据导出的方法还是值得借鉴学习的。

3、简单在本地电脑测试了一下，记录的数据如下：

	单个文件	单个文件	单个文件	zip文件
数据量	3000	508848	170078	170078
字段数量	102	5	102	102
导出文件大小	1.5M	15.4M	86.2M	82M
耗时	6s	25s	170	83s

注：导出为zip文件也只是导出了两个文件，每个文件最多10万条数据，如果每个文件拆分的更细的话，应该是会更快的，后面没有详细的测试。有时间会将代码优化好，并进行详细的测试。

最后：当然就是代码啦。

多线程导出主要实现类：

@Service
public class ExportExcel {

    @Resource
    CacheService cacheService;
 
    @Resource
    private CommonThreadManage commonThreadManage;

    private static final long SYS_REDIS_EXPIRE_TIME = 30;
//    private static final int ROW_SIZE = 100000;
//    private static final int ROW_PAGE = 10000;

    private static final int ROW_SIZE = 10000;
    private static final int ROW_PAGE = 10;

    public String exportTable(ExportTable exportTable) {
        StringBuffer path = new StringBuffer();
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        StringBuffer sign = new StringBuffer();
        // redis key
        sign.append(exportTable.getId());
        try {
            String fileName = exportTable.getFileName();
            int rowCount = exportTable.getRowCount();
            List<List<String>> head = exportTable.getHead();
            Set<Integer> mergeIndex = exportTable.getMergeIndex();

            List<ExportTable.ExportColumn> fields = exportTable.getFields();
            // 用来记录需要为 行 列设置样式
            Map<String, Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>>> map = new HashMap<>();
            sign.append("#").append(fields.stream().map(e -> e.isShow()? "true" : "false").collect(Collectors.joining(",")));
            setFontStyle(0, exportTable.getFields(), map);
            // 获取表头长度
            int headRow = head.stream().max(Comparator.comparingInt(List::size)).get().size();

            // 数据量超过十万 则不带样式
            // 只处理表头：表头合并 表头隐藏 表头冻结
            if (rowCount * fields.size() > ROW_SIZE * 6.4) {
                map.put("cellStyle", null);
            }
            sign.append("#").append(exportTable.getStyle());
            // 数据量超过百万或者数据为空，只返回有表头得单元格
            if (rowCount == 0 || rowCount * fields.size() >= ROW_SIZE * 1800) {
                EasyExcel.write(outputStream)
                        // 这里放入动态头
                        .head(head).sheet("数据")
                        // 传入表头样式
                        .registerWriteHandler(EasyExcelUtils.getStyleStrategy())
                        // 当然这里数据也可以用 List<List<String>> 去传入
                        .doWrite(new LinkedList<>());
                byte[] bytes = outputStream.toByteArray();
                // 上传文件到FaS stDF 返回上传路径
//                return fastWrapper.uploadFile(bytes, bytes.length, "xlsx") + "?filename=" + fileName + ".xlsx";
                return FileUtil.writeFileByBytes(fileName, bytes);
            }
            /**
             * 相同的下载文件请求 直接返回
             * the redis combines with datasetId - filter - size of data
             */
            if (cacheService.exists(sign.toString())) {
                return cacheService.get(sign.toString());
            }
            /**
             * 分sheet页
             * divide into sheets with 10M data per sheet
             */
            int sheetCount = (rowCount / (ROW_SIZE * ROW_PAGE)) + 1;
            String[] paths = new String[sheetCount];
            ByteArrayInputStream[] ins = new ByteArrayInputStream[sheetCount];

            // 创建线程池
//            ExecutorService threadExecutor = Executors.newFixedThreadPool(10);
            // 自定义线程池
            Executor threadExecutor = commonThreadManage.asyncCommonExecutor();
            CountDownLatch threadSignal = new CountDownLatch(sheetCount);
            for (int i = 0; i < sheetCount; i++) {
                int finalI = i;
                threadExecutor.execute(() -> {
                    // excel文件流
                    ByteArrayOutputStream singleOutputStream = new ByteArrayOutputStream();
                    ExcelWriter excelWriter = EasyExcel.write(singleOutputStream).build();

                    // 单sheet页写入数
                    int sheetThreadCount = finalI == (sheetCount - 1)? (rowCount - finalI * (ROW_SIZE * ROW_PAGE)) / ROW_SIZE + 1 : ROW_PAGE;
                    CountDownLatch sheetThreadSignal = new CountDownLatch(sheetThreadCount);
                    for (int j = 0; j < sheetThreadCount; j++) {
                        int page = finalI * ROW_PAGE + j + 1;
                        // 最后一页数据
                        int pageSize = j == (sheetThreadCount - 1) && finalI == (sheetCount - 1)? rowCount % ROW_SIZE : ROW_SIZE;
                         threadExecutor.execute(() -> {
                            try {
                                writeExcel(page, pageSize, head, map, headRow, excelWriter, mergeIndex, finalI);
                                sheetThreadSignal.countDown();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        });
                    }
                    try {
                        sheetThreadSignal.await();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                    // 关闭写入流
                    excelWriter.finish();
                    paths[finalI] = (finalI + 1) + "-" + fileName + ".xlsx";
                    ins[finalI] = new ByteArrayInputStream(singleOutputStream.toByteArray());
                    // 单文件
                    if (sheetCount == 1) {
                        byte[] bytes = singleOutputStream.toByteArray();
                        try {
                            path.append(FileUtil.writeFileByBytes(fileName + ".xlsx",bytes));
                        } catch (IOException e) {
                            throw new RuntimeException(e);
                        }
                        // 将sign存入redis并设置过期时间
                        cacheService.setEx(sign.toString(), path.toString(), SYS_REDIS_EXPIRE_TIME, TimeUnit.MINUTES);
                    }
                    threadSignal.countDown();
                });
            }
            threadSignal.await();

            if (sheetCount!= 1) {
                ZipUtil.zip(outputStream, paths, ins);
                byte[] bytes = outputStream.toByteArray();
                // 上传文件到FastDFS  返回上传路径
//                path.append(fastWrapper.uploadFile(bytes, bytes.length, "zip"))
//                        .append("?filename=").append(fileName).append(".zip");
                path.append(FileUtil.writeFileByBytes(fileName + ".zip",bytes));
                // 将sign存入redis并设置过期时间
                cacheService.setEx(sign.toString(), path.toString(), SYS_REDIS_EXPIRE_TIME, TimeUnit.MINUTES);
            }
        } catch (Exception e) {
            // 更详细的错误处理
            System.err.println("An error occurred during export: " + e.getMessage());
            e.printStackTrace();
        }
        return path.toString();
    }

    private void writeExcel(int page, int pageSize, List<List<String>> head,
                            Map<String, Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>>> map, int headRow,
                            ExcelWriter excelWriter, Set<Integer> mergeIndex, int sheetCount) {


        // todo 这里进行数据拼装

            WriteSheet writeSheet = EasyExcel.writerSheet(sheetCount, "第" + (sheetCount + 1) + "页数据")
                    // 这里放入动态头
                    .head(head)
                    // 传入样式
                    .registerWriteHandler(EasyExcelUtils.getStyleStrategy())
                    .registerWriteHandler(new CellColorSheetWriteHandler(map, headRow))
                    .registerWriteHandler(new MergeStrategy(CollectionUtils.size(data), mergeIndex))
                    // 当然这里数据也可以用 List<List<String>> 去传入
                    .build();
            excelWriter.write(data, writeSheet);
        }
    }


    private void setFontStyle(int row, List<ExportTable.ExportColumn> fields,
                              Map<String, Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>>> map) {
        Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>> rowStyle = new HashMap<>();
        List<Map<Integer, ExportTable.ExportColumn.Font>> columnStyles = new ArrayList<>();
        Map<Integer, ExportTable.ExportColumn.Font> columnStyle = new HashMap<>();

        for (int column = 0; column < fields.size(); column++) {
            int finalColumn = column;
            Optional<ExportTable.ExportColumn> any = fields.stream().filter(x -> x.getColumnNum() == finalColumn).findAny();
            if (any.isPresent()) {
                columnStyle.put(column, any.get().getFont());
            }
        }

        columnStyles.add(columnStyle);

        rowStyle.put(row, columnStyles);
        map.put("head", rowStyle);
    }
}

excel导出处理单元格：

/**
 * @Author Ash
 * @description 拦截处理单元格创建
 */
public class CellColorSheetWriteHandler implements CellWriteHandler {
    /**
     * 多行表头行号
     */
    private int headRow;

    /**
     * 字体
     */
    private ExportTable.ExportColumn.Font columnFont = new ExportTable.ExportColumn.Font();

    private static volatile XSSFCellStyle cellStyle = null;

    public static XSSFCellStyle getCellStyle(Workbook workbook, WriteCellStyle contentWriteCellStyle) {
        if (cellStyle == null) {
            synchronized (XSSFCellStyle.class) {
                if (cellStyle == null) {
                    cellStyle = (XSSFCellStyle) StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
                }
            }
        }
        return cellStyle;
    }

    /**
     * 字体
     * Map<Integer, ExportTable.ExportColumn.Font> 当前列的字段样式
     * Map<Integer, List<Map<...>>> 当前行包含那几列需要设置样式
     * String head：表头；
     * String cell：内容；
     */
    private Map<String, Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>>> map;

    /**
     * 有参构造
     */
    public CellColorSheetWriteHandler(Map<String, Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>>> map, int headRow) {
        this.map = map;
        this.headRow = headRow;
    }

    public CellColorSheetWriteHandler() {
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    /**
     * 在单元上的所有操作完成后调用
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 当前行的第column列
        int column = cell.getColumnIndex();
        // 当前第row行
        int row = cell.getRowIndex();
        AtomicInteger fixNum = new AtomicInteger();
        // 处理行，表头
        if (headRow > row && map.containsKey("head")) {
            Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>> fonts = map.get("head");
            fonts.get(row).forEach(e -> {
                e.entrySet().forEach(ele -> {
                    // 获取冻结字段
                    if (null != ele.getValue().getFixed() && !StringUtils.isEmpty(ele.getValue().getFixed())) {
                        fixNum.getAndIncrement();
                    }
                    // 字段隐藏
                    if (!ele.getValue().isShow()) {
                        writeSheetHolder.getSheet().setColumnHidden(ele.getKey(), true);
                    }
                });
            });
            if (fixNum.get() > 0 && row == 0) {
                writeSheetHolder.getSheet().createFreezePane(fixNum.get(), headRow, fixNum.get(), headRow);
            } else {
                writeSheetHolder.getSheet().createFreezePane(0, headRow, 0, headRow);
            }
            setStyle(fonts, row, column, cell, writeSheetHolder, head);
        }
        // 处理内容
        if (headRow <= row && map.containsKey("cell") && !map.containsKey("cellStyle")) {
            Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>> fonts = map.get("cell");
            setStyle(fonts, -1, column, cell, writeSheetHolder, head);
        }
    }

    private void setStyle(Map<Integer, List<Map<Integer, ExportTable.ExportColumn.Font>>> fonts, int row, int column, Cell cell, WriteSheetHolder writeSheetHolder, Head head) {
        fonts.get(row).forEach(e -> {
            if (e.containsKey(column)) {
                // 根据单元格获取workbook
                Workbook workbook = cell.getSheet().getWorkbook();
                //设置列宽
                if (null != e.get(column).getWidth() && !e.get(column).getWidth().isEmpty()) {
                    writeSheetHolder.getSheet().setColumnWidth(head.getColumnIndex(), Integer.parseInt(e.get(column).getWidth()) * 20);
                } else {
                    writeSheetHolder.getSheet().setColumnWidth(head.getColumnIndex(), 2000);
                }
                // 单元格策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                // 设置垂直居中为居中对齐
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                // 设置左右对齐方式
                if (null != e.get(column).getAlign() && !e.get(column).getAlign().isEmpty()) {
                    contentWriteCellStyle.setHorizontalAlignment(getHorizontalAlignment(e.get(column).getAlign()));
                } else {
                    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
                }
                if (!e.get(column).equals(columnFont) || column == 0) {
                    /**
                     * Prevent the creation of a large number of objects
                     * Defects of the EasyExcel tool(巨坑，简直脱发神器）
                     */
                    cellStyle = (XSSFCellStyle) StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
                    // 设置单元格背景颜色
                    if (null != e.get(column).getBackground() && !e.get(column).getBackground().isEmpty()) {
                        cellStyle.setFillForegroundColor(new XSSFColor(hex2Color(e.get(column).getBackground())));
                    } else {
                        if (cell.getRowIndex() >= headRow)
                            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                    }

                    // 创建字体实例
                    Font font = workbook.createFont();
                    // 设置字体是否加粗
                    if (null != e.get(column).getFontWeight() && !e.get(column).getFontWeight().isEmpty())
                        font.setBold(getBold(e.get(column).getFontWeight()));
                    // 设置字体和大小
                    if (null != e.get(column).getFontFamily() && !e.get(column).getFontFamily().isEmpty())
                        font.setFontName(e.get(column).getFontFamily());
                    if (0 != e.get(column).getFontSize())
                        font.setFontHeightInPoints((short) e.get(column).getFontSize());
                    XSSFFont xssfFont = (XSSFFont) font;
                    //设置字体颜色
                    if (null != e.get(column).getColor() && !e.get(column).getColor().isEmpty())
                        xssfFont.setColor(new XSSFColor(hex2Color(e.get(column).getColor())));
                    cellStyle.setFont(xssfFont);
                    // 记录上一个样式
                    columnFont = e.get(column);
                }

                // 设置当前行第column列的样式
                cell.getRow().getCell(column).setCellStyle(cellStyle);
                // 设置行高
                cell.getRow().setHeight((short) 400);
            }
        });
    }

    private HorizontalAlignment getHorizontalAlignment(String align) {
        switch (align) {
            case "center":
                return HorizontalAlignment.CENTER;
            case "right":
                return HorizontalAlignment.RIGHT;
            default:
                return HorizontalAlignment.LEFT;
        }
    }

    private boolean getBold(String fontWeight) {
        return "bold".equalsIgnoreCase(fontWeight);
    }

    private Color hex2Color(String hexStr) {
        if(hexStr != null && hexStr.length() == 7){
            int[] rgb = new int[3];
            rgb[0] = Integer.valueOf(hexStr.substring( 1, 3 ), 16);
            rgb[1] = Integer.valueOf(hexStr.substring( 3, 5 ), 16);
            rgb[2] = Integer.valueOf(hexStr.substring( 5, 7 ), 16);
            return new Color(rgb[0], rgb[1], rgb[2]);
        }
        return null;
    }

    public static void main(String[] args) {
        CellColorSheetWriteHandler cellColorSheetWriteHandler = new CellColorSheetWriteHandler();
        System.out.println(cellColorSheetWriteHandler.hex2Color("#C71585"));
    }

}

excel格式类：

public class EasyExcelUtils {

    public static HorizontalCellStyleStrategy getStyleStrategy(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)12);
        // 字体样式
        headWriteFont.setFontName("Frozen");
        // 字体颜色
        headWriteFont.setColor(IndexedColors.BLACK1.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式(修改默认对齐方式——4.14 版本1.3.2)
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        //        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 水平对齐方式(修改默认对齐方式——4.14 版本1.3.2)
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)12);
        // 字体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}

excel导出通用格式类：

public class ExportTable {

    private String id;
    private String style;
    private int rowCount;
    private List<ExportColumn> fields;
    private String fileName;
    private List<List<String>> head;
    private Set<Integer> mergeIndex;

    public ExportTable() {
    }

    public ExportTable(String id, String style, int rowCount, List<ExportColumn> fields, String fileName, List<List<String>> head, Set<Integer> mergeIndex) {
        this.id = id;
        this.style = style;
        this.rowCount = rowCount;
        this.fields = fields;
        this.fileName = fileName;
        this.head = head;
        this.mergeIndex = mergeIndex;
    }


    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getStyle() {
        return style;
    }

    public void setStyle(String style) {
        this.style = style;
    }

    public int getRowCount() {
        return rowCount;
    }

    public void setRowCount(int rowCount) {
        this.rowCount = rowCount;
    }

    public List<ExportColumn> getFields() {
        return fields;
    }

    public void setFields(List<ExportColumn> fields) {
        this.fields = fields;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public List<List<String>> getHead() {
        return head;
    }

    public void setHead(List<List<String>> head) {
        this.head = head;
    }

    public Set<Integer> getMergeIndex() {
        return mergeIndex;
    }

    public void setMergeIndex(Set<Integer> mergeIndex) {
        this.mergeIndex = mergeIndex;
    }

    public static class ExportColumn {

        private long columnNum;
        private String columnName;
        private boolean isShow;
        private Font font;

        public ExportColumn() {
        }

        public long getColumnNum() {
            return columnNum;
        }

        public void setColumnNum(long columnNum) {
            this.columnNum = columnNum;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public boolean isShow() {
            return isShow;
        }

        public void setShow(boolean show) {
            isShow = show;
        }

        public Font getFont() {
            return font;
        }

        public void setFont(Font font) {
            this.font = font;
        }

        public static class Font {
            private String fontName;
            private int fontSize;
            private String fixed;
            private boolean isShow;

            private String fontFamily;
            private String fontWeight;
            private String color;
            private String background;
            private String align;
            private String width;

            public Font() {
            }

            public Font(String fontName, int fontSize, String fixed, boolean isShow, String fontFamily, String fontWeight, String color, String background, String align, String width) {
                this.fontName = fontName;
                this.fontSize = fontSize;
                this.fixed = fixed;
                this.isShow = isShow;
                this.fontFamily = fontFamily;
                this.fontWeight = fontWeight;
                this.color = color;
                this.background = background;
                this.align = align;
                this.width = width;
            }

            public String getFontName() {
                return fontName;
            }

            public void setFontName(String fontName) {
                this.fontName = fontName;
            }

            public int getFontSize() {
                return fontSize;
            }

            public void setFontSize(int fontSize) {
                this.fontSize = fontSize;
            }

            public String getFixed() {
                return fixed;
            }

            public void setFixed(String fixed) {
                this.fixed = fixed;
            }

            public boolean isShow() {
                return isShow;
            }

            public void setShow(boolean show) {
                isShow = show;
            }

            public String getFontFamily() {
                return fontFamily;
            }

            public void setFontFamily(String fontFamily) {
                this.fontFamily = fontFamily;
            }

            public String getFontWeight() {
                return fontWeight;
            }

            public void setFontWeight(String fontWeight) {
                this.fontWeight = fontWeight;
            }

            public String getColor() {
                return color;
            }

            public void setColor(String color) {
                this.color = color;
            }

            public String getBackground() {
                return background;
            }

            public void setBackground(String background) {
                this.background = background;
            }

            public String getAlign() {
                return align;
            }

            public void setAlign(String align) {
                this.align = align;
            }

            public String getWidth() {
                return width;
            }

            public void setWidth(String width) {
                this.width = width;
            }
        }
    }
}

excel单元格合并：

public class MergeStrategy extends AbstractMergeStrategy {

    /**
     * 合并的列编号，从0开始
     * 指定的index或自己按字段顺序数
     */
    private Set<Integer> mergeCellIndex = new HashSet<>();

    /**
     * 数据集大小，用于区别结束行位置
     */
    private Integer maxRow = 0;

    // 禁止无参声明
    private MergeStrategy() {
    }

    public MergeStrategy(Integer maxRow, Set<Integer> mergeCellIndex) {
        this.mergeCellIndex = mergeCellIndex;
        this.maxRow = maxRow;
    }

    private final Map<Integer, MergeRange> lastRow = new HashMap<>();

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int currentCellIndex = cell.getColumnIndex();
        // 判断该行是否需要合并
        if (mergeCellIndex != null && mergeCellIndex.contains(currentCellIndex)) {
            String currentCellValue = cell.getStringCellValue();
            int currentRowIndex = cell.getRowIndex();
            if (!lastRow.containsKey(currentCellIndex)) {
                // 记录首行起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
                return;
            }
            //有上行这列的值了，拿来对比.
            MergeRange mergeRange = lastRow.get(currentCellIndex);
            if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
                // 结束的位置触发下合并.
                // 同行同列不能合并，会抛异常
                if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
                }
                // 更新当前列起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
            }
            // 合并行 + 1
            mergeRange.endRow += 1;
            // 结束的位置触发下最后一次没完成的合并
            if (relativeRowIndex.equals(maxRow - 1)) {
                MergeRange lastMergeRange = lastRow.get(currentCellIndex);
                // 同行同列不能合并，会抛异常
                if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
                }
            }
        }
    }
}

class MergeRange {
    public int startRow;
    public int endRow;
    public int startCell;
    public int endCell;
    public String lastValue;

    public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
        this.startRow = startRow;
        this.endRow = endRow;
        this.startCell = startCell;
        this.endCell = endCell;
        this.lastValue = lastValue;
    }
}

通用线程池：

/**
 * 通用线程池,对于使用频次比较低的,异步执行不需要返回结果的,可以使用此方法
 */
@Configuration
@EnableAsync
public class CommonThreadManage {
    /**
     * 日志服务
     */
    private static final Logger logger = LoggerFactory.getLogger(CommonThreadManage.class);
    /**
     * 线程数量
     */
    private static final int THREAD_COUNT = 100;
    /**
     * 线程数量
     */
    private static final int THREAD_MAX_COUNT = 150;
    /**
     * 线程数量最大任务队列数量
     */
    private static final int THREAD_TASK_MAX_COUNT = 1000;

    /**
     * 异步线程配置
     *
     * @return 返回线程池配置
     */
    @Bean
    public Executor asyncCommonExecutor() {
        logger.info("start asyncCommonExecutor");
        ThreadPoolTaskExecutor executor = new VisiableThreadPoolTaskExecutor();
        //配置核心线程数
        executor.setCorePoolSize(THREAD_COUNT);
        //配置最大线程数
        executor.setMaxPoolSize(THREAD_MAX_COUNT);
        //配置队列大小
        executor.setQueueCapacity(THREAD_TASK_MAX_COUNT);
        //配置线程池中的线程的名称前缀
        executor.setThreadNamePrefix("async-hotel-common-");
        // rejection-policy：当pool已经达到max size的时候，如何处理新任务
        // CALLER_RUNS：不在新线程中执行任务，而是有调用者所在的线程来执行
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        //执行初始化
        executor.initialize();
        return executor;
    }
}

文件读取工具类：

public class FileUtil {
    /**
     * 以byte[]方式读取文件
     *
     * @param fileName 文件名
     * @return
     * @throws IOException
     */
    public static byte[] readFileByBytes(String fileName) throws IOException {
        try (InputStream in = new BufferedInputStream(new FileInputStream(fileName));
             ByteArrayOutputStream out = new ByteArrayOutputStream();) {
            byte[] tempbytes = new byte[in.available()];
            for (int i = 0; (i = in.read(tempbytes)) != -1;) {
                out.write(tempbytes, 0, i);
            }
            return out.toByteArray();
        }
    }

    /**
     * 向文件写入byte[]
     *
     * @param fileName 文件名
     * @param bytes    字节内容
     * @param append   是否追加
     * @throws IOException
     */
    public static void writeFileByBytes(String fileName, byte[] bytes, boolean append) throws IOException {
        try(OutputStream out = new BufferedOutputStream(new FileOutputStream(fileName, append))){
            out.write(bytes);
        }
    }

    /**
     * 从文件开头向文件写入byte[]
     *
     * @param fileName 文件名
     * @param bytes    字节
     * @throws IOException
     */
    public static String writeFileByBytes(String fileName, byte[] bytes) throws IOException {
        writeFileByBytes(fileName, bytes, false);
        return fileName;
    }

}

缓存操作工具：

@Component
@Getter
public class CacheService extends CachingConfigurerSupport {

    @Resource
    private StringRedisTemplate stringRedisTemplate;
    
   
    /**
    * 是否存在key
    *
    * @param key
    * @return
    */
    public Boolean exists(String key) {
    return stringRedisTemplate.hasKey(key);
    }
    
    
    /**
     * 获取指定 key 的值
     *
     * @param key
     * @return
     */
    public String get(String key) {
        return stringRedisTemplate.opsForValue().get(key);
    }
     /**
     * 将值 value 关联到 key ，并将 key 的过期时间设为 timeout
     *
     * @param key
     * @param value
     * @param timeout 过期时间
     * @param unit    时间单位, 天:TimeUnit.DAYS 小时:TimeUnit.HOURS 分钟:TimeUnit.MINUTES
     *                秒:TimeUnit.SECONDS 毫秒:TimeUnit.MILLISECONDS
     */
    public void setEx(String key, String value, long timeout, TimeUnit unit) {
        stringRedisTemplate.opsForValue().set(key, value, timeout, unit);
    }
    /**
     * 获取字符串的长度
     *
     * @param key
     * @return
     */
    public Long size(String key) {
        return stringRedisTemplate.opsForValue().size(key);
    }
    
    /**
     * 追加到末尾
     *
     * @param key
     * @param value
     * @return
     */
    public Integer append(String key, String value) {
        return stringRedisTemplate.opsForValue().append(key, value);
    }
    
    /**
     * 获取集合的元素, 从小到大排序
     *
     * @param key
     * @param start 开始位置
     * @param end   结束位置, -1查询所有
     * @return
     */
    public Set<String> zRange(String key, long start, long end) {
        return stringRedisTemplate.opsForZSet().range(key, start, end);
    }
    
    
}

参考

1. EasyExcel 带格式多线程导出百万数据（实测好用）_多线程easyexcel导出-CSDN博客
2. Java中实体与Map的相互转换_java 实体类转map-CSDN博客