如何在excel宏或VB.Net中将JSON数据转换为xml数据 [英] How to convert JSON data to xml data in excel macro or VB.Net

问题描述

我正在获取JSON格式的数据

I am getting data in JSON format

{"name":"ryan"，"age":1，"roll":2，"address":"aaa"}，{"name":"ryan"，"age":1，"roll :2，" address:" aaa}，{" name:" ryan，" age:1，" roll:2，" address:" aaa}，{" name:" ryan ，"年龄:1，"滚动:2，"地址:" aaa}]

{"name":"ryan","age":1,"roll":2,"address":"aaa"},{"name":"ryan","age":1,"roll":2,"address":"aaa"},{"name":"ryan","age":1,"roll":2,"address":"aaa"},{"name":"ryan","age":1,"roll":2,"address":"aaa"}]

如何将其转换为xml格式.这样我就可以在填充我的Excel电子表格中使用它. 还是有任何方法可以直接将JSON数据直接转换为Excel宏

How can it be converted into xml format. So that i can use it in populating my excel spreadsheet. Or is there any way to directly convert JSON data to excel in macro

推荐答案

或者有什么方法可以将JSON数据直接转换为宏的excel?

Or is there any way to directly convert JSON data to excel in macro?

有时JSON文本非常复杂，在这种情况下，最好使用JSON解析库获取数据.

Sometimes JSON text is very complex and in those cases it is best to use a JSON parsing library to get at the data.

但是，有时数据仅表示2D数据表，而JSON文本似乎就是这种情况.

However, sometimes the data simply represent a 2D table of data and this appears to be the case with your JSON text.

在后一种情况下，可以通过简单的VBA处理将数据写入工作表.

In the latter case, the data can be written to a worksheet by way of simple VBA processing.

在标准代码模块中放置以下例程:

Place the following routines in a standard code module:

Public Sub JsonTable2Range(rOut As Range, json As String)
    Dim i&, j&, p1&, p2&, sRow$, cols, v, vp
    i = 1
    p1 = 1
    Do
        p1 = InStr(p1, json, "{"): If p1 = 0 Then Exit Do
        p2 = InStr(p1, json, "}")
        sRow = Mid$(json, p1 + 1, p2 - p1 - 1)
        cols = Split(sRow, ",")
        If i = 1 Then
            ReDim v(0 To UBound(Split(json, "}")) + 1, 0 To UBound(cols) + 1)
            For j = 0 To UBound(cols)
                vp = Split(cols(j), ":")
                v(0, j) = ProcessValuePair(vp, 0)
            Next
        End If
        For j = 0 To UBound(cols)
            vp = Split(cols(j), ":")
            v(i, j) = ProcessValuePair(vp, 1)
        Next
        i = i + 1
        p1 = p1 + 1
    DoEvents
    Loop
    If i > 1 Then rOut.Resize(UBound(v), UBound(v, 2)) = v
End Sub

Private Function ProcessValuePair(vp, n)
    If Asc(Mid$(vp(n), 1, 1)) = 10 Then vp(n) = Mid$(vp(n), 2)
    vp(n) = Trim$(vp(n))
    If Left$(vp(n), 1) = "'" Or Left$(vp(n), 1) = """" Then
        vp(n) = Mid$(vp(n), 2, Len(vp(n)) - 2)
    Else
        vp(n) = Val(vp(n))
    End If
    ProcessValuePair = vp(n)
End Function

这里是上面的用法...

Here is how to use the above...

如果您已经在VBA变量中添加了JSON文本(也许该变量称为sJSON)，则要将sJSON解析为从单元格A1开始的活动工作表，请执行以下操作:

If you have the JSON text already in a VBA variable (perhaps that variable is called sJSON) then to parse sJSON to the active worksheet beginning in cell A1, do this:

JsonTable2Range [a1], sJSON

另一方面，如果您在A1单元格中具有JSON文本， Sheet2，并且您希望解析输出到Sheet1上的单元格A1，请执行以下操作:

On the other hand, if you have the JSON text in cell A1 on Sheet2 and you'd like for the parsing to output to cell A1 on Sheet1, do this:

JsonTable2Range [sheet1!a1], [sheet2!a1]

这篇关于如何在excel宏或VB.Net中将JSON数据转换为xml数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！