Импорт JSON в Excel

Можно ли использовать script вызовы JSON в макросе?

Я хочу получить строку JSON через соединение API. Похоже, проблема заключается в том, что Excel ожидает, что параметры будут переданы в HTML-строке, но JSON передает параметры в тело HTML. Любые идеи?

Ответы

Ответ 1

Поскольку это VBA, я бы использовал COM для вызова xmlhttprequest, но использовал его синхронно, чтобы не нарушать однопоточную среду исполнения VBAs. Класс образцов, который иллюстрирует запрос post и get в этом следует:

'BEGIN CLASS syncWebRequest

Private Const REQUEST_COMPLETE = 4

Private m_xmlhttp As Object
Private m_response As String

Private Sub Class_Initialize()
    Set m_xmlhttp = CreateObject("Microsoft.XMLHTTP")
End Sub

Private Sub Class_Terminate()
    Set m_xmlhttp = Nothing
End Sub


Property Get Response() As String
    Response = m_response
End Property

Property Get Status() As Long
    Status = m_xmlhttp.Status
End Property

Public Sub AjaxPost(Url As String, Optional postData As String = "")
    m_xmlhttp.Open "POST", Url, False
    m_xmlhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    m_xmlhttp.setRequestHeader "Content-length", Len(postData)
    m_xmlhttp.setRequestHeader "Connection", "close"
    m_xmlhttp.send (postData)
    If m_xmlhttp.readyState = REQUEST_COMPLETE Then
        m_response = m_xmlhttp.responseText
    End If
End Sub

Public Sub AjaxGet(Url As String)
    m_xmlhttp.Open "GET", Url, False
    m_xmlhttp.setRequestHeader "Connection", "close"
    m_xmlhttp.send
    If m_xmlhttp.readyState = REQUEST_COMPLETE Then
        m_response = m_xmlhttp.responseText
    End If
End Sub

'END CLASS syncWebRequest

Итак, теперь вы можете вызвать вышеуказанное, чтобы вернуть ответ сервера:

Dim request As New syncWebRequest
request.ajaxGet "http://localhost/ClientDB/AllClients?format=json" 
Dim json as string 
json = request.Response

Проблема здесь в том, что мы хотим иметь возможность читать данные, возвращенные с сервера, в некотором роде, более того, чем напрямую манипулировать строкой JSON. Для меня работала VBA-JSON (экспорт кода google здесь). COM-тип Collection - обрабатывать массивы JSON и Dictionary для обработки элементов и их объявлений с помощью метода парсера factory Parse, который в основном упрощает создание этих сборников словарей.

Итак, теперь мы можем разобрать JSON:

[{"Name":"test name","Surname":"test surname","Address":{"Street":"test street","Suburb":"test suburb","City":"test city"}}]

в нечто вроде следующего:

Set clients = parser.parse(request.Response)
For Each client In clients
    name = client("Name")
    surname = client("Surname")
    street = client("Address")("Street")
    suburb = client("Address")("Suburb")
    city = client("Address")("City")
Next

Это приятно, но как насчет того, что вы можете редактировать и публиковать данные? Ну, также существует метод toString для создания строки JSON из данных JSON [Collection/Dictionary], предполагая, что сервер принимает JSON обратно.