easyExcel分页导出数据实现

@PostMapping("/excel/export")
@ApiOperation(value = "通用导出")
@LogAnnotation(module = IasrConstants.MODULE, recordRequestParam = true)
public void exportQueryData(@RequestBody ExcelQueryDto excelQueryDto, HttpServletResponse response) {
    long startTime = System.currentTimeMillis(); // 记录开始时间
    if(ObjectUtils.isEmpty(excelQueryDto) || StringUtils.isEmpty(excelQueryDto.getQuerySql())){
        throw new BusinessException("无导出sql，请检查！");
    }
    ExcelWriter excelWriter = null;
    try {
        Pageable pageable = PageRequest.of(0, Integer.parseInt(exportDataNums));
        Page<Map<String, Object>> dataPage = projPlanReportRepository.findReportPage(pageable, excelQueryDto.getQuerySql(), excelQueryDto.getParamList());
        String fileName = excelQueryDto.getFileName();
        fileName = Optional.ofNullable(fileName).orElse("导出数据");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        String dataSheetName = StringUtils.isEmpty(excelQueryDto.getDataSheetName()) ? "查询数据" : excelQueryDto.getDataSheetName();
        WriteSheet writeSheet = EasyExcel.writerSheet(dataSheetName).build();
        excelWriter = EasyExcel.write(response.getOutputStream()).build();
        if(!ObjectUtils.isEmpty(dataPage) && !CollectionUtils.isEmpty(dataPage.getContent())){
            List<String> excelTitleList = CollectionUtils.isEmpty(excelQueryDto.getExcelTitleList())?new ArrayList<>(dataPage.getContent().get(0).keySet()):excelQueryDto.getExcelTitleList();
            List<List<String>> headerList = Collections.singletonList(excelTitleList);
            excelWriter.write(headerList, writeSheet);
    
            int totalPage = dataPage.getTotalElements()>100000?100000/Integer.parseInt(exportDataNums):dataPage.getTotalPages();
            int currPage = 0;
            logger.info("查询数据共"+totalPage+"页,数据共"+dataPage.getTotalElements()+"条。。。");
            while (currPage<=totalPage){
                logger.info("开始查询第"+currPage+"页。。。。");
                try {
                    pageable = PageRequest.of(currPage, Integer.parseInt(exportDataNums));
                    Page<Map<String, Object>> pageData = projPlanReportRepository.findReportPage(pageable, excelQueryDto.getQuerySql(), excelQueryDto.getParamList());
                    if (!ObjectUtils.isEmpty(pageData) && !CollectionUtils.isEmpty(pageData.getContent())) {
                        Map<String, Map<String, Object>> dictionaryMap = excelQueryDto.getDictitoryMap();
                        List<List<Object>> dataList = getExcelDateList(excelQueryDto, pageData, headerList, dictionaryMap);
                        excelWriter.write(dataList, writeSheet);
                    }
                } catch (Exception exception) {
                    logger.error("查询异常", exception);
                }
                logger.info("查询第"+currPage+"页结束");
                currPage++;
            }
            if(!ObjectUtils.isEmpty(excelQueryDto.getExportSqlFlag()) && excelQueryDto.getExportSqlFlag()){
                String sqlSheetName = StringUtils.isEmpty(excelQueryDto.getSqlSheetName()) ? "查询sql" : excelQueryDto.getSqlSheetName();
                WriteSheet writeSqlSheet = EasyExcel.writerSheet(2,sqlSheetName).build();
                List<List<String>> dataList = Collections.singletonList(Collections.singletonList(excelQueryDto.getQuerySql()));
                excelWriter.write(dataList,writeSqlSheet);
            }
            excelWriter.finish();
        }
    } catch (Exception e) {
        logger.error("导出excel异常", e);
        throw new BusinessException("导出excel异常:"+e.getMessage());
    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
        logger.info("导出excel耗时：" + (System.currentTimeMillis()-startTime)/1000 + "s");
    }

}

private static List<List<Object>> getExcelDateList(ExcelQueryDto excelQueryDto, Page<Map<String, Object>> pageData, List<List<String>> headerList, Map<String, Map<String, Object>> dictionaryMap) {
    List<List<Object>> dataList;
    if(MapUtils.isNotEmpty(excelQueryDto.getDictitoryMap())){
        dataList = pageData.getContent().stream()
                .map(data -> headerList.get(0).stream()
                        .map(header -> {
                            Object originalValue = data.get(header);
                            if (dictionaryMap.containsKey(header)) {
                                Map<String, Object> valueMap = dictionaryMap.get(header);
                                return valueMap.getOrDefault(ObjectUtils.isEmpty(originalValue)?"":originalValue.toString(), originalValue);
                            } else {
                                return originalValue;
                            }
                        })
                        .collect(Collectors.toList()))
                .collect(Collectors.toList());
    } else {
        dataList = pageData.getContent().stream().map(data -> headerList.get(0).stream().map(data::get).collect(Collectors.toList())).collect(Collectors.toList());
    }
    return dataList;
}

@Override
@Transactional
public Page<Map<String, Object>> findReportPage(Pageable pageable, String sql, List<Object> paramList) {
    Page<Map> pageResult;
    if(CollectionUtils.isNotEmpty(paramList)){
       pageResult = JpaUtils.nativeQuery(sql, Map.class, pageable,paramList.toArray());
    }else {
       pageResult = JpaUtils.nativeQuery(sql, Map.class, pageable);
    }
       return new PageImpl<>(pageResult.getContent().stream()
          .map(m -> {
             Map<String, Object> stringObjectMap = new HashMap<>();
             m.forEach((key, value) -> {
                if (value instanceof Date || value instanceof Character){
                   value = value.toString();
                }
                stringObjectMap.put(String.valueOf(key), value);
             });
             return stringObjectMap;
          }).collect(Collectors.toList()),
          pageResult.getPageable(),
          pageResult.getTotalElements());
}