将Excel数据转换为JSON，然后将其发送到API

Question

我一直在使用下面的代码，它将数据转换为Json格式，然后将post/ API端点。

但它不起作用，没有错误发生。但是数据没有发送到API。你的帮助将不胜感激。

我真的不知道错误发生在哪里，它很好地将数据转换为json，但是为什么它不发布到API。当看到时API响应是空的。

Option Explicit

Sub ConvertAndSend()
    Dim apiJSON As String
    apiJSON = ConvertJSON
    
    Dim apiResponse As String
    apiResponse = httpPost("put in api endpoint url", apiJSON)
End Sub

Function ConvertJSON() As String
    
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(1)
            
    Dim lcolumn As Long
    lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim lrow As Long
    lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim titles() As String
    ReDim titles(lcolumn)
    Dim i As Long
    For i = 1 To lcolumn
        titles(i) = wks.Cells(1, i)
    Next i
    
    Dim json As String
    json = "["
    
    Dim dq As String
    
    dq = """"
    
    Dim j As Long
    For j = 2 To lrow
        For i = 1 To lcolumn
            If i = 1 Then
                json = json & "{"
            End If
            
            Dim cellvalue As Variant 'or declare as String
            cellvalue = wks.Cells(j, i)
            json = json & dq & titles(i) & dq & ":" & dq & cellvalue & dq
            If i <> lcolumn Then
                json = json & ","
            End If
        Next i
        json = json & "}"
        If j <> lrow Then
            json = json & ","
        End If
    Next j
    ConvertJSON = json & "]"
End Function

Function httpPost(url As String, msg As String) As String
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", url, False
        .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"    'Don't think it's necessary
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded"   'Consult API documentation on the required Content-Type
        '.setRequestHeader "secret-pass-key", "your-key"    <--if needed
        .send msg
        httpPost = .responseText
    End With
End Function

Answer 1

根据JSON示例，API一次只接受1行数据，因此您必须构建一个JSON字符串并一次一行地发送给API。

由于示例JSON在开头和结尾都没有一个it.

• sku，括号，所以需要删除uniqueID，而epid值使用的是一个不与" "括起来的数值，因此您也需要删除它们。

我修改了你的代码(还是我的？)以生成所需的格式。运行ConvertAndSend现在将为一行构建一个JSON字符串，然后将其发送到循环中的API。

Option Explicit

Private wks As Worksheet
Private lcolumn As Long
Private titles() As String

Private Sub ConvertAndSend()
    Set wks = ThisWorkbook.Sheets(1)
    lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
    
    GetKeys
    
    Dim lrow As Long
    lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    Dim apiJSON As String
    Dim apiResponse As String
    For i = 2 To lrow
        apiJSON = ConvertJSON(i)
        apiResponse = httpPost("API Endpoint URL", apiJSON)
        Debug.Print apiResponse
    Next i
End Sub

Private Sub GetKeys()
    ReDim titles(lcolumn) As String
    Dim i As Long
    For i = 1 To lcolumn
        titles(i) = wks.Cells(1, i)
    Next i
End Sub

Function ConvertJSON(argRow As Long) As String
    Dim dq As String
    dq = Chr(34)

    Dim json As String
    json = "{"
        
    Dim j As Long
    For j = 1 To lcolumn
    
        Select Case titles(j)
            Case "sku", "uniqueID", "epid"
                json = json & dq & titles(j) & dq & ":" & wks.Cells(argRow, j).Value2
            Case Else
                json = json & dq & titles(j) & dq & ":" & dq & wks.Cells(argRow, j).Value2 & dq
        End Select
        
        If j <> lcolumn Then json = json & ","
    Next j
    
    ConvertJSON = json & "}"
End Function

Function httpPost(url As String, msg As String) As String
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", url, False
        .setRequestHeader "Content-type", "application/json"
        .send msg
        httpPost = .responseText
    End With
End Function

如果代码看起来很混乱，因为在我的区域已经很晚了，如果API响应仍然是相同的(验证错误)，那么我确实很抱歉，那么我相信您给它的值是不可接受的，我们没有办法帮助您。