如何从Excel连接MongoDB?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (27)

我想用EXCEL宏连接到MongoDB数据库,有人知道如何完成这个任务吗?

提问于
用户回答回答于

简单的方法是

  1. 创建一个C#DLL,通过可用的c#驱动程序与mongodb进行交互。
  2. 搞定COM可见(在Assemblyinfo.cs中),构建并注册它
  3. 转到excel宏>visual basic编辑器。
  4. 单击Tools->Reference,然后选择已注册的程序集
  5. 然后用在你的VBA里,就像这样。
Private Sub CallMongo()
    Dim mongoObj As New MyMongoAssembly
    mongoObj.AddItem("adas");
End Sub
用户回答回答于

几乎所有与命令行接口的东西都可以用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解析器,但在本例中,我们将使用TimHall提供的VBA-JSON

在编写本报告时,VBA-JSON有一个问题需要解决,即使用它解析从MongoDB返回的字符串。任何包含括号的值,例如"_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,并将每个对象添加到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

扫码关注云+社区