在springboot中用easyexcel实现execl的导入导出功能

EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel (alibaba.com)

先看数据，由于是学习数据量很小

 

1.导入maven依赖

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

2.创建实体类

@Data是Lombook的注解

@TableName，@TableField则是mybatisplus的注解，@TableField是为了实现自动填充

@ExcelProperty 

@Data
@TableName("users")//表user是postsql的内置关键字
public class User {
    @ExcelProperty("id")//用来标记数据是属于那个列的
    private Integer id;
    @ExcelProperty("name")
    private String name;
    @ExcelProperty("age")
    private Integer age;
    @ExcelProperty("email")
    private String email;

    // 注意！这里需要标记为填充字段
    @TableField(fill = FieldFill.INSERT)
    private Date create_time;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date update_time;

}

自动填充的工具类

@Slf4j
@Component//一定不要忘记把处理器加到IOC容器里面！
public class MyMetaObjectHandler implements MetaObjectHandler {
    //插入时的填充策略
    @Override
    public void insertFill(MetaObject metaObject) {
    log.info("start insert fill.....");
    this.setFieldValByName("create_time",new Date(),metaObject);
    this.setFieldValByName("update_time",new Date(),metaObject);
    }

    //更新时的填充策略
    @Override
    public void updateFill(MetaObject metaObject) {
    log.info("start update fill.....");
    this.setFieldValByName("update_time",new Date(),metaObject);
    }
}

3.配置数据库连接

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: 123456
    driver-class-name: org.postgresql.Driver

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true

4.由于是用的mybatisplus，并且业务需求不复杂，实现dao层

@Mapper
public interface UserDao extends BaseMapper<User> {

}

5.service和serviceimpl层

public interface UserService {
    List<User> users();

    int add(User user);

}

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;

    @Override
    public List<User> users() {
        return userDao.selectList(null);
    }

    public int add(User user){
        return userDao.insert(user);
    }
}

6.controller层，导出导入都在里面

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/query")
    public List<User> query(){
        return userService.users();
    }

    @PostMapping("/add")
    public int add(User user){
        return userService.add(user);
    }


    //easyexcel的实现方式
    //导入excel
    @GetMapping("/myimport")
    public void myimport() throws FileNotFoundException
    {
//读取文件的流
        File file = new File("D:/User.xlsx");
        FileInputStream is = new FileInputStream(file);

//UserReadListener是一个监听器，代码在下边
        UserReadListener listener = new UserReadListener(userService);
        EasyExcel.read(is,
                User.class,
                listener).sheet(0)//sheet是读第几个工作表，从第0个开始
                .headRowNumber(1)//表示列头占一行
                .doRead();
    }


    //导出excel
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException{
        List<User> users = userService.users();

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");//设置response返回数据为execl格式的
        response.setCharacterEncoding("utf-8");//设置一下编码

        //设定要导出的excel的文件名字
        String fileName = URLEncoder.encode("测试数据","UTF-8").replaceAll("\\+","%20");
        response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");//告诉响应头我们传出的是一个附件，附件名称是fileName.xlsx

        EasyExcel.write(response.getOutputStream())
                .head(User.class)//设置列头
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("数据")
                .doWrite(users);
    }


 }

监听器类

public class UserReadListener implements ReadListener<User> {

//为什么要这样来拿到userService呢，因为监听器并没有给spring管理
    private UserService userService;

    public UserReadListener(UserService userService){
        this.userService = userService;
    }

    /*
    * 每读一行触发一次*/
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        userService.add(user);
        System.out.println("读取到:"+user);
    }

    /*
    * 读完了触发*/
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取完毕");
    }
}

最后给大家看一下我的目录结构

 

启动测试一下

先导入

成功导入

 

 再导出

 完成收工！