# 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
Python+PySide6开发Excel数据校验工具源代码.zip
107 浏览量
2024-01-09
16:23:33
上传
评论 1
收藏 77KB ZIP 举报
Python+PySide6开发Excel数据校验工具源代码.zip （19个子文件） 
.vscode 
settings.json 64B app data_validation.py 18KB __pycache__ data_validation.cpython-39.pyc 13KB input demo pandas切片.ipynb 31KB NewWindowShow.py 1KB TreeWidget_Use_Demo1.py 2KB TableWidget_Use_Demo1.py 1KB TableColumn_create_Demo1.py 864B .ipynb_checkpoints pandas切片-checkpoint.ipynb 72B config_save config_output ui dv_mainwindow.ui 8KB dvConfig.py 2KB dv_mainwindow_entr.py 17KB dv_mainwindow_ui.py 11KB images 
table_icon_128.png 4KB table_icon.png 96KB __pycache__ dv_config_window_ui.cpython-39.pyc 4KB dv_mainwindow_ui.cpython-39.pyc 6KB dvConfig.cpython-39.pyc 2KB dv_mainwindow_entr.cpython-39.pyc 12KB资源评论
