EasyExcel

Excel导入导出的应用场景
1、数据导入:减轻录入工作量
2、数据导出:统计信息归档
3、数据传输:异构系统之间数据传输

是什么：EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。

为什么：以使用简单、节省内存著称

EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理

添加依赖

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

            <!--xls-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <!--xlsx-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
    </dependencies>

1、写操作 

1.1 创建实体类

@Data
public class DemoData {
    //设置表头名称
    @ExcelProperty("学生编号")
    private int sno;

    //设置表头名称
    @ExcelProperty("学生姓名")
    private String sname;
}

1.2 创建测试类 

    @Test
    public void writeTest(){
        // 写法1
        String fileName = "D:\\Java\\EasyExcelFile\\write.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("学生列表").doWrite(data());
    }

    //循环设置要添加的数据，最终封装到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;
    }

2、读操作

2.1 创建实体类

2.2 创建监听器

public class ExcelListener extends AnalysisEventListener<DemoData> {
    //读取每一行数据
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        System.out.println(demoData);
    }
    //数据读取完做的事
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}

2.3 测试

    @Test
    public void readTest(){
        String fileName = "D:\\Java\\EasyExcelFile\\write.xlsx";
        EasyExcel.read(fileName,DemoData.class,new ExcelListener()).sheet().doRead();
    }

监听器直接new，不能交个框架管理,即不能加@Component注解,所以不能自动注入service，也就无法调用service中的方法。因此需要使用 有参构造，手动注入service

3、读取课程分类

3.1 代码生成器生成代码

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="EduSubject对象", description="课程科目")
public class EduSubject implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "课程类别ID")
    @TableId(value = "id", type = IdType.ID_WORKER_STR)
    private String id;

    @ApiModelProperty(value = "类别名称")
    private String title;

    @ApiModelProperty(value = "父ID")
    private String parentId;

    @ApiModelProperty(value = "排序字段")
    private Integer sort;

    @ApiModelProperty(value = "创建时间")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtCreate;

    @ApiModelProperty(value = "更新时间")
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date gmtModified;
}

3.2 引入依赖

3.3 实现controller

@Api(description="课程分类管理")
@RestController
@RequestMapping("/eduservice/edu-subject")
@CrossOrigin
public class EduSubjectController {
    @Autowired
    private EduSubjectService subjectService;

    @ApiOperation(value = "课程分类管理")
    @PostMapping("addSubject")
    public R addSubject(MultipartFile file){
        subjectService.addSubject(file,subjectService);
        return R.ok();
    }
}

3.4 创建(导入表的)实体

@Data
public class ExcelSubjectData {

    @ExcelProperty(index = 0)
    private String oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;
}

3.5 实现service

public interface EduSubjectService extends IService<EduSubject> {
    void addSubject(MultipartFile file, EduSubjectService subjectService);
}

@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {

    @Override
    public void addSubject(MultipartFile file,EduSubjectService subjectService) {
        try {
            InputStream inputStream = file.getInputStream();
            EasyExcel.read(inputStream, ExcelSubjectData.class,
                    new SubjectExcelListener(subjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
            throw new GuliException(20001,"导入课程分类失败");
        }
    }
}

6、创建监听器

（1）监听器直接new，不能交个框架管理，service不能自动注入

（2）有参构造，手动注入service

监听器直接new，不能交个框架管理,即不能加@Component注解,所以不能自动注入service，也就无法调用service中的方法。因此需要使用 有参构造，手动注入service

public class SubjectExcelListener extends AnalysisEventListener<ExcelSubjectData> {

    public EduSubjectService subjectService;

    public SubjectExcelListener() {}
    //创建有参数构造，传递subjectService用于操作数据库
    public SubjectExcelListener(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }

    @Override
    public void invoke(ExcelSubjectData excelSubjectData, AnalysisContext analysisContext) {
        // 1 读取数据验空
        if (excelSubjectData == null)
            throw new GuliException(20001,"导入课程分类失败");
        // 2 判断一级分类名称是否重复
        EduSubject existOneSubject = this.existOneSubject(subjectService, excelSubjectData.getOneSubjectName());
        // 3 一级不重复插入数据库
        if (existOneSubject==null){
            existOneSubject = new EduSubject();
            existOneSubject.setTitle(excelSubjectData.getOneSubjectName());
            existOneSubject.setParentId("0");
            subjectService.save(existOneSubject);
        }
        String pid = existOneSubject.getId();
        // 4 判断二级名称是否重复
        EduSubject existTwoSubject = this.existTwoSubject(subjectService, excelSubjectData.getTwoSubjectName(), pid);
        // 5 二级不重复插入数据库
        if (existTwoSubject==null){
            existTwoSubject = new EduSubject();
            existTwoSubject.setTitle(excelSubjectData.getTwoSubjectName());
            existTwoSubject.setParentId(pid);
            subjectService.save(existTwoSubject);
        }
    }

    // 判断一级分类名称是否重复
    private EduSubject existOneSubject(EduSubjectService  subjectService,String name){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("parent_id","0");
        wrapper.eq("title",name);
        // 如果查询出的结果有多个，这时候会抛异常
        EduSubject eduSubject = subjectService.getOne(wrapper);
        // getOne方法最终得到的是 实体类对象，其结果可以通过getXXXX()方法获取对象值
        return eduSubject;
    }
    // 判断二级分类名称是否重复
    private EduSubject existTwoSubject(EduSubjectService  subjectService,String name,String pid){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("parent_id",pid);
        wrapper.eq("title",name);
        EduSubject eduSubject = subjectService.getOne(wrapper);
        return eduSubject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}