esayexcel快速入门

1.导入pom

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.1.0</version>
        </dependency>

2.创建esayBaen用于封装

package com.pzh.guli.service.edu.pojo.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class ExcelSubject {
    @ExcelProperty("一级分类")
    private String levelOneTitle;
    @ExcelProperty("二级分类")
    private String levelTowTile;
}

3.创建监听器用于解析数据和进行数据库的修改操作（我这里是封装二级目录比较复杂 我单独说关键点）

@Slf4j
@AllArgsConstructor
@NoArgsConstructor
public class ExcelSubjectListener extends AnalysisEventListener<ExcelSubject> {
    private SubjectMapper subjectMapper;
    @Override
    public void invoke(ExcelSubject data, AnalysisContext analysisContext) {
        log.info("解析到一条数据"+data);
        log.info("一级目录"+data.getLevelOneTitle());
        String levelOneTitle = data.getLevelOneTitle();
        String levelTowTile = data.getLevelTowTile();
        String parentId=null;
         //判断数据是否存在
        Subject byTitle = this.getByTitle(levelOneTitle);
        if (byTitle==null){
            Subject subject=new Subject();
            subject.setParentId("0");
            subject.setTitle(levelOneTitle);
            subjectMapper.insert(subject);
            Subject again = getByTitle(levelOneTitle);
            parentId=again.getId();
        }
        else {
            parentId=byTitle.getId();
        }
        //判断后面是否存在
        Subject two = this.getByTitle(levelTowTile, parentId);
        if (two==null){
            Subject subject=new Subject();
            subject.setTitle(levelTowTile);
            subject.setParentId(parentId);
            subjectMapper.insert(subject);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
       log.info("剩余解析完成");
    }
    private Subject getByTitle(String title){
        QueryWrapper<Subject> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("title",title).eq("parent_id","0");
        return subjectMapper.selectOne(queryWrapper);
    }
    private Subject getByTitle(String title,String parentId){
        QueryWrapper<Subject> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("title",title).eq("parent_id",parentId);
        return subjectMapper.selectOne(queryWrapper);
    }
}

关键点：invoke(ExcelSubject data，AnalysisContext analysisContext)

data就是监听器解析的数据 你就可以进行数据库操作,你需要知道的是这种数据操作是一行一行的解析的，对内存的节省很有作用。

4.使用EeayEecel.read读取数据（serviceImp）

  @Resource
    private SubjectMapper subjectMapper;
    @Override
    public void batchImport(InputStream inputStream) {
        EasyExcel.read(inputStream, ExcelSubject.class,new ExcelSubjectListener(subjectMapper))
                .excelType(ExcelTypeEnum.XLS)
                .sheet().doRead();
    }

 最后自行编写controller实现方法测试。