easyExcel的多线程导出

1、导出的核心方法：

import cn.com.cgnpc.aep.bizcenter.common.service.impl.FileExportContext;
import cn.com.cgnpc.aep.bizcenter.common.service.impl.FileExportService;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;
 
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.CountDownLatch;
 
@Component
public class ExcelExportUtil {
 
    @Resource
    @Qualifier("excelThreadPool")
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;
 
    /**
     * @param totalCount 总记录条数
     * @param clazz 导出的Excel对象
     * @param service 具体实现查询数据的服务类
     * @param map 查询参数
     */
    public void exportExcel(HttpServletResponse response,int totalCount, Class<?> clazz, FileExportService service, Map<String,Object> map) throws Exception{
 
        FileExportContext context = new FileExportContext(service);
 
        //文件名
        String fileName = String.valueOf(System.currentTimeMillis());
 
        //ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        OutputStream outputStream = null;
        try {
            //每一个Sheet存放1w条数据
            Integer sheetDataRows = 100;
            //每次写入的数据量5000,每页查询5000
            Integer writeDataRows = 50;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : totalCount % sheetDataRows % writeDataRows == 0 ? totalCount % sheetDataRows / writeDataRows : (totalCount % sheetDataRows / writeDataRows)+1;
 
            outputStream = response.getOutputStream();
 
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
 
            Map<Integer,List<List<String>>> pageMap = new ConcurrentHashMap<>(Math.toIntExact(sheetNum));
 
            CountDownLatch countDownLatch = new CountDownLatch(Math.toIntExact(sheetNum));
            // 多线程查询参数Map
            Map<Integer,Map<String,Object>> queryMap = new ConcurrentHashMap<>();
 
            //开始分批查询分次写入 sheetNum
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("Sheet"+i);
                sheet.setSheetNo(i);
                int finalNum = i;
                threadPoolTaskExecutor.submit(()->{
                    ConcurrentHashMap<String,List<List<String>>> dataListMap = new ConcurrentHashMap<>();
                    //循环写入次数, j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                    for (int j = 0; j < (finalNum != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                        int finalJ = j;
                        queryMap.put(finalNum,new HashMap<String,Object>(){
                        {
                put("page",finalNum * sheetDataRows + finalJ * writeDataRows);
                put("pageSize",writeDataRows);
                putAll(map);
    }
});
                        // 策略模式调用查询
                    List<List<String>> dataList = Optional.ofNullable(pageMap.get(finalNum)).orElse(new ArrayList<>());
                    dataList.addAll(context.queryPageExcel(queryMap.get(finalNum)));
                    pageMap.put(finalNum,dataList);    
                    }
                    
                    countDownLatch.countDown();
                });
            }
            try{
                countDownLatch.await();
            }catch (Exception e){
                e.printStackTrace();
            }
            // 关闭线程
            threadPoolTaskExecutor.shutdown();
 
            pageMap.forEach((k,v)->{
                // 分sheet保存数据
                WriteSheet writeSheet = EasyExcel.writerSheet(k, "Sheet" + (k + 1)).head(clazz)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(v,writeSheet);
                pageMap.remove(k);
            });
 
            // 下载EXCEL，返回给前端stream流
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
        //return new ByteArrayInputStream(outputStream.toByteArray());
    }
 
}

多线程配置：

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
 
import java.util.concurrent.ThreadPoolExecutor;
 
@Configuration
public class ConcurrentThreadGlobalConfig {
 
    @Bean("excelThreadPool")
    public ThreadPoolTaskExecutor defaultThreadPool(){
        // 创建线程池
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        // 核心线程数
        executor.setCorePoolSize(Runtime.getRuntime().availableProcessors()+1);
        // 最大线程数
        executor.setMaxPoolSize(Runtime.getRuntime().availableProcessors()+1);
        // 队列中的最大数目
        executor.setQueueCapacity(600);
        // 线程名称的前缀
        executor.setThreadNamePrefix("defaultThreadPool_");
        // 线程拒绝策略，由调用者决定
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        // 空闲线程时间
        executor.setKeepAliveSeconds(60);
        // 加载线程
        executor.initialize();
        return executor;
    }
 
}