【springboot】【easyexcel】excel文件读取

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;}@Autowiredprivate ResultService resultService;@Overridepublic void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {excelListener.resultService.updateLevel(excelVo);}@Overridepublic 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<>();@Overridepublic void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {if (excelVo == null) {throw new RuntimeException("excel读取失败");}list.add(excelVo);}@Overridepublic 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;}@Overridepublic void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {if (excelVo == null) {throw new RuntimeException("excel读取失败");}namelistService.insertOne(excelVo,city,username);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("excel读取完成");}
}