springboot集成EasyExcel

依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
</dependency>

写入本地的excel

1. 实体类

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDataDemo {
    @ExcelProperty("学生编号")  //excel表头名称
    private Integer stuNum;
    @ExcelProperty("学生名称")
    private String stuName;
}

2. Api写法

@SpringBootTest
public class EasyExcelTest {
    @Test
    public void test(){
        //实现写操作
        //1. 设置要写入的文件路径
        String filename = "E:\\excel\\write.xlsx";
        //2. 准备数据
        List<ExcelDataDemo> data = new ArrayList<>();
        data.add(new ExcelDataDemo(201430317,"诺亚"));
        data.add(new ExcelDataDemo(201430329,"雷杰多"));
        data.add(new ExcelDataDemo(201430365,"撒加"));
        //3. 调用easyexcel的方法实现写操作。**写完后，文件流会自动关闭**
        //   参数1：文件名称  参数2：实体类
        //   sheet：excel文件底部栏的sheet
        EasyExcel.write(filename, ExcelDataDemo.class).sheet("学生列表").doWrite(data);
    }
}

生成write.xlsx文件：

内容：

读取本地的excel

1. 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ReadExcelDemo {
    @ExcelProperty(value = "学生编号",index = 0)  //index表示excel第几列
    private Integer stuNum;
    @ExcelProperty(value = "学生名称",index = 1)
    private String stuName;
}

2. 创建监听

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jarvis.eduservice.entity.ReadExcelDemo;

import java.util.Map;

public class ExcelListener extends AnalysisEventListener<ReadExcelDemo> {

    //一行一行读取excel中的内容，跳过表头直接读取第二行内容
    //第一个参数data代表excel中的一行数据
    @Override
    public void invoke(ReadExcelDemo data, AnalysisContext analysisContext) {
        System.out.println("****"+data);
        //业务逻辑
        doSomething(data);//根据自己业务做处理
    }
    //读取表头
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头："+headMap);
    }
    //读取后的操作
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3. Api写法

@Test
public void testRead(){
    //实现读操作
    //1. 设置要读取的文件路径
    String filename = "E:\\excel\\write.xlsx";
    //2. 读取  会先读表头，然后读数据
    EasyExcel.read(filename, ReadExcelDemo.class,new ExcelListener()).sheet().doRead();
}

=====output:
表头：{0=学生编号, 1=学生名称}
****ReadExcelDemo(stuNum=201430317, stuName=诺亚)
****ReadExcelDemo(stuNum=201430329, stuName=雷杰多)
****ReadExcelDemo(stuNum=201430365, stuName=撒加)

后台接收文件存入数据库

1. Controller层

@RestController
@RequestMapping("/eduservice/eduSubject")
@CrossOrigin
public class EduSubjectController {
    @Autowired
    private EduSubjectService eduSubjectService;

    /**
     * 接收上传的文件，读取内容，添加到数据库
     * @param file  MultipartFile类型一般用于接受前台传过来的文件
     * @return
     */
    @PostMapping("/addSubject")
    public CommonResponseVo addSubject(MultipartFile file) throws IOException {
        eduSubjectService.saveSubjectFromExcel(file);
        return CommonResponseVo.onSuccess("添加成功！");
    }
}

2. Service层

public interface EduSubjectService extends IService<EduSubject> {
    void saveSubjectFromExcel(MultipartFile file) throws IOException;
}

@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
    @Override
    public void saveSubjectFromExcel(MultipartFile file) throws IOException {
        //将this传入ExcelListener构造器中，
        //使得监听器可以通过该service操作数据库进行写入
        //ExcelSubjectData是excel表格对应的实体类
        EasyExcel.read(file.getInputStream(), ExcelSubjectData.class,new ExcelListener(this))
            .sheet().doRead();
    }
}

3. excel实体类

@Data
public class ExcelSubjectData {
    @ExcelProperty(value = "一级分类",index = 0)
    private String oneSubjectName;
    @ExcelProperty(value = "二级分类",index = 1)
    private String twoSubjectName;
}

对应excel的数据：

4. 数据库实体类

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

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "课程类别ID")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    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 LocalDateTime gmtCreate;

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

5. 监听器

public class ExcelListener extends AnalysisEventListener<ExcelSubjectData> {

    public EduSubjectService eduSubjectService;

    public ExcelListener(){}
    /**
     * 这个监听器无法交给spring管理，需要每次new出来，
     * 如果在监听器中要使用spring中的bean(如要操作数据库)，则可以通过有参构造传入
     * @param eduSubjectService
     */
    public ExcelListener(EduSubjectService eduSubjectService){
        this.eduSubjectService = eduSubjectService;
    }

    @Override
    public void invoke(ExcelSubjectData data, AnalysisContext analysisContext) {
        if(data == null) {
            return;
        }

        //一级分类的课程
        EduSubject oneSubject = existSubject(data.getOneSubjectName(), "0");
        if(oneSubject == null){
            oneSubject = new EduSubject();
            oneSubject.setTitle(data.getOneSubjectName()).setParentId("0");
            eduSubjectService.save(oneSubject);
        }
        //二级分类的课程
        EduSubject twoSubject = existSubject(data.getTwoSubjectName(), oneSubject.getId());
        if(twoSubject == null){
            twoSubject= new EduSubject();
            twoSubject.setTitle(data.getTwoSubjectName()).setParentId(oneSubject.getId());
            eduSubjectService.save(twoSubject);
        }
    }

    //是否可添加课程
    private EduSubject existSubject(String subjectName, String parentId){
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",subjectName).eq("parent_id",parentId);
        EduSubject subject = eduSubjectService.getOne(wrapper);
        return subject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

swagger-ui界面测试后，数据库中有了数据：