EXCEL扩展API使用手册

野性酷女 2023-07-08 02:42 27阅读 0赞

EXCEL扩展API使用手册

## 一、使用说明： ##

1、 打开EXCEL。  
2、 右键点击SHEET1，选择“查看代码”，如下图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70]  
3、 点击“工具栏”中的“模块”，如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70 1]  
4、 点击左侧“模块1”，打开后，将API代码粘贴至“模块1”内。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70 2]

## 二、API代码： ##

Function FIND_LIST(find_str, list)
        For Each mylist In list
            If InStr(find_str, mylist) > 0 Then
                FIND_LIST = mylist
                Debug.Print mylist
                Exit Function
            End If
        Next mylist
    End Function
    '超级清洗字符串函数
    Function SUCLEAN(cell_text As Range, clean_str As String)
        Debug.Print clean_str
        Dim Cleaned As String
        Select Case clean_str
            Case "汉字"
                Cleaned = CleanRegExpStr(cell_text, "[\u4e00-\u9fa5]", True, True)
            Case "数字"
                Cleaned = CleanRegExpStr(cell_text, "[0-9]", True, True)
            Case "字母"
                Cleaned = CleanRegExpStr(cell_text, "[a-zA-Z]", True, True)
            Case "特殊字符"
                Cleaned = CleanRegExpStr(cell_text, "[^%&',;=?${}<>\x22]", True, True)
            Case Else
                Cleaned = CleanRegExpStr(cell_text, clean_str, True, True)
        End Select
        SUCLEAN = Cleaned
    End Function
    '超级挑捡字符串函数
    Function SUPICK(cell_text As Range, pick_str As String)
        Debug.Print pick_str
        Dim Picked As String
        Select Case pick_str
            Case "汉字"
                Picked = PickRegExpStr(cell_text, "[\u4e00-\u9fa5]", True, True)
            Case "数字"
                Picked = PickRegExpStr(cell_text, "[0-9]", True, True)
            Case "字母"
                Picked = PickRegExpStr(cell_text, "[a-zA-Z]", True, True)
            Case "手机号"
                Picked = PickRegExpStr(cell_text, "(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{
       8}", True, True)
            Case "邮箱"
                Picked = PickRegExpStr(cell_text, "[\w!#$%&'*+/=?^_`{|}~-]+(?:\.[\w!#$%&'*+/=?^_`{|}~-]+)*@(?:[\w](?:[\w-]*[\w])?\.)+[\w](?:[\w-]*[\w])?", True, True)
            Case "身份证号"
                Picked = PickRegExpStr(cell_text, "\d{
       15}|\d{
       18}", True, True)
            Case "网址"
                Picked = PickRegExpStr(cell_text, "[a-zA-z]+://[\w!#$%&'*+/=?._-]*", True, True)
            Case Else
                Picked = PickRegExpStr(cell_text, pick_str, True, True)
        End Select
        SUPICK = Picked
    End Function
    Function CleanRegExpStr(TargetRange As Range, myPattern As String, myGlobal As Boolean, myIgnoreCase As Boolean) As String
        Dim mRegExp As Object       '正则表达式对象
        Dim mMatches As Object      '匹配字符串集合对象
        Dim mMatch As Object        '匹配字符串
        Dim mReplace As String      '替换后的字符串
        
        mReplace = TargetRange.Text
        Debug.Print mReplace
        Set mRegExp = CreateObject("Vbscript.Regexp")
        With mRegExp
            .Global = myGlobal                              'True表示匹配所有, False表示仅匹配第一个符合项
            .IgnoreCase = myIgnoreCase                          'True表示不区分大小写, False表示区分大小写
            .Pattern = myPattern          '"([0-9])?[.]([0-9])+|([0-9])+ 匹配字符模式
            Set mMatches = .Execute(TargetRange.Text)   '执行正则查找，返回所有匹配结果的集合，若未找到，则为空
            For Each mMatch In mMatches
                Debug.Print mMatch
                mReplace = Replace(mReplace, mMatch, "")
                Debug.Print "mReplace:" & mReplace
            Next
            
        End With
        Set mRegExp = Nothing
        Set mMatches = Nothing
        Debug.Print "CLEAN_RESULT:" & mReplace
        CleanRegExpStr = mReplace
    End Function
    Function PickRegExpStr(TargetRange As Range, myPattern As String, myGlobal As Boolean, myIgnoreCase As Boolean) As String
        Dim mRegExp As Object       '正则表达式对象
        Dim mMatches As Object      '匹配字符串集合对象
        Dim mMatch As Object        '匹配字符串
        Dim mReplace As String      '替换后的字符串
        
        mPick = ""
        Set mRegExp = CreateObject("Vbscript.Regexp")
        With mRegExp
            .Global = myGlobal                              'True表示匹配所有, False表示仅匹配第一个符合项
            .IgnoreCase = myIgnoreCase                          'True表示不区分大小写, False表示区分大小写
            .Pattern = myPattern          '"([0-9])?[.]([0-9])+|([0-9])+ 匹配字符模式
            Set mMatches = .Execute(TargetRange.Text)   '执行正则查找，返回所有匹配结果的集合，若未找到，则为空
            For Each mMatch In mMatches
                Debug.Print mMatch
                mPick = mPick & mMatch
                Debug.Print "mPick:" & mPick
            Next
            
        End With
        Set mRegExp = Nothing
        Set mMatches = Nothing
        Debug.Print "PICK_RESULT:" & mPick
        PickRegExpStr = mPick
    End Function

## 三、函数使用说明： ##

1、 FIND\_LIST函数  
功能:列表匹配单元格并返回匹配成功的结果。  
=FIND\_LIST(FIND\_STR, LIST)  
FIND\_STR:单元格 如：C6  
LIST:待匹配列表 如：F6：F8  
例：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70 3]  
2、 SUCLEAN函数  
功能：清洗单元格内指定的内容  
=SUCLEAN(CELL\_TEXT,CLEAN\_STR)  
CELL\_TEXT: 单元格 如：C2  
CLEAN\_STR: 清洗指令 如：汉字、数字、字母、特殊字符、正则表达式  
例：  
![在这里插入图片描述][20200224171943735.png]  
3、 SUPICK函数  
功能：挑拣单元格内指定的内容  
=SUPICK(CELL\_TEXT,PICK\_STR)  
CELL\_TEXT: 单元格 如：C2  
PICK\_STR: 清洗指令 如：汉字、数字、字母、手机号、邮箱、身份证号、网址、正则表达式  
例：  
![在这里插入图片描述][20200224172002994.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020022417182850.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200224171851860.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200224171913143.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020022417192873.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDQ5OTc1Nw==,size_16,color_FFFFFF,t_70
[20200224171943735.png]: https://img-blog.csdnimg.cn/20200224171943735.png
[20200224172002994.png]: https://img-blog.csdnimg.cn/20200224172002994.png