Excel文件的下载和导入数据 by hutool

下载

  @GetMapping("/download")
  @ApiOperation("下载费用详情导入模板")
  public void downloadTemplate(HttpServletResponse response) {
    try {
      ClassPathResource classPathResource = new ClassPathResource("xlsTemplate/import_the_cost_template.xls");
      InputStream in = classPathResource.getInputStream();
      response.setContentType("application/blob");
      response.setHeader("Content-Disposition", "attachment; filename=import_the_cost_template.xls");
      IoUtil.copy(in, response.getOutputStream());
      in.close();
    } catch (IOException e) {
      log.error("导入费用详情模板下载失败", e);
      ExceptionTools.error("导入费用详情模板下载失败");
    }
  }

导入

  @PostMapping("/import")
  @ApiOperation("导入费用详情")
  public AjaxResult importOrderDetail(@RequestParam("file") MultipartFile file) {
    List<ExpenseOrderDetailEntity> orderDetailList = ListUtil.list(false);
    try {
      //读sheet
      ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
      List<List<Object>> read = reader.read(1);
      for (int i = 0; i < read.size(); i++) {//行数据
        try {
          List<Object> rowList = read.get(i);
          String timeStr = rowList.get(1).toString();
          String nameStr = rowList.get(2).toString();
          String desStr = rowList.get(3).toString();
          String moneyStr = rowList.get(4).toString();
          String spenderNameStr = rowList.get(5).toString();
          if (StrUtil.hasBlank(timeStr, nameStr, moneyStr)) {
            continue;
          }
          //ExpenseOrderDetailEntity
          ExpenseOrderDetailEntity orderDetail = new ExpenseOrderDetailEntity();
          orderDetail.setTime(Timestamp.valueOf(timeStr));
          orderDetail.setName(nameStr);
          orderDetail.setDes(desStr);
          orderDetail.setMoney(Long.valueOf(moneyStr));
          orderDetail.setSpenderName(spenderNameStr);
          orderDetailList.add(orderDetail);
        } catch (Exception e) {
          log.error("导入费用详情失败", e);
          return AjaxResult.error(StrUtil.format("{}行数据错误无法解析", i));
        }
      }
      return AjaxResult.success(orderDetailList);
    } catch (IOException e) {
      log.error("文件上传失败", e);
      return AjaxResult.error("文件上传失败");
    }
  }