Function check(FilePatch)
Dim xlsDoc,chk_colnum,chk_rownum,chk_tmpStr,numFlag,charFlag,emptFlag,flag
Set xlsDoc = CreateObject("Excel.Application")
xlsDoc.Workbooks.Open(FilePatch)
xlsDoc.Worksheets(1).Activate
'检查列数
chk_colnum = 1
chk_tmpStr = xlsDoc.Cells(2,chk_colnum).value
While not IsEmpty(chk_tmpStr)
chk_colnum = chk_colnum+1
chk_tmpStr = xlsDoc.Cells(2,chk_colnum).value
Wend
'检查行数
chk_rownum = 1
chk_tmpStr = xlsDoc.Cells(chk_rownum,1).value
While not IsEmpty(chk_tmpStr)
chk_rownum = chk_rownum+1
chk_tmpStr = xlsDoc.Cells(chk_rownum,1).value
Wend
chk_rownum = chk_rownum -1
chk_colnum = chk_colnum-1
For chk_i=2 To chk_colnum
chk_j = 3
emptFlag = False
numFlag = False
numcombFlag = False
chinaflag = False
flag =False
While (numFlag And chinaflag)<>True And (numFlag And numcombFlag)<>True And (numcombFlag And chinaflag)<>True And chk_j<chk_rownum
chk_tmpStr = xlsDoc.Cells(chk_j,chk_i).Value
If StrComp(chk_tmpStr,"") Then
emptFlag = True
If IsNumeric(chk_tmpStr) Then
numFlag = True
ElseIf isNumComb(chk_tmpStr) Then
numCombFlag = True
ElseIf ischina(chk_tmpStr) Then
chinaflag = True
ElseIf not StrComp(chk_tmpStr,"-") Then
flag = True
End If
End If
chk_j = chk_j+1
Wend
If numFlag And chinaflag Then
'MsgBox "1数据异常"&xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在没有翻译的值,请确认",,""
Reporter.ReportEvent micFail,"数据异常",xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在没有翻译的值,请确认"
ElseIf numFlag And numcombFlag Then
'MsgBox "2数据异常"&xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在没有翻译的值,请确认",,""
Reporter.ReportEvent micFail,"数据异常",xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在取值错误,请确认"
ElseIf Not emptFlag Then
Reporter.ReportEvent micFail,"数据异常",xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据全部为空,请确认"
'MsgBox "3数据异常"&xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在没有翻译的值,请确认",,""
ElseIf (Not numFlag) And (Not numcombFlag) And (Not chinaflag) And flag Then
Reporter.ReportEvent micFail,"数据异常",xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段的值都为-,请确认"
'MsgBox "5数据异常"&xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在没有翻译的值,请确认",,""
ElseIf numcombFlag And chinaflag Then
Reporter.ReportEvent micFail,"数据异常",xlsDoc.Cells(1,1)&"---"&xlsDoc.Cells(2,chk_i)&"---该字段数据异常,可能存在没有翻译的值,请确认"
End If
Next
xlsDoc.Quit
Set xlsDoc = Nothing
End Function
Function isNumComb(str)
Dim regEx,tmpflag
tmpflag = False
Set regEx = New RegExp
regEx.IgnoreCase = True
regEx.Pattern = "^[0-9|a-f]{0,2}-[0-9|a-f]{0,2}-[0-9|a-f]{0,2}$"
tmpflag = regEx.Test(str)
If Not tmpflag Then
regEx.Pattern = "^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$"
tmpflag = regEx.Test(str)
End If
isNumComb = tmpflag
End Function
Function ischina(str)
Dim regEx,tmpflag,regEx1
tmpflag = False
Set regEx = New RegExp
regEx.IgnoreCase = True
Set regEx1 =New RegExp
regEx1.Pattern ="[\u3400-\u9FFF]"
regEx.Pattern = "[a-z]"
china_k=1
char = Mid(str,china_k,1)
While regEx.Test(char)<>True And regEx1.Test(char)<>True And china_k<Len(str)
china_k = china_k+1
Wend
If regEx.Test(char)<>True And regEx1.Test(char)<>True Then
ischina = False
Else
ischina = True
End If
End Function
Function splitSpace(strMen)
strMen = Trim(strMen)
loca = InStrRev(strMen," ")
While loca<>0
strMen = Left(strMen,loca-1)&Right(strMen,Len(strMen)-loca)
loca = InStrRev(strMen," ")
Wend
splitSpace = strMen
End Function |