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

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>

1
2
3
4
5
6
7
8

      //  覆盖默认上传行为
      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
            });
          });
      },

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111

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
  })
}



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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);
        }
    }


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

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();
            }
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180

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

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


1
2
3
4
5
6
7
8
9
10
11
12
13
14