EasyExcel结合多线程+控制sheet页全量导出

业务分析

内部平台需要一个导出mysql数据到excel的方法，所以使用了EasyExcel

因为EasyExcel的sheet页是放到一个List里面的，如果把百万量级的数据放到sheet页中全量写入会有OOM风险，所以最终选择的方案是分sheet页写入

同时因为该平台是多用户的，所以不仅要控制当前方法的sheet页的总量，还要估算多用户场景下sheet页加起来的量大小，如果规定每个写入的sheet页的量级为50w，那么10个用户并行导出的时候是不是500w的量级？

那就会出现OOM问题，为了严格控制sheet页的和总量，就要细致化到控制每个线程的每次写入的sheet页的量

为了优化多线程，还可以上深分页优化来进一步优化

多优化逻辑：线程+多sheet页+深分页

代码逻辑解析

我会开3个线程，然后sheet页按照10w为一批写入，3个线程就是30w

0-30w就用主线程，30w-60w就用两个线程，60w以后就用3个线程

本次要导出的是90w量级的数据，我们严格控制sheet也就是每次写入的一批数据量batchSize为10w

导出数据总量、每批数据量，算出写入sheet页的总批次

总批次/要开启的线程数，得到每个线程要写入的批次

有3个线程，每个线程的第一次查询要拿到第一批的数据的最后一个数据的id（sql是根据id排序的）

SQL： @Select("select * from excel where id >#{lastMaxId} order by id limit #{batchsize} ")

然后每个线程除了第一次查询，其他查询都可以优化深分页

为什么我要写入多个文件而不是多线程写入一个文件？

首先我尝试并发写同一个文件，然后报错了，因为文件是不能并发写的，多线程写入会导致文件烂掉然后再也打不开

然后我想到了我们多线程写不同的文件，然后通过python脚本去合并多个sheet页和多个xsml文件

有个小问题就是xsml文件本身的问题：一个sheet页最多只能写入，所以文件本身就会有限制，这就是为什么要写入多个文件和多个sheet页最终合并文件和sheet页

测试类代码

package com.example.kiratest.test.Excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.kiratest.EasyExcel.Mapper.ExcelMapper;
import com.example.kiratest.EasyExcel.pojo.Excel;
import com.github.pagehelper.PageHelper;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ThreadPoolExecutor;
//并发写同一个文件
@SpringBootTest
@Slf4j
public class MutiExcelTest {

    @Resource
    private ExcelMapper excelMapper;

    @Resource
    Map<String, ThreadPoolExecutor> ThreadPoolMap;

    @Test
    public void getData() throws InterruptedException {

        ThreadPoolExecutor easyExcelThreadPool = ThreadPoolMap.get("EasyExcelThreadPool");//拿到线程池

        int pageSize = 100000;//10w为一批
        Integer total = excelMapper.countTotal();//统计数据总数
        log.info("总数:{}",total);
        //batchSize<3,用一个线程,6就是2个线程,9就是3个线程
        int batchSize = (int) Math.ceil((double) total / pageSize);//一共要执行的批次
        log.info("batchSize:{}",batchSize);
        int everythreadSize = batchSize / 3;//一共开三个线程,每个线程应该负责多少批次



        int threadCount = 1; // 主线程
        if (batchSize > 3 && batchSize < 6) threadCount += 1;
        else if (batchSize > 6) threadCount += 2;
        CountDownLatch countDownLatch = new CountDownLatch(threadCount);

        //开始EasyExcel写入
        String filePath = "C:\\Users\\ziJian.zheng\\IdeaProjects\\Kira-Test\\src\\main\\resources\\templates\\test1.xlsx";
        //用来写入的ExcelWriter
        ExcelWriter writer = EasyExcel.write(filePath).build();


//
//
//
        //开启异步线程
        if (batchSize > 3 ) {
            easyExcelThreadPool.execute(() -> {
                try {
                    //用来写入的ExcelWriter
                    ExcelWriter writer1 = EasyExcel.write(filePath).build();
                    int first = everythreadSize + 1;//作为sheet页标识,也就是批次标识
                    Integer lastId = null;
                    //多Sheet写入
                    for (int i = 0; i < everythreadSize; i++) {
                        WriteSheet sheet = EasyExcel.writerSheet("批次" + first++).build();
                        if (i == 0) {//也就是我们的第一次写入
                            PageHelper.startPage(everythreadSize+1,pageSize);//第一次普通的分页查询,后面要知道id，使用书签法来优化深度分页问题
                            List<Excel> excels = excelMapper.selectAllOrderById();

                            if (excels.size() > batchSize)
                                lastId = excels.get(excels.size() - 1).getId();
                            log.info("LastId:{}",lastId);
                            writer1.write(excels, sheet);

                        } else { //我们的书签法优化
                            List<Excel> excels = excelMapper.deepPaginationSelect(lastId, pageSize);

                            lastId = excels.get(excels.size() - 1).getId();
                            log.info("LastId:{}",lastId);
                            writer1.write(excels, sheet);

                        }
                    }
                    writer1.finish();
                } finally {
                    countDownLatch.countDown();
                }
            });
        }

        if (batchSize > 6) {
            easyExcelThreadPool.execute(() -> {
                try {
                    //用来写入的ExcelWriter
                    ExcelWriter writer2 = EasyExcel.write(filePath).build();
                    int first = 2*everythreadSize + 1;//作为sheet页标识,也就是批次标识
                    Integer lastId = null;
                    //多Sheet写入
                    for (int i = 0; i < everythreadSize; i++) {
                        WriteSheet sheet = EasyExcel.writerSheet("批次" + first++).build();
                        if (i == 0) {//也就是我们的第一次写入
                            PageHelper.startPage(everythreadSize*2+1,pageSize);//第一次普通的分页查询,后面要知道id，使用书签法来优化深度分页问题
                            List<Excel> excels = excelMapper.selectAllOrderById();

                            if (excels.size() > batchSize)
                                lastId = excels.get(excels.size() - 1).getId();
                            log.info("LastId:{}",lastId);
                            writer2.write(excels, sheet);

                        } else { //我们的书签法优化
                            List<Excel> excels = excelMapper.deepPaginationSelect(lastId, pageSize);
                            lastId = excels.get(excels.size() - 1).getId();
                            log.info("LastId:{}",lastId);
                            writer2.write(excels, sheet);

                        }
                    }
                    writer2.finish();
                } finally {
                    countDownLatch.countDown();
                }


            });
        }


        //主线程执行
        try {
            int first = 0;//作为sheet页标识,也就是批次标识
            Integer lastId = null;
            //多Sheet写入
            for (int i = 0; i < everythreadSize; i++) {
                WriteSheet sheet = EasyExcel.writerSheet("批次" + first++).build();
                if (i == 0) {//也就是我们的第一次写入
                    PageHelper.startPage(1, pageSize);//第一次普通的分页查询,后面要知道id，使用书签法来优化深度分页问题
                    List<Excel> excels = excelMapper.selectAllOrderById();
                    if (excels.size() > batchSize)
                        lastId = excels.get(excels.size() - 1).getId();
                    log.info("LastId:{}",lastId);
                    writer.write(excels, sheet);

                } else { //我们的书签法优化
                    List<Excel> excels = excelMapper.deepPaginationSelect(lastId, pageSize);
                    lastId = excels.get(excels.size() - 1).getId();
                    log.info("LastId:{}",lastId);
                    writer.write(excels, sheet);

                }
            }
        }
        finally {
            writer.finish();
            countDownLatch.countDown();
        }

        countDownLatch.await();

        log.info("导出结束");


    }

    @Test
    void test(){
        List<Excel> excels = excelMapper.deepPaginationSelect(100, 1000);
        System.out.println(excels);
    }


}

Mapper

package com.example.kiratest.EasyExcel.Mapper;

import com.example.kiratest.EasyExcel.pojo.Excel;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ExcelMapper {
    @Select("select * from excel order by id")
     List<Excel> selectAllOrderById();  //查询数据根据id排序,如果这个要分页的话我们就是用我们的PageHelper

    @Select("select count(*) from excel")
    Integer countTotal();

    @Select("select * from excel where id >#{lastMaxId} order by id limit #{batchsize} ")
    List<Excel> deepPaginationSelect(Integer lastMaxId,Integer batchsize); //深度分页通过书签法解决

}

POJO类

package com.example.kiratest.EasyExcel.pojo;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(20)
public class Excel {

    @ExcelIgnore
    private Integer id;

    @ExcelProperty(value = "用户姓名",index = 0)
    private String name;

    @ExcelProperty(value = "用户Id",index = 1)
    private String uesrId;

    @ExcelProperty(value = "居住地址",index = 2)
    private String location;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createTime;
}