Python+PySide6开发Excel数据校验工具源代码.zip

# conding:utf-8 ''' 数据校验类定义 ''' import numpy as np import pandas as pd import re import os import IPy import string class colNum(): def __init__(self, col_num): self._number = col_num self.sequence = list(map(lambda x: chr(x), range(ord('A'), ord('Z') + 1))) if isinstance(self._number, int): self._L = self.ten2TwentySix(self._number) else: self._T = self.twentySix2Ten(self._number) # 十进制转26进制 def ten2TwentySix(self, num): num = num - 1 L = [] if num > 25: while True: d = int(num / 26) remainder = num % 26 if d <= 25: L.insert(0, self.sequence[remainder]) L.insert(0, self.sequence[d - 1]) break else: L.insert(0, self.sequence[remainder]) num = d - 1 else: L.append(self.sequence[num]) return "".join(L) # 26进制转十进制 def twentySix2Ten(self, s): l = len( s ) sum = 1 #实现从A对应1 if l > 1: for i in range( l - 1 ): index = self.sequence.index( s[i] ) print( index ) num = pow( 26, l - 1 ) * (index + 1) print( num ) l = l - 1 sum = sum + num sum = sum + self.sequence.index( s[-1] ) else: sum = sum + self.sequence.index( s[-1] ) return sum class DataValidation(): def __init__(self, file_name, df, start_row, end_row, start_col, end_col, vld_config, config_report_save_path) -> None: # 数据初始化 self._file_name = file_name self._df = df self._start_row = start_row self._start_col = start_col self._end_row = end_row self._end_col = end_col # 获取待校验数据区域 self._vld_data = self.get_validation_area() #print('-------------------------------------') #print(self._vld_data) # 获取校验配置表 self._vld_config = vld_config self._config_report_save_path = config_report_save_path # 获取待校验数据区域 def get_validation_area(self): return self._df.iloc[self._start_row-1:self._end_row, self._start_col:self._end_col] # 身份证号码校验 def id_card_validation(self, idcard): ''' 当前只完成简单校验功能,更完整的校验功能参考:https://github.com/jxlwqq/id-validator.py/tree/master 当前校验代码参考:https://zhuanlan.zhihu.com/p/24449773中的校验方法 ''' IDCARD_REGEX = '[1-9][0-9]{14}([0-9]{2}[0-9X])?' if isinstance(idcard, int): idcard = str(idcard) elif isinstance(idcard, str): idcard = idcard.replace(' ', '') else: return '身份证中可能存在特殊字符' if not re.match(IDCARD_REGEX, idcard): return '身份证号码错误' items = [int(item) for item in idcard[:-1]] ## 加权因子表 factors = (7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2) ## 计算17位数字各位数字与对应的加权因子的乘积 copulas = sum([a * b for a, b in zip(factors, items)]) ## 校验码表 ckcodes = ('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2') if ckcodes[copulas % 11].upper() == idcard[-1].upper(): return '正确证件号' else: return '身份证号码错误' # 固定电话号码校验 def landline_validation(self, x): ''' 固定电话号码校验 ''' if isinstance(x, int): x = str(x) fixed_phone_pattern = '^0\d{2,3}-?\d{7,8}$' if re.match(fixed_phone_pattern, x): return '正确号码' else: return '固定电话号码错误' # 手机号码校验 def mobile_phone_validation(self, x): ''' 手机号码校验 ''' if isinstance(x, int): x = str(x) if isinstance(x, float): x = str(int(x)) mobile_phone_pattern = '^1[3456789]\d{9}$' if re.match(mobile_phone_pattern, x): return '正确号码' else: return '手机号码错误' # 日期时间校验 def date_time_validation(self, x): pass # 邮箱校验 def email_validation(self, x): ''' 完善的Email地址格式校验,可以参考:https://github.com/JoshData/python-email-validator/tree/main 当前只是简单检查Email格式是不是合法 ''' REGEX_PATTERN = "^[a-zA-Z0-9_+&*-]+(?:\\.[a-zA-Z0-9_+&*-]+)*@(?:[a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,7}$" if (re.search(REGEX_PATTERN, x)): return '正确邮箱' else: return '邮箱格式错误' # IP地址校验 def ip_validation(self, x): try: version = IPy.IP(x).version() if version == 4 or version == 6: return '正确地址' else: return 'IP地址校验不通过' except Exception as e: return 'IP地址校验不通过' # 邮编校验 def post_code_validation(self, x): REGEX_PATTERN = "[1-9]\d{5}(?!d)" if (re.search(REGEX_PATTERN, x)): return '正确邮编' else: return '邮编可能错误' # MAC地址校验 def mac_code_validation(self, mac): """ 验证Mac地址 :param mac: Mac地址 :return: Ture or False """ # 以'-'作为分隔符 if mac.find('-') != -1: pattern = re.compile(r"^\s*([0-9a-fA-F]{2}-){5}[0-9a-fA-F]{2}\s*$") if pattern.match(mac): return '正确Mac地址' else: return 'Mac地址可能错误' # 以':'作为分隔符 if mac.find(':') != -1: pattern = re.compile(r"^\s*([0-9a-fA-F]{2}:){5}[0-9a-fA-F]{2}\s*$") if pattern.match(mac): return '正确Mac地址' else: return 'Mac地址可能错误' # 非空校验 def null_validation(self, x): if isinstance(x, str): if len(x.replace(' ', '')) > 0: return '非空' else: return '空值' else: if np.isnan(x): return '空值' else: return '非空' # 数字校验 def number_validation(self, x): pass # 域名校验 def domain_validation(self, x): ''' 详细的域名校验流程,可以参考:https://blog.51cto.com/u_16213453/7158128 目前仅校验域名格式是否符合域名格式 ''' REGEX_PATTERN = r'^[a-zA-Z0-9]+(\.[a-zA-Z0-9-]+)*(\.[a-zA-Z]{2,})$' if re.match(REGEX_PATTERN, x): return '域名格式正确' else: return '域名格式错误' # URL校验 def url_validation(self, x): ''' URL格式校验,目前仅校验域名格式 ''' REGEX_PATTERN = r'^(http|https)://[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}(?:/[\w.-]*)*/?$' if re.match(REGEX_PATTERN, x): return 'URL格式正确' else: return 'URL格式错误' # 统一社会信用代码校验 def unified_social_credit_code_validation(self, code): """ 校验社会统一社会信用代码, 参考:https://blog.csdn.net/qq_42774234/art