代码之家  ›  专栏  ›  技术社区  ›  Boris Barroso

如何从Excel连接Mongodb

  •  17
  • Boris Barroso  · 技术社区  · 14 年前

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

    9 回复  |  直到 14 年前
        1
  •  7
  •   natancodes    9 年前

    炮弹法

    几乎所有与命令行接口的东西都可以通过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(您可以 find it on GitHub ).

    在编写本文时,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生成以下输出 Example Dataset .

    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 ,然后运行 执行官 (以及与proc交互的任何其他代码)。这种方法的缺点是StdIn(在某种程度上是StdOut)必须写入文件。

        2
  •  5
  •   RameshVel    14 年前

    简单的方法是

    1. 创建一个C#dll,通过可用的C#驱动程序与Mongo db交互。
    2. 成功 Com可见 (在Assemblyinfo.cs中),构建并注册它
    3. 转到excel宏->visual basic编辑器
    4. 单击“工具”->“引用”,然后选择已注册的程序集
    5. 在你的VBA中使用,就像这样。

    .

    Private Sub CallMongo()
        Dim mongoObj As New MyMongoAssembly
        mongoObj.AddItem("adas");
    End Sub
    

    就这样。。

        3
  •  3
  •   Thomas Browne    11 年前

    我自己的解决方案是让Python使用pymongo和win32com将它们粘合在一起。然后你就可以随心所欲了。在我的例子中,Python循环只是不断地“监听”某些Excel单元格,从Mongo调用它需要的内容,然后将其放回Excel中。它很灵活,很多事情都可以这样做。这是完整的代码库,但是您必须更改对Mongodb的调用,以匹配您自己的数据库。在这里,您还将看到一些可以从Python中更改Excel单元格的颜色和内容的方法。哦,我应该提到的是,它充满了ansi转义序列,所以您可能希望从中运行Python ansicon ConEmu .

    import win32com.client as win32
    import time    # will need this for time parsing
    from optparse import OptionParser
    import pdb     # debugger, when necessary
    import string  # for string parsing and the alphabet
    from pymongo import MongoClient
    import inspect
    from datetime import datetime, timedelta, tzinfo
    from dateutil import tz
    from bson.son import SON
    import msvcrt # for getch
    import os
    import sys # for stdout.write
    from collections import OrderedDict
    
    
    def parseCmdLine():
        parser = OptionParser(description="Retrieve realtime data.")
        parser.add_option("--f",
                          dest="file",
                          help="filename",
                          default="bbcapture.xls")
        parser.add_option("--mongohost",
                          dest="mongohost",
                          default="192.168.1.30")
        parser.add_option("--mongoport",
                          dest="mongoport",
                          type="int",
                          default=27017)
    
        (options, args) = parser.parse_args()
        return(options)
    
    options = parseCmdLine() # parse the commandline
    client = MongoClient(options.mongohost, options.mongoport) # link to mongo
    db = client.bb # the database
    bbsecs = db.bbsecs # now all the collections
    bbdaily = db.bbdaily
    bbticks = db.bbticks
    linkstatusperiod = False # for the moving period in the top left excel cell showing we're linked
    
    def ansi(colour = "white", bright = False, back = "black"):
    # ansi colour sequences
        brit = {True:       "\033[1m",
                False:      "\033[0m"}
        colo = {"black":    "\033[30m", 
                "red":      "\033[31m",
                "green":    "\033[32m",
                "yellow":   "\033[33m",
                "blue":     "\033[34m",
                "magenta":  "\033[35m",
                "cyan":     "\033[36m",
                "white":    "\033[37m"}
        bakk = {"black":    "\033[40m", 
                "red":      "\033[41m",
                "green":    "\033[42m",
                "yellow":   "\033[43m",
                "blue":     "\033[44m",
                "magenta":  "\033[45m",
                "cyan":     "\033[46m",
                "white":    "\033[47m"}
        sys.stdout.write(brit[bright])
        sys.stdout.write(colo[colour])
        sys.stdout.write(bakk[back])
    
    
    def mdaily(ticker = "USDEUR Curncy", field = "LAST_PRICE", sortdirection = 1, numget = 1000000):
        ansi("cyan", False)
        print "\nGetting", ticker, "field", field, "from Mongo...",
        lister = OrderedDict()
        #for post in bbdaily.find({"ticker": ticker, "fieldname": field}).limit(numget).sort("time", sortdirection):
        for post in bbdaily.find({"$query": {"ticker": ticker, "fieldname": field}, "$orderby": {"time": -1}}).limit(numget):
            lister[str(post["time"])] = post["fieldvalue"]
        ansi("cyan", True)
        print "got", len(lister), "values",
        ansi()
        return lister
    
    def mtick(tickers, sortdirection = 1, numget = 1000000):
        if len(tickers) == 0:
            return []
        else:
            ansi("green", False)
            print "\n Getting minutes for for", tickers, 
            tickerdic = OrderedDict()
            for eachticker in tickers:
                eachdic = dict()
                print numget
                for post in bbticks.find({"ticker": eachticker}).limit(numget):
                    eachdic[post["time"]] = [post["open"], post["high"], post["low"], post["close"]]
                ansi("green")
                tickerdic[eachticker] = eachdic
                print "got", len(eachdic), "for ticker", eachticker, 
            ansi("green", True)
            print "got", len(tickerdic), "tickers",
            dates = [set(tickerdic[x].keys()) for x in tickerdic] # get all the dates
            dates = set.intersection(*dates) # get the unique ones
            dates = [x for x in dates] # convert to list
            if sortdirection == -1:
                dates = sorted(dates, reverse = True)
            else:
                dates = sorted(dates, reverse = False)
            retlist = [[[x, tickerdic[y][x][0], tickerdic[y][x][1], tickerdic[y][x][2], tickerdic[y][x][3]] for x in dates] for y in tickerdic.keys()]
            ansi()
            return retlist
    
    def getsecs():
        seclist = []
        for post in bbsecs.find():
            seclist.append(post["ticker"])
        return(seclist)
    
    
    
    
    def offsetString(startrow, startcol, endrow, endcol):
        startrowstr = str(startrow)
        endrowstr = str(endrow)
        if(startcol > 26):
            startcolstr = string.uppercase[startcol / 26 - 1] + string.uppercase[startcol % 26 - 1]
        else:
            startcolstr = string.uppercase[startcol - 1]
        if(endcol > 26):
            endcolstr = string.uppercase[endcol / 26 - 1] + string.uppercase[endcol % 26 - 1]
        else:
            endcolstr = string.uppercase[endcol - 1]
        return(startcolstr + startrowstr + ":" + endcolstr + endrowstr)
    
    def main():
        excel = win32.gencache.EnsureDispatch("Excel.Application")
        excel.Visible = 1
        try: # try to link to the file
            ansi("red", False)
            print "Linking to", options.file
            wb = excel.Workbooks(options.file)
            ws = wb.Worksheets("MongoData")
            ansi()
        except: # not open then try to load it
            try:
                ansi("red", False)
                print "Not open.... trying to open in current directory", os.getcwd()
                ansi()
                wb = excel.Workbooks.Open(os.getcwd() + "\\" + options.file)
                ws = wb.Worksheets("MongoData")
                ansi()
            except: # can't load then ask to create it
                ansi("red", True)
                print options.file, "not found here. Create? (y/n) ",
                ansi("yellow", True)
                response = msvcrt.getch()
                print response
                ansi()
                if response.upper() == "Y":
                    wb = excel.Workbooks.Add()
                    ws = excel.Worksheets.Add()
                    ws.Name = "MongoData"
                    wb.SaveAs(os.getcwd() + "\\" + options.file)
                else: # don't wanna create it then exit
                    print "bye."
                    return
        # see if ticks sheet works otherwise add it
        try:
            wst = wb.Worksheets("MongoTicks")
        except:
            wst = excel.Worksheets.Add()
            wst.Name = "MongoTicks"
            wst.Cells(3, 2).Value = 1
        # see if securities list sheet works otherwise add it
        try:
            wall = wb.Worksheets("AllSecurities")
            wall.Cells(1, 1).Value = "List of all securities"
            wall.Range("A1:A1").Interior.ColorIndex = 8
            wall.Range("A:A").ColumnWidth = 22
        except:
            wall = excel.Worksheets.Add()
            wall.Name = "AllSecurities"
            wall.Cells(1, 1).Value = "List of all securities"
            wall.Range("A1:A1").Interior.ColorIndex = 8
            wall.Range("A:A").ColumnWidth = 22
    
        ansi("green", True)
        print "talking to", options.file, 
        ansi("green", False)
        print "... press any key when this console has the focus, to end communication"
        ansi()
        def linkstatusupdate():
            global linkstatusperiod
            if linkstatusperiod:
                ws.Cells(1, 1).Value = "Talking to Python|"
                wst.Cells(1, 1).Value = "Talking to Python!"
                linkstatusperiod = False
            else:
                ws.Cells(1, 1).Value = "Talking to Python|"
                wst.Cells(1, 1).Value = "Talking to Python!"
                linkstatusperiod = True
            ws.Cells(1, 2).Value = datetime.now()
        # daily worksheet header formatting
        ws.Cells(1, 1).Value = "Excel linked to Python"
        ws.Cells(1, 3).Value = "Sort direction:"
        ws.Cells(1, 4).Value = 1
        ws.Cells(1, 5).Value = "Fetch max:"
        ws.Cells(2, 1).Value = "Enter tickers:"
        ws.Cells(3, 1).Value = "Start data:"
        ws.Cells(4, 1).Value = "End data:"
        ws.Range("A:A").ColumnWidth = 22
        ws.Range("B:B").ColumnWidth = 20
        ws.Range("A2:GS2").Interior.ColorIndex = 19 # beige 200 columns
        ws.Range("A3:GS4").Interior.ColorIndex = 15 # grey
        ws.Range("A2").Interior.ColorIndex = 3 # red
        ws.Range("A3:A4").Interior.ColorIndex = 16 # dark grey
        # minute worksheet header formatting
        wst.Cells(1, 1).Value = "Excel linked to Python"
        wst.Cells(2, 1).Value = "Enter tickers:"
        #wst.Cells(3, 1).Value = "Enter periodicity:"
        wst.Cells(1, 3).Value = "Sort direction:"
        wst.Cells(1, 4).Value = 1
        wst.Cells(1, 5).Value = "Fetch max:"
        wst.Range("A:A").ColumnWidth = 22
        wst.Range("B:B").ColumnWidth = 20
        wst.Range("A2:GS3").Interior.ColorIndex = 19 # beige 200 columns
        wst.Range("A4:GS5").Interior.ColorIndex = 15 # grey
        wst.Range("A2:A3").Interior.ColorIndex = 4 # red
        wst.Range("6:100000").Clear()
        linkstatusperiod = False
        oldsecd = []
        oldseci = []
        oldnumget = oldsortdir = toldnumget = toldsortdir = 0
        while not msvcrt.kbhit():
            try:
                print "...", wb.Name,
                securities = ws.Range("B2:GS2").Value[0]
                sortdir = ws.Cells(1, 4).Value
                if sortdir == None:
                    sortdir = 1
                sortdir = int(sortdir)
                numget = ws.Cells(1, 6).Value
                if numget == None:
                    numget = 1000000
                numget = int(numget)
                securities = [x for x in securities if x is not None]
                if not ((oldsecd == securities) and (oldnumget == numget) and (oldsortdir == sortdir)): # clear content of cells 
                    ws.Range("5:1000000").Clear()
                    ws.Range("B3:GS4").Clear()
                    ws.Range("B3:GS4").Interior.ColorIndex = 15 # grey
                    oldsecd = securities
                    oldnumget = numget
                    oldsortdir = sortdir
                currentcol = 0
                for sec in securities:
                    linkstatusupdate()
                    secdata = mdaily(sec, "LAST_PRICE", sortdir, numget)
                    currentrow = 0
                    vallist = []
                    datelist = []
                    if sortdir == -1:
                        sortedkeys = sorted(secdata, reverse = True)
                    else: 
                        sortedkeys = sorted(secdata, reverse = False)
                    for eachkey in sortedkeys:
                        datelist.append(eachkey)
                        vallist.append(secdata[eachkey])
                    #now stick them in Excel
                    ws.Range(offsetString(5 + currentrow, 2 + currentcol, 5 + currentrow + len(vallist) - 1, 2 + currentcol)).Value = \
                            tuple([(x, ) for x in vallist])
                    if currentcol == 0:
                        ws.Range(offsetString(5 + currentrow, 1, 5 + currentrow + len(vallist) - 1, 1)).Value = \
                            tuple([(x, ) for x in datelist])
                    if len(sortedkeys) > 0:
                        ws.Cells(3, 2 + currentcol).Value = sortedkeys[len(sortedkeys) - 1].split()[0] # start data date
                        ws.Cells(4, 2 + currentcol).Value = sortedkeys[0].split()[0] # end data date
                    currentcol += 1
                # now do the tick data
                securitiest = wst.Range("B2:GS2").Value[0]
                securitiest = [x for x in securitiest if x is not None]
                tsortdir = wst.Cells(1, 4).Value
                if tsortdir == None:
                    tsortdir = 1
                tsortdir = int(tsortdir)
                tnumget = wst.Cells(1, 6).Value
                if tnumget == None:
                    tnumget = 1000000
                tnumget = int(tnumget)
                if not ((oldseci == securitiest) and (toldnumget == tnumget) and (toldsortdir == tsortdir)): # clear the contents of the cells 
                    wst.Range("6:1000000").Clear()
                    wst.Range("B4:GS5").Clear()
                    wst.Range("B4:GS5").Interior.ColorIndex = 15 # grey
                    oldseci = securitiest
                    toldnumget = tnumget
                    toldsortdir = tsortdir
                secdata = mtick(securitiest, tsortdir, tnumget)
                currentsec = 0
                for x in secdata:
                    sender = [tuple(y[1:5]) for y in x]
                    wst.Range(offsetString(6, 2 + currentsec * 4, 6 + len(x) - 1, 5 + currentsec * 4)).Value = sender
                    if currentsec == 0: # then put the dates in 
                        dates = [tuple([y[0], ]) for y in x]
                        wst.Range(offsetString(6, 1, 6 + len(x) - 1, 1)).Value = dates
                    wst.Range(offsetString(5, 2 + currentsec * 4, 5, 5 + currentsec * 4)).Value = ["open", "high", "low", "close"]
                    currentsec += 1
                for x in range(0, len(securitiest)):
                    wst.Cells(4, 2 + x * 4).Value = securitiest[x]
                linkstatusupdate()
                allsecs = tuple([(yy, ) for yy in getsecs()])
                wall.Range(offsetString(2, 1, len(allsecs) + 1, 1)).Value = allsecs
    
            except:
                print "\nExcel busy",
            time.sleep(1)
    
        endchar = msvcrt.getch() # capture the last character so it doesn't go to console
        print "\nbye."
    
    
    if __name__ == "__main__":
        main()
    
        4
  •  2
  •   Irfan    9 年前

    progress(下面提到)、easysoft和cdata提供了ODBC驱动程序。
    我试过进步,它做得很好。 所有这些驱动程序都是授权软件,也有试用版。

    最容易使用的是 cdata Excel Add-In 可以查询、更新 还允许使用基于excel的公式&VBA。也有执照。

    另一种方法是使用 pymongo 在python而不是mongo客户端中, 将结果转储到csv文件并通过VBA导入csv。 从python查询mongoDB相当简单。
    下面是从MongoDB示例数据集查询的示例。

    查询的Python文件“queryMongoDB.py”

    SERVER = "192.168.43.22" # Replace wit with Server IP or Hostname running mongod
    PORT   = "27017"
    
    def queryMongoDB():
        try:
           from pymongo import MongoClient
           client = MongoClient("mongodb://" + SERVER + ":" + PORT)
           db = client.test
           queryResp = db.restaurants.find({'address.zipcode': "11215", 'cuisine': 'Indian'}, {'name': 1, 'address.building': 1, 'address.street': 1, 'borough': 1, '_id': 0})
    
           if queryResp.count() > 0 :
              for row in queryResp:
                 printStr = ""
                 if 'name' in row:
                    printStr = row['name'] + ","
                 else:
                    printStr = ","
                 if 'building' in str(row):
                    printStr = printStr + row['address']['building'] + ","
                 else:
                    printStr = printStr + ","
                 if 'street' in str(row):
                    printStr = printStr + row['address']['street'] + ","
                 else:
                    printStr = printStr + ","
                 if 'borough' in row:
                    printStr = printStr + row['borough']
                 print(printStr)
           else:
              return -2
           return 0
        except ImportError:
           return -1
    
    queryMongoDB()
    

    执行此脚本将打印为标准输出

    Kinara Indian Restaurant,473,5 Avenue,Brooklyn
    Baluchi'S,310,5 Avenue,Brooklyn
    Kanan Indian Restaurant,452,3Rd Ave,Brooklyn
    New Aarpan,396,5Th Ave,Brooklyn
    Indian Spice,351,7Th Ave,Brooklyn
    

    使用WshShell、macro_querymongdb()的Excel VBA宏

    Sub macro_queryMongoDB()
       Dim pythonExecutable  As String
       Dim pythonQueryScript As String
       pythonExecuatble = "python.exe" ' Path to python interpreter
       pythonQueryScript = "queryMongoDB.py" 'Full path to the above Python script
    
       If Dir(pythonExecuatble) <> "" And Dir(pythonQueryScript) <> "" Then
          Dim objShell         As Object
          Dim objWshScriptExec As Object
          Dim objStdOut        As Object
    
          Set objShell = CreateObject("WScript.Shell")
          Set objWshScriptExec = objShell.Exec(pythonExecuatble & " " & pythonQueryScript) ' Execute the Python script
          Set objStdOut = objWshScriptExec.StdOut
    
          Set mybook = Excel.ActiveWorkbook
          Set mybookSheet = mybook.ActiveSheet
    
          Dim rline            As String
          Dim strline          As String
          Dim lineCount        As Long
    
          ' Parse the results
          lineCount = 1
          While Not objStdOut.AtEndOfStream
             rline = objStdOut.ReadLine
             If rline <> "" Then
                strline = rline & vbCrLf
                mybookSheet.Range(mybookSheet.Cells(lineCount, "A"), mybookSheet.Cells(lineCount, "D")).Value = Split(strline, ",")
                lineCount = lineCount + 1
             End If
          Wend
          MsgBox "Query Successful"
       Else
          MsgBox "Python executable or Python query DB script doesn't exist."
       End If
    End Sub
    

    运行此宏将把逗号分隔的数据填充到行中,如下所示

    enter image description here

        5
  •  1
  •   Jerod Johnson    9 年前

    我可以重复其他的答案,即使用ODBC驱动程序连接到Excel中的MongoDB数据。当然,问题是你没有办法使用宏。

    正如欧文提到的 CData Excel Add-In 会允许你这么做的。(完全公开,我为CData软件工作)。您可以在我们的 Help documentation ,但我在这里提供了一个相关的代码片段来演示将MongoDB数据读入excel的基本功能:

    Sub DoSelect()
      On Error GoTo Error
      p_id = InputBox("_id:", "Get _id")
      If p_id = False Then
        Exit Sub
      End If
      Dim module As New ExcelComModule
      module.SetProviderName ("MongoDB")
      Cursor = Application.Cursor
      Application.Cursor = xlWait
      Dim nameArray
      nameArray = Array("_idparam")
      Dim valueArray
      valueArray = Array(p_id)
      Query = "SELECT City, CompanyName FROM Customers WHERE _id = @_idparam"
      module.SetConnectionString ("Server=127.0.0.1;Port=27017;Database=test;User=test;Password=test;")
      If module.Select(Query, nameArray, valueArray) Then
        Dim ColumnCount As Integer
        ColumnCount = module.GetColumnCount
        For Count = 0 To ColumnCount - 1
          Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
        Next
        Dim RowIndex As Integer
        RowIndex = 2
        While (Not module.EOF)
          For columnIndex = 0 To ColumnCount - 1
            If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
              Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
            Else
              Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
            End If
          Next
          module.MoveNext
          RowIndex = RowIndex + 1
        Wend
        MsgBox "The SELECT query successful."
      Else
        MsgBox "The SELECT query failed."
      End If
      Application.Cursor = Cursor
      Exit Sub
    Error:
      MsgBox "ERROR: " & Err.Description
      Application.Cursor = Cursor
    End Sub
    

    我们的2016版本目前处于测试阶段,因此您可以从今天开始免费使用Excel中的MongoDB数据。

        6
  •  0
  •   Leopd    13 年前

    我认为最好的答案是为MongoDB编写或找到ODBC驱动程序。如果你找到了就告诉我。

    除此之外,您可以为mongodb编写一个web前端,将适当的查询呈现到HTML表中,并使用Excel的功能从web页面解析HTML表。不像ODBC那么干净,但比一次又一次地导出csv要好。

        7
  •  0
  •   Pieter Olivier    11 年前

    你总是可以看看这个解决方案,而不是自己尝试过,它确实需要几个跳跃: http://sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot

        8
  •  0
  •   user3777612    11 年前

    下面是一个可靠的ODBC驱动程序,它还通过将嵌套的mongoDB数据模型作为一组关系表公开给Excel和其他ODBC应用程序来帮助维护mongoDB数据的保真度:

    http://www.progress.com/products/datadirect-connect/odbc-drivers/data-sources/mongodb

        9
  •  0
  •   Anatoly Alekseev richie    7 年前

    他们说附近有第三方Mongodb COM驱动程序: http://na-s.jp/MongoCOM/index.en.html 安装并引用之后,可以运行以下查询

        Dim oConn
        Dim oCursor,o
    
        Set oConn = oMng.NewScopedDBConnection( cHostAndPort )                  
        Set oCursor = oConn.Query( "ec.member", oMng.FJ("{ ""age"": 37 }")
        Do While oCursor.More
            Set o = oCursor.Next
            MsgBox "mname: " & o("mname")
            MsgBox "dept: "  & o("dept")
            MsgBox "age: "   & o("age")     
        Loop
    

    这是为那些认为在每次需要某个数据块时取消MongoDb结构的规范化并将其动态转换为SQL可查询形式是一种过分的做法;-)