Excel导入

  1. 首先要先拿到Excel的File对象，然后使用ExcelUtils.formartExcelData 方法校验excel数据，最多支持26列数据。
  2. 支持数字，必填，邮箱，身份证验证，也支持自定义验证器，valid value是ex.xxx的代表使用自定义校验器。
  1. List<MonthInsideCarTemplateDTO> insideCarTemplateDTOS = ExcelUtils.formartExcelData(MonthInsideCarTemplateDTO.class,file.getInputStream(),"[" +
  2.                     "{'index':'b','valid':['required'],'field':'ownerName'}," +
  3.                     "{'index':'c','field':'ownerMobile'}," +
  4.                     "{'index':'d','valid':['required','int'],'field':'lotNum'}," +
  5.                     "{'index':'e','valid':'ex.leaseType','field':'leaseTypeId'}," +
  6.                     "{'index':'f','valid':['required','double'],'field':'balance'}," +
  7.                     "{'index':'g','valid':'ex.plateNums','field':'plateNums'}," +
  8.                     "{'index':'h','valid':'ex.rule','field':'ruleId'}," +
  9.                     "{'index':'i','field':'startDate'}," +
  10.                     "{'index':'j','field':'endDate'}," +
  11.                     "]", 0,10,validator);

一个自定义验证器例子：

  1.  private static  class ImportDataValidator implements ExcelValidor {
  3.         private Map<String,String> leaseTypeMap;
  5.         private Map<String,String> monthlyRuleMap;
  7.         private Set<String> plateNumberSet;
  9.         private ParkLeaseTypeService parkLeaseTypeService; //租户类型Service
  11.         private ParkMonthlyRuleService parkMonthlyRuleService; //月租收费规则
  13.         private PayInsidePlateBindService payInsidePlateBindService;
  15.         private String parkId;
  17.         public ImportDataValidator(ParkMonthlyRuleService monthlyRuleService, ParkLeaseTypeService parkLeaseTypeService,String parkId,PayInsidePlateBindService payInsidePlateBindService){
  18.             this.parkMonthlyRuleService = monthlyRuleService;
  19.             this.parkLeaseTypeService=parkLeaseTypeService;
  20.             this.parkId =parkId;
  21.             this.payInsidePlateBindService = payInsidePlateBindService;
  22.         }
  24.         //(验证日期是yyyy-MM-dd支持闰年的正则表达式)
  25.        // String rexp1 = "((\\d{2}(([02468][048])|([13579][26]))[\\-]((((0?[13578])|(1[02]))[\\-]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-]((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-]((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-]((((0?[13578])|(1[02]))[\\-]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-]((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-]((0?[1-9])|(1[0-9])|(2[0-8]))))))";
  26.         public boolean validParam(Object param, String valid, StringBuilder errorBuilder, char colName, int rowIndex) {
  27.             if(CheckUtils.isNullOrEmpty(param))
  28.             {
  29.                 errorBuilder.append("第" +  rowIndex + "行，第" + colName + "列为必填;");
  30.                 return false;
  31.             }
  32.             boolean validedSuccess = false;
  33.             switch (valid)
  34.             {
  35.                 case "ex.rule":
  36.                     validedSuccess  = monthlyRuleMap.containsKey(param);
  37.                     break;
  38.                 case "ex.leaseType":
  39.                     validedSuccess  = leaseTypeMap.containsKey(param);
  40.                     break;
  41.                 case "ex.plateNums":
  42.                     String regets = ",|，|\\s+";
  43.                     String[] plateNums = ConverterUtils.toString(param).split(regets);
  44.                     boolean isAllPass = true;
  45.                     for(String palteNumber :plateNums)
  46.                     {
  47.                         if(plateNumberSet.contains(palteNumber))
  48.                         {
  49.                             isAllPass = false;
  50.                             continue;
  51.                         }
  52.                         plateNumberSet.add(ConverterUtils.toString(palteNumber));
  53.                     }
  54.                     validedSuccess  = isAllPass;
  55.                     break;
  56.             }
  57.             if(!validedSuccess)
  58.             {
  59.                 errorBuilder.append("第" +  rowIndex + "行，第" + colName + "列输入有误;");
  60.                 return false;
  61.             }
  62.             return true;
  63.         }
  65.         public void init(Map<String, String> paramMap){
  66.                 List<ParkLeaseType> parkLeaseTypeList = parkLeaseTypeService.findForList(ParkLeaseType.builder().parkId(paramMap.get("parkId")).groupCode(paramMap.get("groupCode")).build());
  67.                 leaseTypeMap  = parkLeaseTypeList.stream().collect(Collectors.toMap(ParkLeaseType::getLeaseName, ParkLeaseType::getId));
  68.                 List<ParkMonthlyRule> monthlyRuleList =  parkMonthlyRuleService.findForList(ParkMonthlyRule.builder().parkId(paramMap.get("parkId")).groupCode(paramMap.get("groupCode")).build());
  69.                 monthlyRuleMap = monthlyRuleList.stream().collect(Collectors.toMap(ParkMonthlyRule::getRuleName, ParkMonthlyRule::getId));
  70.                 List<PayInsidePlateBind> insidePlateBindList = payInsidePlateBindService.findForList(PayInsidePlateBind.builder().parkId(paramMap.get("parkId")).groupCode(paramMap.get("groupCode")).build());
  71.                 plateNumberSet = insidePlateBindList.stream().map(PayInsidePlateBind::getPlateNumber).collect(Collectors.toSet());
  72.         }
  73.     }

如果校验失败，会抛出：IllegalArgumentException 获取 exception的message，即可给前台提示错误。