JSON 轉化 到 excel

sk7984

如何 解開內容， 把資料放在 cell 內。
試了很久也沒成功， 因有2 層的array, ， 不知怎樣提取 object.

sk7984

請看附件.

{"Result":[{"StatusList":[{"Status":"已收寄","DealTime":"2018-08-20  10:15:55","Location":"香港"},{"Status":"已处理","DealTime":"2018-08-20  10:41:17","Location":"香港"}],"ErrorMessage":"","OrderID":"UX316736811CH","OrderId":"UX316736811CH"},{"StatusList":[{"Status":"已收寄","DealTime":"2018-08-21  16:06:42","Location":"香港"},{"Status":"已处理","DealTime":"2018-08-21  16:10:00","Location":"香港"}],"ErrorMessage":"","OrderID":"UX316779825CH  ","OrderId":"UX316779825CH  "}],"Status":"success","ErrorMessage":""}

解讀後結果:

Status1	DealTime1	Location1	Status2	DealTime2	Location2	ErrorMessage	OrderID1	OrderId2
已收寄	2018-08-20 10:15:55	香港	已处理	2018-08-20 10:41:17	香港		UX316736811CH	UX316736811CH
已收寄	2018-08-21 16:06:42	香港	已处理	2018-08-21 16:10:00	香港		UX316779825CH	UX316779825CH

案例2
Response:

{        "site":  "{5BEC7C29-FF95-4ECC-9314-064B52618EEE}",        "from":  "2017-01-16",        "to":  "2017-01-22",        "timeSheet": [           {              "date": "2017-01-16",              "person":  "{E2A5FDE1-33F8-43CA-A01D-5DD4A3A5E23A}",              "personName":  "James Smith",              "company":  "{B03CF7B3-0BE9-44B4-8E55-47782FDD87C0}",              "companyName":  "Acme Company Ltd",              "minutes":  "510",              "activities":  [                 {                    "name":  "Training",                    "code":  "TR",                    "minutes":  "240"                 },                 {                    "name":  "Administration",                    "code":  "AD",                    "minutes":  "150"                 },                 {                    "name":  "Payroll",                    "code":  "PR",                    "minutes":  "60"                 },                 {                    "name":  "Meal break",                    "code":  "",                    "minutes":  "60"                 }              ]           }        ]     }

解讀後結果:

date

person

personName

company

companyName

minutes

activities/0/name

activities/0/code

activities/0/minutes

activities/1/name

activities/1/code

activities/1/minutes

activities/2/name

activities/2/code

activities/2/minutes

activities/3/name

activities/3/code

activities/3/minutes

2017-01-16

{E2A5FDE1-33F8-43CA-A01D-5DD4A3A5E23A}

James Smith

{B03CF7B3-0BE9-44B4-8E55-47782FDD87C0}

Acme Company Ltd

510

Training

TR

240

Administration

AD

150

Payroll

PR

60

Meal break

60

sk7984

網站 responese 如下。

sk7984

網站 responese 如下。        {"Result":[{"StatusList":[{"Status":"Loaded","DealTime":"2018-08-20 10:15:55","Location":"CN"},{"Status":"Delivered","DealTime":"2018-08-20 10:41:17","Location":"US"}],"ErrorMessage":"","OrderID":"UF11245782CZ","OrderId":"UF11245782CZ"}],"Status":"success","ErrorMessage":""}

我是来讨说法的

用正则也可以

hehex

sk7984 发表于 2018-8-22 11:52
網站 responese 如下。        {"Result":[{"StatusList":[{"Status":"Loaded","DealTime":"2018-08-20 10: ...

你想要什么结果？
Excel cells 里面最终是什么样子的？
就这一句，你模拟个样子不难吧？

xyxcc177

Sub test()
Dim s, k, j  As Integer
Dim filename As String
Dim txt, str As String
Dim drr(0 To 10, 0 To 2)
filename = "d:\ddd.txt"

Open filename For Input As #1
   txt = ""
   Do While Not EOF(1)
    Line Input #1, str
    txt = txt & str
   Loop
   Close #1
   txt = Replace(txt, Chr(34), "")
   
Dim arr, brr
brr = Array("Status:", "DealTime:", "Location:")
For j = 0 To 2
  arr = Split(txt, brr(j))
   s = UBound(arr)
    For k = 1 To s
      drr(k - 1, j) = getText(Split(arr(k), ",")(0))
    Next
  Next
   
Sheet3.Range("a1").Resize(10, 3).Value = drr

End Sub

Function getText(ByVal str As String) As String
str = Replace(str, "}", "")
str = Replace(str, "]", "")
getText = str
End Function

mxf21cn

Sub test1()
        k9 = "{'Result':[{'StatusList':[{'Status':'Loaded','DealTime':'2018-08-20 10:15:55','Location':'CN'},{'Status':'Delivered','DealTime':'2018-08-20 10:41:17','Location':'US'}],'ErrorMessage':'','OrderID':'UF11245782CZ','OrderId':'UF11245782CZ'}],'Status':'success','ErrorMessage':''}"
        strJOSN = k9
        Set oDom = CreateObject("htmlfile")
        Set oWindow = oDom.parentWindow
        oWindow.execScript "a=" & strJOSN

        MsgBox oWindow.eval("a.Status")
        MsgBox oWindow.eval("a.Result[0].OrderID")
        MsgBox oWindow.eval("a.Result[0].StatusList[0].DealTime")
End Sub
估计你想要的是这个

duquancai

sk7984 发表于 2018-8-22 18:56
請看附件.

1. Sub main()
   
2.     Dim js As Object, s$
   
3.     Set js = CreateObject("MSScriptControl.ScriptControl")
   
4.     js.Language = "JavaScript": js.AddObject "g", Range("a6")
   
5.     s = [a1].Value
   
6.     j = "i=0;a=" & s & ".timeSheet;for(k in a[0]){i++;if(k!='activities')g(1,i)=a[0][k];else{b=a[0][k];for(j=0;j<b.length;j++)for(k in b[j])g(1,i++)=b[j][k];}};"
   
7.     js.eval (j)
   
8. End Sub

复制代码

sk7984

mxf21cn 发表于 2018-8-22 18:16
Sub test1()
        k9 = "{'Result':[{'StatusList':[{'Status':'Loaded','DealTime':'2018-08-20 10:15 ...

error 438 "object doesn't support this property

謝謝你. 但打開不了, 運行時出現  error 438 "object doesn't support this property or ...