一行代码搞定各种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。非阿里云用户可以考虑其他存储方式,原理一样。
-
异步导出
异步导出的交互形式:
第一次请求异步导出接口: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
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。