SpringBoot-EasyExcel 操作-导入导出

pom

<!--        EasyExcel 操作Excel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<!--        hutool工具类-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.15</version>
</dependency>

controller:

@PostMapping({"import"})
public R importExcel(@RequestPart MultipartFile file) throws IOException {
    return this.service.importExcel(file);
}

@GetMapping({"export"})
public void exportExcel(HttpServletResponse response) throws IOException {
    this.service.exportExcel(response);
}

service

/**
 * 导入Excel*/
R importExcel(MultipartFile file) throws IOException;

/**
 *导出Excel */
void exportExcel(HttpServletResponse response) throws IOException;

serviceImpl

@Override
public R importExcel(MultipartFile file) throws IOException {
    //1.验证是否上传的有文件内容
    if(!file.isEmpty()){
        //2.解析 Excel 集合
        List<Content> list= EasyExcel.read(file.getInputStream(),Content.class,null).sheet().doReadSync();
        //3.批量新增
        if(dao.insertBatch(list)>0){
            return R.ok();
        }
    }
    return R.fail();
}

@Override
public void exportExcel(HttpServletResponse response) throws IOException {
    //1.设置为下载
    response.setHeader("Content-Disposition","attachment;filename="+System.currentTimeMillis()+".xlsx");
    //2.查询数据
    List<Content> list=dao.selectAll();
    //3.生成Excel
    EasyExcel.write(response.getOutputStream(), Content.class).sheet().doWrite(list);
}

dao

/**
 * 批量新增数据（MyBatis原生foreach方法）
 * 导入的时候
 */
int insertBatch(List<Content> entities);

mapper,插入数据时使用

<insert id="insertBatch">
    insert into t_content(id,name, title, info, cdate, result, ctime)
    values
    --                动态标签 foreach 循环
    <foreach collection="list" item="entity" separator=",">
        (#{entity.id},#{entity.name}, #{entity.title}, #{entity.info}, #{entity.cdate}, #{entity.result}, #{entity.ctime})
    </foreach>
</insert>