若依 el-upload 上传execl 导入后 返回错误数据excel

<el-upload
              action="#"
              :before-upload="handleBeforeUpload"
              :show-file-list="false"
              :headers="uploadData.headers"
              class="upload-file-uploader"
              :http-request="upAndDowndoad"
            >
              <el-button size="mini" type="primary">选取文件</el-button>
              <div class="el-upload__tip" slot="tip">
                请上传
                <template v-if="uploadData.fileType"> 格式为 <b style="color: #f56c6c">{{ uploadData.fileType.join("/") }}</b> </template>
                的文件
              </div>
            </el-upload>

upAndDowndoad(params){
      var that=this;
      let form = new FormData()
      form.append('file', params.file)
      importOwnerAndHouse(form).then(response => {
        const isBlob = blobValidate(response);
        if (isBlob) {
          const blob = new Blob([response])
          saveAs(blob, '业主房屋导入模板-需修改重新上传.xlsx')
        } else {
          response.text().then(data =>{
              var resp=JSON.parse(data);
              if(resp.code == 500){
                this.$modal.msgError(resp.msg);
              }else{
                that.$modal.msgSuccess(resp.msg);
              }
          }).catch(err =>{
          }) 
        }
      });
    }



// 验证是否为blob格式
export function blobValidate(data) {
  return data.type !== 'application/json'
}

//导入业主和房屋
export function importOwnerAndHouse(data) {
  return request({
    url: '/business/owner/importOwnerAndHouse',
    method: 'post',
    responseType: 'blob',
    data
  })
}

@PostMapping("/importOwnerAndHouse")
    public void importOwnerAndHouse(MultipartFile file,HttpServletResponse response) throws Exception {
        LoginUser loginUser = SecurityUtils.getLoginUser();
        String communityId = loginUser.getSysUser().getCommunityId();

        if(StringUtils.isBlank(communityId)){
            throw new ServiceException("用户未绑定小区,请重新登录重试");
        }

        ExcelReader reader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream());
        List<Sheet> sheets = reader.getSheets();
        Sheet rows = sheets.get(0);
        List<Map<String, String>> sheetData = ImportExcelUtil.getSheetData(rows);

        List<ImportOwnerAndHouseDto> list1=new ArrayList<>();
        for(Map<String, String> map:sheetData){
            ImportOwnerAndHouseDto dto=new ImportOwnerAndHouseDto();
            dto.setOwnerName(map.get("姓名"));
            dto.setOwnerIdCard(map.get("身份证号"));
            dto.setOwnerPhone(map.get("联系电话"));
            dto.setOwnerSex(map.get("性别"));

            dto.setUnitName(map.get("房屋所在单元"));
            dto.setFloor(map.get("楼层"));
            dto.setHouseNumber(map.get("门牌号"));
            dto.setIndoorArea(map.get("室内面积"));
            dto.setType(map.get("房屋类型"));
            list1.add(dto);
        }

        List<ImportOwnerAndHouseDto> failList=new ArrayList<>();

        List<ImportOwnerAndHouseDto> okList=new ArrayList<>();

        for (ImportOwnerAndHouseDto dto:list1){
            if(StringUtils.isBlank(dto.getOwnerIdCard())){
                dto.setImportFailReason("业主身份证号不能为空");
                failList.add(dto);
                continue;
            }

            if(StringUtils.isBlank(dto.getUnitName())){
                dto.setImportFailReason("房屋所在单元不能为空");
                failList.add(dto);
                continue;
            }
            if(StringUtils.isBlank(dto.getFloor())){
                dto.setImportFailReason("楼层不能为空");
                failList.add(dto);
                continue;
            }
            if(StringUtils.isBlank(dto.getHouseNumber())){
                dto.setImportFailReason("门牌号不能为空");
                failList.add(dto);
                continue;
            }
            if(StringUtils.isBlank(dto.getIndoorArea())){
                dto.setImportFailReason("室内面积不能为空");
                failList.add(dto);
                continue;
            }
            if(StringUtils.isBlank(dto.getType())){
                dto.setImportFailReason("房屋类型不能为空");
                failList.add(dto);
                continue;
            }
            if(StringUtils.isNotBlank(dto.getOwnerSex())){
                if(StringUtils.equals(dto.getOwnerSex(),"男")){
                    dto.setOwnerSex("0");
                }else if(StringUtils.equals(dto.getOwnerSex(),"女")){
                    dto.setOwnerSex("1");
                }else{
                    dto.setImportFailReason("性别只能为男女");
                    failList.add(dto);
                    continue;
                }
            }
            BBuildingUnit one = bBuildingUnitService.lambdaQuery().eq(BBuildingUnit::getName, dto.getUnitName()).one();
            if(one == null){
                dto.setImportFailReason("房屋所在单元 填写错误");
                failList.add(dto);
                continue;
            }else {
                dto.setUnitName(String.valueOf(one.getId()));
            }

            if(StringUtils.equals(dto.getType(),"住宅")){
                dto.setType("0");
            }else if(StringUtils.equals(dto.getType(),"商铺")){
                dto.setType("1");
            }else{
                dto.setImportFailReason("房屋类型只能为住宅,商铺");
                failList.add(dto);
                continue;
            }
            okList.add(dto);
        }

        for (ImportOwnerAndHouseDto dto:okList){
            BOwner one = bOwnerService.lambdaQuery().eq(BOwner::getIdCard, dto.getOwnerIdCard()).one();
            if(one == null){
                if(StringUtils.isBlank(dto.getOwnerName())
                || StringUtils.isBlank(dto.getOwnerPhone())
                        || StringUtils.isBlank(dto.getOwnerSex())
                ){
                    dto.setImportFailReason("增加业主信息时,业主姓名,手机号,性别为空,不能导入");
                    failList.add(dto);
                    continue;
                }
                BOwner owner=new BOwner();
                owner.setCommunityId(Long.valueOf(communityId));
                owner.setName(dto.getOwnerName());
                owner.setIdCard(dto.getOwnerIdCard());
                owner.setPhone(dto.getOwnerPhone());
                owner.setSex(Integer.valueOf(dto.getOwnerSex()));
                bOwnerService.save(owner);
                one = owner;


                BAppUser one1 = bAppUserService.lambdaQuery().eq(BAppUser::getUserName, one.getPhone()).one();
                if(one1 == null){
                    /**
                     * 为业主新建移动端登录用户
                     */
                    R<SysDept> byId = remoteDeptService.getById(String.valueOf(one.getCommunityId()),SecurityConstants.INNER);

                    BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
                    //初始密码为123456 +  身份证后4位
                    String encode = passwordEncoder.encode("123456" + StringUtils.substring(one.getIdCard(), one.getIdCard().length() - 4, one.getIdCard().length()));

                    BAppUser user=new BAppUser();
                    user.setPropertyCompanyId(byId.getData().getParentId());
                    user.setCommunityId(one.getCommunityId());
                    user.setUserName(one.getPhone());
                    user.setNickName(one.getName());
                    user.setSex(one.getSex());
                    user.setPhonenumber(one.getPhone());
                    user.setUserType(0);//业主
                    user.setPassword(encode);
                    user.setOwnerId(one.getId());
                    user.setAuthority("");

                    bAppUserService.save(user);
                }
            }
            //业主信息导入完成
            //导入房屋信息
            Long count = bHouseService.lambdaQuery()
                    .eq(BHouse::getUnitId, dto.getUnitName())
                    .eq(BHouse::getFloor, dto.getFloor())
                    .eq(BHouse::getHouseNumber, dto.getHouseNumber())
                    .count();
            if(count > 0){
                dto.setImportFailReason("同单位,楼层,门牌号,已经存在房屋信息");
                failList.add(dto);
                continue;
            }else{
                BHouse house=new BHouse();
                house.setCommunityId(Long.valueOf(communityId));
                house.setUnitId(Long.valueOf(dto.getUnitName()));
                house.setFloor(Integer.valueOf(dto.getFloor()));
                house.setIndoorArea(new BigDecimal(dto.getIndoorArea()));
                house.setOwnerId(one.getId());
                house.setHouseNumber(dto.getHouseNumber());
                house.setType(Integer.valueOf(dto.getType()));
                bHouseService.save(house);
            }
        }
        List<List<Object>> list2 =new ArrayList<>();
        for (ImportOwnerAndHouseDto dto:failList){
            List<Object> list3=new ArrayList<>();
            list3.add(dto.getOwnerName());
            list3.add(dto.getOwnerIdCard());
            list3.add(dto.getOwnerPhone());
            list3.add(StringUtils.equals(dto.getOwnerSex(),"0")?"男":"女");
            BBuildingUnit byId = bBuildingUnitService.getById(dto.getUnitName());
            list3.add(byId!=null?byId.getName():"");
            list3.add(dto.getFloor());
            list3.add(dto.getHouseNumber());
            list3.add(dto.getIndoorArea());
            if(StringUtils.equals(dto.getType(),"0")){
                dto.setType("住宅");
            }else if(StringUtils.equals(dto.getType(),"1")){
                dto.setType("商铺");
            }
            list3.add(dto.getType());
            list3.add(dto.getImportFailReason());
            list2.add(list3);
        }

        if (!list2.isEmpty()){
            //导出本次导入失败的数据
            List<String> allUnitName=new ArrayList<>();
            List<BBuildingUnit> list = bBuildingUnitService.lambdaQuery().eq(BBuildingUnit::getCommunityId, communityId)
                    .eq(BBuildingUnit::getPid, 0).list();
            for(BBuildingUnit unit:list){
                List<BBuildingUnit> list3 = bBuildingUnitService.lambdaQuery()
                        .eq(BBuildingUnit::getPid, unit.getId()).list();
                List<String> collect = list3.stream().map(u -> u.getName()).collect(Collectors.toList());
                allUnitName.addAll(collect);
            }

            String[] strings = allUnitName.stream().toArray(String[]::new);

            ClassPathResource resource = new ClassPathResource("excelTemplate/ownerAndHouseTemplate.xlsx");
            ExcelReader reader1 = cn.hutool.poi.excel.ExcelUtil.getReader(resource.getStream());
            Sheet rows1 = reader1.getSheets().get(0);

            //this.setDropDownBox((XSSFSheet) rows,new String[]{"参数1", "参数2", "参数3"},2,99999999,1,7);

            BigExcelWriter bigExcelWriter = new BigExcelWriter(rows1);
            bigExcelWriter.passRows(1);//跳过表头

            CellRangeAddressList addressList = new CellRangeAddressList(1, 9999, 4, 4);
            bigExcelWriter.addSelect(addressList,strings);

            // 一次性写出内容，使用默认样式，强制输出标题
            bigExcelWriter.write(list2);
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition","attachment;filename=file.xlsx");
            ServletOutputStream out=response.getOutputStream();
            // 终止后删除临时文件
            //file.deleteOnExit();
            bigExcelWriter.flush(out, true);
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        }else {
            JSONObject json=new JSONObject();
            json.put("code",200);
            json.put("msg","导入成功");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json;charset=utf-8");
            PrintWriter out= null;
            out=response.getWriter();
            out.write(json.toString());
        }
    }