正则表达式如何匹配excel文件
*.xl*
用Java正则表达式来过滤Excle格式。比如Excle文件格式有.xlsx”和.xls两种。那么正则表达式怎么写呢。
\\.[x]{1}[l]{1}[s]{1}[x]?
那样写的 话,就是 \\.xlsx?
EXCEL正则表达式
假设数据在A1,在B1输入公式:
=LEFT(A1,LENB(A1)-LEN(A1))
excel中使用正则表达式
Function JiSuan(Rng As Range) As Single
Dim Reg As New RegExp
Dim R As String
On Error GoTo ErrHand
R = (Rng)
Reg.Global = True
Reg.Pattern = "[\u4e00-\u9fa5]|m|M"
R = Reg.Replace(R, "")
Reg.Pattern = "×"
R = Reg.Replace(R, "*")
RegSZCC R
RegSZJJ R
JiSuan = Round(CSng(R), 4)
Set Reg = Nothing
Exit Function
ErrHand:
JiSuan = Err.Description
End Function
Sub RegSZCC(ByRef S As String)
Dim Reg As New RegExp
Dim MS
Dim R As String
Reg.Pattern = "(\d \.{0,1}\d*)(\*|\\)(\d \.{0,1}\d*)"
Set MS = Reg.Execute(S)
If MS.Count = 1 Then
If MS(0).SubMatches(1) = "\" Then
R = Format(CSng(MS(0).SubMatches(0)) / CSng(MS(0).SubMatches(2)), "0.00000")
Else
R = Format(CSng(MS(0).SubMatches(0)) * CSng(MS(0).SubMatches(2)), "0.00000")
End If
S = Reg.Replace(S, R)
RegSZCC S
End If
End Sub
Sub RegSZJJ(ByRef S As String)
Dim Reg As New RegExp
Dim MS
Dim R As String
Reg.Pattern = "(\d \.{0,1}\d*)(\ |\-)(\d \.{0,1}\d*)"
Set MS = Reg.Execute(S)
If MS.Count = 1 Then
If MS(0).SubMatches(1) = " " Then
R = Format(CSng(MS(0).SubMatches(0)) CSng(MS(0).SubMatches(2)), "0.00000")
Else
R = Format(CSng(MS(0).SubMatches(0)) - CSng(MS(0).SubMatches(2)), "0.00000")
End If
S = Reg.Replace(S, R)
RegSZJJ S
End If
End Sub
EXCEL用方法
B1=jisuan(A1)