塑封机维修,vba解析json数据(6)-爱游戏平台

苗坤旺离型膜

json数据结构中array和object可以相互嵌套,如下图所示,body是一个array,其中的元素为object([0], [1], [2] … ),对象[0]中即包含普通的键值对c:"商品",也包含object p(其中包含5个键值对)。现在需要提取方框标注部分的键值。使用vba按照字符串逐个去解析肯定也可以实现,但是会比较复杂,json来自于javascript,那么使用javascript肯定是最简洁的爱游戏平台的解决方案。 假设json数据保存在m1单元格中,示例代码如下。

sub jsdemo_1()

dim objjason as object

dim objsc as object

dim objitem as object

dim irow as integer

range("a:d").clear

[a1:d1] = array("c", "p.x", "p.y", "p.z")

set objsc = createobject("scriptcontrol")

objsc.language = "jscript"

set objjason = objsc.eval("s=" & replace([m1], """c""", """cc"""))

irow = 2

for each objitem in objjason.body

with objitem

cells(irow, "a") = .cc

cells(irow, "b") = .p.x

cells(irow, "c") = .p.y

cells(irow, "d") = .p.z

irow = irow 1

end with

next objitem

set objsc = nothing

set objjason = nothing

set objitem = nothing

end sub

代码解析 第6行代码清空工作表中的前四列。 第7行代码写入标题行。 第8行代码创建scriptcontrol对象。 第9行代码指定语言为javascript。 第10行代码使用eval函数加载json字符串,返回结果为jscripttypeinfo对象。此代码中使用了replace函数将json数据中的"c"替换为"cc",其原因在于vba编辑器的代码自动格式化功能会将 cells(irow, "a") = .c自动修改为cells(irow, "a") = .c,由于数据中并不存在节点c,这将导致运行时错误,替换为cc后可以解决这个问题。 第12行到第20行代码使用循环结构遍历body节点。 第14行到第17行代码将结果写入工作表中。

运行示例代码,结果如下。 其他形式的json嵌套数据,也可以使用类似的js代码进行解析。

相关博文链接: vba解析json数据(1)-- split函数 vba解析json数据(2)–正则表达式 vba解析json数据(3)–javascript vba解析json数据(4)–javascript进阶 vba解析json数据(5)–javascript回写excel

本文使用的json数据。

({“outline”:null,“outlinemiss”:null,“font”:{“ad6123f4770bf78a652954510010001”:“宋体”,“ad6123f4770bf78a652954510020001”:“宋体”},“style”:[{“t”:“style”,“c”:[0],“s”:{“font-size”:“18”}},{“t”:“style”,“c”:[0,3,5,6,1],“s”:{“font-family”:“ad6123f4770bf78a652954510010001”}},{“t”:“style”,“c”:[0,2],“s”:{“font-size”:“18”}},{“t”:“style”,“c”:[3],“s”:{“font-size”:“15.12”}},{“t”:“style”,“c”:[0,1,2,3,5,6,4],“s”:{“color”:"#000000"}},{“t”:“style”,“c”:[6,5],“s”:{“bold”:“true”,“letter-spacing”:“0.359”}},{“t”:“style”,“c”:[6],“s”:{“font-size”:“20.88”}}],“body”:[{“c”:“商品”,“p”:{“h”:1048,“w”:729.359,“x”:79.82,“y”:105.82,“z”:197},“ps”:{"_vector":1},“s”:null,“t”:“pic”},{“c”:“一级分类”,“p”:{“h”:18,“w”:72,“x”:84.636,“y”:110.207,“z”:198},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“二级分类”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:110.207,“z”:199},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“一体机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:136.487,“z”:201},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“打印机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:167.447,“z”:202},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“打印机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:198.452,“z”:204},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“打印机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:229.412,“z”:206},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“激光打印机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:167.447,“z”:203},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“喷墨打印机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:198.452,“z”:205},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“针式打印机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:229.412,“z”:207},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“复印机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:256.052,“z”:208},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“传真机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:278.012,“z”:209},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“扫描仪”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:299.972,“z”:210},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“有绳电话机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:321.962,“z”:212},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“无绳电话机(子母机)”,“p”:{“h”:18,“w”:161.999,“x”:353.67,“y”:343.923,“z”:214},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“数字无绳电话机”,“p”:{“h”:18,“w”:125.999,“x”:353.67,“y”:365.882,“z”:216},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“无线电话机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:387.842,“z”:218},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“碎纸机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:409.802,“z”:219},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“点验钞机”,“p”:{“h”:18,“w”:71.999,“x”:353.67,“y”:431.762,“z”:220},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“电动”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:453.767,“z”:222},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“手动”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:475.728,“z”:224},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“纸卡考勤机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:497.687,“z”:227},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“热敏纸卡考勤机(无需色带)”,“p”:{“h”:15.12,“w”:181.439,“x”:353.31,“y”:520.681,“z”:229},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[3]},{“c”:“办公设备”,“p”:{“h”:24.12,“w”:97.559,“x”:85.356,“y”:133.75,“z”:200},“ps”:{"_cover":true,"_enter":1},“s”:{“font-size”:“24.119”},“t”:“word”,“r”:[5]},{“c”:“电话机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:321.962,“z”:211},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“电话机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:343.923,“z”:213},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“电话机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:365.882,“z”:215},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“电话机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:387.842,“z”:217},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“装订机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:453.767,“z”:221},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“装订机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:475.728,“z”:223},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“考勤机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:497.687,“z”:226},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“考勤机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:519.647,“z”:228},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“考勤机”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:541.607,“z”:230},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“切纸刀(机)”,“p”:{“h”:18,“w”:90,“x”:210.675,“y”:563.568,“z”:232},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“热熔式 胶圈 铁圈 梳式 财务(铆管)”,“p”:{“h”:15.12,“w”:241.919,“x”:536.61,“y”:476.761,“z”:225},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[3]},{“c”:“会议设备”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:651.437,“z”:238},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“会议设备”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:673.397,“z”:240},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“会议设备”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:695.357,“z”:242},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“监控设备”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:717.318,“z”:244},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“耗材及配件”,“p”:{“h”:20.879,“w”:105.839,“x”:84.996,“y”:763.474,“z”:247},“ps”:{"_cover":true},“t”:“word”,“r”:[6]},{“c”:“打印耗材”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:764.507,“z”:248},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“打印耗材”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:793.307,“z”:250},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“打印耗材”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:818.867,“z”:252},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“装订耗材”,“p”:{“h”:18,“w”:72,“x”:210.675,“y”:1015.862,“z”:277},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“考勤卡/考勤板”,“p”:{“h”:18,“w”:117,“x”:210.675,“y”:1060.503,“z”:272},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“过塑膜”,“p”:{“h”:18,“w”:54,“x”:210.675,“y”:1082.463,“z”:273},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“办公设备配件”,“p”:{“h”:18,“w”:108,“x”:210.675,“y”:1104.426,“z”:274},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“白色复印纸”,“p”:{“h”:18,“w”:90,“x”:210.675,“y”:1129.661,“z”:276},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“指纹考勤机”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:541.607,“z”:231},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“木质 钢质”,“p”:{“h”:18,“w”:80.999,“x”:353.67,“y”:563.568,“z”:233},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“塑封机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:585.527,“z”:235},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“收银机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:607.517,“z”:236},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“标签票据打印机”,“p”:{“h”:18,“w”:125.999,“x”:353.67,“y”:629.478,“z”:237},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“投影机”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:651.437,“z”:239},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“投影幕”,“p”:{“h”:18,“w”:53.999,“x”:353.67,“y”:673.397,“z”:241},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“电子白板”,“p”:{“h”:18,“w”:71.999,“x”:353.67,“y”:695.357,“z”:243},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“监控摄像头”,“p”:{“h”:18,“w”:89.999,“x”:353.67,“y”:717.318,“z”:245},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“扫描枪盘点机”,“p”:{“h”:18,“w”:107.999,“x”:353.67,“y”:739.307,“z”:246},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“硒鼓粉盒”,“p”:{“h”:18,“w”:71.999,“x”:353.67,“y”:764.507,“z”:249},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“碳粉”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:793.307,“z”:251},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“墨盒”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:818.867,“z”:253},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“墨水”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:840.827,“z”:255},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“色带”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:862.832,“z”:257},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“碳带”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:884.792,“z”:259},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“鼓”,“p”:{“h”:18,“w”:18,“x”:353.67,“y”:906.752,“z”:261},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“墨粉”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:928.713,“z”:263},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“版纸”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:950.672,“z”:265},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“油墨”,“p”:{“h”:18,“w”:36,“x”:353.67,“y”:972.632,“z”:267},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“宝塔线装订线蜡线?”,“p”:{“h”:18,“w”:152.999,“x”:353.67,“y”:994.622,“z”:268},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“装订条钉胶圈铆管”,“p”:{“h”:18,“w”:143.999,“x”:353.67,“y”:1016.582,“z”:270},“ps”:{"_cover":true},“t”:“word”,“r”:[0]},{“c”:“装订胶片热熔封套”,“p”:{“h”:18,“w”:143.999,“x”:353.67,“y”:1038.542,“z”:271},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“a3 a4”,“p”:{“h”:18,“w”:45,“x”:536.969,“y”:563.568,“z”:234},“ps”:{"_cover":true,"_enter":1},“s”:{“font-family”:“ad6123f4770bf78a652954510020001”},“t”:“word”,“r”:[2]},{“c”:" 装订机皮带 装订机针头",“p”:{“h”:18,“w”:198,“x”:536.969,“y”:994.622,“z”:269},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“打印机复印机等维修的零部件等”,“p”:{“h”:18,“w”:251.999,“x”:353.67,“y”:1103.706,“z”:396},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[0]},{“c”:“办公用纸”,“p”:{“h”:20.879,“w”:84.599,“x”:84.996,“y”:1128.628,“z”:275},“ps”:{"_cover":true,"_enter":1},“t”:“word”,“r”:[6]}],“page”:{“ph”:1262.879,“pw”:892.439,“iw”:729,“ih”:1048,“v”:6,“t”:“2”,“pptlike”:false,“cx”:79.82,“cy”:105.82,“cw”:729.359,“ch”:1048.36}})

太平洋电脑网上的分类菜单显示

微信二维码
微信二维码
支付宝二维码
网站地图