poi excel 校验(超多校验条件)

poi excel 校验文件名称是不是超过100字符，在校验类型符不符合xls,xlsx格式，在校验模板是不是我们提供的，符合在校验写的内容是不是超了10000行（不包括表头） 在校验是不是满足要么第一列全有，要么第二列全有，要么前俩列都有，不允许交叉出现。其他列出现的内容不统计，若不满足，则告知第几行为空，若满足然后还需校验满足条件的内容存不存在重复的内容，若存在，告知哪一个内容是重复的。都满足了才允许上次成功，并告知长传成功了几个卡号卡密。

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
public class RestRsp<T> extends BaseRsp {

    private T result;

    public static <T> RestRsp<T> success(T result) {
        return new RestRsp<>(result);
    }

    public static <T> RestRsp<T> fail(int code, String message) {
        RestRsp<T> response = new RestRsp<T>();
        response.setCode(code);
        response.setSuccess(false);
        response.setMessage(new Message(message));
        return response;
    }

    public static <T> RestRsp<T> fail(String message) {
        return fail(-1, message);
    }
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
public class UploadReq {

    @NotNull(message = "文件不能为空")
    private MultipartFile file;
    // private Integer level;
}

@PostMapping("/allright/file/upload")
    public RestRsp<Map<String, Object>> upload(@Valid UploadReq request) {
        log.debug("Success to handle the OptionController upload request={}", request);
        Map<String, Object> uploadResultMap = allRightsService.upload(request);
        Map<String,Long> rightsInfoMap = (Map<String,Long>) uploadResultMap.get("data");
        uploadResultMap.put("tip", String.format("成功上传%s个卡号，%s个卡密",
                rightsInfoMap.get("cardNum").toString(), rightsInfoMap.get("cardPwd").toString()));
        uploadResultMap.remove("data");
        return RestRsp.success(uploadResultMap);
    }

public interface AllRightsService {
    Map<String, Object> upload(UploadReq request);
}

@Service
public class AllRightsServiceImpl implements AllRightsService {
    @Override
    public Map<String, Object> upload(UploadReq request) {
        Map<String, Object> resultMap = new HashMap<>();
        MultipartFile file = request.getFile();
        String originalFilename = file.getOriginalFilename();
        if (originalFilename.length() > 100) {
            throw new MaCommonException(MaCommonError.INVALID_REQUEST, "上传的文件名称不能超过100字符");
        }
        String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
        Workbook workbook;
        try {
            InputStream is = file.getInputStream();
            if (EXCEL_FILE_TYPE_2003.equals(fileType)) {
                workbook = new HSSFWorkbook(is);
            } else if (EXCEL_FILE_TYPE_2007.equals(fileType)) {
                workbook = new XSSFWorkbook(is);
            } else {
                throw new MaCommonException(MaCommonError.INVALID_REQUEST, "文件格式不正确");
            }
        } catch (Exception e) {
            log.error("read excel exception", e);
            throw new MaCommonException(MaCommonError.INVALID_REQUEST, "文件格式不正确");
        }
        // getSheetAt 的参数是索引， 引用第几个sheet
        Sheet sheet = workbook.getSheetAt(0);
        // getPhysicalNumberOfRows   获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；
        // getLastRowNum 获取最后一行的编号,从0行开始,最后有数据的行是第n行则返回 n-1
        int rows = sheet.getLastRowNum();


        if (rows > EXCEL_FILE_MAX_ROWS) {
            throw new MaCommonException(MaCommonError.FORM_OPTION_EXCEL_MAXNUM_EXCEPTION);
        }
        // 获取标题内容
        Row rowTitle = sheet.getRow(0);
        if (ObjectUtils.isEmpty(rowTitle)) {
            throw new MaCommonException(MaCommonError.INVALID_REQUEST, "请使用正确的模板");
        }
        // 查看标题是否符合模板 当取到null，返回空字符串blank
        Cell cellOne = rowTitle.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        Cell cellTwo = rowTitle.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        Object cellFormatValueOne = getCellFormatValue(cellOne);
        Object cellFormatValueTwo = getCellFormatValue(cellTwo);
        if (ObjectUtils.isEmpty(cellFormatValueOne) && ObjectUtils.isEmpty(cellFormatValueTwo)) {
            throw new MaCommonException(MaCommonError.INVALID_REQUEST, "请使用正确的模板");
        }
        if (!("卡号").equals(cellFormatValueOne) || !("卡密").equals(cellFormatValueTwo)) {
            throw new MaCommonException(MaCommonError.INVALID_REQUEST, "请使用正确的模板");
        }
        int j = 0;
        for (int i = rows; i >= 1; i--) {
            //Row row = null;
            Cell cellOneC = null;
            Cell cellTwoC = null;
            // 空行跳过
            if (sheet.getRow(i) == null){
                continue;
            }
            //try {
            //    row = sheet.getRow(i);
            //} catch (NullPointerException e) {
            //    cellOneC = sheet.getRow(i).createCell(0);
            //    cellTwoC = sheet.getRow(i).createCell(1);
            //    log.info("错误信息{}",e);
            //}
            if (!ObjectUtils.isEmpty(sheet.getRow(i))) {
                 cellOneC = sheet.getRow(i).getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                 cellTwoC = sheet.getRow(i).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            }
            Object cellFormatValueOneC = getCellFormatValue(cellOneC);
            Object cellFormatValueTwoC = getCellFormatValue(cellTwoC);
            if(!ObjectUtils.isEmpty(cellFormatValueOneC) || !ObjectUtils.isEmpty(cellFormatValueTwoC)){
                j = i ;
                break;
        }
    }
    //resultMap.put("data", checkFile(sheet, rows + 1));
        resultMap.put("data", checkFile(sheet, j));
        resultMap.put("url", doUpload(file));
        return resultMap;
}
      private Map<String, Long> checkFile(Sheet sheet, int rows) {
        Map<String, Long> m = new HashMap<>();
        ArrayList<String> strings1 = new ArrayList<>();
        ArrayList<String> strings2 = new ArrayList<>();
        ArrayList<Integer> empty1 = new ArrayList<>();
        ArrayList<Integer> empty2 = new ArrayList<>();
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (ObjectUtils.isEmpty(row)) {
                empty1.add(i);
                empty2.add(i);
                continue;
            }
            Cell cell1 = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String cellFormatValue1 = getCellFormatValue(cell1);
            // 如果是空的
            if (cellFormatValue1.isEmpty()) {
                empty1.add(i);
            }else{
                strings1.add(cellFormatValue1);
            }
            Cell cell2 = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String cellFormatValue2 = getCellFormatValue(cell2);
            if (cellFormatValue2.isEmpty()) {
                empty2.add(i);
            }else{
                strings2.add(cellFormatValue2);
            }
        }
        // 合法
        if ((empty1.size() == 0 && empty2.size() == 0)
            || (empty1.size() == 0 && empty2.size() == rows - 1)
                || (empty2.size() == 0 && empty1.size() == rows - 1)){

            // 判断单元格是否存在超100字符的元素
            for (int i = 0; i < strings1.size(); i++) {
                if (strings1.get(i).length() > OPTION_EXCEL_STR_MAX_LENGTH){
                    throw new MaCommonException(FORM_OPTION_OVER_LENGTH_EXCEPTION,i + 2, 1);
                }
            }
            // 查找集合中重复的元素
            Set<String> collect = getRepeat(strings1);

            String tip = "";
            String repeatNames = collect.stream().collect(Collectors.joining(","));
            tip = String.format("当前表格第一列有重复数据%s", repeatNames);
            if (collect.size() > 0) {
                throw new MaCommonException(13001, tip);
            }

            for (int i = 0; i < strings2.size(); i++) {
                if (strings2.get(i).length() > OPTION_EXCEL_STR_MAX_LENGTH){
                    throw new MaCommonException(FORM_OPTION_OVER_LENGTH_EXCEPTION,i + 2, 2);
                }
            }
            Set<String> collect1 = getRepeat(strings2);
            String tip1= "";
            String repeatNames1 = collect1.stream().collect(Collectors.joining(","));
            tip1 = String.format("当前表格第二列有重复数据%s", repeatNames1);
            if (collect1.size() > 0) {
                throw new MaCommonException(13001, tip1);
            }


            m.put("cardNum", rows - 1 - Long.valueOf(empty1.size()));
            m.put("cardPwd", rows - 1 - Long.valueOf(empty2.size()));
        }else{
             if (empty1.size() == 0){
                 throw new MaCommonException(ALL_RIGHTS_EXCEL_EMPTY_EXCEPTION,empty2.get(0) + 1);
             } else if (empty2.size() == 0){
                 throw new MaCommonException(ALL_RIGHTS_EXCEL_EMPTY_EXCEPTION,empty1.get(0) + 1);
             }else{
                 int errorRow = empty1.get(0) >= empty2.get(0) ? empty1.get(0) + 1
                         : empty2.get(0) + 1;
                 throw new MaCommonException(ALL_RIGHTS_EXCEL_EMPTY_EXCEPTION,errorRow);
             }
        }
            return m;
        }
  @NotNull
    private static Set<String> getRepeat(ArrayList<String> strings1) {
        HashSet<String> set = new HashSet<>();
        List<String> repeatElements = new ArrayList<>();
        for (int i = 0; i < strings1.size(); i++) {
            String value = strings1.get(i);
            if (set.contains(value)) {
                // 重复元素
                repeatElements.add(value);
            } else {
                set.add(value);
            }
        }
        Set<String> collect = repeatElements.stream().distinct().collect(Collectors.toSet());
        return collect;
    }
 private String doUpload (MultipartFile file){
            // 上传文件
            String uuid = UUID.randomUUID().toString().replaceAll("-+", "");
            String fileName = uuid + "-" + file.getOriginalFilename();
            String url = minioConf.getEndpoint() + minioService.saveObject(minioConf.getBucket(), "allright/"
                    + fileName, file);
            log.info("upload excel url is {}", url);
            return url;
        }


        private String getCellFormatValue (Cell cell){
            // new DataFormatter().formatCellValue(cell) 可以直接获取单元格内容
            return ObjectUtils.isEmpty(cell) ? "" : new DataFormatter().formatCellValue(cell);
        }
}