public ResponseEntity export(PrizeDto param) {
            param.setPageNum(1);
            param.setPageSize(10000);
            ESPageEntity<ESPrizeVo> result = null;
            try {
                result = esUtil2.query(authCode, indexName, fuzzyQueryOrder, fuzzyQueryOrderVersion, param, ESPrizeVo.class);
            } catch (Throwable ex) {
                log.error(ex.getMessage(), ex);
            }
            ExcelUtil<ESPrizeVo> util = new ExcelUtil<>(ESPrizeVo.class);
            List<ESPrizeVo> list = JSON.parseArray(JSON.toJSONString(result.getList()), ESPrizeVo.class);
            return util.exportExcel(list, "數(shù)據(jù)記錄", null);
        }
        
        //util.exportExcel 基本與若以框架自帶的ExcelUtils一致

{
  "size": 100,       // 每次滾動(dòng)返回的文檔數(shù)量
  "query": {
    "match_all": {}  // 你的查詢條件，可以是任何復(fù)雜的查詢
  },
  "sort": ["_doc"]   // 【最佳實(shí)踐】使用 `_doc` 排序以獲得最高效的檢索性能
}

{
  "scroll": "1m",    // 【重要】可以重新設(shè)置 scroll 的超時(shí)時(shí)間，重置倒計(jì)時(shí)
  "scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAD4WYm9laVYtZndUQlNsdDcwakFMNjU1QQ=="
}

public class ESScrollUtil extends ESUtil implements EsSearch {
?
    @Value("${es.xxx.order.authCode}")
    private String authCode;
?
    @Value("${es.xxx.order.indexName}")
    private String indexName;
?
    public static final TransmittableThreadLocal<String> localScrollId = new TransmittableThreadLocal<>();
  
    // 每次滾動(dòng)完進(jìn)行TL清除
    @Override
    public void cleanLocalScrollId() {
        localScrollId.remove();
    }
?
    public <R extends BaseESResult> ScrollRes<R> getFirstScrollId(Class<R> clazz, String templateName, String templateVersion, Object body) {
        ParameterizedTypeImpl type = new ParameterizedTypeImpl(new Type[]{clazz}, null, ESPageEntity.class);
        ScrollRes<R> baseESResultScrollRes = super.queryForScroll(authCode, indexName, templateName, templateVersion, body, type);
        localScrollId.set(baseESResultScrollRes.getScrollId());
        return baseESResultScrollRes;
    }
?
   
    public <R extends BaseESResult> ScrollRes<R> nextScrollPage(Class<R> clazz){
        if (StringUtilsExt.isEmpty(localScrollId.get())) {
            log.error("請先調(diào)用getFirstScrollId方法獲取scrollId");
            throw new CustomException("請先調(diào)用getFirstScrollId方法獲取scrollId");
        }
        ParameterizedTypeImpl type = new ParameterizedTypeImpl(new Type[]{clazz}, null, ESPageEntity.class);
        ScrollRes<R> res = super.queryForScrollNextPage(authCode, indexName, localScrollId.get(), type);
        localScrollId.set(res.getScrollId());
        return res;
    }
}

@Slf4j
public class BaseExcelExporter {
    // clazz緩存, key為類名, value為字段名和注解的map
    protected static final HashMap<Class<?>, List<Excel>> cacheExcelInfo = new HashMap<>();
?
    // 解析Excel緩存, key為 className + "_" + 注解name, value為字段
    protected static final HashMap<String,Field> cacheField = new HashMap<>();
?
    protected static ExecutorService executorService;
?
    static {
        ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
        // 兩倍最大并發(fā)數(shù)滿足了最大處理情況（單次oss處理器需要兩個(gè)線程），需要使用包裝Ttl線程池，繼承ScrollID
        threadPoolTaskExecutor.setCorePoolSize(MAX_CONCURRENCY*2);
        threadPoolTaskExecutor.setMaxPoolSize(MAX_CONCURRENCY*2);
        threadPoolTaskExecutor.setQueueCapacity(0);
        threadPoolTaskExecutor.setThreadNamePrefix("excel-exporterThread-");
        threadPoolTaskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.AbortPolicy());
        threadPoolTaskExecutor.initialize();
        executorService = TtlExecutors.getTtlExecutorService(threadPoolTaskExecutor.getThreadPoolExecutor());
    }
    /**
     * 提交ES查詢?nèi)蝿?wù)，將查詢結(jié)果放入阻塞隊(duì)列，異?；虿樵償?shù)據(jù)為空時(shí)將空數(shù)據(jù)放入隊(duì)列
     * @param task 滾動(dòng)查詢?nèi)蝿?wù)
     * @param taskResQueue 結(jié)果阻塞隊(duì)列
     * @param <R> 結(jié)果類型
     */
    protected <R extends BaseESResult> void commitEsQueryTask(Supplier<ScrollRes<R>> task,
                                                              BlockingQueue<List<R>> taskResQueue){
        try {
            executorService.submit(() -> {
                try {
                    for (;;){
                        ScrollRes<R> taskResult = task.get();
                        List<R> res = taskResult.getRes();
                        if(res.isEmpty()){
                            log.debug("任務(wù)已完成，退出循環(huán)");
                            taskResQueue.put(new ArrayList<>());
                            return;
                        }
                        taskResQueue.put(res);
                    }
                }catch (Exception e) {
                    log.error("ES滾動(dòng)查詢出現(xiàn)異常", e);
                }
            });
        } catch (RejectedExecutionException e) {
            log.error("Excel導(dǎo)出線程池已滿，無法提交任務(wù)", e);
            throw e;
        }
    }
?
    protected <R extends BaseESResult> void syncWriteTaskResult(String handlerName, Class<R> clazz,
                                                                BlockingQueue<List<R>> dataQueue,
                                                                Sheet sheet, CellStyle dateCellStyle){
        long start;
        try {
            for (;;){
                List<R> taskRes = dataQueue.poll(9, TimeUnit.SECONDS);
                if(taskRes == null){
                    log.error("【{}】獲取阻塞隊(duì)列數(shù)據(jù)超時(shí)! 線程退出阻塞等待", handlerName);
                    break;
                }
                log.info("主線程獲取到阻塞隊(duì)列數(shù)據(jù)，數(shù)據(jù)大小:{} 當(dāng)前隊(duì)列剩余數(shù)據(jù)批數(shù)：{}",taskRes.size(), dataQueue.size());
                // 獲取的list為空則結(jié)束了（可能任務(wù)執(zhí)行異常了）
                if(taskRes.isEmpty()){
                    log.info("Excel導(dǎo)出任務(wù)已完成，主線程退出循環(huán)");
                    break;
                }
                // 寫入數(shù)據(jù)
                start = System.currentTimeMillis();
                writeRows(sheet, taskRes, clazz, dateCellStyle);
                log.info("主線程寫入數(shù)據(jù)耗時(shí):{} ms", System.currentTimeMillis() - start);
            }
        } catch (InterruptedException e) {
            log.error("【{}】 消費(fèi)線程被中斷，線程池繁忙", handlerName, e);
            throw new CustomException("系統(tǒng)導(dǎo)出繁忙中，請稍后重試");
        }
    }
?
    public void analysisExcelAnno(Class<?> clazz){
        List<Excel> excels = cacheExcelInfo.computeIfAbsent(clazz, k -> new ArrayList<>());
        //獲取字段上的注解
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            Excel excel = declaredField.getAnnotation(Excel.class);
            if (excel != null) {
                excels.add(excel);
                cacheField.put(clazz.getSimpleName()+"_"+excel.name(), declaredField);
            }
        }
    }
?
    /**
     * 獲取excel表頭, 值為 {@link Excel} 注解的 name 屬性, 順序與字段順序一致
     * 如果是第一次導(dǎo)出該類則會(huì)進(jìn)行 {@link #analysisExcelAnno(Class)} 解析
     * @param clazz 待導(dǎo)出的類
     * @return 表頭
     */
    public String[] getExcelHeader(Class<?> clazz){
        List<Excel> excels = cacheExcelInfo.getOrDefault(clazz, null);
        if (excels == null) {
            analysisExcelAnno(clazz);
            excels = cacheExcelInfo.get(clazz);
        }
        return excels.stream().map(Excel::name).toArray(String[]::new);
    }
?
    public Field getFieldByExcelName(Class<?> clazz, String name){
        Field field = cacheField.get(clazz.getSimpleName()+"_"+name);
        if(field == null){
            throw new CustomException("未找到對應(yīng)的字段,請檢查是否執(zhí)行analysisExcelAnno(clazz) 過該類");
        }
        return field;
    }
?
    /**
     * 寫入數(shù)據(jù)行,需要注意寫入后list數(shù)據(jù)已被清空，無法再次使用
     * @param sheet 工作表
     * @param data 待寫入數(shù)據(jù)
     * @param clazz 數(shù)據(jù)類型
     * @param dateCellStyle 單元格樣式
     */
    public void writeRows(Sheet sheet, List<?> data, Class<?> clazz, CellStyle dateCellStyle){
        String[] headers = getExcelHeader(clazz);
        // 獲取當(dāng)前行數(shù)，判斷是否需要?jiǎng)?chuàng)建表頭
        int currentRowNum = sheet.getPhysicalNumberOfRows();
        // 如果當(dāng)前行數(shù)為 0，說明表頭還未創(chuàng)建，需要?jiǎng)?chuàng)建表頭
        if (currentRowNum == 0) {
            Row headerRow = sheet.createRow(0); // 第 0 行作為表頭行
            for (int i = 0; i < headers.length; i++) {
                // 創(chuàng)建表頭單元格，值為注解的 name 屬性
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                // 設(shè)置每個(gè)列的寬度
                Excel excel = cacheExcelInfo.get(clazz).get(i);
                sheet.setColumnWidth(i, (int) ((excel.width() + 0.72) * 256));
            }
            currentRowNum++; // 表頭占用第 0 行，所以數(shù)據(jù)從第 1 行開始
        }
?
        // 寫入數(shù)據(jù)行
        for (Object item : data) {
            Row dataRow = sheet.createRow(currentRowNum++); // 從當(dāng)前行開始寫入數(shù)據(jù)
            for (int j = 0; j < headers.length; j++) {
                Cell cell = dataRow.createCell(j); // 創(chuàng)建每列單元格
                Field field = getFieldByExcelName(clazz,headers[j]); // 根據(jù)表頭名稱獲取字段
                try {
                    Object value = field.get(item); // 獲取字段值
                    if (value instanceof Date) {
                        // 如果是日期類型，設(shè)置日期值和樣式
                        cell.setCellValue((Date) value);
                        cell.setCellStyle(dateCellStyle);
                    } else {
                        // 其他類型直接轉(zhuǎn)換為字符串
                        cell.setCellValue(value == null ? "" : value.toString());
                    }
                } catch (IllegalAccessException e) {
                    log.error("獲取字段值異常", e);
                    throw new CustomException("獲取字段值異常");
                }
            }
        }
        // 顯式清空輔助jvm回收
        data.clear();
    }
}

    public interface ExcelExportHandler {
        // 導(dǎo)出接口，執(zhí)行此方法即完成了excel導(dǎo)出
        public <R extends BaseESResult> ExcelHandleRes export(Class<R> clazz, ScrollRes<R> firstScroll, Supplier<ScrollRes<R>> nextScroll, SXSSFWorkbook workbook, CellStyle dateCellStyle);
        // 判斷該處理器是否支持處理該滾動(dòng)查詢，這里通過res的count即文檔數(shù)量來選擇同步返回流處理器還是Oss異步處理器
        public Boolean support(ScrollRes<?> scrollRes);
    }
    ?

    ?
    /**
     * 簡單Excel導(dǎo)出處理器，支持導(dǎo)出數(shù)據(jù)量小于40000的數(shù)據(jù)。
     * 主線程寫入數(shù)據(jù)，子線程請求ES進(jìn)行滾動(dòng)查詢，提高導(dǎo)出效率。
     */
    @Service
    @Slf4j
    public class SampleExportHandler extends BaseExcelExporter implements ExcelExportHandler {
    ?
        @Override
        public <R extends BaseESResult> ExcelHandleRes export(Class<R> clazz, ScrollRes<R> firstScroll,
                                                              Supplier<ScrollRes<R>> nextScroll, SXSSFWorkbook workbook,
                                                              CellStyle dateCellStyle) {
            ServletOutputStream outputStream = null;
            try {
                Sheet sheet = workbook.createSheet("Sheet1");
                // 設(shè)置一個(gè)較小值，size 即為緩沖池的滾動(dòng)批次大小，這里最多允許兩個(gè)滾動(dòng)批次結(jié)果存在隊(duì)列中
                BlockingQueue<List<R>> dataQueue = new LinkedBlockingQueue<>(2);
                // 提交Es滾動(dòng)查詢?nèi)蝿?wù)至線程池中
                commitEsQueryTask(nextScroll, dataQueue);
                // 后寫入第一次滾動(dòng)的數(shù)據(jù)
                writeRows(sheet, firstScroll.getRes(), clazz, dateCellStyle);
                // 主線程消費(fèi)阻塞隊(duì)列的寫入數(shù)據(jù)
                syncWriteTaskResult("SampleExcel導(dǎo)出處理器",clazz, dataQueue, sheet, dateCellStyle);
                HttpServletResponse response = getResponse();
                // 獲取輸出流
                outputStream = response.getOutputStream();
                // 將工作簿寫入輸出流
                workbook.write(outputStream);
                // 刷新輸出流
                outputStream.flush();
                return new ExcelHandleRes(true);
            } catch (Exception e){
                log.error("導(dǎo)出 Excel 時(shí)發(fā)生異常",e);
                return new ExcelHandleRes(false);
            }
            finally {
                if(outputStream != null){
                    try {
                        outputStream.close();
                    } catch (Exception e) {
                        log.error("outputStream 關(guān)閉時(shí)發(fā)生異常, ", e);
                    }
                }
            }
        }
    ?
        private HttpServletResponse getResponse() {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            try {
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export.xlsx", "UTF-8"));
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException(e);
            }
            return response;
        }
    ?
        @Override
        public Boolean support(ScrollRes<?> scrollRes) {
            return scrollRes.getCount() <= 40000;
        }
    ?
    }
    ?

    @Component
    public class ExcelExportHandlerFactory {
    ?
        @Autowired
        private List<ExcelExportHandler> handlers;
    ?
        public ExcelExportHandler buildHandler(ScrollRes<?> scrollRes) {
            for (ExcelExportHandler handler : handlers) {
                if (handler.support(scrollRes)) {
                    return handler;
                }
            }
            return null;
        }
    }

    public class LargeExcelUtil implements ApplicationContextAware {
        private static ExcelExportHandlerFactory excelExportHandlerFactory;
    ?
        public static final int MAX_CONCURRENCY = 2;
        // 最大并發(fā)數(shù)量
        private static final AtomicInteger atomicInteger = new AtomicInteger(MAX_CONCURRENCY);
    ?
        /**
         * 一定要設(shè)置分頁大??！
         * @param clazz 返回結(jié)果的類型,必須使用 {@link Excel} 注解
         * @param esSearch es查詢工具類
         * @param templateName es查詢模板名稱
         * @param templateVersion es查詢模板版本
         * @param body es查詢參數(shù)
         * @return
         * @param <R>
         */
        public static <R extends BaseESResult> ExcelHandleRes export(Class<R> clazz, EsSearch esSearch, String templateName, String templateVersion, Object body, Integer pageSize) {
            if(atomicInteger.decrementAndGet() < 0){
                atomicInteger.incrementAndGet();
                throw new CustomException("系統(tǒng)導(dǎo)出繁忙中，請稍后重試");
            }
            // 通過第一次滾動(dòng)獲取scrollID以及滾動(dòng)數(shù)據(jù)量再通過數(shù)據(jù)量獲取處理器來執(zhí)行導(dǎo)出
            ScrollRes<R> firstScrollId = esSearch.<R>getFirstScrollId(clazz, templateName, templateVersion, body);
            ExcelExportHandler excelExportHandler = excelExportHandlerFactory.buildHandler(firstScrollId);
            if (excelExportHandler == null) {
                throw new RuntimeException("待導(dǎo)出數(shù)據(jù)太多了，暫不支持的導(dǎo)出");
            }
            SXSSFWorkbook workbook = null;
            try {
                // 創(chuàng)建 SXSSFWorkbook工作簿，內(nèi)存最大保存pageSize個(gè)數(shù)據(jù), 開啟壓縮臨時(shí)文件減少磁盤空間，但不要開啟使用共享字符會(huì)提高一倍多的寫入時(shí)間
                workbook = new SXSSFWorkbook(null,pageSize,true,false);
                CellStyle dateCellStyle = workbook.createCellStyle();
                // 格式化日期數(shù)據(jù)
                CreationHelper creationHelper = workbook.getCreationHelper();
                dateCellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                return excelExportHandler.export(clazz, firstScrollId, () -> esSearch.nextScrollPage(clazz), workbook, dateCellStyle);
            }
           finally {
                // 因?yàn)槭褂昧薼ocalStorage存儲scrollID，使用完后必須清除
                esSearch.cleanLocalScrollId();
                atomicInteger.incrementAndGet();
                try {
                    if(workbook != null){
                        // 先嘗試關(guān)閉工作簿同時(shí)釋放了內(nèi)存與臨時(shí)文件
                        workbook.close();
                    }
                } catch (IOException e) {
                    log.error("SXSSFWorkbook close 失敗，錯(cuò)誤信息", e);
                }finally {
                    if(workbook != null){
                        // 如果關(guān)閉工作簿失敗則釋放臨時(shí)文件
                        workbook.dispose();
                    }
                }
            }
        }
    ?
        @Override
        public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
            excelExportHandlerFactory = applicationContext.getBean(ExcelExportHandlerFactory.class);
        }
    }