java解析上传的Excel文件

java解析上传的Excel文件

 public Boolean uploadModuleXls(Long id, MultipartFile multipartFile) {
        //校验文件后缀
        String extension = Objects.requireNonNull(FilenameUtils.getExtension(multipartFile.getOriginalFilename())).toLowerCase();
        if (!Objects.equals(extension, FileExtensionEnum.XLSX.getName())) {
            throw new UnprocessableException(AdvertEnum.FILE_EXTENSION_WRONG);
        }
        //解析excel数据
        List<DeviceUploadDTO> deviceUploadDTOList = new ArrayList<>();
        File file = multipartFile2File(multipartFile);
        EasyExcelFactory.read(file, DeviceUploadDTO.class, new PageReadListener<DeviceUploadDTO>(deviceUploadDTOList::addAll)).sheet().doRead();
        log.info("解析导入桩excel数据：{}", JSON.toJSONString(deviceUploadDTOList));

        //SN码转大写
        try {
            deviceUploadDTOList.forEach(deviceUploadDTO -> deviceUploadDTO.setPileSn(deviceUploadDTO.getPileSn().toUpperCase()));
        } catch (Exception e) {
            log.error("Conversion failed");
        }
        //校验数据
        checkData(deviceUploadDTOList, id);

        List<DeviceUploadCheckDetailVO> validDeviceUploadList = Lists.newArrayList();
        deviceUploadDTOList.forEach(deviceUploadDTO -> {
                validDeviceUploadList.add(DozerConvert.map(deviceUploadDTO, DeviceUploadCheckDetailVO.class));
        });
        return  savePileList(id,validDeviceUploadList);

    }

  /**
     * 校验并且保存桩数据
     *
     * @param deviceUploadDTOList 上传的桩数据
     */
    private List<DeviceUploadDTO>  checkData(List<DeviceUploadDTO> deviceUploadDTOList, Long id) {

        //校验是否上传空文件
        if (CollectionUtils.isEmpty(deviceUploadDTOList)) {
            throw new UnprocessableException(AdvertEnum.EMPTY_FILE);
        }
        //删除全部空白数据
        for (int i = deviceUploadDTOList.size() - 1; i >= 0; i--) {
            DeviceUploadDTO deviceUploadDTO = deviceUploadDTOList.get(i);
            if (StringUtils.isBlank(deviceUploadDTO.getPileName()) && StringUtils.isBlank(deviceUploadDTO.getPileSn()) && StringUtils.isBlank(deviceUploadDTO.getPilePin())) {
                deviceUploadDTOList.remove(i);
            }
        }
        //校验是否上传空数据
        if (CollectionUtils.isEmpty(deviceUploadDTOList)) {
            throw new UnprocessableException(AdvertEnum.DATA_NOT_COMPLETED);
        }

        Map<String, Integer> SNToSNCountMap = buildSNToSNCountMap(deviceUploadDTOList);
        for (int i = 0; i <= deviceUploadDTOList.size() - 1; i++) {
            DeviceUploadDTO deviceUploadDTO = deviceUploadDTOList.get(i);

            //1、必填字段校验
            if (StringUtils.isBlank(deviceUploadDTO.getPileName()) ||
                    StringUtils.isBlank(deviceUploadDTO.getPileSn()) ||
                    StringUtils.isBlank(deviceUploadDTO.getPilePin())){
                throw new UnprocessableException(AdvertEnum.Article_N_DATA_NOT_COMPLETED, new Object[]{i + 1});
            }
            //2.名称校验
            String materialName =  deviceUploadDTO.getPileName();
            String CHINESE_LETTER_DIGIT_REGEX = "^[\\u4E00-\\u9FA5A-Za-z0-9\\s_-]{1,100}$";
            boolean matches = materialName.matches(CHINESE_LETTER_DIGIT_REGEX);
            if (!matches){
                throw new UnprocessableException(AdvertEnum.Article_N_THE_DEVICE_NAME_IS_INVALID,new Object[]{i + 1});
            }

            //3、SN是否和文档本身重复
            Integer integer = SNToSNCountMap.get(deviceUploadDTO.getPileSn());
            if (integer > 1) {
                throw new UnprocessableException(AdvertEnum.SN_REPEAT_WITH_FILE, new Object[]{i + 1});
            }
  ..........
        }
        return  deviceUploadDTOList;

    }