17. SpringBoot+Vue实现数据的批量导入和导出

1800 字约 4 分钟读完1684 次阅读更新于 2026/5/3

全部数据批量导出

ExcelWriter writer = ExcelUtil.getWriter(true);
writer.write(list, true);

// 设置浏览器响应的格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户信息表", "UTF-8") + ".xlsx");

ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();

为什么导出数据的时候会发生这个错误呢？
image.png
https://doc.hutool.cn/pages/poi/#%E4%BB%8B%E7%BB%8D
找依赖
https://mvnrepository.com/

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

如何给导出的 excel 表头设置中文？
只需要在实体类的属性上面加一个注解 @Alias("中文")

部分数据批量导出

为什么会翻车？
因为没写查询条件
image.png

@GetMapping("/export")
public void exportData(@RequestParam(required = false) String username,
                       @RequestParam(required = false) String name,
                       HttpServletResponse response) throws IOException {
    ExcelWriter writer = ExcelUtil.getWriter(true);
    // 第一种全部导出
    List<User> list = new ArrayList<>();
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like(StrUtil.isNotBlank(username), "username", username);
    queryWrapper.like(StrUtil.isNotBlank(name), "name", name);
    list = userService.list(queryWrapper);   // 查询出当前User表的所有数据

    writer.write(list, true);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户信息表", "UTF-8") + ".xlsx");
    ServletOutputStream outputStream = response.getOutputStream();
    writer.flush(outputStream, true);
    writer.close();
    outputStream.flush();
    outputStream.close();
}

三种情况的导出完整的代码

@GetMapping("/export")
public void exportData(@RequestParam(required = false) String username,
                       @RequestParam(required = false) String name,
                       @RequestParam(required = false) String ids,  //   1,2,3,4,5
                       HttpServletResponse response) throws IOException {
    ExcelWriter writer = ExcelUtil.getWriter(true);

    List<User> list;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    if (StrUtil.isNotBlank(ids)) {     // ["1", "2", "3"]   => [1,2,3]
        List<Integer> idsArr1 = Arrays.stream(ids.split(",")).map(Integer::valueOf).collect(Collectors.toList());
        queryWrapper.in("id", idsArr1);
    } else {
        // 第一种全部导出或者条件导出
        queryWrapper.like(StrUtil.isNotBlank(username), "username", username);
        queryWrapper.like(StrUtil.isNotBlank(name), "name", name);
    }
    list = userService.list(queryWrapper);   // 查询出当前User表的所有数据
    writer.write(list, true);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户信息表", "UTF-8") + ".xlsx");
    ServletOutputStream outputStream = response.getOutputStream();
    writer.flush(outputStream, true);
    writer.close();
    outputStream.flush();
    outputStream.close();
}

批量导入

InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
List<Address> list = reader.readAll();

导入数据出错了：
image.png
出现这个错误的原因是什么？
image.png
因为 user 表导入的数据的用户名重复了

导入完整的代码

@PostMapping("/import")
public Result importData(MultipartFile file) throws IOException {
    ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
    List<User> userList = reader.readAll(User.class);
    // 写入数据到数据库
    try {
        userService.saveBatch(userList);
    } catch (Exception e) {
        e.printStackTrace();
        return Result.error("数据批量导入错误");
    }
    return Result.success();
}

前端 Vue 的导入和导出代码

handleImport(res, file, fileList) {
  if (res.code === '200') {
    this.$message.success("操作成功")
    this.load(1)
  } else {
    this.$message.error(res.msg)
  }
},
exportData() {   // 批量导出数据
  if (!this.ids.length) {   // 没有选择行的时候，全部导出  或者根据我的搜索条件导出
    window.open('http://localhost:9090/user/export?token=' + this.user.token + "&username="
        + this.username + "&name=" + this.name)
  } else {      // [1,2,3] => '1,2,3'
    let idStr = this.ids.join(',')
    window.open('http://localhost:9090/user/export?token=' + this.user.token + '&ids=' + idStr)
  }
},