Я хочу подключиться к базе данных mongodb, используя макросы excel, знает ли кто-нибудь, как выполнить эту задачу?
Как подключить Mongodb из Excel
Ответ 1
Подход к раковине
Довольно многое, что связано с командной строкой, можно получить с помощью 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("..."), выдаст ошибку. Быстрое и грязное исправление для этого - использовать RegEx для очистки строки для синтаксического анализатора. Вам нужно будет ссылаться на библиотеку Microsoft VBScript Regular Expressions 5.5 для работы следующей функции.
Private Function CleanString(str As String) As String
Dim temp As String
Dim rx As New RegExp
With rx
.IgnoreCase = True
.Global = True
.Pattern = "[a-z]*\(" ' Left
temp = .Replace(str, "")
.Pattern = "\)" ' Right
temp = .Replace(temp, "")
End With
CleanString = temp
End Function
Затем мы можем разобрать 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 Restaurant Second Avenue
Due Third Avenue
Lenox Hill Grill/Pizza Lexington Avenue
Quatorze Bistro East 79 Street
Luke 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, не может быть скрыто.
Обходным решением для обоих вышеперечисленных было бы использование двухслойного подхода, где VBA вызывает скрытый script с помощью wsh.Run, который затем запускает Exec (а также любой другой код, который взаимодействует с proc). Недостатком этого подхода является то, что StdIn (и до степени StdOut) должен быть записан в файл.
Ответ 2
Простой способ
- создать С# dll для взаимодействия с Mongo db через доступные драйверы С#.
- Сделайте видимым Com > (в Assemblyinfo.cs), создайте его и зарегистрируйте
- Перейти к макросу excel → визуальному базовому редактору
- Выберите инструменты- > ссылка и выберите зарегистрированную сборку
- И используйте его в своем VBA, как это.
.
Private Sub CallMongo()
Dim mongoObj As New MyMongoAssembly
mongoObj.AddItem("adas");
End Sub
thats all..
Ответ 3
Моим решением было позволить 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()
Ответ 4
Есть драйверы ODBC, доступные от прогресса (см. ниже), от easysoft и cdata.
Я пробовал с прогрессом, и он хорошо справляется с этой задачей.
Все эти драйверы являются лицензированными программными продуктами и доступна пробная версия.
Наиболее простым в использовании является cdata Excel Add-In, который может запрашивать, обновлять а также позволяет использовать формулу на основе excel и VBA. Он также лицензирован.
Еще один способ - запросить с помощью pymongo в python вместо клиента mongo,
сбрасывая результаты в файл csv и импортируя csv через VBA.
Запросить mongoDB из python довольно просто.
Ниже приведен пример запроса из набора данных примера MongoDB.
Файл 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()
Выполнение этого script будет выводиться на стандартный вывод в виде
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
Запуск этого макроса будет заполнять данные, разделенные запятыми, в строки как
Ответ 5
Я могу повторить другие ответы, относящиеся к использованию драйвера 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.
Ответ 6
Я думаю, лучший ответ - написать или найти драйвер ODBC для MongoDB. Дайте мне знать, если найдете его.
За исключением этого, вы можете написать веб-интерфейс для mongodb, который отображает соответствующий запрос в таблицу HTML, и использовать функцию Excel для анализа HTML-таблиц с веб-страниц. Не так чисто, как ODBC, но лучше, чем экспорт CSV снова и снова.
Ответ 7
вы всегда можете взглянуть на это решение, не пробовали его самостоятельно, и это требует нескольких прыжков: http://sqlmag.com/blog/integrating-mongodb-and-open-source-data-stores-power-pivot
Ответ 8
Здесь находится надежный драйвер ODBC, который также помогает поддерживать точность данных вашего mongoDB, подвергая вложенную модель данных MongoDB как набор реляционных таблиц в Excel и другие приложения ODBC:
http://www.progress.com/products/datadirect-connect/odbc-drivers/data-sources/mongodb
Ответ 9
Говорят, что сторонний движок 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-запросивную форму "на лету" каждый раз, когда вам нужен какой-то фрагмент данных, является излишним; -)