一、EasyExcel 简介
1、Excel导入导出的应用场景
- 数据导入：减轻录入工作量
- 数据导出：统计信息归档
- 数据传输：异构系统之间数据传输
2、EasyExcel特点
Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
3、案例1:EasyExcel进行Excel写操作
1、pom中引入xml相关依赖
<dependencies> <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.1</version> </dependency> <!-- 因为EasyExcel底层是Poi所以需要引入poi的依赖 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> </dependencies>
2、创建实体类
@Data public class WriteData { //设置excel表头名称 @ExcelProperty("学生编号") private Integer sno; @ExcelProperty("学生姓名") private String sname; }
3、实现写操作
public static void testWrite(){ //实现excel的写操作 //1.设置写入文件夹地址和excel名称 String filename = "F:\\write.xlsx"; //2.调用easyexcel里面的方法实现写操作 EasyExcel.write(filename, WriteData.class).sheet("学生列表").doWrite(getData()); } public static List <WriteData> getData(){ List <WriteData> list = new ArrayList <>(); for(int i = 0;i < 10;i++){ String sname = "lucy"+i; list.add(new WriteData(i, sname)); } return list; }
4、案例2:EasyExcel进行Excel读操作
1、创建实体类并标记对应列关系
@Data public class ReadData { //设置excel表头名称 @ExcelProperty(index = 0) private Integer sno; @ExcelProperty(index = 1) private String sname; }
2、创建监听器进行excel文件读取
public class ExcelListner extends AnalysisEventListener<ReadData> { List<ReadData> list = new ArrayList<ReadData>(); //一行一行的去读取 @Override public void invoke(ReadData readData, AnalysisContext analysisContext) { System.out.println("****"+readData); list.add(readData); } //读取Excel表头信息 @Override public void invokeHeadMap(Map <Integer, String> headMap, AnalysisContext context) { System.out.println("表头信息:"+headMap); } //读取完成之后执行 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } }
3、进行读操作
public static void testRead(){ String filename = "F:\\write.xlsx"; EasyExcel.read(filename,ReadData.class,new ExcelListner()).sheet().doRead(); }
二、课程分类添加功能(后端)
核心:EasyExcel读取excel内容实现
1、引入easyexcel依赖
2、使用代码生成器把课程分类代码生成
3、创建实体类和excel对应关系
@Data public class SubjectData { @ExcelProperty(index = 0) private String oneSujectName; @ExcelProperty(index = 1) private String twoSujectName; }
4、编写 EduSubjectController 类
@Api(description = "课程分类") @RestController @RequestMapping("/eduservice/subject") @CrossOrigin public class EduSubjectController { @Autowired private EduSubjectService eduSubjectService; //添加课程分类 @ApiOperation(value = "添加课程分类") @PostMapping("addSubjects") public R addSubjects(MultipartFile file){ boolean flag = eduSubjectService.saveSubjects(file); if(flag){ return R.ok().message("文件导入成功!"); }else{ return R.error().message("文件导入失败!"); } } }
5、编写EduSubjectServiceImpl 类
@Service public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService { @Autowired private EduSubjectService eduSubjectService; //添加课程分类 @Override public boolean saveSubjects(MultipartFile file) { try { InputStream inputStream = file.getInputStream(); //进行excel文件读取 EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListner(eduSubjectService)).sheet().doRead(); return true; }catch (Exception e){ e.printStackTrace(); return false; } } }
6、创建读取Excel监听器
public class SubjectExcelListner extends AnalysisEventListener<SubjectData> { //因为SubjectExcelListner在EduSubjectServiceImpl每次会被new的形式使用,所以SubjectExcelListner不能交给 //Spring进行管理,也就不能使用@Autowird或@Resource注解注入对象. //但是该类需要调用service中的方法进行数据库操作,该如何使用呢? //可以通过构造方法传递参数的形式,使用service对象. private EduSubjectService eduSubjectService; public SubjectExcelListner(EduSubjectService eduSubjectService) { this.eduSubjectService = eduSubjectService; } public SubjectExcelListner() {} //一行一行的去读取 @Override public void invoke(SubjectData subjectData, AnalysisContext analysisContext) { if(subjectData==null){ throw new GuLiException(20001,"数据为空!"); } //添加一级分类 EduSubject oneSubject = this.existOneSubject(subjectData.getOneSubjectName()); if(oneSubject==null){//没有相同的一级分类 oneSubject = new EduSubject(); oneSubject.setTitle(subjectData.getOneSubjectName()); oneSubject.setParentId("0"); eduSubjectService.save(oneSubject); } //获取一级分类id String pid = oneSubject.getId(); //添加二级分类 EduSubject twoSubject = this.existTwoSubject(subjectData.getTwoSubjectName(), pid); if(twoSubject==null){ twoSubject = new EduSubject(); twoSubject.setTitle(subjectData.getTwoSubjectName()); twoSubject.setParentId(pid); eduSubjectService.save(twoSubject); } } //读取Excel表头 @Override public void invokeHeadMap(Map <Integer, String> headMap, AnalysisContext context) { System.out.println("表头信息:"+headMap); } //读取完成后的操作 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } //判断一级分类是否重复 private EduSubject existOneSubject(String name){ QueryWrapper <EduSubject> wrapper = new QueryWrapper <>(); wrapper.eq("title", name).eq("parent_id",0); EduSubject subject = eduSubjectService.getOne(wrapper); return subject; } //判断二级分类是否重复 private EduSubject existTwoSubject(String name,String pid){ QueryWrapper <EduSubject> wrapper = new QueryWrapper <>(); wrapper.eq("title", name).eq("parent_id", pid); EduSubject subject = eduSubjectService.getOne(wrapper); return subject; } }
