问如何从Excel连接Mongodb

EN

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

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

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

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