Как подключить Mongodb из Excel

Я хочу подключиться к базе данных mongodb, используя макросы excel, знает ли кто-нибудь, как выполнить эту задачу?

Подход Shell

Практически все, что взаимодействует с командной строкой, можно получить с помощью Shell.

Вот пример bare-bones, который соединяется с исполняемым экземпляром MongoDB и печатает запрос в окне Immediate. Вам нужно будет добавить ссылку на Windows Script Host Object Model .

 Private Sub Test() Dim wsh As New WshShell Dim proc As WshExec Dim line As String Set proc = wsh.Exec("mongo") With proc .StdIn.WriteLine "use test" .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})" .StdIn.WriteLine "quit()" Do While .Status = WshRunning line = .StdOut.ReadLine If line = "Type ""it"" for more" Then .StdIn.WriteLine "it" ElseIf line Like "{*" Then Debug.Print line End If DoEvents Loop End With End Sub 

Однако печатать необработанные строки JSON не очень интересно или полезно. Вы можете написать свой собственный парсер JSON, но для этого примера мы будем использовать VBA-JSON от Tim Hall (вы можете найти его на GitHub ).

На момент написания статьи есть одна проблема с VBA-JSON, которую нужно решать при ее использовании для анализа строк, возвращенных из MongoDB. Любые значения, содержащие круглые скобки, например "_id": ObjectId("...") , "_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 = "[az]*\(" ' Left temp = .Replace(str, "") .Pattern = "\)" ' Right temp = .Replace(temp, "") End With CleanString = temp End Function 

Затем мы можем разобрать JSON, возвращенный из MongoDB, и добавить каждый объект в Collection . Доступ к значениям становится довольно простым.

 Private Sub Mongo() Dim wsh As New WshShell Dim proc As WshExec Dim line As String Dim response As New Collection Dim json As Object Set proc = wsh.Exec("mongo") With proc .StdIn.WriteLine "use test" .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})" .StdIn.WriteLine "quit()" Do While .Status = WshRunning line = .StdOut.ReadLine If line = "Type ""it"" for more" Then .StdIn.WriteLine "it" ElseIf line Like "{*" Then response.Add ParseJson(CleanString(line)) End If DoEvents Loop End With For Each json In response Debug.Print json("name"), json("address")("street") Next End Sub 

… Который даст следующий результат из набора данных примера MongoDB.

 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 и WriteLine блокируют функции .
  • Окно, открытое Exec не может быть скрыто .

wsh.Run для обоих вышеизложенных было бы использование двухслойного подхода, где VBA вызывает скрытый скрипт с использованием wsh.Run , который затем запускает Exec (а также любой другой код, который взаимодействует с proc). Недостатком этого подхода является то, что StdIn (и до степени StdOut) должен быть записан в файл.

Простой способ

  1. создайте C # dll для взаимодействия с Mongo db через доступные драйверы c #.
  2. Сделать Com видимым (в Assemblyinfo.cs), построить его и зарегистрировать
  3. Перейти к макросу excel -> визуальный базовый редактор
  4. Нажмите «Инструменты» -> ссылка и выберите свою зарегистрированную сборку
  5. И используйте его в своем VBA, вот так.

,

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

это все..

Моим решением было позволить Python склеить их вместе, используя pymongo и win32com. Тогда довольно просто запустить все, что захотите. В моем случае у меня есть цикл Python, который просто непрерывно «прослушивает» определенные ячейки Excel, вызывается, что нужно от Mongo, а затем возвращает его в Excel. Это гибко, и многое можно сделать таким образом. Вот полная база кода, но вам придется изменить вызовы на Mongodb, чтобы они соответствовали вашей собственной базе данных. Здесь вы также увидите некоторые способы изменить цвета и вещи в ячейках Excel из Python. О, я должен упомянуть, что он набросился на escape-последовательности ansi, поэтому вы можете запустить Python из ansicon или ConEmu .

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

Есть драйверы ODBC, доступные от прогресса (см. Ниже), от easysoft и cdata.
Я пробовал с прогрессом, и он хорошо справляется с этой задачей. Все эти драйверы являются лицензированными программными продуктами и доступна пробная версия.

Самый простой в использовании – это надстройка Excel cdata, которая может запрашивать, обновлять и также использовать формулу на основе excel и VBA. Это тоже лицензировано.

Еще один способ – запросить использование pymongo в python вместо клиента mongo, сбрасывая результаты в файл csv и импортируя csv через VBA. Запрос mongoDB из python довольно прост.
Ниже приведен пример запроса из набора данных MongoDB Example.

Файл Python для запроса «queryMongoDB.py»

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

Выполнение этого скрипта будет выводиться на стандартный вывод в виде

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

Макрос Excel VBA с использованием 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 

Запуск этого макроса будет заполнять данные, разделенные запятыми, в строки как

введите описание изображения здесь

Я могу повторить другие ответы, относящиеся к использованию драйвера ODBC для подключения к данным MongoDB в Excel. Проблема, конечно же, в том, что вы не можете использовать макросы.

Как сказал Ирфан, надстройка CData Excel позволит вам сделать именно это. (Полное раскрытие, я работаю для CData Software). Вы можете прочитать о подключении к 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 

Наша версия 2016 в настоящее время находится в бета-версии, поэтому сегодня вы можете начать работать с данными MongoDB в Excel бесплатно.

Я думаю, лучший ответ – написать или найти драйвер ODBC для MongoDB. Дай мне знать, если найдешь.

Если это запрещено, вы можете написать веб-интерфейс для mongodb, который отображает соответствующий запрос в таблицу HTML, и использовать функцию Excel для анализа HTML-таблиц с веб-страниц. Не так чисто, как ODBC, но лучше, чем экспорт CSV снова и снова.

вы всегда можете взглянуть на это решение, не пробовали его сами, и это требует нескольких прыжков: http://sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot

Вот твердый драйвер ODBC, который также помогает поддерживать верность ваших данных mongoDB, подвергая вложенную модель данных MongoDB как набор реляционных таблиц в Excel и другие приложения ODBC:

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

Они говорят, что есть сторонний драйвер Mongodb COM: http://na-s.jp/MongoCOM/index.en.html После установки и ссылки на него вы можете запускать запросы, такие как

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

Это для тех, кто считает, что де-нормализация структур MongoDb и перевод их в SQL-запрашиваемую форму «на лету» каждый раз, когда вам нужен какой-то фрагмент данных, это перебор 😉

  • загрузить excel sheet в mongo
  • tMongoDBbulkLoad недоступен для загрузки в обмене талантов
  • Сортировка многомерного массива другим массивом и полное отсутствие ключей в том же порядке
  • Как сохранить данные в файле .xls / .xlsx в mongodb из angularjs?
  • Чтение, загрузка, поиск содержимого файла excel с использованием mongodb
  • Ошибка создания геопространственного индекса данных после импорта данных из документа excel
  • Импортируйте CSV-файлы из каталога, чтобы засеять базу данных в Rails
  • преобразование файла excel (.xlsx) в JSON
  • Давайте будем гением компьютера.