Excel表格和SpringBoot整合进行导入和导出操作

1. 导入相关的jar包

2. 前端发送请求代码

     <div class="im_export">
            <el-upload class="upload-demo" action="string" accept=".xlsx, .xls" :on-success="handsuccess"
              :before-upload="handbeforeupload" :http-request="httpRequest">
              <el-button size="mini" type="danger" plain>导入</el-button>
            </el-upload>
          </div>
 <el-button type="danger" size="mini" style="margin-left: 10px" plain @click="exportExcel()">导出</el-button>

      //  覆盖默认上传行为
      httpRequest(params) {
        let fd = new FormData();
        fd.append("file", params.file);
        importExcelApi(fd)
          .then(res => {
            if (res.resultCode == 0) {
              this.resultMsgVal = res.resultMsg;
              this.getDataList();
              this.$message({
                message: "上传成功",
                type: "success",
                showClose: true
              });
            } else if (res.resultCode == 500) {
              this.$message({
                message: res.resultMsg,
                type: "warning",
                showClose: true
              });
            } else if (res.resultCode == 40001) {
              this.$message({
                message: res.resultMsg,
                type: "warning",
                showClose: true
              });
            }
          })
          .catch(err => {
            this.$store.dispatch("loading/CHANGE_LOADING", false);
            this.$message({
              message: err,
              type: "warning",
              showClose: true
            });
          });
      },

  // 上传之前的钩子
      handbeforeupload(file) {
        console.log("上传之前", file);
        this.fileName = file.name;
        const isExcel =
          file.name.split(".")[1] === "xlsx" || file.name.split(".")[1] === "xls";
        const isSize = file.size / 1024 / 1024 < 10;
        if (!isExcel) {
          this.$message({
            message: "只能上传xls或xlsx文件!",
            type: "warning",
            showClose: true
          });
        }
        if (!isSize) {
          this.$message({
            message: "上传文件大小不能超过 10MB!",
            type: "warning",
            showClose: true
          });
        }
        return isExcel && isSize;
      },
  handsuccess() {
        // console.log("上传成功");
      },

// 导出函数
      // 点击导出
      exportExcel() {
        console.log(this.form.tableData);
        let params = {
          pageNum: this.page.pageIndex,
          pageSize: this.page.pageSize,
          orderList: this.orderList,
          searchConditionList: this.form.tableData
        };
        excelExportApi(params)
          .then(res => {
            // console.log(res);
            const blob = new Blob([res], {
              type: "application/vnd.ms-excel"
            }); //处理文档流
            const fileName = "疾病分组编码维护.xlsx"; //导出后的文件名
            if (
              !!window.ActiveXObject ||
              "ActiveXObject" in window ||
              window.navigator.userAgent.indexOf("Edge/") > 0
            ) {
              //判断是不是ie的浏览器
              window.navigator.msSaveOrOpenBlob(blob, fileName);
              console.log("ieeeeee");
            } else {
              const elink = document.createElement("a");
              elink.download = fileName;
              elink.style.display = "none";
              elink.href = URL.createObjectURL(blob);
              document.body.appendChild(elink);
              elink.click();
              URL.revokeObjectURL(elink.href); // 释放URL 对象
              document.body.removeChild(elink);
              console.log("noieeeee");
            }
          })
          .catch(err => {
            this.$message({
              message: err || "导出失败",
              type: "warning",
              showClose: true
            });
          });
      },

2.1 前端的请求封装的js，注意:这个里面的导入的时候的js

export function importExcelApi(data) {
  return httpRequest({
    url: constant.clmmaint + '/safeguard/importExcel',
    method: 'post',
    // 注意导出不能有这个
    // responseType: 'blob',
    data: data
  })
}
// 导出
export function excelExportApi (data) {
  return httpRequest({
    url: constant.clmmaint + '/safeguard/download',
    method: 'post',
    responseType: 'blob',
    data: data
  })
}

3. 后端的进行文件的校验，进行Excel字段非空的判断，Excel表格里面字段重复的校验。

3.1 controller层接受请求 ObjectResponse自己封装的响应请求类

    @ApiOperation("疾病分组编码维护-导入")
    @PostMapping(value = "/importExcel")
    public ObjectRestResponse importExcel(@RequestParam("file") MultipartFile file) {
        ObjectRestResponse restResponse = new ObjectRestResponse();
        try {
            restResponse = commoncodeBiz.importExcel(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return restResponse;
    }

    @ApiOperation("疾病分组编码维护-导出")
    @PostMapping(value = "/export")
    public void export(HttpServletResponse response , @RequestBody AdvancedSearchVO advancedSearchVO) {
        String parStr = "";
        try {
            if(advancedSearchVO != null &&
                    !CollectionUtils.isEmpty(advancedSearchVO.getSearchConditionList())
            ){
                parStr += advancedSearchBiz.searchParamers(advancedSearchVO.getSearchConditionList()).toString();
            }

            if(advancedSearchVO != null &&
                    !CollectionUtils.isEmpty(advancedSearchVO.getOrderList())
            ){
                parStr += sortBiz.doSort(advancedSearchVO.getOrderList());
            }
            commoncodeBiz.export(response , parStr);
        } catch (Exception e) {
            log.error("导出异常：",e);
        }
    }

3.2service层

    /**
     * 解析页面导入的文件
     *
     * @return ObjectRestResponse
     */
    public ObjectRestResponse importExcel(MultipartFile file) throws Exception {
        ObjectRestResponse objectRestResponse = new ObjectRestResponse();
        if (file == null) {
            objectRestResponse.setErrorMsg("导入的文件为空");
            throw new NullArgumentException();
        }
        String filename = file.getOriginalFilename();
        if (filename == null) {
            throw new NullArgumentException();
        }
        try {
            // 调用解析文件方法
            objectRestResponse = parseRowCell(filename, file.getInputStream());
            return objectRestResponse;
        } catch (IOException e) {
            throw new Exception(e.getMessage());
        }
    }

    /**
     * 解析文件中的数据
     *
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
     ObjectRestResponse parseRowCell(String filename, InputStream is) throws IOException {
        ObjectRestResponse restResponse = new ObjectRestResponse();
        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(is);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            }
            if (workbook == null) {
                throw new NullArgumentException();
            }
            // 解析第一个sheet
            List<ClmCommoncodeLckl> list = new ArrayList<>();

            Sheet sheet1 = workbook.getSheetAt(0);
            String result1 = parseFileForLchj(sheet1 , list);
//          校验数据是否重复
            for (int i = 0; i < list.size(); i++) {
                int i1 = excelCheckClmCommoncodeLcklData(list.get(i),i);
                if (org.apache.commons.lang.StringUtils.isBlank(list.get(i).getCommcde())) {
                    restResponse.setErrorMsg("第"+(i1+1)+"条数据内部疾病分组编码不能为空,请检查之后再保存");
                    return restResponse;
                }
                if (org.apache.commons.lang.StringUtils.isBlank(list.get(i).getCommcdenam())) {
                    restResponse.setErrorMsg("第"+(i1+1)+"条数据内部疾病分组说明不能为空,请检查之后再保存");
                    return restResponse;
                }
                if(i1 > 0){
                    restResponse.setErrorMsg("你的导入数据的第"+(i1+1)+"行疾病分组编码重复");
                    return restResponse;
                }
            }
            // 插入数据
            insertDatas(list);
            String errorMsg = "";
            if (!"导入成功".equals(result1)){
                errorMsg += result1 + ",";
            }
            if (StringUtils.isNotBlank(errorMsg)){
                restResponse.setErrorMsg(errorMsg);
                return restResponse;
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导入异常：{}",e);
            throw e;
//            restResponse.setSuucessMsg("导入异常");
//            return restResponse;
        }
        restResponse.setSuucessMsg("导入成功");
        return restResponse;
    }

//  校验从excel导入的数据
    private int excelCheckClmCommoncodeLcklData(ClmCommoncodeLckl clmZeroPayLchj,int i) {
        return checkRepeatIllnessGroup(clmZeroPayLchj.getCommcde(),i);
    }



    private int checkRepeatIllnessGroup(String commcde,int i) {

        i = i +1;
        Example example = new Example(ClmCommoncodeLckl.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("commcdetyp","10");
        criteria.andEqualTo("commcde",commcde);
        criteria.andEqualTo("status",1);
        List<ClmCommoncodeLcklDTO> clmCommoncodeLcklList = clmCommoncodeLcklMapper.selectByExample(example);
        if(clmCommoncodeLcklList.size() != 0){
            return i;
        }else {
            return  0;
        }
    }
    /**
     * 导出疾病分组编码维护表
     * @param sheet1 sheet1
     * @param list list
     */
    private void export1(Sheet sheet1,   List<ClmCommoncodeLckl> list ) {
        //根据险种代码查询险种名称
        if (!CollectionUtils.isEmpty(list)){
            int j = 0;
            for (int i = 0; i < list.size(); i++) {
                j = i + 1;
                ClmCommoncodeLckl clmZeroPayLchj = list.get(i);

                Row row = sheet1.createRow(j);

                String taskNo = clmZeroPayLchj.getTaskNo() == null ? "" : clmZeroPayLchj.getTaskNo();
                row.createCell(0).setCellValue(taskNo);

                String crtable = clmZeroPayLchj.getCommcde() == null ? "" : clmZeroPayLchj.getCommcde();
                row.createCell(1).setCellValue(crtable);

                String commcdenam = clmZeroPayLchj.getCommcdenam() == null ? "" : clmZeroPayLchj.getCommcdenam();
                row.createCell(2).setCellValue(commcdenam);
            }
        }

    }

/**
     * 疾病分组编码维护-导出
     * @param response 响应流
     * @param parStr 查询sql
     */
    public void export(HttpServletResponse response, String parStr) {
        List<ClmCommoncodeLckl> list = clmCommoncodeLcklMapper.findList(parStr);
        //创建workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        Sheet sheet1 = workbook.createSheet("疾病分组编码维护");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            // 文件名
            String fileName = UUIDUtils.generateShortUuid() + " - 疾病分组编码.xlsx";
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(fileName, "UTF-8"));

            // 创建疾病分组编码维护
            createHead(sheet1);

            // 导出疾病分组编码维护的内容
            export1(sheet1, list);

            // 写到响应流
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

3.2 Entity需要加上一个@Excel注解进行字段的校验

/**
	 * 类别编码
	 */
	@Column(name = "commcde")
	@Excel(name = "类别编码")
	private String commcde;
	/**
	 * 类别名称
	 */
	@Column(name = "commcdenam")
	@Excel(name = "类别名称")
	private String commcdenam;