SpringBoot整合Hutool实现导入导出功能

前言

像我们平时开发中，我们可能需要把我们的Excel文件导入导出进行数据的查看和数据的增加

实现

 @ApiOperation("导出")
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws Exception {
//        从数据库查询所有数据
        List<User> users = userService.list();
//        通过工具类创建writer 写出到磁盘
//        ExcelWriter writer = ExcelUtil.getWriter(filesUploadPath + "用户信息.xlsx");
//        在内存操作 ，写出到浏览器
        ExcelWriter writer = ExcelUtil.getWriter(true);
//        自定义标题名
        writer.addHeaderAlias("id", "用户id");
        writer.addHeaderAlias("username", "用户名");
        writer.addHeaderAlias("password", "密码");
        writer.addHeaderAlias("nickname", "昵称");
        writer.addHeaderAlias("avatarUrl", "头像");
        writer.addHeaderAlias("sex", "性别");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("email", "邮箱");
        writer.addHeaderAlias("phone", "电话");
        writer.addHeaderAlias("address", "地址");
        writer.addHeaderAlias("onlineStatus", "在线状态");
        writer.addHeaderAlias("status", "用户状态");
        writer.addHeaderAlias("createTime", "注册时间");
//        一次性写出users内的对象到excel，使用默认样式，强制输出标题
        writer.write(users, true);
//        设置浏览器响应格式
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        String fileName = URLEncoder.encode("用户信息", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
//        获取输出流
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        out.close();
        writer.close();
    }

    @ApiOperation("导出")
    @PostMapping("/import")
    public Result<User> imp(MultipartFile file) throws Exception {
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        // 通过 javabean的方式读取Excel内的对象，但是要求表头必须是英文，跟javabean的属性要对应起来
//        List<User> list = reader.readAll(User.class);
        List<List<Object>> list = reader.read(1);
        List<User> users = CollUtil.newArrayList();
        for (List<Object> row : list) {
            User user = new User();
            user.setUsername(row.get(1).toString());
            user.setPassword(row.get(2).toString());
            user.setNickname(row.get(3).toString());
            user.setAvatarUrl(row.get(4).toString());
            user.setSex(Integer.parseInt(row.get(5).toString()));
            user.setAge(Integer.parseInt(row.get(6).toString()));
            user.setEmail(row.get(7).toString());
            user.setPhone(row.get(8).toString());
            user.setAddress(row.get(9).toString());
            user.setOnlineStatus(Integer.parseInt(row.get(10).toString()) == 0 ? false : true);
            user.setStatus(Integer.parseInt(row.get(11).toString()));
            users.add(user);
        }
        return Result.success(userService.saveBatch(users));

    }