我想使用excel宏连接到mongodb数据库,有人知道如何完成这项任务吗？
几乎任何与命令行接口的东西都可以通过Shell访问.
这是一个简单的示例,它连接到正在运行的MongoDB实例并将查询打印到立即窗口.你需要添加一个引用Windows Script Host Object Model.
Private Sub Test()
Dim wsh As New WshShell
Dim proc As WshExec
Dim line As String
Set proc = wsh.Exec("mongo")
With proc
.StdIn.WriteLine "use test"
.StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
.StdIn.WriteLine "quit()"
Do While .Status = WshRunning
line = .StdOut.ReadLine
If line = "Type ""it"" for more" Then
.StdIn.WriteLine "it"
ElseIf line Like "{*" Then
Debug.Print line
End If
DoEvents
Loop
End With
End Sub
然而,仅打印原始JSON字符串并不是非常令人兴奋或有用.您可以编写自己的JSON解析器,但是,对于此示例,我们将使用Tim Hall的VBA-JSON(您可以在GitHub上找到它).
在撰写本文时,在使用它来解析从MongoDB返回的字符串时,必须解决VBA-JSON的一个问题.例如"_id": ObjectId("..."),包含括号的任何值都将引发错误.对此进行快速而又脏的修复是使用RegEx清除解析器的字符串.您需要引用该Microsoft VBScript Regular Expressions 5.5库才能使用以下函数.
Private Function CleanString(str As String) As String
Dim temp As String
Dim rx As New RegExp
With rx
.IgnoreCase = True
.Global = True
.Pattern = "[a-z]*\(" ' Left
temp = .Replace(str, "")
.Pattern = "\)" ' Right
temp = .Replace(temp, "")
End With
CleanString = temp
End Function
然后我们可以解析从MongoDB返回的JSON并将每个对象添加到a Collection.访问值变得非常简单.
Private Sub Mongo()
Dim wsh As New WshShell
Dim proc As WshExec
Dim line As String
Dim response As New Collection
Dim json As Object
Set proc = wsh.Exec("mongo")
With proc
.StdIn.WriteLine "use test"
.StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
.StdIn.WriteLine "quit()"
Do While .Status = WshRunning
line = .StdOut.ReadLine
If line = "Type ""it"" for more" Then
.StdIn.WriteLine "it"
ElseIf line Like "{*" Then
response.Add ParseJson(CleanString(line))
End If
DoEvents
Loop
End With
For Each json In response
Debug.Print json("name"), json("address")("street")
Next
End Sub
...将从MongoDB 示例数据集生成以下输出.
Nectar Coffee Shop Madison Avenue Viand Cafe Madison Avenue Don Filippo Restaurant Lexington Avenue Lusardi'S Restaurant Second Avenue Due Third Avenue Lenox Hill Grill/Pizza Lexington Avenue Quatorze Bistro East 79 Street Luke'S Bar & Grill Third Avenue Starbucks Coffee Lexington Avenue New York Jr. League East 80 Street Doc Watsons 2 Avenue Serafina Fabulous Pizza Madison Avenue Canyon Road Grill 1 Avenue Sushi Of Gari East 78 Street
ReadLine和WriteLine被闭锁功能.
打开的窗口Exec 无法隐藏.
上述两种方法的解决方法是使用两层方法,其中VBA使用隐藏脚本调用wsh.Run,然后运行Exec(以及与proc交互的任何其他代码).这种方法的缺点是必须将StdIn(以及某种程度上的StdOut)写入文件.
简单的方法是
创建一个C＃dll以通过可用的c＃驱动程序与Mongo db进行交互。
使它可见（在Assemblyinfo.cs中），构建并注册
转到excel宏-> Visual Basic编辑器
单击工具->参考，然后选择您注册的程序集
并像这样在您的VBA中使用它。
。
Private Sub CallMongo()
Dim mongoObj As New MyMongoAssembly
mongoObj.AddItem("adas");
End Sub
就这样..
京公网安备 11010802040832号 | 京ICP备19059560号-6 