java使用EasyExcel

//设置表头和添加的数据字段
@Data
 public class DemoData {
    //设置表头名称
    @ExcelProperty(value = "学生编号",index = 0)//value写第一列,index读第一列
    private int sno;
    //设置表头名称
    @ExcelProperty(value = "学生姓名",index = 1)
    private String sname;
}

  public static void main(String[] args) {//写文件
      String path="D:\\excel\\write.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(path,DemoData.class).sheet("学生列表").doWrite(dataList());


Excel();//读取文件

    }
    private static List<DemoData> dataList(){
List<DemoData> list=new ArrayList<>();
        for (int i = 0; i <10 ; i++) {
            DemoData demoData =new DemoData();
            demoData.setSno(i);
            demoData.setSname("791"+i);
            list.add(demoData);
        }
        return list;
    }
    public static void Excel(){//读取文件
        String path="D:\\excel\\write.xlsx";
//需要指定用哪个class，使用监听器
        EasyExcel.read(path,DemoData.class,new ExcelListener()).sheet().doRead();
    }
}

读取文件需要监听器
public class ExcelListener extends AnalysisEventListener<DemoData> {



 //一行一行去读取excle内容
 @Override
 public void invoke(DemoData user, AnalysisContext analysisContext) {
 System.out.println("***"+user);

 }

 //读取excel表头信息
 @Override
 public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext
context) {
 System.out.println("表头信息："+headMap);
 }

 //读取完成后执行
 @Override
 public void doAfterAllAnalysed(AnalysisContext analysisContext) {
 }
 }

案例

实体类
public class subjectExcel {//属性对应excel表中的列
    @ExcelProperty(index = 0,value = "姓名")
    private String oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;
}

Controller
 //导出excel
    @GetMapping("finAll")
    public R finAll(){
        //当前用户桌面路径
        File desktopDir = FileSystemView.getFileSystemView() .getHomeDirectory();
        String path = desktopDir.getAbsolutePath()+"\\lecturer.xlsx";
        System.out.println("desktopPath = " + path);
        List<Teacher> list = teacherService.list(null);//读取数据库
        List<teacherexcel> teacherexcelList=new ArrayList<>();
        for (Teacher teacher : list) {
            teacherexcel teacherexcel=new teacherexcel();
            BeanUtils.copyProperties(teacher,teacherexcel);
            teacherexcelList.add(teacherexcel); }
        EasyExcel.write(path,teacherexcel.class).sheet("教师分类").doWrite(teacherexcelList);//写入excel
        return R.ok().data("itme",list);

    }
    =======================================================
//excel文件上传
    @PostMapping("excelupload")
    public R SubjectExcel(MultipartFile file){//spring容器不能管理监听器对象，创建有参数构造，传递subjectService用于操作数据库
        subjectService.SubjectUploadExcel(file,subjectService);
        return R.ok();
    }
service

  public void SubjectUploadExcel(MultipartFile file, SubjectService subjectService) {//spring容器不能管理监听器对象，创建有参数构造，传递subjectService用于操作数据库
        try {//监听器读取内容
            EasyExcel.read(file.getInputStream(), subjectExcel.class,new SubjectListener(subjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        } }
监听器
public class SubjectListener extends AnalysisEventListener<subjectExcel> {
    public SubjectService subjectService;
    public SubjectListener() { }
    public SubjectListener(SubjectService subjectService) {//spring容器不能管理监听器对象，创建有参数构造，传递subjectService用于操作数据库

        this.subjectService = subjectService;
    }

    @Override //一行一行去读取excle内容
    public void invoke(subjectExcel subjectExcel, AnalysisContext analysisContext) {
        if (subjectExcel==null){
            throw new zdyException(2001,"文件数据空");
        }
        Subject subject = existSubject(subjectExcel.getOneSubjectName());
        if (subject==null){ //判断一级分类重复
            subject=new Subject();
            subject.setTitle(subjectExcel.getOneSubjectName());
            subjectService.save(subject);
        }
        String id = subject.getId();
        //判断二级分类重复
        Subject subject1 = existTwoSubject(subjectExcel.getTwoSubjectName(), id);
        if (subject1==null){
            subject1=new Subject();
            subject1.setTitle(subjectExcel.getOneSubjectName());
            subject1.setParentId(id);
            subjectService.save(subject1);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) { //读取完成后执行

    }
    
    //判断一级分类重复
    public Subject existSubject(String name){ 
        QueryWrapper<Subject> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("title",name);
        queryWrapper.eq("parent_id","0");
        Subject one = subjectService.getOne(queryWrapper);

        return one;
    }
    //判断二级分类重复
    public Subject existTwoSubject(String name,String id){
        QueryWrapper<Subject> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("title",name);
        queryWrapper.eq("parent_id",id);
        Subject one = subjectService.getOne(queryWrapper);
        return one;
    }
}