excel 树结构json_JSONToEXCEL

'获取的JSON输出到Excel单元格

Function JSONToRng(data As String)

Dim strJSON As String

'data = [a1]

'Debug.Print strJSON

Dim strJSCode As String

b = InStr(1, data, ":")

e = InStr(1, data, "}")

strJSON = Mid(data, b + 1, e - b)

strJSCode = "var json,key,col=row=1,d={};for(json in js){row++;for(key in js[json]){if(!d[key]){d[key]=col++;rng(1,d[key])=key;}rng(row,d[key])=js[json][key];}}"

strJSCode = "js=[" & strJSON & "];" & strJSCode

' Debug.Print strJSCode

With CreateObject("ScriptControl")

.Language = "JScript"

.AddObject "rng", Cells(3, 2)

.Eval (strJSCode)

End With

End Function定位字符所在位置

b = InStr(1, data, ":")    ‘从data字符串的第一个字符开始扫描，返回“:”所在下标

e = InStr(1, data, "}")

strJSON = Mid(data, b + 1, e - b)    ’截取data字符串下标为b+1至e-b的内容在VBA中使用JS语句

With CreateObject("ScriptControl")