上传Excel,并批量打标

前端

              <el-tooltip class="item" effect="dark" content="给客户批量打标" placement="top" >
              <el-button
                type="default"
                size="mini"
                icon="el-icon-s-flag"
                @click="handelMark(scope.row)"
              ></el-button>
              </el-tooltip>
<!--              给客户批量打标-->
              <el-dialog title="客户批量打标" :visible.sync="excelDialogVisible" :close-on-click-modal="false" :modal-append-to-body="false" :before-close="handleExcelDialogClose"  width="50%" center>
                <el-upload
                  class="upload-demo"
                  ref="upload"
                  :action="upLoadUrl"
                  :data="uploadData"
                  :on-preview="handlePreview1"
                  :on-remove="handleRemove1"
                  :on-success="handleUploadSuccess"
                  :file-list="excelFileList"
                  :auto-upload="false">
                  <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
                  <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload1(scope.row)">上传到服务器</el-button>
                  <button @click="downloadTemplate" >下载Excel模板</button>
                  <div slot="tip" class="el-upload__tip">只能上传jpg/png文件，且不超过500kb</div>
                </el-upload>
              </el-dialog>

    handelMark(row){
      this.uploadData.staffId = this.staffId
      this.uploadData.tagId = row.customerTagId
      this.excelDialogVisible = true
    },
    submitUpload1() {
      this.$refs.upload.submit()
    },
    handleUploadSuccess(res){
      if(res.data > 0){
        console.log("---------------客户打标数: "+res.data)
        this.$message.success("成功为"+res.data+"位客户打标")
        this.excelDialogVisible = false
        this.$refs.upload.clearFiles()
      }else{
        this.$message.error("无效打标(excel数据无效,重复打标)")
        this.excelDialogVisible = false
        this.$refs.upload.clearFiles()
      }
    },
    handleRemove1(file, fileList) {
      console.log(file, fileList);
    },
    handlePreview1(file) {
      console.log(file);
    },

    submit() {
      this.uploadFiles
        .filter(file => file.status === 'ready')
        .forEach(file => {
          this.$refs['upload-inner'].upload(file.raw);
        });
    },

    return {
      //批量打标
      excelDialogVisible: false,
      excelFileList: [],
      excelFile:{},
      uploading:false,
      upLoadUrl: window.atob(window.SITE_CONFIG.baseUrl) + '/customerTag/tagCustomersByExcel',
      uploadData: {
        staffId: "",
        tagId: ""
      },

控制器

    @PostMapping(value = {"/tagCustomersByExcel"})
    @ApiOperation(value = "批量打标")
    @ResponseBody
    public R<Integer> tagCustomersByExcel(@RequestParam("tagId") Integer tagId, @RequestParam("staffId") Integer staffId, @RequestParam("file") MultipartFile excelFile) {
        Integer succeedNum = null;
        try {
            InputStream inputStream = excelFile.getInputStream();
            succeedNum = customerTagService.tagCustomersInBatch(tagId, staffId, inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return R.success(succeedNum);
    }

服务实现类

    @Override
    public Integer tagCustomersInBatch(Integer tagId, Integer staffId, InputStream inputStream) {
        List<Map<String, String>>  sheet1 = EasyExcel.read(inputStream).sheet(0).headRowNumber(1).doReadSync();
        String tagTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
        Integer succeedNum = 0;
        for (int i = 0; i < sheet1.size(); i++) {
            Map<String, String> rowMap = sheet1.get(i);
            String name = rowMap.get(0);
            String phone = rowMap.get(1);
            Integer customerId = this.customeInfoDao.getIdByNum(phone);
            if(customerId != null ){
                //客户存在
                if(this.customeInfoDao.exisitCustTagRel(customerId, tagId) <= 0){
                    Integer inserted = this.customeInfoDao.insertTagRel(customerId, tagId);
                    succeedNum += inserted;
                }
            }else {
                //客户不存在
                CustomerAllFieldDTO customerAllFieldDTO = new CustomerAllFieldDTO();
                customerAllFieldDTO.setCustomerPhone(phone);
                customerAllFieldDTO.setCustomerName(name);
                customerAllFieldDTO.setCreateStaff(staffId);
                int added = this.customeInfoDao.addCustomerInfo(customerAllFieldDTO);
                if(added > 0) {
                    Integer newCustomerId = this.customeInfoDao.getIdByNum(phone);
                    if(this.customeInfoDao.exisitCustTagRel(customerId, tagId) <= 0){
                        Integer inserted = this.customeInfoDao.insertTagRel(customerId, tagId);
                        succeedNum += inserted;
                    }
                }
            }

        }
        return succeedNum;
    }

DAO

    Integer getIdByNum(String customerPhone);

    Integer exisitCustTagRel(@Param("customerId") Integer customerId, @Param("tagId") Integer tagId);

    Integer insertTagRel(@Param("customerId") Integer customerId, @Param("tagId") Integer tagId);

    int addCustomerInfo(CustomerAllFieldDTO customerAllFieldDTO);

SQL

    <select id="getIdByNum" resultType="java.lang.Integer">
        select CUSTOMER_ID from p_customer where CUSTOMER_PHONE = #{customerPhone} and is_del = 0
    </select>

    <select id="exisitCustTagRel" resultType="java.lang.Integer">
        SELECT count(*) FROM p_customer_tag_rel
        WHERE customer_id = #{customerId}
          AND customer_tag_id = #{tagId}
    </select>

    <insert id="insertTagRel">
        insert into p_customer_tag_rel(customer_id, customer_tag_id) values (#{customerId}, #{tagId})
    </insert>

    <insert id="addCustomerInfo">
        insert into p_customer(customer_phone, customer_name, create_staff, create_time, customer_opt_staff, customer_opt_time, is_del)
        values(#{customerPhone}, #{customerName}, #{createStaff}, now(), #{createStaff}, now(), 0)
    </insert>

VO