sringboot+hutool 实现导出Excel以及通过Excel文件导入

如题，利用springboot跟hutool提供的Excel工具实现导出Excel以及通过Excel文件导入数据

1.引入依赖

	  <!--
          工具集合
          api文档: https://hutool.cn/
      -->
      <dependency>
          <groupId>cn.hutool</groupId>
          <artifactId>hutool-all</artifactId>
          <version>5.3.10</version>
      </dependency>

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

2.导入代码实现

controller代码

@PostMapping("import")
@ApiOperation("导入信息")
public String import(@RequestPart("file") MultipartFile file) {
    List<User> user;
    String fileName = file.getOriginalFilename();
    try {
        user= ExcelImportUtil.User(fileName, file);
    } catch (Exception e) {
        return ResultDto.commonResultByMsg("导入数据失败！");
    }
    return ResultDto.commonResult("成功导入" + user.size() + "条数据！");
}

ExcelImportUtil代码

public static List<User> importOwnShop(String fileName, MultipartFile file) throws Exception {
    List<User> list = new ArrayList<>();
    if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
        throw new Exception("上传文件格式不正确");
    }
    InputStream is = file.getInputStream();
    Workbook wb = new XSSFWorkbook(is);
    Sheet sheet = wb.getSheetAt(0);
    if (sheet == null) {
        throw new Exception("导入失败，请填写数据!)");
    }
    DecimalFormat df = new DecimalFormat("0");
    for (int r = 1; r <= Objects.requireNonNull(sheet).getLastRowNum(); r++) {
        Row row = sheet.getRow(r);
        if (row == null) {
            continue;
        }

        Cell cell1 = row.getCell(1);
        cell1.setCellType(CellType.STRING);
        String name= cell1.getStringCellValue();
        if (!CommUtil.checkNull(name)) {
            throw new Exception("导入失败(第" + (r + 1) + "行,姓名未填写!)");
        }

        Cell cell2 = row.getCell(2);
        cell2.setCellType(CellType.STRING);
        String sex = cell2.getStringCellValue();
        if (!CommUtil.checkNull(sex )) {
            throw new Exception("导入失败(第" + (r + 1) + "行,性别未填写!)");
        }

        User user = new User ();
        user.setName(name);
        user.setSex(sex);
        list.add(user);
    }
    return list;
}

3.导出代码实现

controller代码

@PostMapping("exportOperators")
@ApiOperation("导出信息")
public void exportOperators(HttpServletRequest request, HttpServletResponse response) {
    AICUser user = SSOUtil.getUser();
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.lambda().orderByDesc(User::getCreateTime);
    List<User> export = userService.list(wrapper);
    try {
        ExcelExportUtil.exportUser(export, response);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

ExcelExportUtil代码

public static void exportUser(List<User> user, HttpServletResponse response) {
   SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
   List<String> row0 = CollUtil.newArrayList("导出用户数据", "导出时间：" + df.format(new Date()));
   List<String> row1 = CollUtil.newArrayList("名称", "性别", "创建时间");
   List<List<String>> rows = CollUtil.newArrayList(row0, row1);
   user.forEach(item -> {
       List<String> row = CollUtil.newArrayList();
       row.add(item.getName());
       row.add(item.getSex());
       row.add(df.format(item.getCreateTime()));
       rows.add(row);
   });
   getExport(response, rows, "user.xlsx");
}

private static void getExport(HttpServletResponse response, List<List<String>> rows, String fileName) {
   ExcelWriter writer = ExcelUtil.getWriter();
   writer.write(rows, true);
   response.setContentType("application/vnd.ms-excel;charset=utf-8");
   response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
   try {
       ServletOutputStream out = response.getOutputStream();
       writer.flush(out, true);
       writer.close();
       IoUtil.close(out);
   } catch (Exception e) {
       e.printStackTrace();
   }
}