springBoot中使用easyExcel导出

pom依赖

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>

Controller层

// 导出时方法返回值为void
@PostMapping("/exportQAData")
    public void exportQAData(String startDate, String endDate, HttpServletResponse response) {
        qADataManageService.exportQAData(response, startDate,endDate);
    }

Service层

@Component
@Slf4j
public class QADataManageService implements FileExportService<QADataManageVo> {
    /**
     * 声明并获取dao层实例
     */
    @Autowired
    private QADataManageDao qADataManageDao;
    @Autowired
    private ExcelExportUtil excelExportUtil;

    public Boolean exportQAData(HttpServletResponse response, String startDate,String endDate) {
        Map<String, Object> maps = new HashMap<>();
        maps.put(QADataManageConstant.CUSTOMER_TIME, startDate);
        maps.put(QADataManageConstant.FORECAST_TIME, endDate);
        Integer qaDataTotal = qADataManageDao.getQADataTotal(maps);
        if (qaDataTotal > 0) {
            excelExportUtil.multiThreadExportExcel(response, qaDataTotal, QADataManageVo.class, this, maps, null);
            return true;
        }
        return false;
    }

    @Override
    public List<QADataManageVo> queryPageExcel(Map<String, Object> map) {
        Integer pageNum = (Integer) map.get("page");
        Integer pageSize = (Integer) map.get("pageSize");
        List<QADataManagePo> qaDataManagePos = qADataManageDao.findAllList(pageNum, pageSize, map);
        return buildVoItem(qaDataManagePos);
    }
    }

策略工厂

1. 接口层面

public interface FileExportService<R> {

    /**
     * Description 通用分页接口
     * @date 2023/11/2 11:12
     * @param: map 包含页面大小和当前页数据
     */
    List<R> queryPageExcel(Map<String,Object> map);
}

2. 工厂

public class FileExportFactory<R> {

    private FileExportService<R> fileExportService;

    public FileExportFactory(FileExportService<R> fileExportService){
        this.fileExportService = fileExportService;
    }

    public List<R> queryPageExcel(Map<String,Object> map){
        return fileExportService.queryPageExcel(map);
    }
}

工具类

@Component
public class ExcelExportUtil {

    @Autowired
    @Qualifier("excelThreadPool")
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

    /**
     * @param response   响应
     * @param totalCount 总记录条数
     * @param clazz      导出的Excel对象
     * @throws Exception
     */
    public <R> void exportExcel(HttpServletResponse response, int totalCount, Class<?> clazz, FileExportService<R> service, String fileName) throws Exception {
        //文件名
        fileName = StrUtil.isNotEmpty(fileName) ? fileName : String.valueOf(System.currentTimeMillis());
        FileExportFactory<R> context = new FileExportFactory<>(service);

        OutputStream outputStream = null;
        try {
            //每一个Sheet存放5000条数据
            int sheetDataRows = 5000;
            //每次写入的数据量3000,每页查询3000
            Integer writeDataRows = 3000;
            //计算需要的Sheet数量
            int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            int oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            int 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();
            // 缓存写入的数据
            List<R> dataList = new ArrayList<>();

            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);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    // 每次数据写入后清空集合
                    dataList.clear();
                    // 从数据库获取数据分页获取数据

                    queryMap.put("page", i * sheetDataRows + j * writeDataRows);
                    queryMap.put("pageSize", writeDataRows);
                    dataList = context.queryPageExcel(queryMap);

                    // 分sheet保存数据
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(clazz)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

                    excelWriter.write(dataList, writeSheet);
                }
            }
            // 下载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) {
            logger.info("excel导出异常", e);
            throw new ServiceException("excel导出异常");
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }


    /**
     * @param totalCount 总记录条数
     * @param clazz      导出的Excel对象
     * @param service    具体实现查询数据的服务类
     * @param map        查询参数
     */
    public <R> void multiThreadExportExcel(HttpServletResponse response, int totalCount, Class<?> clazz, FileExportService<R> service, Map<String, Object> map, String fileName) {
        try{
            FileExportFactory<R> context = new FileExportFactory<>(service);
            //文件名
            fileName = StrUtil.isNotEmpty(fileName) ? fileName : String.valueOf(System.currentTimeMillis());

            OutputStream outputStream = null;
            try {
                //每一个Sheet存放1w条数据
                int sheetDataRows = 100;
                //每次写入的数据量5000,每页查询5000
                int writeDataRows = 50;
                //计算需要的Sheet数量
                int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
                //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
                int oneSheetWriteCount = sheetDataRows / writeDataRows;
                //计算最后一个sheet需要写入的次数
                int 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<R>> 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);
                                    if (CollectionUtil.isNotEmpty(map)) {
                                        // 传递其他查询参数
                                        putAll(map);
                                    }
                                }
                            });
                            // 策略模式调用查询
                            List<R> dataList = pageMap.get(finalNum);
                            if (CollectionUtil.isEmpty(dataList)){
                                dataList = new ArrayList<>();
                            }
                            dataList.addAll(context.queryPageExcel(queryMap.get(finalNum)));
                            // 将分页数据进行存储
                            pageMap.put(finalNum, dataList);
                        }
                        countDownLatch.countDown();
                    });
                }
                try {
                    countDownLatch.await();
                } catch (Exception e) {
                    logger.info("多线程启动异常");
                    throw new ServiceException("多线程启动异常");
                }

                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) {
                logger.info("数据导出异常",e);
            } finally {
                if (outputStream != null) {
                    outputStream.close();
                }
            }
        } catch (Exception e) {
            logger.info("多线程导出数据异常");
            throw new ServiceException("多线程导出数据异常");
        }

    }
}