如何从 Excel 连接 Mongodb

How to connect Mongodb from Excel

提问by Boris Barroso

I want to connect to a mongodb database using excel macros, does anybody knows how to acomplish this task?

我想使用 excel 宏连接到 mongodb 数据库，有人知道如何完成这项任务吗？

回答by natancodes

The Shell Approach

壳牌方法

Pretty much anything that interfaces with the Command Line can be accessed with Shell.

几乎所有与命令行接口的东西都可以通过 Shell 访问。

Here's a bare-bones example that connects to a running MongoDB instance and prints a query to the Immediate Window. You'll need to add a reference to the Windows Script Host Object Model.

这是一个连接到正在运行的 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

Just printing the raw JSON strings isn't very exciting or useful, however. You could write your own JSON parser but, for this example, we will use VBA-JSON by Tim Hall (you can find it on GitHub).

然而，仅仅打印原始 JSON 字符串并不是很令人兴奋或有用。您可以编写自己的 JSON 解析器，但在本示例中，我们将使用 Tim Hall 的 VBA-JSON（您可以在 GitHub 上找到它）。

At the time of writing, there is one issue with VBA-JSON that has to be tackled when using it to parse strings returned from MongoDB. Any values that contain parentheses, e.g. "_id": ObjectId("..."), will throw an error. A quick and dirty fix for this is to use RegEx to clean the string for the parser. You will need to reference the Microsoft VBScript Regular Expressions 5.5library for the following function to work.

在撰写本文时，在使用 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

We can then parse the JSON returned from MongoDB and add each object to a Collection. Accessing the values becomes quite simple.

然后我们可以解析从 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

... Which will produce the following output from the MongoDB Example Dataset.

...这将从 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

---

---

Gotchas

陷阱

• ReadLineand WriteLineare blocking functions.
• The window opened by Execcan't be hidden.

• ReadLine并且WriteLine是阻塞函数。
• Exec无法隐藏打开的窗口。

A workaround for both of the above would be to use a two-layer approach, where VBA calls a hidden script using wsh.Run, which then runs the Exec(as well as any other code that interacts with the proc). The downside to this approach is that StdIn (and to an extent StdOut) has to be written to a file.

上述两种情况的解决方法是使用两层方法，其中 VBA 使用 调用隐藏脚本wsh.Run，然后运行Exec（以及与 proc 交互的任何其他代码）。这种方法的缺点是必须将 StdIn（以及一定程度上 StdOut）写入文件。

回答by RameshVel

Simple way is

简单的方法是

1. create a C# dll to interact with Mongo db through available c# drivers.
2. Make it Com visible(in Assemblyinfo.cs), build it and register it
3. Go to the excel macro - > visual basic editor
4. Click tools->reference, and select your registered assembly
5. And use it in your VBA, like this.

1. 创建一个 C# dll 以通过可用的 c# 驱动程序与 Mongo db 交互。
2. 使其Com 可见（在 Assemblyinfo.cs 中），构建它并注册它
3. 转到excel宏->visual basic editor
4. 单击工具->引用，然后选择您注册的程序集
5. 并在您的 VBA 中使用它，就像这样。

.

.

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

thats all..

就这样..

回答by Irfan

There are ODBC drivers available from progress (mentioned below), from easysoft and cdata.
I have tried with progress and it does the job well. All these drivers are licensed softwares and trial version is available too.

有 ODBC 驱动程序可以从 progress（下面提到）、easysoft 和 cdata 中获得。
我已经尝试过取得进展，它做得很好。所有这些驱动程序都是许可软件，也提供试用版。

The most easy to use is the cdata Excel Add-Inwhich can query, update and also allows to use excel based formula & VBA. It's licensed too.

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

One more way is to query using pymongoin python instead of mongo client, dumping the results to a csv file and Importing the csv through VBA. Querying mongoDB from python is fairly easy.
Here is the example to query from MongoDB Example Dataset.

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

The Python file for the query, "queryMongoDB.py"

查询的 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()

Executing this script will print to standard output as

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

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

The Excel VBA macro using WshShell, macro_queryMongoDB()

使用 WshShell、macro_queryMongoDB() 的 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

Running this macro will populate the comma separated data into rows as

运行此宏会将逗号分隔的数据填充到行中

回答by Thomas Browne

My own solution was to let Python glue them together using pymongo and win32com. It's then fairly straightforward to run whatever you want. In my case I have the Python loop simply continuously "listen" to certain Excel cells, call what it needs from Mongo, then put it back into Excel. It's flexible and many things can be done this way. Here is the full code base, but you will have to change the calls to Mongodb to match your own database. In here you will also see certain ways you can change colours and things of Excel cells from within Python. Oh I should mention that it's peppered with ansi escape sequences so you might want to run Python from ansiconor ConEmu.

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

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:       "3[1m",
            False:      "3[0m"}
    colo = {"black":    "3[30m", 
            "red":      "3[31m",
            "green":    "3[32m",
            "yellow":   "3[33m",
            "blue":     "3[34m",
            "magenta":  "3[35m",
            "cyan":     "3[36m",
            "white":    "3[37m"}
    bakk = {"black":    "3[40m", 
            "red":      "3[41m",
            "green":    "3[42m",
            "yellow":   "3[43m",
            "blue":     "3[44m",
            "magenta":  "3[45m",
            "cyan":     "3[46m",
            "white":    "3[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()

回答by Jerod Johnson

I can echo the other answers that refer to using an ODBC driver to connect to MongoDB data in Excel. The problem, of course, there is that there's no way for you to use macros.

我可以回应其他关于使用 ODBC 驱动程序连接到 Excel 中的 MongoDB 数据的答案。当然，问题在于您无法使用宏。

As Irfan mentioned the CData Excel Add-Inwill allow you to do just that. (Full disclosure, I work for CData Software). You can read about connecting to MongoDB using macros in our Help documentation, but I've included a relevant code snippet here to demonstrate the basic functionality of reading your MongoDB data into excel:

正如 Irfan 提到的，CData Excel 插件将允许您做到这一点。（完全披露，我为 CData 软件工作）。您可以在我们的帮助文档中阅读有关使用宏连接到 MongoDB 的信息，但我在此处包含了一个相关的代码片段来演示将 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

Our 2016 Version is currently in Beta, so you can start working with your MongoDB data in Excel for free today.

我们的 2016 版本目前处于测试阶段，因此您可以立即开始在 Excel 中免费使用您的 MongoDB 数据。

回答by Anatoly Alekseev

They say there is a 3rd party Mongodb COM driver around: http://na-s.jp/MongoCOM/index.en.htmlAfter installing and referencing it, you can run queries like

他们说周围有一个 3rd 方 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

This is for those who thinks that de-normalizing of MongoDb structures and translating them into SQL-queryable form on the fly each time you need some chunk of data is an overkill ;-)

这适用于那些认为每次需要一些数据时对 MongoDb 结构进行反规范化并将它们转换为 SQL 可查询形式是一种矫枉过正的人;-)

回答by Leopd

I think the best answer would be to write or find an ODBC driver for MongoDB. Let me know if you find one.

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

Barring that, you could write a web frontend for mongodb that renders the appropriate query into an HTML table, and use Excel's feature to parse HTML tables from web pages. Not as clean as ODBC, but better than exporting CSVs over and over again.

除此之外，您可以为 mongodb 编写一个 Web 前端，将适当的查询呈现到 HTML 表中，并使用 Excel 的功能从网页中解析 HTML 表。不像 ODBC 那样干净，但比一遍又一遍地导出 CSV 好。

回答by Pieter Olivier

you can always look at this solution, haven't tried it myself and it does require a couple of jumps: http://sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot

您可以随时查看此解决方案，自己还没有尝试过，它确实需要几次跳转：http: //sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot

回答by user3777612

Here's a solid ODBC driver that also helps maintain the fidelity of your mongoDB data by exposing the nested MongoDB data model as a set of relational tables to Excel and other ODBC apps:

这是一个可靠的 ODBC 驱动程序，它还通过将嵌套的 MongoDB 数据模型作为一组关系表暴露给 Excel 和其他 ODBC 应用程序来帮助维护 mongoDB 数据的保真度：

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

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