一天一个小技巧之Excel的导入导出

1.引入依赖

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

2.创建实体类

@Data
public class User{

	@ExcelProperty(value = "id" ,index = 0)
	private Long id;

	@ExcelProperty(value = "用户名" ,index = 1)
	private String username;

	@ExcelProperty(value = "密码" ,index = 2)
	private String password;
}

3.导出功能
controller文件

 @GetMapping("/exportData")
    public Result exportData(HttpServletResponse response) {
        userService.exportUserDate(response);
        return Result.ok();
    }

serviceImpl文件

   @Override
    public void exportUserDate(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("用户信息", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            List<User> users= baseMapper.selectList(null);
            //调用方法
            EasyExcel.write(response.getOutputStream(), User.class).sheet("用户信息").doWrite(users);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

4.导出功能

listener文件

public class UserListener extends AnalysisEventListener<User> {

    private UserMapper mapper;

    public UserListener(UserMapper mapper) {
        this.mapper= mapper;
    }

	/**
	*从第二行开始，逐行读取
	*/
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        User user=new User ();
        System.out.println(user);
        userMapper.insert(userMapper);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

serviceImpl

	@Override
	    public void importFile(MultipartFile file) {
	        try {
	            EasyExcel.read(file.getInputStream(), new UserListener(baseMapper)).sheet().doRead();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	    }