主要对easyExcel做了易用性的封装,需要导出的话,就继承这个类。
@Log4j2
public class EasyExcelBaseController<T> {
/**
* 导出时候 获取某一页的数据.
*/
protected List<T> getExportPageData(Object condtion, int current ,int size){
return null;
}
/**
* 导出excel
*/
public void exportExcel(Object condtion,HttpServletResponse response,Class<T> realT, String fileName_en,String fileName_zh,List<String> excludeColumnList){
long startTime = System.currentTimeMillis();
log.info("export {} file start ",fileName_en);
ExcelWriter excelWriter = null;
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(fileName_zh, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter = EasyExcel.write(response.getOutputStream(), realT).excludeColumnFiledNames(excludeColumnList).build();
WriteSheet writeSheet = EasyExcel.writerSheet(1).build();
int current = 1;
List<T> exportDatas = null;
while (true){
//分页查询
exportDatas = getExportPageData(condtion,current,TmsSystemConfig.BATCH_EXPORT_NUM);
//根据数据导出excel
if(exportDatas.size() > 0){
excelWriter.write(exportDatas, writeSheet);
}
if( exportDatas.size() < TmsSystemConfig.BATCH_EXPORT_NUM){
break;
}
//还有数据 继续查询
current++;
}
} catch (Exception e) {
log.error("export excel {} error error list:{}",fileName_en);
log.error("export excel error error list:{}",e);
}finally {
//一定要关掉
if(excelWriter != null){
excelWriter.finish();
}
}
long endTime = System.currentTimeMillis();
log.info("export {} file end , total use time :{} ",fileName_en,endTime-startTime);
}
}
现在,我们就可以继承这个EasyExcelBaseController类,然后实现导出了,如下
public class UserExcelController extends EasyExcelBaseController<UserVO> {
private UserFeignApi userFeignApi;
/**
* 导出用户列表excel
*/
@GetMapping("/export")
@ApiOperation(value = "导出excel", notes = "导excel")
public void exportExcel(UserExportDTO dto, HttpServletResponse response) {
super.exportExcel(dto, response, UserVO.class,"userList","用户导出列表",null);
}
/**
* 查询导出的分页数据的某一页数据
*/
@Override
public List<UserVO> getExportPageData(Object condtion, int current, int size){
UserExportDTO dto = (UserExportDTO ) condtion;
List<UserVO> list = new ArrayList<>();
R<Page<UserVO>> ret = userFeignApi.selectPage(dto,current,size);
if(ret != null && ret.getData() != null){
if(ret.getData().getTotal() > TmsSystemConfig.BATCH_EXPORT_MAX_NUM){
throw new ServiceException("导出数据过多,超过最大值,请优化查询条件,缩小数据范围!");
}
list = ret.getData().getRecords();
}
return list;
}
}
这样就实现了,一个很好用的easyExcel批量分页导出的工具类。