20240805每日后端----------百万级Excel导出工具类，支持使用字典工具类翻译

public class EasyExcelUtil<T,P> {
  private static final Logger log = LoggerFactory.getLogger(EasyExcelUtil.class);
  public Class<T> clazz;

  public Class<P> paramsClazz;

  public EasyExcelUtil(Class<T> clazz, Class<P> paramsClazz) {
        this.clazz = clazz;
        this.paramsClazz = paramsClazz;
  }


  //导出大数据量（百万级）逻辑代码
  public void exportData(HttpServletResponse response, Integer totalCount, String fileName, SelectMethod<P> selectMethod,P params) {
    {
      OutputStream outputStream = null;
      try {
        long startTime = System.currentTimeMillis();
        outputStream = response.getOutputStream();
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置内容
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        ExcelWriter writer = EasyExcelFactory.write(outputStream, clazz)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new CustomSheetWriteHandler())
                .registerWriteHandler(horizontalCellStyleStrategy) // 注册自定义策略
                .registerWriteHandler(new ExcelWidthStyleStrategyHandler()) // 自适应宽度
                .build();
        //每一个Sheet存放100w条数据
        int sheetDataRows = 1000000;
        //数据量大于10万，每次写入的数据量20w，否则每次写入的数据量5w
        int writeDataRows = totalCount > 100000 ? 200000 : 50000;

        //计算需要的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));

        //开始分批查询分次写入
        //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
        for (int i = 0; i < sheetNum; i++) {
          //创建Sheet
          WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetNum-1, "第" + (sheetNum)+"页")
                  .head(clazz)
                  .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                  .build();
          //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
          for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
            //分页查询
            PageMethod.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
            Object result = selectMethod.list(params);
            writer.write((Collection<?>) result, writeSheet);
          }
        }
        response.reset();
        // 下载EXCEL
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setContentType("application/octet-stream; charset=UTF-8");
        writer.finish();
        outputStream.flush();
        //导出时间结束
        long endTime = System.currentTimeMillis();
        log.info("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
      } catch (IOException e) {
        log.error("导出Excel异常{}", e.getMessage());
        throw new CustomException("导出Excel失败，请联系网站管理员！");
      } finally {
        if (outputStream != null) {
          try {
            outputStream.close();
          } catch (Exception e) {
            e.printStackTrace();
          }
        }
      }
    }
  }
}