基于反射、POI和OSS的异步导出工具(含同步）

一行代码搞定各种excel导出需求的精简导出组件。

前言

平时我们的项目中，经常会遇到各种各样的导出需求，不管是导出何种类型的DO，同步导出还是异步导出，小数据量导出亦或是大数据量的导出，有没有一个通用的工具类，只需要ExcelHelper.export()就搞定了，而不需要自己去为各类需求编码各种各样的导出方法。

本篇就是分享这样一种精简的导出工具。

同步导出
```
ExcelHelper.export(String fileName, List<T> list, HttpServletResponse response);
```
fileName随便定义，list直接传入数据集即可。（数据DO类导出字段需要加@HeaderColumn注解，下述）
异步导出
```
excelHelper.exportAsync(DataFetcher<T> dataFetcher);
```
dataFetcher传入一个Lambda表达式，自定义取数查询逻辑，分页查询和数据量上限可以自行定义。

简述

Apache POI

POI提供了很多对Microsoft Office的功能，这里只涉及POI的Excel导出功能。

POI提供了三种Excel导出的API。

HSSF——Excel '97(-2007)格式的导出，即.xls，最大行数65535，列数256

XSSF—— Excel 2007 OOXML格式的导出，即.xlsx，最大行数1048576，列数16384

SXSSF——poi3.8-beta3版本加入，基于XSSF针对大数据量的导出做了优化。HSSF和XSSF会将所有Row放到内存中，不但容易导致OOM，而且频繁GC性能较低。而SXSSF提供了一种流式API，会在内存中维护一个滑动窗口，不断将数据刷到磁盘中，滑动窗口默认大小为100，内存消耗和性能都得到了提升。

本文当然使用第三种API。
反射ReflectASM

实现各种各样的数据DO的导出通用性，反射是必不可少的。

不过我们知道反射的性能开销是很大的，对于大数据量导出，如果频繁用反射获取属性值或方法调用，性能是非常低下的。

这里引入了高效的反射工具ReflectASM，通过字节码生成技术使得其性能几乎跟代码直接调用一样，原理请自行查阅。不过生成字节码MethodAccess、FeildAccess这一步是比较耗时的，这里使用了本地缓存来缓存字节码，这样字节码生成在每个导出任务中至多执行一次。
对象存储OSS

异步导出的话，需要将导出的excel存储起来，提供给用户下载。阿里云上有很方便的对象存储平台OSS。非阿里云用户可以考虑其他存储方式，原理一样。

文档：https://help.aliyun.com/docum...

控制台：https://oss.console.aliyun.co...
异步导出

异步导出的交互形式：

第一次请求异步导出接口：xx/xxxExportAsync
返回：
{
```
"success": true,
"data": {
    "token": "xxxxxxx"
},
"msg": ""
```
}

之后用拿到的token轮询请求： xxxx/getExport?token=xxxxxx
成功返回：
{
```
"success": true,
"data": {
    "status": "SUCCESS",
  "url": "http://xxxxxxxxx",

    "msg":""
 
},
"msg": ""
```
}
失败：status为FAILURE，msg为失败信息
处理中：status为PROCESSING

任务完成后，用户直接用返回的url下载excel。

工具包引入

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10-FINAL</version>
</dependency>
<dependency>
    <groupId>com.esotericsoftware.reflectasm</groupId>
    <artifactId>reflectasm</artifactId>
    <version>1.09</version>
</dependency>
<dependency>
    <groupId>com.aliyun.oss</groupId>
    <artifactId>aliyun-sdk-oss</artifactId>
    <version>2.8.3</version>
</dependency>
<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>20.0</version>
</dependency>

其他还需要spring和servlet，一般工程都有就不列了，其中还有jdk8的语法，用低版本jdk的可以自行替换掉。

实现

列头注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface HeaderColumn {

    String value() default "";

    String sortIndex() default "";

    boolean visible() default true;

    boolean sortable() default false;

    boolean editable() default false;
}

这里只用value属性就可以了，表示列名，如下

@HeaderColumn("商品名称")
private String itemTitle;

反射缓存

 * 缓存ReflectASM生成的字节码
 */
private static LoadingCache<Class<?>, MethodAccess> methodCache = CacheBuilder.newBuilder()
    .maximumSize(1000)
    .build(new CacheLoader<Class<?>, MethodAccess>() {
        @Override
        public MethodAccess load(Class<?> clazz) {
            return MethodAccess.get(clazz);
        }
    });

/**
 * 类与属性映射缓存
 */
private static LoadingCache<Class<?>, Field[]> declaredFieldsCache = CacheBuilder.newBuilder()
    .maximumSize(1000)
    .build(new CacheLoader<Class<?>, Field[]>() {
        @Override
        public Field[] load(Class<?> clazz) {
            Field[] result = clazz.getDeclaredFields();
            return result.length == 0 ? NO_FIELDS : result;
        }
    });

反射工具多与缓存结合使用，可以提升性能。

OSS接入与异步线程池

private static final String END_POINT = "http://oss-xxxx.com";
private static final String ACCESS_KEY_ID = "********";
private static final String ACCESS_KEY_SECRET = "********";
private static final String BUCKET_NAME = "********";
private static final String XLSX_SUFFIX = ".xlsx";
private static OSSClient ossClient;

private static final int DEFAULT_CORE_POOL_SIZE = 10;
private static final int DEFAULT_MAX_POOL_SIZE = 720;
private static final int DEFAULT_KEEP_ALIVE_TIME = 10;
private static final String DEFAULT_THREAD_NAME_PREFIX = "ExcelHelper-Thread-";
private static ExecutorService executor;

@PostConstruct
    void init() {
        ossClient = new OSSClient(END_POINT, ACCESS_KEY_ID, ACCESS_KEY_SECRET);
        SetBucketLifecycleRequest request = new SetBucketLifecycleRequest(BUCKET_NAME);
        // 距最后修改时间1天后过期。
        request.AddLifecycleRule(new LifecycleRule("rule0", "", LifecycleRule.RuleStatus.Enabled, 1));
        ossClient.setBucketLifecycle(request);

        executor = new ThreadPoolExecutor(DEFAULT_CORE_POOL_SIZE, DEFAULT_MAX_POOL_SIZE, DEFAULT_KEEP_ALIVE_TIME,
            TimeUnit.MINUTES, new SynchronousQueue<>(), new ThreadFactory() {

            private int counter = 0;

            @Override
            public Thread newThread(Runnable run) {
                Thread t = new Thread(run, DEFAULT_THREAD_NAME_PREFIX + counter);
                counter++;
                return t;
            }
        }, (r, e) -> {
            throw new RejectedExecutionException(
                "ExcelHelper thread pool is full, max pool size : " + DEFAULT_MAX_POOL_SIZE);
        });
    }
    
    @PreDestroy
    void destroy() {
        if (null != ossClient) {
            ossClient.shutdown();
        }
        if (null != executor) {
            executor.shutdown();
        }
    }

注意替换oss接入相关常量，导出文件不需要在oss持久存储，所以设置了1天自动删除节省空间。

创建表头

   /**
    * 获取表头各列属性描述
    *
    * @param clazz 数据类型
    * @return 表头属性描述
    * @throws ExecutionException e
    */
   private static LinkedHashMap<String, String> createHeaders(Class clazz) throws ExecutionException {
   
       LinkedHashMap<String, String> headers = new LinkedHashMap<>();
       Class<?> searchType = clazz;
       while (Object.class != searchType && searchType != null) {
           Field[] fields = declaredFieldsCache.get(searchType);
           for (Field field : fields) {
               HeaderColumn annotation = field.getAnnotation(HeaderColumn.class);
               if (annotation != null) {
                   headers.put(field.getName(), annotation.value());
               }
           }
           searchType = searchType.getSuperclass();
       }
   
       return headers;
   }

LinkedHashMap保证列头的顺序性，有些数据DO是有继承父类的，所以要加上循环输出父类注解属性。

写入表数据

       /**
        * 创建Excel
        *
        * @param list  数据列表
        * @param sheet excel中的sheet
        * @param <T>   泛型T
        * @throws ExecutionException e
        */
       private static <T> void createExcel(List<T> list, Sheet sheet) throws ExecutionException {
   
           if (list == null || list.isEmpty()) {
               return;
           }
           Class clazz = list.get(0).getClass();
   
           /* 表头 */
           LinkedHashMap<String, String> headers = createHeaders(clazz);
           Row header = sheet.createRow(0);
           Iterator<Map.Entry<String, String>> headTitle = headers.entrySet().iterator();
           for (int i = 0; headTitle.hasNext(); i++) {
               Cell cell = header.createCell(i);
               cell.setCellValue(headTitle.next().getValue());
           }
   
           MethodAccess access = methodCache.get(clazz);
   
           /* 表数据 */
           for (int i = 0; i < list.size(); i++) {
               Object obj = list.get(i);
               Row row = sheet.createRow(i + 1);
               Iterator<Map.Entry<String, String>> headTitle2 = headers.entrySet().iterator();
               for (int j = 0; headTitle2.hasNext(); j++) {
                   Cell cell = row.createCell(j);
                   String dataIndex = headTitle2.next().getKey();
                   //反射获取属性值
                   Object result;
                   try {
                       result = access.invoke(obj, createGetMethod(dataIndex));
                   } catch (Exception e) {
                       result = access.invoke(obj, createIsMethod(dataIndex));
                   }
                   if (result instanceof String) {
                       cell.setCellValue((String)result);
                   } else if (result instanceof Date) {
                       Date date = (Date)result;
                       cell.setCellValue(DEFAULT_DATE_TIME_FORMATTER.format(date.toInstant()));
                   } else if (result instanceof Integer) {
                       cell.setCellValue((Integer)result);
                   } else if (result instanceof Double) {
                       cell.setCellValue((Double)result);
                   } else if (result instanceof Boolean) {
                       cell.setCellValue((Boolean)result);
                   } else if (result instanceof Float) {
                       cell.setCellValue((Float)result);
                   } else if (result instanceof Short) {
                       cell.setCellValue((Short)result);
                   } else if (result instanceof Byte) {
                       cell.setCellValue((Byte)result);
                   } else if (result instanceof Long) {
                       cell.setCellValue((Long)result);
                   } else if (result instanceof BigDecimal) {
                       cell.setCellValue(((BigDecimal)result).doubleValue());
                   } else if (result instanceof Character) {
                       cell.setCellValue((Character)result);
                   } else {
                       cell.setCellValue(result == null ? "" : result.toString());
                   }
               }
           }
       }

这里使用了reflectASM来取属性数据，方法拼凑如下，注意布尔型属性的get方法可能is开头。

/**
 * 通过属性名称拼凑getter方法
 *
 * @param fieldName 属性名称
 * @return getter方法名
 */
private static String createGetMethod(String fieldName) {
    if (fieldName == null || fieldName.length() == 0) {
        return null;
    }
    return "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}

/**
 * 通过属性名称拼凑is方法
 *
 * @param fieldName 属性名称
 * @return getter方法名
 */
private static String createIsMethod(String fieldName) {
    if (fieldName == null || fieldName.length() == 0) {
        return null;
    }
    return "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}

同步导出

   /**
    * 同步导出excel
    *
    * @param fileName 文件名
    * @param list     数据列表
    * @param response http响应
    * @param <T>      元素类型
    * @throws Exception e
    */
   public static <T> void export(String fileName, List<T> list, HttpServletResponse response)
       throws Exception {
       Preconditions.checkNotNull(fileName);
       Preconditions.checkNotNull(list);
   
       SXSSFWorkbook wb = new SXSSFWorkbook();
       Sheet sheet = wb.createSheet();
   
       createExcel(list, sheet);
       output(fileName, wb, response);
   }
   
   /**
        * 输出excel到response
        *
        * @param fileName 文件名
        * @param wb       SXSSFWorkbook对象
        * @param response response
        */
       private static void output(String fileName, SXSSFWorkbook wb, HttpServletResponse response) throws IOException {
           OutputStream out = null;
           try {
               response.setCharacterEncoding("utf-8");
               response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
               response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
               out = response.getOutputStream();
               wb.write(out);
           } finally {
               if (out != null) {
                   out.flush();
                   out.close();
               }
               wb.dispose();
           }
       }

同步导出适合数据量小的任务，将excel直接以附件形式放到response里提供下载。

同步导出直接在controller层调用下面即可。

ExcelHelper.export(String fileName, List<T> list, HttpServletResponse response);

异步导出

       /**
        * 异步导出excel
        *
        * @param dataFetcher 数据获取接口
        * @param <T>         元素类型
        * @return 导出任务token
        */
       public <T> Map<String, String> exportAsync(DataFetcher<T> dataFetcher) {
   
           //生成任务查询token
           String token = UUID.randomUUID().toString();
   
           RiskAsyncExportDO riskAsyncExportDO = new RiskAsyncExportDO();
           riskAsyncExportDO.setGmtCreate(new Date());
           riskAsyncExportDO.setGmtModified(new Date());
           riskAsyncExportDO.setToken(token);
           riskAsyncExportDO.setStatus(PROCESSING);
           riskAsyncExportDO.setUrl("");
           riskAsyncExportDO.setMsg("");
           riskAsyncExportRepository.save(riskAsyncExportDO);
   
           //异步导出任务
           executor.execute(new ThreadPoolTask<>(token, dataFetcher));
   
           Map<String, String> result = Maps.newHashMap();
           result.put("token", token);
           return result;
       }
   
   /**
    * 异步导出线程
    *
    * @param <T> 泛型T
    */
   private class ThreadPoolTask<T> implements Runnable, Serializable {
   
       private final String token;
       private final DataFetcher<T> dataFetcher;
   
       ThreadPoolTask(String token, DataFetcher<T> dataFetcher) {
           this.token = token;
           this.dataFetcher = dataFetcher;
       }
   
       @Override
       public void run() {
           try {
               List<T> list = dataFetcher.fetchData();
   
               SXSSFWorkbook wb = new SXSSFWorkbook();
               Sheet sheet = wb.createSheet();
   
               createExcel(list, sheet);
               outputAsync(token + XLSX_SUFFIX, wb);
   
               /*oss生成含签名的资源url*/
               GeneratePresignedUrlRequest request = new GeneratePresignedUrlRequest(BUCKET_NAME, token + XLSX_SUFFIX,
                   HttpMethod.GET);
               //设置url一天过期
               request.setExpiration(Date.from(LocalDateTime.now().plusDays(1).atZone(ZoneId.systemDefault())
                   .toInstant()));
               URL signedUrl = ossClient.generatePresignedUrl(request);
   
               //更新导出任务状态
               riskAsyncExportRepository.updateBytoken(token, SUCCESS, signedUrl.toString(), "");
           } catch (Exception e) {
               //任务失败
               riskAsyncExportRepository.updateBytoken(token, FAILURE, "",
                       e.getMessage() == null ? "null" : e.getMessage());
           }
       }
   }
   
       /**
        * 上传excel到oss
        *
        * @param key oss的key
        * @param wb  SXSSFWorkbook对象
        * @throws Exception e
        */
       private static void outputAsync(String key, SXSSFWorkbook wb) throws Exception {
           try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
               wb.write(out);
               ossClient.putObject(BUCKET_NAME, key, new ByteArrayInputStream(out.toByteArray()));
           } finally {
               wb.dispose();
           }
       }
   
       /**
        * 函数式数据获取接口
        *
        * @param <T> 泛型T
        */
       @FunctionalInterface
       public interface DataFetcher<T> {
           /**
            * 数据获取方法，由业务层实现该方法
            *
            * @return 数据列表
            */
           List<T> fetchData();
       }
   
       /**
        * 获取导出任务结果
        *
        * @param token 导出任务token
        * @return 导出任务结果
        */
       public Map<String, String> getExport(String token) {
           RiskAsyncExportDO riskAsyncExportDO = riskAsyncExportRepository.findByToken(token);
           if (riskAsyncExportDO == null) {
               return null;
           }
           Map<String, String> result = Maps.newHashMap();
           result.put("status", riskAsyncExportDO.getStatus());
           result.put("url", riskAsyncExportDO.getUrl());
           result.put("msg", riskAsyncExportDO.getMsg());
           return result;
       }

代码逻辑

exportAsync会返回导出任务token，同时将任务信息插入到任务表中，并开一个线程去做查询导出。

异步线程中查询接口DataFetcher作为参数由具体业务传入执行，之后生成excel并上传到oss，返回含签名信息的url（1天有效期），完成后更新任务表的任务status和导出url。

使用说明

在具体页面controller中注入excelHelper。然后在异步导出接口中调用excelHelper.exportAsync(DataFetcher<T> dataFetcher); 该接口返回本次任务token。

DataFetcher为业务自定义数据查询接口，dk8可使用lamdba表达式，低版本重写接口方法亦可，该接口主要是业务查询逻辑，注意自行分页。

之后在一个通用controller中写一个查询导出任务结果的方法供前端轮询，该方法中调用getExport(String token)；

页面导出接口使用示例：

Map<String, String> result = excelHelper.exportAsync(() -> {
            List<AscpLogSupplierVO> list = new ArrayList<>();
            JsonResult<CaiyunIndexTableResult<AscpLogSupplierVO>> jsonResult;
            int i = 1;
            while (true) {
                logQueryVO.setPageIndex(i);
                logQueryVO.setPageSize(1000);
                jsonResult = getSupplier(logQueryVO);
                if (jsonResult.getData() != null && jsonResult.getData().getList() != null
                    && jsonResult.getData().getList().size() > 0 && list.size() < 100000) {
                    list.addAll(jsonResult.getData().getList());
                } else {
                    break;
                }
                i++;
            }
            return list;
        });

通用轮询接口示例：

@GetMapping("/getExport")
public JsonResult getExport(String token) {
    try {
        Map<String, String> map = excelHelper.getExport(token);
        return JsonResult.succ(map);
    } catch (Exception e) {
        return JsonResult.fail(e.getMessage());
    }
}

导出任务表结构：

CREATE TABLE `async_export` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `token` varchar(255) NOT NULL DEFAULT '' COMMENT 'oss导出token',
  `status` varchar(64) NOT NULL DEFAULT '' COMMENT '导出任务状态',
  `url` varchar(1024) NOT NULL DEFAULT '' COMMENT '下载链接',
  `msg` varchar(1024) NOT NULL DEFAULT '' COMMENT '失败信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='异步导出任务表';

注

DataFetcher中不要使用类似SessionUtil含有ThreadLocal属性的类，因为DataFetcher是在新线程工作，ThreadLocal属性会丢失。可以将session信息获取放到外层，传入到DataFetcher。

附完整代码

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface HeaderColumn {

    String value() default "";

    String sortIndex() default "";

    boolean visible() default true;

    boolean sortable() default false;

    boolean editable() default false;
}

@Component
public class ExcelHelper {

    private static final Field[] NO_FIELDS = {};

    private static final String SUCCESS = "SUCCESS";
    private static final String FAILURE = "FAILURE";
    private static final String PROCESSING = "PROCESSING";

    private static final String END_POINT = "http://oss-xxxx.com";
    private static final String ACCESS_KEY_ID = "********";
    private static final String ACCESS_KEY_SECRET = "********";
    private static final String BUCKET_NAME = "********";
    private static final String XLSX_SUFFIX = ".xlsx";
    private static OSSClient ossClient;

    private static final int DEFAULT_CORE_POOL_SIZE = 10;
    private static final int DEFAULT_MAX_POOL_SIZE = 720;
    private static final int DEFAULT_KEEP_ALIVE_TIME = 10;
    private static final String DEFAULT_THREAD_NAME_PREFIX = "ExcelHelper-Thread-";
    private static ExecutorService executor;

    private static final DateTimeFormatter DEFAULT_DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern(
        "yyyy-MM-dd HH:mm:ss").withLocale(Locale.CHINA).withZone(ZoneId.systemDefault());

    /**
     * 缓存ReflectASM生成的字节码
     */
    private static LoadingCache<Class<?>, MethodAccess> methodCache = CacheBuilder.newBuilder()
        .maximumSize(1000)
        .build(new CacheLoader<Class<?>, MethodAccess>() {
            @Override
            public MethodAccess load(Class<?> clazz) {
                return MethodAccess.get(clazz);
            }
        });

    /**
     * 类与属性映射缓存
     */
    private static LoadingCache<Class<?>, Field[]> declaredFieldsCache = CacheBuilder.newBuilder()
        .maximumSize(1000)
        .build(new CacheLoader<Class<?>, Field[]>() {
            @Override
            public Field[] load(Class<?> clazz) {
                Field[] result = clazz.getDeclaredFields();
                return result.length == 0 ? NO_FIELDS : result;
            }
        });

    private final RiskAsyncExportRepository riskAsyncExportRepository;

    @Autowired
    public ExcelHelper(RiskAsyncExportRepository riskAsyncExportRepository) {
        this.riskAsyncExportRepository = riskAsyncExportRepository;
    }

    @PostConstruct
    void init() {
        ossClient = new OSSClient(END_POINT, ACCESS_KEY_ID, ACCESS_KEY_SECRET);
        SetBucketLifecycleRequest request = new SetBucketLifecycleRequest(BUCKET_NAME);
        // 距最后修改时间1天后过期。
        request.AddLifecycleRule(new LifecycleRule("rule0", "", LifecycleRule.RuleStatus.Enabled, 1));
        ossClient.setBucketLifecycle(request);

        executor = new ThreadPoolExecutor(DEFAULT_CORE_POOL_SIZE, DEFAULT_MAX_POOL_SIZE, DEFAULT_KEEP_ALIVE_TIME,
            TimeUnit.MINUTES, new SynchronousQueue<>(), new ThreadFactory() {

            private int counter = 0;

            @Override
            public Thread newThread(Runnable run) {
                Thread t = new Thread(run, DEFAULT_THREAD_NAME_PREFIX + counter);
                counter++;
                return t;
            }
        }, (r, e) -> {
            throw new RejectedExecutionException(
                "ExcelHelper thread pool is full, max pool size : " + DEFAULT_MAX_POOL_SIZE);
        });
    }

    /**
     * 同步导出excel
     *
     * @param fileName 文件名
     * @param list     数据列表
     * @param response http响应
     * @param <T>      元素类型
     * @throws Exception e
     */
    public static <T> void export(String fileName, List<T> list, HttpServletResponse response)
        throws Exception {
        Preconditions.checkNotNull(fileName);
        Preconditions.checkNotNull(list);

        SXSSFWorkbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet();

        createExcel(list, sheet);
        output(fileName, wb, response);
    }

    /**
     * 异步导出excel
     *
     * @param dataFetcher 数据获取接口
     * @param <T>         元素类型
     * @return 导出任务token
     */
    public <T> Map<String, String> exportAsync(DataFetcher<T> dataFetcher) {

        //生成任务查询token
        String token = UUID.randomUUID().toString();

        RiskAsyncExportDO riskAsyncExportDO = new RiskAsyncExportDO();
        riskAsyncExportDO.setGmtCreate(new Date());
        riskAsyncExportDO.setGmtModified(new Date());
        riskAsyncExportDO.setToken(token);
        riskAsyncExportDO.setStatus(PROCESSING);
        riskAsyncExportDO.setUrl("");
        riskAsyncExportDO.setMsg("");
        riskAsyncExportRepository.save(riskAsyncExportDO);

        //异步导出任务
        executor.execute(new ThreadPoolTask<>(token, dataFetcher));

        Map<String, String> result = Maps.newHashMap();
        result.put("token", token);
        return result;
    }

    /**
     * 获取导出任务结果
     *
     * @param token 导出任务token
     * @return 导出任务结果
     */
    public Map<String, String> getExport(String token) {
        RiskAsyncExportDO riskAsyncExportDO = riskAsyncExportRepository.findByToken(token);
        if (riskAsyncExportDO == null) {
            return null;
        }
        Map<String, String> result = Maps.newHashMap();
        result.put("status", riskAsyncExportDO.getStatus());
        result.put("url", riskAsyncExportDO.getUrl());
        result.put("msg", riskAsyncExportDO.getMsg());
        return result;
    }

    /**
     * 异步导出线程
     *
     * @param <T> 泛型T
     */
    private class ThreadPoolTask<T> implements Runnable, Serializable {

        private final String token;
        private final DataFetcher<T> dataFetcher;

        ThreadPoolTask(String token, DataFetcher<T> dataFetcher) {
            this.token = token;
            this.dataFetcher = dataFetcher;
        }

        @Override
        public void run() {
            try {
                List<T> list = dataFetcher.fetchData();

                SXSSFWorkbook wb = new SXSSFWorkbook();
                Sheet sheet = wb.createSheet();

                createExcel(list, sheet);
                outputAsync(token + XLSX_SUFFIX, wb);

                /*oss生成含签名的资源url*/
                GeneratePresignedUrlRequest request = new GeneratePresignedUrlRequest(BUCKET_NAME, token + XLSX_SUFFIX,
                    HttpMethod.GET);
                //设置url一天过期
                request.setExpiration(Date.from(LocalDateTime.now().plusDays(1).atZone(ZoneId.systemDefault())
                    .toInstant()));
                URL signedUrl = ossClient.generatePresignedUrl(request);

                //更新导出任务状态
                riskAsyncExportRepository.updateBytoken(token, SUCCESS, signedUrl.toString(), "");
            } catch (Exception e) {
                //任务失败
                riskAsyncExportRepository.updateBytoken(token, FAILURE, "",
                    e.getMessage() == null ? "null" : e.getMessage());
            }
        }
    }

    /**
     * 创建Excel
     *
     * @param list  数据列表
     * @param sheet excel中的sheet
     * @param <T>   泛型T
     * @throws ExecutionException e
     */
    private static <T> void createExcel(List<T> list, Sheet sheet) throws ExecutionException {

        if (list == null || list.isEmpty()) {
            return;
        }
        Class clazz = list.get(0).getClass();

        /* 表头 */
        LinkedHashMap<String, String> headers = createHeaders(clazz);
        Row header = sheet.createRow(0);
        Iterator<Map.Entry<String, String>> headTitle = headers.entrySet().iterator();
        for (int i = 0; headTitle.hasNext(); i++) {
            Cell cell = header.createCell(i);
            cell.setCellValue(headTitle.next().getValue());
        }

        MethodAccess access = methodCache.get(clazz);

        /* 表数据 */
        for (int i = 0; i < list.size(); i++) {
            Object obj = list.get(i);
            Row row = sheet.createRow(i + 1);
            Iterator<Map.Entry<String, String>> headTitle2 = headers.entrySet().iterator();
            for (int j = 0; headTitle2.hasNext(); j++) {
                Cell cell = row.createCell(j);
                String dataIndex = headTitle2.next().getKey();
                //反射获取属性值
                Object result;
                try {
                    result = access.invoke(obj, createGetMethod(dataIndex));
                } catch (Exception e) {
                    result = access.invoke(obj, createIsMethod(dataIndex));
                }
                if (result instanceof String) {
                    cell.setCellValue((String)result);
                } else if (result instanceof Date) {
                    Date date = (Date)result;
                    cell.setCellValue(DEFAULT_DATE_TIME_FORMATTER.format(date.toInstant()));
                } else if (result instanceof Integer) {
                    cell.setCellValue((Integer)result);
                } else if (result instanceof Double) {
                    cell.setCellValue((Double)result);
                } else if (result instanceof Boolean) {
                    cell.setCellValue((Boolean)result);
                } else if (result instanceof Float) {
                    cell.setCellValue((Float)result);
                } else if (result instanceof Short) {
                    cell.setCellValue((Short)result);
                } else if (result instanceof Character) {
                    cell.setCellValue((Character)result);
                }
            }
        }
    }

    /**
     * 获取表头各列属性描述
     *
     * @param clazz 数据类型
     * @return 表头属性描述
     * @throws ExecutionException e
     */
    private static LinkedHashMap<String, String> createHeaders(Class clazz) throws ExecutionException {

        LinkedHashMap<String, String> headers = new LinkedHashMap<>();
        Class<?> searchType = clazz;
        while (Object.class != searchType && searchType != null) {
            Field[] fields = declaredFieldsCache.get(searchType);
            for (Field field : fields) {
                HeaderColumn annotation = field.getAnnotation(HeaderColumn.class);
                if (annotation != null) {
                    headers.put(field.getName(), annotation.value());
                }
            }
            searchType = searchType.getSuperclass();
        }

        return headers;
    }

    /**
     * 输出excel到response
     *
     * @param fileName 文件名
     * @param wb       SXSSFWorkbook对象
     * @param response response
     */
    private static void output(String fileName, SXSSFWorkbook wb, HttpServletResponse response) throws IOException {
        OutputStream out = null;
        try {
            response.setCharacterEncoding("utf-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            out = response.getOutputStream();
            wb.write(out);
        } finally {
            if (out != null) {
                out.flush();
                out.close();
            }
            wb.dispose();
        }
    }

    /**
     * 上传excel到oss
     *
     * @param key oss的key
     * @param wb  SXSSFWorkbook对象
     * @throws Exception e
     */
    private static void outputAsync(String key, SXSSFWorkbook wb) throws Exception {
        try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            wb.write(out);
            ossClient.putObject(BUCKET_NAME, key, new ByteArrayInputStream(out.toByteArray()));
        } finally {
            wb.dispose();
        }
    }

    /**
     * 通过属性名称拼凑getter方法
     *
     * @param fieldName 属性名称
     * @return getter方法名
     */
    private static String createGetMethod(String fieldName) {
        if (fieldName == null || fieldName.length() == 0) {
            return null;
        }
        return "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    }

    /**
     * 通过属性名称拼凑is方法
     *
     * @param fieldName 属性名称
     * @return getter方法名
     */
    private static String createIsMethod(String fieldName) {
        if (fieldName == null || fieldName.length() == 0) {
            return null;
        }
        return "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    }

    @PreDestroy
    void destroy() {
        if (null != ossClient) {
            ossClient.shutdown();
        }
        if (null != executor) {
            executor.shutdown();
        }
    }

    /**
     * 函数式数据获取接口
     *
     * @param <T> 泛型T
     */
    @FunctionalInterface
    public interface DataFetcher<T> {
        /**
         * 数据获取方法，由业务层实现该方法
         *
         * @return 数据列表
         */
        List<T> fetchData();
    }
}

导出任务表的DAO层就省略了。

个人博客：www.hellolvs.cn

基于反射、POI和OSS的异步导出工具(含同步）

前言

简述

Apache POI

反射ReflectASM

对象存储OSS

异步导出

工具包引入

实现

列头注解

反射缓存

OSS接入与异步线程池

创建表头

写入表数据

同步导出

异步导出

代码逻辑

使用说明

注

附完整代码

hellolvs

引用和评论

在Java程序中监听mysql的binlog

Bitmap 和布隆过滤器傻傻分不清？你这不应该啊

Jerry和您聊聊Chrome开发者工具

Spring 实现 3 种异步流式接口，干掉接口超时烦恼

💢线上高延迟请求排查

每一个前端，都要拥有属于自己的埋点库~

高质量代码的原则

基于反射、POI和OSS的异步导出工具(含同步）

前言

简述

Apache POI

反射ReflectASM

对象存储OSS

异步导出

工具包引入

实现

列头注解

反射缓存

OSS接入与异步线程池

创建表头

写入表数据

同步导出

异步导出

代码逻辑

使用说明

注

附完整代码

hellolvs

引用和评论

在Java程序中监听mysql的binlog

Bitmap 和 布隆过滤器傻傻分不清？你这不应该啊

Jerry和您聊聊Chrome开发者工具

Spring 实现 3 种异步流式接口，干掉接口超时烦恼

💢线上高延迟请求排查

每一个前端，都要拥有属于自己的埋点库~

高质量代码的原则

Bitmap 和布隆过滤器傻傻分不清？你这不应该啊