pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
ExcelVo
字段映射
@Data
public class ExcelVo {
@ExcelProperty(value = "序号", index = 0)
private Integer num;
@ExcelProperty(value = "工号", index = 1)
private String id;
@ExcelProperty(value = "姓名", index = 2)
private String username;
...
}
逐行读取并处理
controller：接收文件接口
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public JsonResult upload(@RequestParam("file") MultipartFile file) {
if (file.isEmpty()) {
return false;
}
try {
ExcelListener excelListener = new ExcelListener();
EasyExcel.read(file.getInputStream(), ExcelVo.class, excelListener).sheet().doRead();
return true;
}catch (Exception e){
}
}
ExcelListener ：逐行处理
@Component
public class ExcelListener extends AnalysisEventListener<ExcelVo> {
public static ExcelListener excelListener; //声明对象
@PostConstruct //初始化
public void init() {
excelListener = this;
excelListener.resultService = this.resultService;
}
@Autowired
private ResultService resultService;
@Override
public void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {
excelListener.resultService.updateLevel(excelVo);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("excel读取完成");
}
}
全部读取并处理
controller：接收文件接口
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public JsonResult upload(@RequestParam("file") MultipartFile file, @RequestParam("city") String city, @RequestParam("username") String username) {
if (file.isEmpty()) {
throw new RuntimeException("文件上传失败");
}
try {
ExcelListener excelListener = new ExcelListener();
EasyExcel.read(file.getInputStream(), ExcelVo.class, excelListener).sheet().doRead();
List<ExcelVo> list = excelListener.getList();
namelistService.insertList(list, city, username);
return JsonResult.ok(true);
} catch (Exception e) {
}
}
ExcelListener ：逐行存入list，再批量处理
@Component
@Data
public class ExcelListener extends AnalysisEventListener<ExcelVo> {
List<ExcelVo> list = new ArrayList<>();
@Override
public void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {
if (excelVo == null) {
throw new RuntimeException("excel读取失败");
}
list.add(excelVo);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("excel读取完成");
}
}
向ExcelListener 传参
controller：接收文件接口
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public JsonResult upload(@RequestParam("file") MultipartFile file, @RequestParam("city") String city, @RequestParam("username") String username) {
if (file.isEmpty()) {
throw new RuntimeException("文件上传失败");
}
try {
ExcelListener excelListener = new ExcelListener(namelistService, city, username);
EasyExcel.read(file.getInputStream(), ExcelVo.class, excelListener).sheet().doRead();
return true;
} catch (Exception e) {
}
}
ExcelListener ：通过构造函数重载，传入service层接口或其他参数
@Component
@Data
public class ExcelListener extends AnalysisEventListener<ExcelVo> {
private NamelistService namelistService;
private String city;
private String username;
public ExcelListener() {
}
public ExcelListener(NamelistService namelistService) {
this.namelistService = namelistService;
}
public ExcelListener(NamelistService namelistService, String city, String username) {
this.namelistService = namelistService;
this.city = city;
this.username = username;
}
@Override
public void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {
if (excelVo == null) {
throw new RuntimeException("excel读取失败");
}
namelistService.insertOne(excelVo,city,username);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("excel读取完成");
}
}
