基于EasyExcel多线程分页导出excel

第七更，基于EasyExcel 多线程分页导出excel
在项目中，BA要求全量导出表中数据，估计有十几万条，同事使用的是EasyPoi导致内存泄漏，我帮他优化，使用阿里的EasyExcel，解决了内存泄漏问题，但是导出17万数据仍需要84秒（本地测试），于是想到了多线程优化，最终测试时间为40秒，服务器上速度会更快，代码如下

Maven依赖

	<dependencies>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>
        
   </dependencies>

线程池配置

import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.*;

@Configuration
public class ThreadPoolConfig {

    @Bean("excelThreadPool")
    public ExecutorService buildExcelThreadPool() {
        int cpuNum = Runtime.getRuntime().availableProcessors();
        BlockingQueue<Runnable> workQueue = new LinkedBlockingQueue<>(1000);
        ThreadFactory threadFactory = new ThreadFactoryBuilder().setNameFormat("excel-pool-%d").build();
        return new ThreadPoolExecutor(10 * cpuNum, 30 * cpuNum,
                1, TimeUnit.MINUTES, workQueue, threadFactory);
    }
}

导出代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Future;
import java.util.concurrent.atomic.AtomicInteger;

@Component
public class MultiThreadExcelExport {

    @Autowired
    @Qualifier("excelThreadPool")
    private ExecutorService executorService;

    @SuppressWarnings("unchecked")
    public void exportExcel() {
        String fileName = "D:\\demo.xlsx";
        ExcelWriter writer = EasyExcel.write(fileName, User.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
        // 根据数据读写速度来调整，一般来说读的逻辑复杂，比较慢，如果读比写快，这里设为1
        int N = 2;
        // 大小设置为2就可以，作为缓冲
        BlockingQueue<List<User>> queue = new ArrayBlockingQueue<>(2);
        AtomicInteger start = new AtomicInteger(0);
        // 分页大小可以适当调整
        int pageSize = 10000;
        //开启多个线程分页查数据
        for (int i = 0; i < N; i++) {
            executorService.submit(() -> {
                while (true) {
                	//自增
                    int startNum= start.getAndAdd(pageSize);
                    try {
                        List<User> list = findPage(startNum, pageSize);
                        if (CollectionUtils.isEmpty(list)) {
                            //读到没数据也要放入空集合
                            queue.put(Collections.EMPTY_LIST);
                            break;
                        }
                        queue.put(list);
                    } catch (Exception e) {
                        //异常情况也要放入空集合，防止写线程无法退出循环
                        queue.put(Collections.EMPTY_LIST);
                        throw new RuntimeException(e);
                    }
                }
            });
        }
        Future<?> submit = executorService.submit(() -> {
         	int count = 0;
            while (true) {
                List<User> list = null;
                try {
                    list = queue.take();
                } catch (InterruptedException e) {
                    Thread.interrupted();
                }
                if (CollectionUtils.isEmpty(list)) {
                    count++;
                    // 当获取到两次空集合时，说明已经读完
                    if (count == N) {
                        break;
                    }
                    continue;
                }
                writer.write(list, writeSheet);
            }
            writer.finish();
        });
        try {
            // 阻塞等待完成，异步处理也可以去掉这段代码
            submit.get();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private List<User> findPage(int startNum, int pageSize) {
        // todo 实现分页查询
        return new ArrayList<>();
    }

    public static class User {

        private Long id;
        @ExcelProperty("姓名")
        private String name;

        @ExcelProperty("编号")
        private String number;

        public Long getId() {
            return id;
        }

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

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getNumber() {
            return number;
        }

        public void setNumber(String number) {
            this.number = number;
        }
    }

}