时间戳

首页 > 解决方案 > 使用 VBA 如何将 API 数据集从 HTTP GET 函数加载到 Excel 中？

excel - 使用 VBA 如何将 API 数据集从 HTTP GET 函数加载到 Excel 中？

问题描述

我正在尝试将 API 调用的 DataSet 输出直接加载到工作表上的单元格区域中。具体来说，我只需要 TASK_ID、TASK_NUMBER、TASK_RESUME 和 TASK_GROUP_NAME。他们在此处为我正在使用的 API 函数提供了一些 .NET 代码示例，但这些代码并未直接转换为 VBA。

我已经成功地解析了 JSON 并将其用于先前的函数，但是对于这个任务，我需要将响应实际输出回 Excel。我可以选择将 HTTP 响应格式化为 XML、RecordSet、DataSet 或 JSON。因为这对我来说也是一个学习机会，所以我想学习并利用 DataSet 来实现这个功能，但如果 XML 更有意义，那么我会相信你的判断，但理想情况下我希望能够使用任何一个。

我已经注释掉了我从 API 示例中复制的相关 .NET 代码，上面的部分是我从 XML 教程中找到的。

Private URL As String
Sub Query_Click()
Dim ws As Worksheet: Set ws = Worksheets("Generator")

URL = Worksheets("API").Range("B7")
Dim strResp As String: strResp = GetHTTP(ByVal URL)
Dim xmlDoc As New MSXML2.DOMDocument
If Not xmlDoc.LoadXML(strResp) Then
    MsgBox "Load Error"
End If

'    Dim dsOutput As DataSet: dsOutput = Deserialize(Of DataSet)(strXML)
'            If dsOutput IsNot Nothing AndAlso dsOutput.Tables.Count > 0 Then
'                If dsOutput.Tables.Contains("dtAPIErrors") Then
'                    Throw New Exception(String.Format("{0}: {1}", _
'                                                      dsOutput.Tables(0).Rows(0).Item("ErrorNumber").ToString, _
'                                                      dsOutput.Tables(0).Rows(0).Item("ErrorDescription").ToString))
'                End If
'            End If

    Set xmlDoc = Nothing

End Sub
Private Function GetHTTP(ByVal URL As String) As String
    On Error Resume Next
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", URL, False
        .Send
        GetHTTP = .ResponseText
    End With

End Function

'Private Function Deserialize(Of T)( ByVal strXML As String) As T
'        Dim objet As T = Nothing
'        Dim objType As Type = GetType(T)
'        Try
'            If Not String.IsNullOrEmpty(strXML) Then
'                Dim objSerializer As New System.Xml.Serialization.XmlSerializer(objType)
'                Dim objText As New System.Text.StringBuilder()
'                Dim objXmlReader As New System.IO.StringReader(strXML)
'                objet = DirectCast(objSerializer.Deserialize(objXmlReader), T)
'                objXmlReader.Close()
'            End If
'            Return objet
'        Catch ex As Exception
'            Throw
'        Finally
'            If objet IsNot Nothing Then objet = Nothing
'        End Try
'End Function

我想从第 9 行开始加载所有内容，将任务 ID 加载到 A 列，将任务 # 加载到 B 列，将任务简历（AKA 任务名称）加载到 C 列，将任务组加载到 D 列。如果我能得到一个仅 1 个 DataSet 记录的工作示例我确信我将能够为其他记录复制它。

<DataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="dsOutput">
<xs:element name="dsOutput" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="dtOutput" msdata:CaseSensitive="False" msdata:Locale="en-US">
<xs:complexType>
<xs:sequence>
<xs:element name="TASK_ID" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_NUMBER" type="xs:decimal" minOccurs="0"/>
<xs:element name="TASK_RESUME" type="xs:string" minOccurs="0"/>
<xs:element name="DATE_TASK_MODIFIED" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="IS_ASSIGNED_OR_REVIEWER" type="xs:int" minOccurs="0"/>
<xs:element name="ASSIGNED" type="xs:string" minOccurs="0"/>
<xs:element name="REVIEWER" type="xs:string" minOccurs="0"/>
<xs:element name="ASSIGNED_ID" type="xs:string" minOccurs="0"/>
<xs:element name="REVIEWER_ID" type="xs:string" minOccurs="0"/>
<xs:element name="PROJECT_ID" type="xs:int" minOccurs="0"/>
<xs:element name="PROJECT_NUMBER" type="xs:string" minOccurs="0"/>
<xs:element name="PROJECT_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="PROJECT_TYPE_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="COMPANY_ID" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_GROUP_ID" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_GROUP_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="TASK_GROUP_MYORDER" type="xs:decimal" minOccurs="0"/>
<xs:element name="TASK_TYPE_ID" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_TYPE_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="TASK_TYPE_MYORDER" type="xs:decimal" minOccurs="0"/>
<xs:element name="TASK_STATUS_ID" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_STATUS_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="TASK_STATUS_MYORDER" type="xs:decimal" minOccurs="0"/>
<xs:element name="TASK_STATUS_COMPLETED" type="xs:unsignedByte" minOccurs="0"/>
<xs:element name="TASK_STATUS_COMPLETED_LOGICAL_SORT" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_PRIORITY_ID" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_PRIORITY_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="TASK_PRIORITY_MYORDER" type="xs:decimal" minOccurs="0"/>
<xs:element name="USER_CREATOR_ID" type="xs:int" minOccurs="0"/>
<xs:element name="USERNAME" type="xs:string" minOccurs="0"/>
<xs:element name="FIRST_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="LAST_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="TASK_DESC_CREATOR" type="xs:string" minOccurs="0"/>
<xs:element name="TASK_DESC_CREATOR_PLAIN" type="xs:int" minOccurs="0"/>
<xs:element name="ESTIMATED_TIME" type="xs:decimal" minOccurs="0"/>
<xs:element name="DATE_EXPECTED_START_TASK" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="DATE_EXPECTED_END_TASK" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="DATE_TASK_CREATED" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="POURCENTAGE_DONE" type="xs:decimal" minOccurs="0"/>
<xs:element name="ACTUAL_HOURS" type="xs:int" minOccurs="0"/>
<xs:element name="ACTUAL_PERCENT_DONE" type="xs:int" minOccurs="0"/>
<xs:element name="DOCUMENT_COUNT" type="xs:decimal" minOccurs="0"/>
<xs:element name="ACTUAL_START_DATE" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="ACTUAL_END_DATE" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="DIVIDE_HOURS" type="xs:boolean" minOccurs="0"/>
<xs:element name="WEEKEND_ALLOWED" type="xs:int" minOccurs="0"/>
<xs:element name="UPDATE_USER_ID" type="xs:int" minOccurs="0"/>
<xs:element name="UPDATE_USERNAME" type="xs:string" minOccurs="0"/>
<xs:element name="UPDATE_FIRST_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="UPDATE_LAST_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="COUNT_SUCCESSORS" type="xs:decimal" minOccurs="0"/>
<xs:element name="COUNT_PREDECESSORS" type="xs:decimal" minOccurs="0"/>
<xs:element name="TASK_RECUR_MODIFIED" type="xs:boolean" minOccurs="0"/>
<xs:element name="TIMESHEET_INOUT_ID" type="xs:int" minOccurs="0"/>
<xs:element name="COMMENT_COUNT" type="xs:decimal" minOccurs="0"/>
<xs:element name="DATE_STATUS_STARTED" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="DATE_STATUS_COMPLETED" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0"/>
<xs:element name="POSITION" type="xs:double" minOccurs="0"/>
<xs:element name="TASK_DEPENDENCY_ID" type="xs:int" minOccurs="0"/>
<xs:element name="MARKED" type="xs:int" minOccurs="0"/>
<xs:element name="READY_TO_START" type="xs:int" minOccurs="0"/>
<xs:element name="TASK_RECURRENCY_ID" type="xs:int" minOccurs="0"/>
<xs:element name="EXPIRATION_DATE" type="xs:int" minOccurs="0"/>
<xs:element name="STANDBY" type="xs:int" minOccurs="0"/>
<xs:element name="WEEKEND_ALLOWED_NAME" type="xs:string" minOccurs="0"/>
<xs:element name="NAV_TASK_POSITION" type="xs:int" minOccurs="0"/>
<xs:element name="PERIODICITY_SUMMARY" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<dsOutput>
<dtOutput diffgr:id="dtOutput1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
<TASK_ID>5</TASK_ID>
<TASK_NUMBER>1.0000000</TASK_NUMBER>
<TASK_RESUME>Wolf</TASK_RESUME>
<DATE_TASK_MODIFIED>2019-03-29T15:56:29.937</DATE_TASK_MODIFIED>
<IS_ASSIGNED_OR_REVIEWER>0</IS_ASSIGNED_OR_REVIEWER>
<PROJECT_ID>2</PROJECT_ID>
<PROJECT_NUMBER>0000002</PROJECT_NUMBER>
<PROJECT_NAME>Canines</PROJECT_NAME>
<PROJECT_TYPE_NAME>General</PROJECT_TYPE_NAME>
<COMPANY_ID>1</COMPANY_ID>
<TASK_GROUP_ID>4</TASK_GROUP_ID>
<TASK_GROUP_NAME>Development</TASK_GROUP_NAME>
<TASK_GROUP_MYORDER>9999999</TASK_GROUP_MYORDER>
<TASK_TYPE_ID>6</TASK_TYPE_ID>
<TASK_TYPE_NAME>Addition</TASK_TYPE_NAME>
<TASK_TYPE_MYORDER>9999999</TASK_TYPE_MYORDER>
<TASK_STATUS_ID>5</TASK_STATUS_ID>
<TASK_STATUS_NAME>Completed</TASK_STATUS_NAME>
<TASK_STATUS_MYORDER>9999999</TASK_STATUS_MYORDER>
<TASK_STATUS_COMPLETED>1</TASK_STATUS_COMPLETED>
<TASK_STATUS_COMPLETED_LOGICAL_SORT>2</TASK_STATUS_COMPLETED_LOGICAL_SORT>
<TASK_PRIORITY_ID>7</TASK_PRIORITY_ID>
<TASK_PRIORITY_NAME>Normal</TASK_PRIORITY_NAME>
<TASK_PRIORITY_MYORDER>3</TASK_PRIORITY_MYORDER>
<USER_CREATOR_ID>1</USER_CREATOR_ID>
<USERNAME>Dracius</USERNAME>
<FIRST_NAME>Ace</FIRST_NAME>
<LAST_NAME>Rimmer</LAST_NAME>
<TASK_DESC_CREATOR/>
<ESTIMATED_TIME>0.00</ESTIMATED_TIME>
<DATE_EXPECTED_START_TASK>2019-03-29T00:00:00</DATE_EXPECTED_START_TASK>
<DATE_EXPECTED_END_TASK>2019-03-29T00:00:00</DATE_EXPECTED_END_TASK>
<DATE_TASK_CREATED>2019-03-29T13:24:31.82</DATE_TASK_CREATED>
<POURCENTAGE_DONE>10</POURCENTAGE_DONE>
<DOCUMENT_COUNT>0</DOCUMENT_COUNT>
<ACTUAL_START_DATE>2019-03-29T00:00:00</ACTUAL_START_DATE>
<ACTUAL_END_DATE>2019-03-29T00:00:00</ACTUAL_END_DATE>
<DIVIDE_HOURS>true</DIVIDE_HOURS>
<WEEKEND_ALLOWED>0</WEEKEND_ALLOWED>
<UPDATE_USER_ID>3</UPDATE_USER_ID>
<UPDATE_USERNAME>Fenrir</UPDATE_USERNAME>
<COUNT_SUCCESSORS>1</COUNT_SUCCESSORS>
<COUNT_PREDECESSORS>0</COUNT_PREDECESSORS>
<TASK_RECUR_MODIFIED>false</TASK_RECUR_MODIFIED>
<COMMENT_COUNT>0</COMMENT_COUNT>
<DATE_STATUS_STARTED>2019-03-29T19:55:24.24</DATE_STATUS_STARTED>
<DATE_STATUS_COMPLETED>2019-03-29T19:55:24.24</DATE_STATUS_COMPLETED>
<POSITION>325056</POSITION>
<MARKED>0</MARKED>
<READY_TO_START>0</READY_TO_START>
<WEEKEND_ALLOWED_NAME>No</WEEKEND_ALLOWED_NAME>
</dtOutput>
<dtOutput diffgr:id="dtOutput2" msdata:rowOrder="1" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput3" msdata:rowOrder="2" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput4" msdata:rowOrder="3" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput5" msdata:rowOrder="4" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput6" msdata:rowOrder="5" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput7" msdata:rowOrder="6" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput8" msdata:rowOrder="7" diffgr:hasChanges="inserted">...</dtOutput>
<dtOutput diffgr:id="dtOutput9" msdata:rowOrder="8" diffgr:hasChanges="inserted">...</dtOutput>
</dsOutput>
</diffgr:diffgram>
</DataSet>

示例错误输出：

<DataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="dsOutput">
<xs:element name="dsOutput" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="dtAPIErrors">
<xs:complexType>
<xs:sequence>
<xs:element name="ErrorNumber" type="xs:string" minOccurs="0"/>
<xs:element name="ErrorDescription" type="xs:string" minOccurs="0"/>
<xs:element name="ErrorType" type="xs:string" minOccurs="0"/>
<xs:element name="Language" type="xs:string" minOccurs="0"/>
<xs:element name="ParamName" type="xs:string" minOccurs="0"/>
<xs:element name="Id" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<dsOutput>
<dtAPIErrors diffgr:id="dtAPIErrors1" msdata:rowOrder="0">
<ErrorNumber>API0011-009</ErrorNumber>
<ErrorDescription>
Task doesn't exist. It might have been deleted by another user.
</ErrorDescription>
<ErrorType>MSG_FUNCTIONAL</ErrorType>
<Language>en-US</Language>
</dtAPIErrors>
</dsOutput>
</diffgr:diffgram>
</DataSet>

标签： excelvbaapiweb-scrapingdataset

解决方案

您可以将其视为 xml 并使用 xpath。在这里，我正在从文件中读取，但您会将响应加载到 xmldocument 中。这确实是为了表明您可以通过 xpath 进行选择。我选择 withSelectSingleNode但您可以看到如何为SelectNodes.

如果每个 xpath 的节点集长度相同，这只会按行对齐。如果不是，则必须使用共享父节点，然后从该节点访问子节点。行计数器将在共享父节点的循环内递增。

如果使用多个请求，则需要对请求进行另一个 For 循环，然后应在该循环的顶部找到下一个空闲行以确定开始写入的位置。

Option Explicit
Public Sub test()
    Dim xmlDoc As Object, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
        .validateOnParse = True
        .setProperty "SelectionLanguage", "XPath"
        .async = False

        If Not .Load("C:\Users\User\Desktop\Test.xml") Then
            Err.Raise .parseError.ErrorCode, , .parseError.reason
        End If
    End With
    Dim node As Object, i As Long, xpath As Variant, j As Long
    j = 1
    For Each xpath In Array("TASK_ID", "TASK_NUMBER", "TASK_RESUME", "TASK_GROUP_NAME")
        ws.Cells(9, j) = xmlDoc.SelectSingleNode("//" & xpath).Text
        j = j + 1
    Next
End Sub

在您的情况下，加载函数返回字符串。编辑：为多个节点更新

Option Explicit
Public Sub test()
    Dim xmlDoc As Object, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
        .validateOnParse = True
        .setProperty "SelectionLanguage", "XPath"
        .async = False

        If Not .LoadXML(GetHTTP(url)) Then
            Err.Raise .parseError.ErrorCode, , .parseError.reason
        End If
    End With
    Dim node As Object, i As Long, xpath As Variant, j As Long, errorNode As Object, k As Long
    Set errorNode = xmlDoc.SelectSingleNode("//ErrorDescription")
    If Not errorNode Is Nothing Then
        MsgBox errorNode.Text
        Exit Sub
    End If
    j = 1
    For Each xpath In Array("TASK_ID", "TASK_NUMBER", "TASK_RESUME", "TASK_GROUP_NAME")
        k = 0
        For Each node In xmlDoc.SelectNodes("//" & xpath)
            ws.Cells(9 + k, j) = node.Text
            k = k + 1
        Next
        j = j + 1
    Next
End Sub

推荐阅读