I want to connect to a mongodb database using excel macros, does anybody knows how to acomplish this task开发者_如何学编程?

The Shell Approach

Pretty much anything that interfaces with the Command Line can be accessed with 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.

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).

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.5 library for the following function to work.

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.

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.

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

• ReadLine and WriteLine are blocking functions.
• The window opened by Exec can't be hidden.

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.

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.

.

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

thats all..

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.

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

One more way is to query using pymongo in 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.

The Python file for the query, "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()

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

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 ansicon or 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()

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.

As Irfan mentioned the CData Excel Add-In will 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:

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.

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

    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 ;-)

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

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.

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

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:

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