度过了一段忙碌的时间,新需求开发,老系统的技改,现在终于轻松了一点;话不多说,直接进入主题。
之前进行excel的导入和导出,一般使用Apache POI,写出来的代码一大堆,不仅不够美观简洁,新手还容易出错。现在推荐使用阿里的EasyExcel,方便简单,容易懂。
1.jar包依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
2.写一个通用的Excel处理工具类,便于给整个系统使用
/**
* 导出文件时为Writer生成OutputStream
* @param fileName
* @param response
* @return
* @throws Exception
*/
public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
throws Exception{
try{
fileName = URLEncoder.encode(fileName,"utf-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//此处指定了文件类型为xls,如果是xlsx的,请自行替换修改
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e){
throw new Exception("导出文件失败!");
}
}
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,String fileName,
String sheetName, BaseRowModel model) throws Exception {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLS);
Sheet sheet = new Sheet(1, 0, model.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
3.定义一个Excel模板(模板中的内容可自己定义)
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class MemberTagModel extends BaseRowModel {
@ExcelProperty(value="会员ID",index=0)
private String memberId;
@ExcelProperty(value="标签类型",index=1)
private String tagType;
@ExcelProperty(value="标签值",index=2)
private String tagValue;
}
4.controller层代码,进行模板下载
@RestController
@Slf4j
@RequestMapping("/member/XXX")
public class MemberTagController {
@RequestMapping(value="/downloadModel.html",method = RequestMethod.GET)
public void downloadModel(HttpServletResponse response) {
try {
String fileName = "XXX"; // 下载的文件名,自己定义
String sheetName = "XXX"; //sheet的名字,自己定义
ExcelUtil.writeExcel(response, null, fileName, sheetName, new MemberTagModel());
} catch(Exception e){
log.error("模板下载失败",e);
}
}
}
5.写一个通用的监听器,便于处理解析上传excel的数据
public class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
@Override
public void invoke(Object obj, AnalysisContext context) {
datas.add(obj);//数据存储到list,供批量处理,或后续自己业务逻辑处理。
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// datas.clear();//解析结束销毁不用的资源
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
6.controller层代码,文件上传之后的处理
@RestController
@Slf4j
@RequestMapping("/member/XXX")
public class MemberTagController {
@PostMapping("/uploadFile.json")
public R uploadFile(@RequestParam(value = "file", required=true) MultipartFile file){
//此处千万不要用InputStream,InputStream会导致无法解析出文件类型
BufferedInputStream buffer = null;
try{
buffer = new BufferedInputStream(file.getInputStream());
ExcelListener listener = new ExcelListener();
ExcelReader excelReader = new ExcelReader(buffer, ExcelTypeEnum.XLS,listener);
excelReader.read(new Sheet(1,0,MemberTagModel.class));
List<Object> list = listener.getDatas();
//excel中第一行为栏目,必然是存在一行的
if(CollectionUtils.isEmpty(list) || list.size() < 2){
return R.error("导入数据为空!");
}
//进行业务处理,list为excel中解析出的数据
doSomething ....
} catch(Exception e){
log.error("批量导入会员打标失败",e);
} finally {
if(buffer != null){
try{
buffer.close();
} catch (IOException e){
log.error("导入失败",e);
}
}
}
return R.ok();
}
}