VBA部分

  1. Function JsonToObject(str)
  2.     Set myJs = CreateObject("MSScriptControl.ScriptControl")
  3.     myJs.Language = "javascript"
  4.     Set myObject = myJs.Eval("eval(" & str & ")")
  5.     Set myJs = Nothing
  6.     Set JsonToObject = myObject
  7. End Function
  9. Sub FillingData()
  10.     Set mySheet = ActiveSheet
  11.     Let myIndex = 1
  12.     Let lieming = Array("c1", "c2", "c3")
  14.     For Each a In lieming
  15.         mySheet.Cells(1, myIndex) = CStr(a)
  16.         myIndex = myIndex + 1
  17.     Next a
  19.     Let myIndex = 1
  20.     Let str1 = "[{'c1':'服务品质','c2temp':[{'c3':'IRR','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']},{'c3':'服务评价','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']}]},{'c1':'保单品质','c2temp':[{'c3':'IRR','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']},{'c3':'服务评价','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']}]}]"
  21.     Set object1 = JsonToObject(str1)
  23.     With mySheet
  24.         For Each a In object1
  26.             For Each b In a.c2temp
  28.                 For Each c In b.c2temp
  29.                     myIndex = myIndex + 1
  30.                     .Cells(myIndex, 1) = a.c1
  31.                     .Cells(myIndex, 2) = CStr(c)
  32.                     .Cells(myIndex, 3) = b.c3
  33.                     ' Debug.Print a.c1&; b.c3&; c
  34.                 Next c
  36.             Next b
  38.         Next a
  39.     End With
  40.     Set object1 = Nothing
  41.     Set mySheet = Nothing
  42. End Sub

JS部分

  1. JSON.stringify(object);