多线程导出mysql海量数据到excel表格

1.导入maven依赖包

   <!--引入easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

2.导入工具类

package com.dk.config;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class ExcelHandler {

    private List<WriteSheet> sheets;
    private String dirName;
    private static final Integer DEFAULT_SHEETS_NUM = 5;
    private static final Integer DEFAULT_PER_SHEET_NUM = 1000000;

    public ExcelHandler(){}

    public ExcelHandler(String folderName){
        dirName = folderName;
    }

    /**
     *  创建excel和sheet，创建时可以指定sheet数量
     * @param excelName
     * @param clazz
     * @param sumSheet
     * @return
     */
    public ExcelWriter create(String excelName, Class clazz, int sumSheet){
        ExcelWriter excelWriter = EasyExcel.write(route(excelName), clazz.asSubclass(clazz)).build();
        createSheets(sumSheet);
        return excelWriter;
    }

    /**
     *  创建excel和sheet，sheet 数量默认 5， 最高可存放500W 行左右的数据，受每个sheet存放数据的限制
     * @param excelName
     * @param clazz
     * @return
     */
    public ExcelWriter create(String excelName, Class clazz){
        ExcelWriter excelWriter = EasyExcel.write(route(excelName), clazz.asSubclass(clazz)).build();
        createSheets(DEFAULT_SHEETS_NUM);
        return excelWriter;
    }

    /**
     *  写数据到excel， 仅使用一个sheet，不可用于百万以上数据
     * @param excelWriter
     * @param list
     */
    public void write(ExcelWriter excelWriter, List list){
        excelWriter.write(list, sheets.get(0));
    }

    /**
     *  写数据到excel
     * @param excelWriter
     * @param list    每一次的数据
     * @param sheetNum  sheet页码
     */
    public void write(ExcelWriter excelWriter, List list, int sheetNum){
        excelWriter.write(list, sheets.get(sheetNum));
    }

    /**
     *  写完数据关闭(finish 有关流操作)，必须的操作
     * @param excelWriter
     */
    public void finish(ExcelWriter excelWriter){
        excelWriter.finish();
    }

    /**
     *  创建指定数量的sheet
     * @param num
     */
    private void createSheets(int num){
        sheets = new ArrayList();
        for (int i = 0; i <= num; i++) {
            WriteSheet sheet = EasyExcel.writerSheet(i, "sheet"+i).build();
            sheets.add(sheet);
        }
    }

    /**
     *  获取excel存放路径
     * @param excelName
     * @return
     */
    private String route(String excelName){
        if (null == dirName){
            dirName = "D:\\";
        }
        String filePath = "D:\\" + dirName + "/";
        File file = new File(filePath);
        if (!file.exists()){
            file.mkdirs();
        }
        return filePath + excelName + ".xlsx";
    }
}

3.对需要导出的实体类添加注解

package com.dk.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

/**
 * <p>
 * 
 * </p>
 *
 * @author qinyu
 * @since 2023-04-19
 */


@Data
@EqualsAndHashCode(callSuper = false)
@TableName("five_w")
@AllArgsConstructor
@NoArgsConstructor
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(25)
public class W implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty("编号")
    private Long id;

    @TableField("name")
    @ExcelProperty("名字")
    private String name;

    @TableField("age")
    @ExcelProperty("年龄")
    private Integer age;

    @TableField("sex")
    @ExcelProperty("性别")
    private String sex;

    @TableField("phone")
    @ExcelProperty("电话")
    private String phone;

    @TableField("email")
    @ExcelProperty("邮箱")
    private String email;


}

4.自定义spring线程池ThreadPoolTaskExecutor 和 jdk 线程池 ThreadPoolExecutor

package com.dk.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Executor;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

@Configuration
@EnableAsync
public class ThreadPoolExecutorConfig {


    @Bean("threadPoolExecutor")
    public ThreadPoolExecutor threadPoolExecutor(){
        return new ThreadPoolExecutor(16,32,0L, TimeUnit.SECONDS,new LinkedBlockingQueue<>(1),new ThreadPoolExecutor.AbortPolicy());
    }

    @Bean("springThreadPool")
    public ThreadPoolTaskExecutor springThreadPool(){

        ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();

        threadPoolTaskExecutor.setCorePoolSize(10);
        threadPoolTaskExecutor.setMaxPoolSize(20);
        threadPoolTaskExecutor.setQueueCapacity(10);
        threadPoolTaskExecutor.setKeepAliveSeconds(20);
        threadPoolTaskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());

        threadPoolTaskExecutor.initialize();
        return threadPoolTaskExecutor;



    }

}

5.controller代码

@GetMapping("export")
public String  export(){
    wService.export();
    return "ok";
}

6.service层代码

 @Autowired
    private WMapper wMapper;

    @Autowired
    private ThreadPoolTaskExecutor springThreadPool;


    @Override
    public void export() {
        List<W> ws = wMapper.selectList(null);//查询出来全部需要导出的数据

        int pageSize = 20000;//每个excel存储的条数
        int totalPage = (ws.size() % pageSize) == 0 ? ws.size() / pageSize : (ws.size() / pageSize) + 1;//计算总页数
        for (int i = 1; i <= totalPage; i++) {//有几页就循环几次

            int pageNo = i;
            springThreadPool.execute(() -> {//使用线程池执行导出

                ExcelHandler handler = null;
                ExcelWriter writer = null;

                int start = pageNo <= 1 ? 0 : (pageNo - 1) * pageSize; //计算分页起始值
                //使用lambda表达式对总数据进行分页
                List<W> collect1 = ws.stream().skip(start).limit(pageSize).collect(Collectors.toList());
                try {
                    System.out.println(Thread.currentThread().getName() + "第" + pageNo + "次导入开始" + new Date());
                    // 创建handler对象 -- 参数文件夹名
                    handler = handler = new ExcelHandler("Thread"); //指定文件夹的名字
                    writer = writer = handler.create("记录" + pageNo, W.class);//指定表名 和实体类
                    handler.write(writer, collect1);
                    System.out.println("第" + pageNo + "次导入完毕" + new Date() + Thread.currentThread().getName());
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (null != writer) {
                        handler.finish(writer);
                    }
                }


            });
        }
    }

7.执行结果 测试5w 数据仅需2s