blocks|key|1109982|text|我认为最好的答案是为MongoDB编写或找到一个ODBC驱动程序。如果你找到了，请告诉我。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1109983|除此之外，您可以为mongodb编写一个web前端，将适当的查询呈现到HTML表中，并使用Excel的功能从网页中解析HTML表。不像ODBC那样干净，但比一遍又一遍地导出CSV要好。|1109984|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|895236|text|你可以随时看看这个解决方案，我自己还没有试过，它确实需要几次跳跃：http://sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|895237|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot^0|X|2A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

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

blocks|key|895295|text|我自己的解决方案是让Python使用pymongo和win32com将它们粘合在一起。然后就可以很直接地运行你想要的任何东西了。在我的例子中，Python循环只是不断地“监听”某些Excel单元格，从Mongo调用它所需的内容，然后将其放回Excel中。它很灵活，很多事情都可以通过这种方式来完成。下面是完整的代码库，但您必须更改对Mongodb的调用以匹配您自己的数据库。在这里，您还将看到在Python中更改Excel单元格的颜色和内容的某些方法。哦，我应该提一下，它充满了ansi转义序列，所以你可能想要从ansicon或ConEmu运行Python。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|895296|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]+%2B+string.uppercase[startcol+%25+26+-+1]
++++else:
++++++++startcolstr+=+string.uppercase[startcol+-+1]
++++if(endcol+>+26):
++++++++endcolstr+=+string.uppercase[endcol+/+26+-+1]+%2B+string.uppercase[endcol+%25+26+-+1]
++++else:
++++++++endcolstr+=+string.uppercase[endcol+-+1]
++++return(startcolstr+%2B+startrowstr+%2B+":"+%2B+endcolstr+%2B+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()+%2B+"\\"+%2B+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()+%2B+"\\"+%2B+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%7C"
++++++++++++wst.Cells(1,+1).Value+=+"Talking+to+Python!"
++++++++++++linkstatusperiod+=+False
++++++++else:
++++++++++++ws.Cells(1,+1).Value+=+"Talking+to+Python%7C"
++++++++++++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+%2B+currentrow,+2+%2B+currentcol,+5+%2B+currentrow+%2B+len(vallist)+-+1,+2+%2B+currentcol)).Value+=+\
++++++++++++++++++++++++tuple([(x,+)+for+x+in+vallist])
++++++++++++++++if+currentcol+==+0:
++++++++++++++++++++ws.Range(offsetString(5+%2B+currentrow,+1,+5+%2B+currentrow+%2B+len(vallist)+-+1,+1)).Value+=+\
++++++++++++++++++++++++tuple([(x,+)+for+x+in+datelist])
++++++++++++++++if+len(sortedkeys)+>+0:
++++++++++++++++++++ws.Cells(3,+2+%2B+currentcol).Value+=+sortedkeys[len(sortedkeys)+-+1].split()[0]+#+start+data+date
++++++++++++++++++++ws.Cells(4,+2+%2B+currentcol).Value+=+sortedkeys[0].split()[0]+#+end+data+date
++++++++++++++++currentcol+%2B=+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+%2B+currentsec+*+4,+6+%2B+len(x)+-+1,+5+%2B+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+%2B+len(x)+-+1,+1)).Value+=+dates
++++++++++++++++wst.Range(offsetString(5,+2+%2B+currentsec+*+4,+5,+5+%2B+currentsec+*+4)).Value+=+["open",+"high",+"low",+"close"]
++++++++++++++++currentsec+%2B=+1
++++++++++++for+x+in+range(0,+len(securitiest)):
++++++++++++++++wst.Cells(4,+2+%2B+x+*+4).Value+=+securitiest[x]
++++++++++++linkstatusupdate()
++++++++++++allsecs+=+tuple([(yy,+)+for+yy+in+getsecs()])
++++++++++++wall.Range(offsetString(2,+1,+len(allsecs)+%2B+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()|code-block|syntax|javascript|895297|entityMap|0|LINK|mutability|MUTABLE|url|http://adoxa.hostmyway.net/ansicon/|1|http://conemu.codeplex.com/^0|74|7|0|7C|6|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

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 <a href="http://adoxa.hostmyway.net/ansicon/" rel="nofollow">ansicon</a> or <a href="http://conemu.codeplex.com/" rel="nofollow">ConEmu</a>. 

<pre><code>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 &gt; 26):
 startcolstr = string.uppercase[startcol / 26 - 1] + string.uppercase[startcol % 26 - 1]
 else:
 startcolstr = string.uppercase[startcol - 1]
 if(endcol &gt; 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) &gt; 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()
</code></pre>

blocks|key|1110126|text|下面是一个可靠的ODBC驱动程序，它将嵌套的mongoDB数据模型作为一组关系表公开给Excel和其他ODBC应用程序，从而帮助维护MongoDB数据的保真度：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110127|http://www.progress.com/products/datadirect-connect/odbc-drivers/data-sources/mongodb|offset|length|1110128|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2D|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

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:

<a href="http://www.progress.com/products/datadirect-connect/odbc-drivers/data-sources/mongodb" rel="nofollow">http://www.progress.com/products/datadirect-connect/odbc-drivers/data-sources/mongodb</a>

blocks|key|895449|text|可以从progress+(下面提到)、easysoft和cdata获得ODBC驱动程序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|895450|我已经尝试了进度，它做得很好。所有这些驱动程序都是授权软件，也有试用版可用。|895451|最容易使用的是cdata+Excel+Add-In，它可以进行查询、更新，还允许使用基于excel的公式和VBA。它也是有许可证的。|offset|length|895452|另一种方法是在python中使用pymongo而不是mongo客户端进行查询，将结果转储到csv文件并通过VBA导入csv。从python查询mongoDB相当简单。|895453|以下是从MongoDB示例数据集中查询的示例。|895454|查询的Python文件"queryMongoDB.py“|895455|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://"+%2B+SERVER+%2B+":"+%2B+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']+%2B+","
+++++++++++++else:
++++++++++++++++printStr+=+","
+++++++++++++if+'building'+in+str(row):
++++++++++++++++printStr+=+printStr+%2B+row['address']['building']+%2B+","
+++++++++++++else:
++++++++++++++++printStr+=+printStr+%2B+","
+++++++++++++if+'street'+in+str(row):
++++++++++++++++printStr+=+printStr+%2B+row['address']['street']+%2B+","
+++++++++++++else:
++++++++++++++++printStr+=+printStr+%2B+","
+++++++++++++if+'borough'+in+row:
++++++++++++++++printStr+=+printStr+%2B+row['borough']
+++++++++++++print(printStr)
+++++++else:
++++++++++return+-2
+++++++return+0
++++except+ImportError:
+++++++return+-1

queryMongoDB()|code-block|syntax|javascript|895456|执行此脚本将打印到标准输出，格式为|895457|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|895458|使用WshShell的Excel+VBA宏，macro_queryMongoDB()|895459|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+%2B+1
+++++++++End+If
++++++Wend
++++++MsgBox+"Query+Successful"
+++Else
++++++MsgBox+"Python+executable+or+Python+query+DB+script+doesn't+exist."
+++End+If
End+Sub|895460|运行此宏将把逗号分隔的数据填充到行中，如下所示|895461|​|895462|📷|atomic|895463|895464|entityMap|0|LINK|mutability|MUTABLE|url|http://www.cdata.com/drivers/mongodb/download/excel/|1|https://api.mongodb.com/python/current/|2|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/4b694cdde3cd2ed0db6af5e58d3e6fe8.jpeg|imageAlt^0|0|0|7|I|0|0|G|7|1|0|0|0|0|0|0|0|0|0|0|0|1|2|0|0^^$0|@$1|2|3|4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1Q|8|@]|9|@$F|1R|G|1S|1|1T]]|A|$]]|$1|H|3|I|5|6|7|1U|8|@]|9|@$F|1V|G|1W|1|1X]]|A|$]]|$1|J|3|K|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|20|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|21|8|@]|9|@]|A|$]]|$1|U|3|V|5|P|7|22|8|@]|9|@]|A|$Q|R]]|$1|W|3|X|5|6|7|23|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|P|7|24|8|@]|9|@]|A|$Q|R]]|$1|10|3|11|5|6|7|25|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|26|8|@]|9|@]|A|$]]|$1|14|3|15|5|16|7|27|8|@]|9|@$F|28|G|29|1|2A]]|A|$]]|$1|17|3|13|5|6|7|2B|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|2C|8|@]|9|@]|A|$]]]|19|$1A|$5|1B|1C|1D|A|$1E|1F]]|1G|$5|1B|1C|1D|A|$1E|1H]]|1I|$5|1J|1C|1K|A|$1L|1M|1N|-4]]]]

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 <a href="http://www.cdata.com/drivers/mongodb/download/excel/" rel="nofollow noreferrer">cdata Excel Add-In</a> which can query, update
and also allows to use excel based formula &amp; VBA. It's licensed too.

One more way is to query using <a href="https://api.mongodb.com/python/current/" rel="nofollow noreferrer">pymongo</a> 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"

<pre><code>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() &gt; 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()
</code></pre>

Executing this script will print to standard output as

<pre><code>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
</code></pre>

The Excel VBA macro using WshShell, macro_queryMongoDB()

<pre><code>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) &lt;&gt; "" And Dir(pythonQueryScript) &lt;&gt; "" Then
 Dim objShell As Object
 Dim objWshScriptExec As Object
 Dim objStdOut As Object

 Set objShell = CreateObject("WScript.Shell")
 Set objWshScriptExec = objShell.Exec(pythonExecuatble &amp; " " &amp; 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 &lt;&gt; "" Then
 strline = rline &amp; 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
</code></pre>

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

<a href="https://i.stack.imgur.com/h4dmz.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/h4dmz.jpg" alt="enter image description here"></a>

blocks|key|895520|text|关于使用ODBC驱动程序连接到Excel中的MongoDB数据，我可以回答其他问题。当然，问题是你没有办法使用宏。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|895521|正如Irfan提到的，CData+Excel+Add-In将允许您这样做。(完全公开，我在CData软件公司工作)。您可以在我们的Help+documentation中阅读有关使用宏连接到MongoDB的内容，但我在这里包含了相关的代码片段，以演示将MongoDB数据读取到excel中的基本功能：|offset|length|895522|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+%2B+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+%2B+1).Value+=+Conversion.CDate(module.GetValue(columnIndex))
++++++++Else
++++++++++Application.ActiveSheet.Cells(RowIndex,+columnIndex+%2B+1).Value+=+module.GetValue(columnIndex)
++++++++End+If
++++++Next
++++++module.MoveNext
++++++RowIndex+=+RowIndex+%2B+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|code-block|syntax|javascript|895523|我们的2016版本目前处于测试阶段，所以你可以从今天开始在Excel中免费使用MongoDB数据。|895524|entityMap|0|LINK|mutability|MUTABLE|url|http://www.cdata.com/drivers/mongodb/excel|1|http://cdn.cdata.com/help/DGB/xls/pg_lsmacros.htm^0|0|B|I|0|1T|I|1|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]|$D|11|E|12|1|13]]|A|$]]|$1|F|3|G|5|H|7|14|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|15|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]|U|$5|P|Q|R|A|$S|V]]]]

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 <a href="http://www.cdata.com/drivers/mongodb/excel" rel="nofollow">CData Excel Add-In</a> 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 <a href="http://cdn.cdata.com/help/DGB/xls/pg_lsmacros.htm" rel="nofollow">Help documentation</a>, but I've included a relevant code snippet here to demonstrate the basic functionality of reading your MongoDB data into excel:

<pre><code>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: " &amp; Err.Description
 Application.Cursor = Cursor
End Sub
</code></pre>

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

blocks|key|895532|text|他们说有一个第三方Mongodb+COM驱动程序：http://na-s.jp/MongoCOM/index.en.html在安装和引用它之后，你可以运行如下查询|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|895533|++++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|code-block|syntax|javascript|895534|这是为那些认为每次需要某些数据块时将MongoDb结构反规范化并将其动态转换为SQL可查询形式的人准备的;-)|895535|entityMap|0|LINK|mutability|MUTABLE|url|http://na-s.jp/MongoCOM/index.en.html^0|P|11|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

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

<pre><code> 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: " &amp; o("mname")
 MsgBox "dept: " &amp; o("dept")
 MsgBox "age: " &amp; o("age") 
 Loop
</code></pre>

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 want to connect to a mongodb database using excel macros, does anybody knows how to acomplish this task?

How to connect Mongodb from Excel

数据库

MongoDB

Python

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

问如何从Excel连接Mongodb
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从Excel连接MongodbEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从Excel连接Mongodb
EN