SqlCelFuncs

SqlCelFuncs即SqlCel函数。将SqlCel完整版的函数部分独立出来就产生了SqlCelFuncs。

SqlCel函数试图以函数的形式将VBA和.Net两种技术结合起来扬长避短开发Excel。VBA是Excel自带的开发语言和Excel具有最佳的结合,但是对于大数据的处理和内存计算等问题VBA颇为吃力;.NET具有更强大的开发能力,是一门规范的高级计算机语言,这也是VSTO(Visual Studio Tools For Office)的意义,VSTO试图将Visual Studio和.Net的强大开发能力赋能于Office。但是VSTO也有自身的缺陷。

第一,Visual Studio虽然开发高效但是调试起来比较吃力,启动一次软件就要花很长时间对内存的开销也很大。

第二,VSTO和Excel对象的交互不方便,比如在Excel里插入一个按钮,并给按钮绑定事件用VSTO做起来就比较困难。

第三,VSTO不便于开发启用宏的工作簿。

为了解决以上问题SqlCelFuncs诞生了。通过SqlCelFuncs即可以发挥VBA和Excel的最佳交互也可以发挥.Net的强大能力。

SqlCel函数主要分数据集函数、数据库函数、字符串函数、数组函数、正则函数、网页爬虫函数6大类,另外还有一个字典函数、一个fso函数、两个线程函数和两个错误处理函数共109个函数。 其中最重要的部分是数据集函数。通过数据集函数可以实现强大的内存计算和高效的数据转换和数据处理。另外为了充分发挥.Net的开发能力SqlCel函数还预留了40个接口可供用户开发自定义函数。

数据集函数:play

数据库函数:play

爬虫函数:play

正则函数:play

自定义函数:play

开发自定义函数的项目模板可通过以下链接获取。可在该VSTO项目的四十个预留接口中直接开发自定义函数,项目发布后将生成的程序集LittleSql.dll复制到SqlCelFuncs或SqlCel的安装目录中替换掉原有的LittleSql.dll即可。

C#版本的项目模板:LittleSql_CSharp.zip

VB.Net版本的项目模板:LittleSql_VBNet.zip

如果预留的40个接口不够使用可联系作者增加接口数量。接口函数的具体使用方法可参照项目模板中的类 HowToUseLittleSql.cs 里面有详细的说明。

SqlCel函数可以通过VBA直接调用。需要注意的是用SqlCel函数开发出来的VBA作品需有SqlCelFuncs或SqlCel完整版作为运行环境,庆幸的是SqlCelFuncs是完全免费的。

SqlCelFuncs安装后在Excel/WPS中没有SqlCel选项卡,您可以通过检查Com加载项对话框(文件 -> 选项 -> 加载项 -> COM加载项 -> 转到 或点击“开发工具”选项卡下的COM加载项)来判断SqlCelFuncs是否安装成功。如下:

sqlcelfuncsinstall

如果SqlCelFuncs前面的复选框正常勾选说明已安装成功。

打开Visual Basic编辑器按住Ctrl+G弹出立即窗口在里面录入以下代码可调出SqlCel函数对话框:

Application.COMAddIns("SqlcelAddin").Object.show

如下:

funcspanel

可在该对话框中学习SqlCel函数的使用,在开发的过程中也可以参考该对话框使用SqlCel函数。

举几个SqlCel函数的使用样例:

使用SqlCel函数首先需在模块中录入以下代码

Public Function s() As Object
    Set s= Application.COMAddIns("SqlCelAddIn").Object
End Function

接下来我们就可以引用SqlCel函数了

Public Function s() As Object
    Set s = Application.COMAddIns("SqlCelAddIn").Object
End Function

Sub SqlCelFuncsExamps()
    Dim rndstr As String
    rndstr = s.rndstr(10)
    Debug.Print rndstr   '生成一个随机字符串

    Dim Str As String
    Str = "ufokddQQkwkt"
    Debug.Print s.ReplaceToUpper(Str, "k")  '输出ufoKddQQKwKt
    Debug.Print s.Reverse(Str)  '输出tkwkQQddkofu

    Dim strbld As Object
    Set strbld = s.strbuilder   '定义一个StringBuilder对象
    Set strbld = s.strappend(strbld, Str)  '追加文本
    Debug.Print s.tostr(strbld) '将对象转换为String

    Dim dic As Object
    Set dic = s.newdic   '定义一个字典
    dic.Add 1, "a"
    Debug.Print dic(1)

    Dim fso As Object
    Set fso = s.newfso   '定义一个fso对象
    Debug.Print fso.folderexists("D:\Program Files") '判断文件夹是否存在

    Dim arr() As String
    arr = s.regmatches("9d0k2", "\d")  '使用正则表达式
    Debug.Print UBound(arr)  '输出2
End Sub

这是一个非常简单的样例,接下来再给出一个复杂一点的样例

Private arr1 As String, tparr, d As Object, arr(13), doc As Variant

Public Function s() As Object
    Set s = Application.COMAddIns("SqlCelAddIn").Object
End Function

Sub WebCraw()
    Dim pg As Integer, i As Integer, zoneKeys()
    Call DefineDic
    zoneKeys = d.keys
    For i = 0 To UBound(zoneKeys)
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        Range("a1:n1") = Array("城市", "区", "镇", "地址", "小区", "建造日期", "房型", "楼层", "面积", "特点", "总价", "单价", "业主", "描述")
        ActiveSheet.Name = d(zoneKeys(i))
        For pg = 1 To 50
            Call getNodes(pg, zoneKeys(i))
        Next pg
    Next i
End Sub

Sub getNodes(pg As Integer, thezone)
    Dim bfstr As String, lastR As Long, i As Integer
    Dim tpnode As String, tpstr As String, url As String
    url = "https://shanghai.anjuke.com/sale/" & thezone & "/p"
    bfstr = "/html[1]/body[1]/div[1]/div[2]/div[4]/ul[1]/"
    Set doc = s.getdoc(url & pg)
    Erase arr()
    For i = 1 To 60
        arr(0) = "上海"
        tpstr = GettheNode(bfstr & "li[" & i & "]/div[2]/div[3]")
        tparr = Split(tpstr, "-")
        arr1 = tparr(0)
        arr(1) = tryM1
        arr(2) = tparr(1)
        arr(3) = tryM3
        arr(4) = Split(tparr(0), " ")(0)
        arr(5) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[2]/span[4]")
        arr(6) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[2]/span[1]")
        arr(7) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[2]/span[3]")
        arr(8) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[2]/span[2]")
        arr(9) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[4]")
        arr(10) = GettheNode(bfstr & "li[" & i & "]/div[3]/span[1]")
        arr(11) = GettheNode(bfstr & "li[" & i & "]/div[3]/span[2]")
        arr(12) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[2]/span[5]")
        arr(13) = GettheNode(bfstr & "li[" & i & "]/div[2]/div[1]/a[1]")
        lastR = Cells(1048576, 1).End(xlUp).Row + 1
        Range("A" & lastR & ":N" & lastR).Value = arr
    Next i
    lastR = Cells(1048576, 1).End(xlUp).Row + 1
    Range("A" & lastR).Select
    DoEvents
End Sub

Function GettheNode(xPath As String)
    If s.isnodein(doc, xPath) Then
        GettheNode = s.getnode(doc, xPath)
    End If
End Function

Function tryM1()
    On Error GoTo line
    tryM1 = Split(tparr(0), ";")(2)
    Exit Function
line:
    tryM1 = ""
End Function

Function tryM3()
    On Error GoTo line
    tryM3 = tparr(2)
    Exit Function
line:
    tryM3 = ""
End Function

Function DefineDic()
    Set d = s.newdic
    d.Add "pudong", "浦东": d.Add "minhang", "闵行": d.Add "baoshan", "宝山": d.Add "xuhui", "徐汇": d.Add "songjiang", "松江": d.Add "jiading", "嘉定"
    d.Add "jingan", "静安": d.Add "putuo", "普陀": d.Add "yangpu", "杨浦": d.Add "hongkou", "虹口": d.Add "changning", "长宁": d.Add "huangpu", "黄浦"
    d.Add "qingpu", "青浦": d.Add "fengxian", "奉贤": d.Add "jinshan", "金山": d.Add "chongming", "崇明": d.Add "shanghaizhoubian", "上海周边"
End Function

以上样例可实现多线程爬取安居客的14个字段。我们可以看到SqlCel的爬虫函数开发效率非常高效且易于维护。现在我们爬取了上海市每个区的房源信息如下: anjukedata 现在我们又要做一件事情就是把这些信息合并到一张表中并导出一个txt文件。我们可以新建一个表,并录入以下代码:

Sub CombineAndExport()
    Dim lastR As Long, i As Integer, arr
    Dim qax As Variant
    For i = 1 To Sheets.Count - 1
        Set qax = s.rngtoqax(Sheets(i).Range("a1").CurrentRegion, True)
        If i = 1 Then arr = s.qaxtoarray(qax, True) Else arr = s.qaxtoarray(qax, False)
        lastR = Cells(1048576, 1).End(xlUp).Row + 1
        s.arraytoexcel arr, Cells(lastR, 1), True
    Next i
    Set qax = s.rngtoqax(Range("a2").CurrentRegion, True)
    s.qaxtofile qax, "e://anjuke.txt", "|" '将数据写入竖线分隔符的文本中
End Sub

数据集函数非常强大,可实现内存数据的计算,查询,修改和读写等。

点击以下链接下载SqlCelFuncs:

SqlCelFuncs.V2.2.7.exe 4.16M