easyExcel 实现 导入 导出功能

easyExcel 实现 导入 导出功能

1.导包

<!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
        </dependency>

2.前端实现

<el-button
            size="small"
            style="background-color: #409eff !important; color: white"
            icon="el-icon-download"
            @click="DaocBtn"
            >导出</el-button
          >
          
<el-button
            size="small"
            style="background-color: #409eff !important; color: white"
            icon="el-icon-upload2"
            @click="DaorBtn"
            >导入</el-button
          >

 // 导出
    DaocBtn() {
      var parms = [];
      var tit = false;
      if (this.xiugai_value.length == 0) {
        parms = this.tableData;
        tit = true;
      } else if (this.xiugai_value.length > 0) {
        parms = this.xiugai_value;
      }
      this.$confirm(
        tit ? "请确认是否导出当前全部数据？" : "请确认是否导出当前所勾选数据？",
        "确认",
        {
          confirmButtonText: "确认",
          cancelButtonText: "取消",
          type: "warning",
        }
      )
        .then(() => {
          download(parms)
            .then((responseData) => {
              console.log("responseData", responseData);
              this.isExcel("xlsx", "用户信息", responseData);
              if (responseData.code == 100) {
              } else {
                this.message_tip(responseData);
              }
            })
            .catch((error) => {
              this.message_tip(error);
            });
        })
        .catch(() => {});
    }

// 导出
export const download = (search) =>
    request({
        url: '/his/sm/user/download',
        method: 'post',
        responseType: 'blob',
        data: search
    })

isExcel(type, name, data) {
      const link = document.createElement("a");
      const blob = new Blob([data], {
        type: "application/vnd.ms-excel;charset=utf-8",
      });
      link.style.display = "none";
      link.href = URL.createObjectURL(blob);
      link.setAttribute("download", `${name}.${type}`);
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    }
    
// 导入
    DaorBtn() {
      this.$confirm("导入功能仅支持导入不存在的用户，请确认是否继续", "确认", {
        confirmButtonText: "确认",
        cancelButtonText: "取消",
        type: "warning",
      })
        .then(() => {})
        .catch(() => {});
    }

3.后端实现

 /**
     * 导入用户信息
     *
     * @param file 用户信息文件
     * @return ResponseEntity 成功/失败
     */
    @PostMapping("importUserToDatabase")
    @ApiOperation(value = "导入用户信息")
    public ResponseEntity importUserToDatabase(MultipartFile file,HttpServletRequest request, HttpServletResponse response,
                                                @RequestBody HisUserVO hisUserVO) {
        return hisUserService.importUserToDatabase(file, request, response, hisUserVO);
    }

/**
     * 导入用户信息
     *
     * @param file 用户信息文件
     */
    public ResponseEntity importUserToDatabase(MultipartFile file,HttpServletRequest request,HttpServletResponse response,
                                               HisUserVO hisUserVO) {
        //创建工具类时传递class，用于后面比对表头使用
        UserListener userListener = new UserListener();

        try {
            request.setCharacterEncoding("utf-8");
            response.setCharacterEncoding("utf-8");
            EasyExcel.read(file.getInputStream(), ExportUserVO.class, userListener).sheet().doRead();
        } catch (Exception e) {
            e.printStackTrace();
            
        }

        return ResponseEntity.ok(true);
    }

/**
     * 导出用户信息
     *
     * @param response 相应
     * @param hisUserVO 用户信息
     * @return String ""
     */
    @PostMapping("download")
    @ApiOperation(value = "导出用户信息",notes = "export", produces = "application/octet-stream")
    public String download(@RequestBody List<ExportUserVO> hisUserVO, HttpServletResponse response) {
        hisUserService.export(hisUserVO, response);
        return "";
    }

 /**
     * 导出用户信息
     *
     * @param hisUserVO 用户信息
     * @param response 响应
     */
    public void export(List<ExportUserVO> hisUserVO, HttpServletResponse response) {

        if (CollectionUtils.isEmpty(hisUserVO)) {
            throw new McException("当前没有记录可以导出");
        }

        try {
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment; filename="
                    + URLEncoder.encode("用户信息", "UTF-8") + ".xls");
            List list = JSON.parseArray(JSON.toJSONString(hisUserVO));
            EasyExcel.write(response.getOutputStream(), ExportUserVO.class).sheet().doWrite(list);
        } catch (IOException e) {
            throw new McException(e.getMessage());
        }
    }