VBA反序列化Json并填充

VBA部分

Function JsonToObject(str) Set myJs = CreateObject("MSScriptControl.ScriptControl") myJs.Language = "javascript" Set myObject = myJs.Eval("eval(" & str & ")") Set myJs = Nothing Set JsonToObject = myObject End Function Sub FillingData() Set mySheet = ActiveSheet Let myIndex = 1 Let lieming = Array("c1", "c2", "c3") For Each a In lieming mySheet.Cells(1, myIndex) = CStr(a) myIndex = myIndex + 1 Next a Let myIndex = 1 Let str1 = "[{'c1':'服务品质','c2temp':[{'c3':'IRR','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']},{'c3':'服务评价','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']}]},{'c1':'保单品质','c2temp':[{'c3':'IRR','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']},{'c3':'服务评价','c2temp':['投诉处理平均时长','保全变更完成率','保全处理平均时长','理赔服务时效']}]}]" Set object1 = JsonToObject(str1) With mySheet For Each a In object1 For Each b In a.c2temp For Each c In b.c2temp myIndex = myIndex + 1 .Cells(myIndex, 1) = a.c1 .Cells(myIndex, 2) = CStr(c) .Cells(myIndex, 3) = b.c3 ' Debug.Print a.c1&; b.c3&; c Next c Next b Next a End With Set object1 = Nothing Set mySheet = Nothing End Sub

JS部分

 JSON.stringify(object);