导入改进第二版

忘是亡心i 2022-05-16 10:57 188阅读 0赞

# 优点：进行完整的校验，如模板是否正确，格式是否正确，都非常方便，导入是字段和列可以打乱。 #

## controller类 ##

## 要改进的两点： ##

## 增加提示列不对应时，明确哪个字段有问题 ##

## getcell是考虑是excel是常规时是数字是的情况，现在都是string类型 ##

2018.8.20 修复日期读取不正常的bug

@ApiOperation(value = "导入")
        @PostMapping(value = "/ImportItam",consumes = "multipart/*",headers = "content-type=multipart/form-data")
        public ResultVO<ExcelResultVO>supplycontractImpotIteam( @ApiParam(value = "上传的文件",required = true) MultipartFile file,@RequestParam(required = true) String supplyCategory) throws Exception {
            
          
            InputStream inputXLS = new BufferedInputStream(file.getInputStream());
    
            List<SupplyContractIteam> supplist = new ArrayList<>();
           tIteam suppnew = new Iteam();
    
            LinkedHashMap<String, String> importlistHashMap = supplyIteamUtil.getImportIteamlistHashMap();
            List<SupplyContractIteam> listByExcel;
            try{
                
    //传入流，定义的Hashmap，对象的list集合，对象，模板的标题，模板的列数
                    listByExcel = ExportLinkHash.getListByExcel(inputXLS, importlistHashMap, supplist, suppnew,"导入模板",13);
               
    
            }catch(Exception e){
                return  resultVOBuilder.failure(e.getMessage());
            }
            if (listByExcel == null || listByExcel.size() == 0) {
                return resultVOBuilder.failure("上传的数据为空");
            }
            
           
            ArrayList<ArrayList<String>> err = new ArrayList<>();
            LinkedHashMap<String,String> linkedHashMap = Maps.newLinkedHashMap();
          
            linkedHashMap.put("合同号","ContractCode");
         
    
    
            for (SupplyContractIteam supp : listByExcel) {
                ArrayList<String> errlist = new ArrayList<>();
                String replace="";
                String errstring=BeanUtil.checkBeanFieldIsNull(supp, linkedHashMap);
                if (!StringUtils.isEmpty(errstring)) {
                    replace = errstring.substring(0,errstring.length()-1);
    
                    replace+="字段必填项为空";
                }
    
                errlist.add(replace);
                err.add(errlist);
            }

### 验证空的工具类，根据传入的hashmap可以准备的判断哪个字段为空，提示语非常明确： ###

/**
    	 * <pre>
    	 * 验证bean中属性是否存在空（不支持基本数据类型）
    	 * </pre>
    	 *
    	 */
    	public static String checkBeanFieldIsNull(Object obj,
    											HashMap<String,String> linkedHashMap) throws Exception {
    			String errString="";
    		for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
    			Object property = getProperty(obj, entry.getValue());
    			if(StringUtils.isEmpty(property)){
    				errString+=entry.getKey()+"、";
    			}
    		}
    		return errString;
    	}

###  //传入流，定义的Hashmap，对象的list集合，对象，模板的标题，模板的列数 ###

public static List getListByExcel(InputStream in, LinkedHashMap<String, String> linkedHashMap, List result, Object resultVoClass,String titleName,Integer count) throws Exception {
    
            //创建Excel工作薄
            Workbook work = getWorkbook(in);
            Sheet sheet = work.getSheetAt(0);
            Row row = sheet.getRow(0);
            int number = 0;
            ArrayList<String> title = new ArrayList<>();
            try {
    
                String stringCellValue2 = row.getCell(0).getStringCellValue();
                if(!titleName.equals(stringCellValue2)){
                    throw new RuntimeException("请传入正确的模板");
                }
    
            }catch (Exception e){
                throw new RuntimeException("传入的模板不正确");
            }
            for (int k = 0; k <= 200; k++) {
                row = sheet.getRow(1);
    
                try{
    
                    row.getCell(k).setCellType(XSSFCell.CELL_TYPE_STRING);
                }catch (NullPointerException e){
                    break;
                }
    
                String stringCellValue1 = row.getCell(k).getStringCellValue();
                String replace = stringCellValue1.replace("*", "");
                title.add(replace);
                number++;
    
            }
    
            //遍历当前sheet中的所有行
            for (int j = 2; j <= sheet.getLastRowNum(); j++) {
                Class<?> aClass = resultVoClass.getClass();
                Object resultVo = aClass.newInstance();
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                int k=0;
                int size=0;
                //通过getCellValue方法获取当前行每一列中的数据
                for (int i = 0; i < number; i++) {
    
                    String titlestring = title.get(i);
                    for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
    
                        if (titlestring.equals(entry.getKey())) {
                            size++;
                            if (row.getCell(i) != null) {
                                try{
                                    String stringCellValue = getCellValue(row.getCell(i));
                                    if (!StringUtils.isEmpty(stringCellValue)) {
                                        k++;
                                        setProperty(resultVo, entry.getValue(), stringCellValue);
    
                                    }
                                }catch(Exception e){
                                   throw new RuntimeException("第"+(j-1)+"行,第"+(i+2)+"列单元格格式错误");
                                }
    
    
                            }
    
                        }
    
                    }
    
    
                }
                 if(k==0){
                    break;
                }
                if(size<count){
                    throw new RuntimeException("导入的模板不正确,字段名称为"+(count+1)+"列");
                }
                if(!StringUtils.isEmpty(resultVo)){
                    //遍历所有的列(把每一行的内容存放到对象中)
                    result.add(resultVo);
                }
            }
    
            return result;
        }
    
        //首字母转大写
        private static String toUpperCaseFirstChar(String s) {
            if (Character.isUpperCase(s.charAt(0))) {
                return s;
            } else {
                StringBuilder sb = new StringBuilder();
                return sb.append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
            }
        }
    
        public static Workbook getWorkbook(InputStream inStr) throws Exception {
            Workbook wb = null;
            wb = WorkbookFactory.create(inStr);
            return wb;
        }
    
        /*该方法用于传入某实例对象以及对象方法名，通过反射调用该对象的某个get方法*/
        public static Object getProperty(Object beanObj, String property) throws IntrospectionException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
            //此处应该判断beanObj,property不为null
            PropertyDescriptor pd = new PropertyDescriptor(property, beanObj.getClass());
            Method getMethod = pd.getReadMethod();
            if (getMethod == null) {
    
            }
            return getMethod.invoke(beanObj);
        }
    
        /*该方法用于传入某实例对象以及对象方法名、修改值，通过放射调用该对象的某个set方法设置修改值*/
        public static Object setProperty(Object beanObj, String property, Object value) throws IntrospectionException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
            //此处应该判断beanObj,property不为null
            PropertyDescriptor pd = new PropertyDescriptor(property, beanObj.getClass());
            Method setMethod = pd.getWriteMethod();
            if (setMethod == null) {
    
            }
            return setMethod.invoke(beanObj, value);
        }
    
    
           /**
         * 获取每个单元格的内容   修复日期读取不正常的bug
         */
        private static  Object getCellValue(Cell cell) {
            Object value = null;
    
            DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
    
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
    
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        value = cell.getDateCellValue();
                    }else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else {
                        value = cell.getRichStringCellValue().getString();
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
            return value;
        }
    }