excel文件读写

excel文件内容的读写

导入依赖

		<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>

1. 设置excel表头名称

   @Data
   public class DemoData {
       //设置excel表头名称
       @ExcelProperty(value = "学生编号",index = 0)
       private Integer sno;
       @ExcelProperty(value = "学生姓名",index = 1)
       private String sname;
   }
   

2. 创建文件并写入操作

   public static void main(String[] args) {
           //实现excel写的操作
           //1 设置写入文件夹地址和excel文件名称
           String filename = "D:\\write.xlsx";
           //2 调用easyexcel里面的方法实现写操作
           //write方法两个参数：第一个参数文件路径名称，第二个参数实体类class
           EasyExcel.write(filename,DemoData.class).sheet("学生列表").doWrite(getData());
        //创建方法返回list集合
       private static List<DemoData> getData() {
           List<DemoData> list = new ArrayList<>();
           for (int i = 0; i < 10; i++) {
               DemoData data = new DemoData();
               data.setSno(i);
               data.setSname("lucy"+i);
               list.add(data);
           }
           return list;
       }
   

3. 读操作

    public static void main(String[] args) {
          //实现excel读操作
           String filename = "F:\\write.xlsx";
           EasyExcel.read(filename,DemoData.class,new ExcelListener()).sheet().doRead();
       }
   

接口式写法

1. controller

   @RestController
   @RequestMapping("/eduservice/subject")
   @CrossOrigin
   public class EduSubjectController {
       @Autowired
       private EduSubjectService subjectService;
       //添加课程分类
       //获取上传过来文件，把文件内容读取出来
       @PostMapping("addSubject")
       public R addSubject(MultipartFile file) {
           //上传过来excel文件
           subjectService.saveSubject(file,subjectService);
           return R.ok();
       }
   }
   

2. domin

   @Data
   public class SubjectData {
       @ExcelProperty(index = 0)
       private String oneSubjectName;
       @ExcelProperty(index = 1)
       private String twoSubjectName;
   }
   

3. listener

   public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
   
       //因为SubjectExcelListener不能交给spring进行管理，需要自己new，不能注入其他对象
       //不能实现数据库操作
       public EduSubjectService subjectService;
       public SubjectExcelListener() {}
       public SubjectExcelListener(EduSubjectService subjectService) {
           this.subjectService = subjectService;
       }
   
       //读取excel内容，一行一行进行读取
       @Override
       public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
           if(subjectData == null) {
               throw new GuliException(20001,"文件数据为空");
           }
   
           //一行一行读取，每次读取有两个值，第一个值一级分类，第二个值二级分类
           //判断一级分类是否重复
           EduSubject existOneSubject = this.existOneSubject(subjectService, subjectData.getOneSubjectName());
           if(existOneSubject == null) { //没有相同一级分类，进行添加
               existOneSubject = new EduSubject();
               existOneSubject.setParentId("0");
               existOneSubject.setTitle(subjectData.getOneSubjectName());//一级分类名称
               subjectService.save(existOneSubject);
           }
   
           //获取一级分类id值
           String pid = existOneSubject.getId();
   
           //添加二级分类
           //判断二级分类是否重复
           EduSubject existTwoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), pid);
           if(existTwoSubject == null) {
               existTwoSubject = new EduSubject();
               existTwoSubject.setParentId(pid);
               existTwoSubject.setTitle(subjectData.getTwoSubjectName());//二级分类名称
               subjectService.save(existTwoSubject);
           }
       }
   
       //判断一级分类不能重复添加
       private EduSubject existOneSubject(EduSubjectService subjectService,String name) {
           QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
           wrapper.eq("title",name);
           wrapper.eq("parent_id","0");
           EduSubject oneSubject = subjectService.getOne(wrapper);
           return oneSubject;
       }
   
       //判断二级分类不能重复添加
       private EduSubject existTwoSubject(EduSubjectService subjectService,String name,String pid) {
           QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
           wrapper.eq("title",name);
           wrapper.eq("parent_id",pid);
           EduSubject twoSubject = subjectService.getOne(wrapper);
           return twoSubject;
       }
   
       @Override
       public void doAfterAllAnalysed(AnalysisContext analysisContext) {
   
       }
   }
   

4. service

   public interface EduSubjectService extends IService<EduSubject> {
   
       //添加课程分类
       void saveSubject(MultipartFile file,EduSubjectService subjectService);
   }
   

5. serviceImpl

   @Service
   public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
   
       //添加课程分类
       @Override
       public void saveSubject(MultipartFile file,EduSubjectService subjectService) {
           try {
               //文件输入流
               InputStream in = file.getInputStream();
               //调用方法进行读取
               EasyExcel.read(in, SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
           }catch(Exception e){
               e.printStackTrace();
           }
       }
   }
   

6. mapper

   public interface EduSubjectMapper extends BaseMapper<EduSubject> {
   
   }
   

7. XXXMapper.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.atguigu.eduservice.mapper.EduSubjectMapper">
   
   </mapper>
   

   https://github.com/Just1ceP4rtn3r/library/commit/3716a8dd98702690dad43bd69e5a19b142f16971