课程分类存储结构.png
EasyExcel写操作.png
EasyExcel读操作.png
EasyExcel读操作分类.png
一、引入pom依赖
<dependencies> <!--阿里开源操作excel表格的工具类,引入的前提是已经引入poi因为他是对poi的封装--> <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.1</version> </dependency> </dependencies>
二、创建实体类
设置表头和添加的数据字段
package com.caiweiwei.demo.excel; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; @Data public class DemoData { //设置表头名称 @ExcelProperty(value = "学生编号",index = 0) private Integer sno; @ExcelProperty(value = "学生姓名",index = 1) private String name; }
三、实现写操作
//实现对excel写的操作 //1.设置写入文件夹的地址和文件的名称 String filename = "F:\\write.xlsx"; //调用easyExcel里面的方法实现写的操作 //write方法的两个参数:第一个参数文件路径名称,第二个参数实体类class EasyExcel.write(filename, DemoData.class).sheet("学生列表").doWrite(getData());
创建方法循环设置要添加到Excel的数据
//循环设置要添加的数据，最终封装到list集合中 private static List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setSno(i); data.setSname("张三"+i); list.add(data); } return list; }
四、实现读操作
创建对应实体类
package com.caiweiwei.demo.excel; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; @Data public class DemoData { //设置表头名称 @ExcelProperty(value = "学生编号",index = 0) private Integer sno; @ExcelProperty(value = "学生姓名",index = 1) private String name; }
创建读取操作的监听器
package com.caiweiwei.demo.excel; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import java.util.Map; public class ExcelListener extends AnalysisEventListener<DemoData> { //一行一行读取excel内容 @Override public void invoke(DemoData demoData, AnalysisContext analysisContext) { System.out.println("*****"+demoData); } //读取表头内容 public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { System.out.println("表头"+headMap); } //读取完成之后做什么事情 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } }
调用实现最终的读取
public static void main(String[] args) throws Exception { // 写法1： String fileName = "F:\\01.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, ReadData.class, new ExcelListener()).sheet().doRead(); }
五、案例代码
功能描述 表格写有一级分类和对应二级分类,读取表格内容将分类存储到数据库中,并把重复的去掉
读取表格实体类
package com.caiweiwei.eduservice.entity.excel; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; @Data public class SubjectData { @ExcelProperty(index = 0) private String oneSubjectName; @ExcelProperty(index = 1) private String twoSubjectName; }
controller层
package com.caiweiwei.eduservice.controller; import com.caiweiwei.commonutils.R; import com.caiweiwei.eduservice.service.EduSubjectService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.CrossOrigin; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; /** * <p> * 课程科目 前端控制器 * </p> * * @author testjava * @since 2020-09-15 */ @RestController @RequestMapping("/eduservice/subject") @CrossOrigin public class EduSubjectController { @Autowired private EduSubjectService eduSubjectService; @PostMapping public R saveSubject(MultipartFile file) { eduSubjectService.saveSubject(file, eduSubjectService); return R.ok(); } }
service实现类
package com.caiweiwei.eduservice.service.impl; import com.alibaba.excel.EasyExcel; import com.caiweiwei.eduservice.entity.EduSubject; import com.caiweiwei.eduservice.entity.excel.SubjectData; import com.caiweiwei.eduservice.listener.SubjectExcelListener; import com.caiweiwei.eduservice.mapper.EduSubjectMapper; import com.caiweiwei.eduservice.service.EduSubjectService; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.InputStream; /** * <p> * 课程科目 服务实现类 * </p> * * @author testjava * @since 2020-09-15 */ @Service public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService { /** * 功能描述:添加课程分类 * * @Author: 蔡威威 * @Date: 2020/9/15 21:36 */ @Override public void saveSubject(MultipartFile file,EduSubjectService eduSubjectService) { try{ //获取文件流 InputStream inputStream=file.getInputStream(); //调用方法进行读取 EasyExcel.read(inputStream, SubjectData.class,new SubjectExcelListener(eduSubjectService)).sheet().doRead(); }catch (Exception e){ e.printStackTrace(); } } }
编写的监听器
package com.caiweiwei.eduservice.listener; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.caiweiwei.eduservice.entity.EduSubject; import com.caiweiwei.eduservice.entity.excel.SubjectData; import com.caiweiwei.eduservice.service.EduSubjectService; import com.caiweiwei.servicebase.exceptionhandler.GuliException; import java.util.Map; public class SubjectExcelListener extends AnalysisEventListener<SubjectData> { //因为SubjectExcelListener不能交给spring管理,需要自己new,不能注入其他对象 //不能实现数据库操作 public EduSubjectService eduSubjectService; public SubjectExcelListener() { } public SubjectExcelListener(EduSubjectService eduSubjectService) { this.eduSubjectService = eduSubjectService; } //读取excel内容,一行一行进行读取 @Override public void invoke(SubjectData subjectData, AnalysisContext analysisContext) { if (subjectData == null) { throw new GuliException(20001, "文件数据为空"); } //一行一行读取,每次读取有两个值,第一个值为一级分类,第二个值为对应的二级分类 EduSubject eduSubject = existOneSubject(eduSubjectService, subjectData.getOneSubjectName()); //添加一级分类 if (eduSubject == null) { eduSubject = new EduSubject(); eduSubject.setParentId("0"); eduSubject.setTitle(subjectData.getOneSubjectName()); eduSubjectService.save(eduSubject); } //添加二级分类 //获取一级分类的id值 String pid = eduSubject.getId(); EduSubject existTwoSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid); if (existTwoSubject == null) { existTwoSubject = new EduSubject(); existTwoSubject.setTitle(subjectData.getTwoSubjectName()); existTwoSubject.setParentId(pid); eduSubjectService.save(existTwoSubject); } } //判断一级分类是否重复 private EduSubject existOneSubject(EduSubjectService eduSubjectService, String name) { QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("title", name); queryWrapper.eq("parent_id", "0"); return eduSubjectService.getOne(queryWrapper); } //判断二级分类是否重复 private EduSubject existTwoSubject(EduSubjectService eduSubjectService, String name, String pid) { QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("title", name); queryWrapper.eq("parent_id", pid); return eduSubjectService.getOne(queryWrapper); } @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } }
上传表格示意图
课程分类
