SqlCelFuncs

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

SqlCel函数封装了很多实用的函数,这些函数主要通过VBA调用,它可以让VBA开发更简洁高效。比如你可以通过数据库函数连接和操作数据库。这时候你就不需要引用数据库连接的驱动,也不需要写复杂的连接字符串,如果你连接的是Mysql数据库还省去了安装ODBC的麻烦,只需一个函数即可实现连接,只需一个函数即可实现查询。

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函数主要包括数据库函数、数据集函数、网页爬虫函数、Python函数、字符串函数、数组函数、正则函数6大类,另外还有一个字典函数、一个fso函数、两个线程函数和两个错误处理函数共109个函数。 其中最常用的部分是数据库函数及网页爬虫函数。Python函数也是一个非常实用的创新。另外为了充分发挥.Net的开发能力SqlCel函数还预留了40个接口可供用户开发自定义函数。

数据库函数:play

连接和操作Mysql、Sql Server、Oracle三种主流数据库。提供了数据库连接、数据库查询及更新、查询结果的格式转换等函数。

爬虫函数:play

实现网页爬虫功能。通过网页标签索引获取标签内容。提供了网页文本获取,网页标签解析,标签与内容的键值对查询等函数。

数据集函数:play

将Excel数据读入内存表单中,可在内存中对数据实现便捷高效的查询、计算及修改。

Python函数:

通过Python函数可在Python的IDE中编写VBA代码或读取VBA文件,也可在VBA中编写Python代码或读取Python文件,从而实现Python与Excel的最佳交互。

正则函数: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函数了,如下:

'引用SqlCel插件必写(随便写在哪个模块都可以)
Public Function s() As Object
    Set s = Application.COMAddIns("SqlCelAddIn").Object
End Function

'主程序入口
Sub QueryMysql()
    s.UseConn "Mysql", "firstdb"   '使用名为firstdb的Mysql连接
    Call QueryToRs  '从firstdb中查询数据
    s.UseConn "Mysql", "seconddb"   '使用名为seconddb的Mysql连接
    Call QueryToArr '从seconddb中查询数据
    Call QueryToQax '从seconddb中查询数据
End Sub

'先定义两个连接
Sub DefMysqlConnection()
    s.DefMysqlConn "firstdb", "127.0.0.1", "test", "root", "123abc", "3306", "utf8"
    s.DefMysqlConn "seconddb", "127.0.0.1", "test2", "root", "123abc", "3306", "utf8"
End Sub

'将数据查到Recordset数据集中
Sub QueryToRs()
    Dim rs As Object, i As Integer
    Set rs = s.QueryToRs("SELECT * FROM cars")
    With Sheets("TB1")
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i
        .Range("A2").CopyFromRecordset rs
    End With
End Sub

'将数据查到二维数组中
Sub QueryToArr()
    Dim arr As Variant
    arr = s.QueryToArr("SELECT* FROM colleges", True)
    s.ArrayToExcel arr, Sheets("TB3").Range("A1")
End Sub

'将数据查询到QAX(DataTable)中
Sub QueryToQax()
    Dim qax As Variant
    Set qax = s.QueryToQax("SELECT * FROM colleges")
    s.QAXToRng qax, Sheets("TB2").Range("A1"), "TableStyleDark10", True
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.5.8.exe 4.16M