1.前言
springboot项目中处理excel时,如将前端传的excel处理后存入数据库或将数据库数据导出成excel,可使用EasyExcel进行处理,EasyExcel使用方式详见官网地址。
2.读excel
2.1定义读监听器
监听器代码如下:
@Getter
public class UploadExcelListener<T> extends AnalysisEventListener<T> {
List<T> data = new ArrayList<>();
@Override
public void invoke(T excelData, AnalysisContext analysisContext) {
// 每处理一行数据,将数据添加到list中
data.add(excelData);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}
2.2controller
controller层代码如下:
@PostMapping("/saveByExcel")
public BaseResponse saveByExcel(@RequestPart("file") MultipartFile file) {
service.saveByExcel(file);
return new BaseResponse<>();
}
2.3service
service层代码如下:
public void saveByExcel(MultipartFile file) {
// excel 数据
List<YourEntity> list = processExcel(file);
// 根据自己的业务进行相应处理
}
private List<YourEntity> processExcel(MultipartFile file) {
UploadExcelListener<YourEntity> uploadExcelListener = null;
try {
uploadExcelListener = new UploadExcelListener<>();
EasyExcel.read(file.getInputStream(), YourEntity.class, uploadExcelListener).sheet().doRead();
} catch (IOException e) {
log.warn("文件上传失败", file.getName());
}
return uploadExcelListener.getData();
}
3.写excel
3.1controller
controller层代码如下:
@GetMapping("/exportToExcel")
public void exportToExcel(HttpServletResponse response) {
service.exportToExcel(response);
}
3.2service
service层代码如下:
public void exportToExcel(HttpServletResponse response) {
// listAll为查询数据库方法,根据自身业务需求进行实现
List<YourEntity> list = listAll();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = "data.xlsx";
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
try {
EasyExcel.write(response.getOutputStream(), YourEntity.class).sheet("sheet名").doWrite(list);
} catch (IOException e) {
log.warn("导出失败");
}
}
大功告成!