VBA解析JSON数据(1)-- Split函数

本文详细介绍了JSON(JavaScript Object Notation)的定义、特点及应用,解释了JSON格式的组成,并提供了VBA解析JSON数据的示例代码。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

什么是JSON?

JSON(JavaScript Object Notation) 是一种及其轻量级的数据交换格式,它是 ECMAScript (欧洲计算机协会制定的JavaScript规范)的一个子集,它使用完全独立于任何编程语言的文本格式来表示数据。
由于JSON天然具备了简洁和清晰的层次结构,易于机器解析和生成,从2005年前后开始JSON成为主流的网络数据传输格式。进行网页数据抓取的时候,很多页面请求的返回结果都是JSON格式的。
详细的JSON标准规范请参考:

http://json.com/specs/

JSON是什么样的?

JSON 本质是一个字符串(由花括号{}包裹起来),它是JavaScript对象的字符串表示法。例如:

{“applySn”: “粤G妆网备字2017050113”, “applySnTT”: “”, “apply_enter_address”: “”, “enterpriseName”: “广州市金日精细化工有限公司”, “enterpriseNameTT”: “”, “is_off”: “N”, “newProcessid”: “20170421153208vq3fu”, “off_date”: “”, “org_name”: “”, “processid”: “20170421153208vq3fu”, “productName”: “悠莅雅柔美亮肤按摩霜”, “productNameTT”: “”, “provinceConfirm”: “2017-05-17”}

如何解读JSON?

据说JSON易于人阅读和编写,我怎么看出来,只看到一堆字符,完全不知道包含什么信息。那么来看看下面这段JSON,是不是顿时觉得清爽了很多,其实内容和上一段完全相同,只是进行了相应的格式化显示。

{
      "applySn": "粤G妆网备字2017050113",
      "applySnTT": "",
      "apply_enter_address": "",
      "enterpriseName": "广州市金日精细化工有限公司",
      "enterpriseNameTT": "",
      "is_off": "N",
      "newProcessid": "20170421153208vq3fu",
      "off_date": "",
      "org_name": "",
      "processid": "20170421153208vq3fu",
      "productName": "悠莅雅柔美亮肤按摩霜",
      "productNameTT": "",
      "provinceConfirm": "2017-05-17"
}

KEY和VALUE都由半角双引号包裹,二者之间由冒号做为间隔(冒号之后可能有空格),也就是如下格式,类似于VBA中字典对象。

"KEY": "VALUE"

例如:provinceConfirm的VALUE为2017-05-17,不难看出此部分数据包含13组KEY和VALUE。
有很多在线工具可以帮助我们实现JSON的格式转换,这是编程过程中一个非常重要的工具。例如:

http://jsonviewer.stack.hu/

除了转换为上面的格式外,还可以转换为表格格式,如下图,有些Excel单元格的味道了。
在这里插入图片描述

VBA解析JSON

非常可惜的是VB流行的年代JSON没出现,所以VBA也不支持JSON对象。但是既然JSON数据本质是一个字符串,VBA中丰富的字符串处理函数应该也可以胜任解析JSON这个工作。下面就以上面的JSON数据为例,演示如何在VBA中进行解析。

Sub JSON_Split()
    Dim Res(100, 1 To 2)
    Dim objRegEx As Object
    Dim iRow, myStr, arrLine, arrItem
    myStr = Trim([A1].Value)
    Range("A3:B3").Value = Array("Key", "Value")
    myStr = Replace(myStr, """", "")
    myStr = Replace(myStr, " ", "")
    arrLine = Split(Mid(myStr, 2, Len(myStr) - 2), ",")
    iRow = 4
    For i = LBound(arrLine) To UBound(arrLine)
        arrItem = Split(Trim(arrLine(i)), ":")
        Cells(iRow, 1).Resize(1, 2).Value = arrItem
        iRow = iRow + 1
    Next
End Sub

代码解析
第5行代码读取保存在工作表A1单元格的JSON字符串。
第6行代码写入第3行的表头。
由于KEY和VALUE中没有空格个引号,因此第7行和第8行代码去除JSON字符串中的引号和空格,便于后续拆分。
第9行使用对号做为分隔符,把JSON字符串进行拆分,结果为"KEY":"VALUE"形式。其中MID函数是为了去除字符串首尾的花括号。
第12行代码冒号做为分隔符再次拆分。
第13行代码将结果写入相应行的单元格中。

运行示例代码,结果如下,妥妥的写入了我们熟悉的单元格,可以很方便使用这些数据了。
在这里插入图片描述

JSON中一个KEY也可以对应多个VALUE,对于这类数据,需要再多使用一次SPLIT函数进行拆分。


相关博文链接:
VBA解析JSON数据(1)-- Split函数
VBA解析JSON数据(2)–正则表达式
VBA解析JSON数据(3)–JavaScript
VBA解析JSON数据(4)–JavaScript进阶
VBA解析JSON数据(5)–JavaScript回写Excel

<think>我们参考用户的问题和之前的回答,用户现在明确询问如何用VBAJSON字符串解析为数组。根据引用[1]和引用[4]提供的信息,我们可以采用两种方法:方法一:使用正则表达式和字符串处理(比较基础,适用于简单JSON)方法二:使用第三方库(如VBA-JSON解析器)来解析,然后转换为数组(更通用,尤其适用于复杂JSON)由于用户要求解析为数组,我们假设JSON字符串是一个数组(如引用[4]中的示例)或对象中包含数组。我们将提供两种方法的示例:方法一:使用正则表达式和字符串分割(适用于简单、规则的JSON数组)步骤:1.移除JSON字符串中的方括号、花括号、引号等字符(使用正则表达式)2.将字符串分割成数组(按逗号分割)3.进一步分割每个元素(按冒号分割)得到键值对但是这种方法在JSON结构复杂(如有嵌套、包含逗号等)时会失效。方法二:使用VBA-JSON解析器(推荐)1.下载并导入VBA-JSON解析器(可以从https://github.com/VBA-tools/VBA-JSON获取)2.使用JsonConverter模块中的ParseJson方法将JSON字符串解析为对象(集合或字典)3.然后遍历该对象,将数据提取到VBA数组中由于方法一有局限性,我们重点使用方法二。步骤详解(方法二):1.导入JsonConverter模块(需要引用MicrosoftScriptingRuntime)2.使用ParseJson函数解析JSON字符串,返回一个集合(Collection)或字典(Dictionary)对象3.判断解析后的对象是数组(集合)还是对象(字典)4.如果是数组(集合),则遍历集合,将每个元素(可能是字典)的值提取到数组中示例代码(解析JSON数组):假设我们有如下JSON字符串(来自引用[4]):[{"id":"2","name":"Andy"},{"id":"3","name":"Bob"}]我们想将其解析为一个二维数组,第一维是记录,第二维是字段(id和name)。注意:由于JSON数组中的每个元素都是对象,我们首先解析为集合,然后每个元素是一个字典。代码步骤:1.解析JSON字符串得到集合对象2.确定数组大小(行数和列数)3.将集合中的数据读入二维数组但是注意:JSON数组中的每个对象可能包含不同的键,所以我们需要先确定所有键(列名)并固定顺序。另一种常见做法:将数据直接输出到Excel工作表,但用户要求解析为数组。我们提供将JSON数组转换为二维数组的代码示例。注意:需要先导入JsonConverter.bas模块。代码示例:```vba'需要先导入JsonConverter模块,并添加对MicrosoftScriptingRuntime的引用'工具->引用->勾选MicrosoftScriptingRuntimeFunctionJSONToArray(jsonStringAsString)AsVariantDimjsonCollectionAsCollectionDimdictAsDictionaryDimiAsLong,jAsLongDimkeys()AsStringDimresultArray()AsVariantDimkeyCountAsLongDimhasKeysAsBoolean'解析JSON字符串SetjsonCollection=JsonConverter.ParseJson(jsonString)'检查集合是否为空IfjsonCollection.Count=0ThenJSONToArray=Array()ExitFunctionEndIf'假设第一个元素包含所有键(每个元素都是字典)Setdict=jsonCollection(1)keys=dict.keys'获取键数组keyCount=dict.Count'定义结果数组,第一维是记录数,第二维是字段数ReDimresultArray(1TojsonCollection.Count,1TokeyCount)'填充数组Fori=1TojsonCollection.CountSetdict=jsonCollection(i)Forj=1TokeyCount'注意:字典的键的顺序可能不固定,所以我们用keys数组的顺序resultArray(i,j)=dict(keys(j-1))NextjNextiJSONToArray=resultArrayEndFunction```使用示例:```vbaSubTest()DimjsonStrAsStringDimarrAsVariantDimiAsLong,jAsLongjsonStr="[{""id"":""2"",""name"":""Andy""},{""id"":""3"",""name"":""Bob""}]"arr=JSONToArray(jsonStr)'输出到立即窗口Fori=LBound(arr,1)ToUBound(arr,1)Forj=LBound(arr,2)ToUBound(arr,2)Debug.Print"arr("&i&","&j&")="&arr(i,j)NextjNextiEndSub```注意事项:1.此函数假设JSON字符串是一个数组,且每个元素都是对象(字典)。2.所有对象应该具有相同的键(否则可能出错)。如果键不同,则需要更复杂的处理(例如先收集所有可能的键)。3.如果JSON数组为空,则返回空数组。如果JSON字符串是一个对象(而不是数组),但包含数组,则可以先解析整个对象,然后定位到数组部分。例如,引用[3]中的JSON:{"heweather6":[{...}]},那么解析后:Setparsed=JsonConverter.ParseJson(jsonString)SetarrayPart=parsed("heweather6")'这是一个集合然后同样使用上述方法将arrayPart转换为数组。因此,根据JSON结构的不同,需要调整代码来定位数组。总结:使用VBA-JSON解析器库是最为可靠的方法,尤其适用于复杂的嵌套JSON结构。如果用户不想使用第三方库,我们也可以提供基础的正则表达式方法,但仅适用于非常简单的JSON数组(无嵌套、无特殊字符)。方法一(基础)示例(仅适用于简单情况):假设JSON字符串是:[{"key1":"value1","key2":"value2"},{"key1":"value3","key2":"value4"}]步骤:1.移除外层方括号和花括号,以及引号?但这样会破坏字符串内的引号,所以不可靠。2.分割每个对象:按"},{"3.分割每个键值对:按","4.分割键和值:按":"但这种方法遇到值中包含逗号、引号等就会出错,因此不推荐。我们建议使用第三方库。相关问题:1.如何在VBA中安装和使用VBA-JSON解析器库?2.如何处理JSON数组中的嵌套对象?3.当JSON数组中的对象键不一致时,如何安全地解析为数组?4.如何将解析后的数组输出到Excel工作表?</think>### 如何在VBA中将JSON字符串解析为数组 在VBA解析JSON字符串为数组,推荐使用第三方库**VBA-JSON解析器**(最可靠方法)。以下是完整实现步骤: #### 方法一:使用VBA-JSON解析器(推荐) 1. **准备工作**: - 下载[VBA-JSON解析器](https://github.com/VBA-tools/VBA-JSON) - 导入模块:`JsonConverter.bas` - 添加引用:`工具 > 引用 > Microsoft Scripting Runtime` 2. **解析JSON数组代码**: ```vba Sub ParseJsonArray() ' JSON字符串示例(来自引用[4]) Dim jsonStr As String jsonStr = "[{""id"":""2"",""company_id"":""2"",""name"":""AndyLee""}]" ' 解析JSON为集合对象 Dim jsonArray As Collection Set jsonArray = JsonConverter.ParseJson(jsonStr) ' 转换为VBA数组 Dim resultArray() As Variant ReDim resultArray(1 To jsonArray.Count, 1 To 3) Dim i As Long For i = 1 To jsonArray.Count Dim item As Dictionary Set item = jsonArray(i) resultArray(i, 1) = item("id") resultArray(i, 2) = item("company_id") resultArray(i, 3) = item("name") Next i ' 输出到Excel工作表 Sheets("Sheet1").Range("A1:C" & jsonArray.Count).Value = resultArray End Sub ``` #### 方法二:正则表达式解析(简单场景) 适用于结构简单的JSON(无嵌套),基于引用[1]: ```vba Sub ParseSimpleJson() Dim jsonStr As String jsonStr = "[{""id"":""2"",""name"":""Andy""}]" ' 1. 清理JSON字符 Dim regex As Object Set regex = CreateObject("VBScript.RegExp") regex.Pattern = "[\[\]{}""]+" jsonStr = regex.Replace(jsonStr, "") ' 2. 分割为键值对数组 Dim items() As String items = Split(jsonStr, ",") ' 3. 提取数据到数组 Dim result(1 To 2) As Variant result(1) = Split(items(0), ":")(1) ' id result(2) = Split(items(1), ":")(1) ' name ' 输出结果 Debug.Print "ID: " & result(1), "Name: " & result(2) End Sub ``` #### 关键说明: 1. **VBA-JSON优势**: - 自动处理嵌套结构(如引用[3]中的`heweather6`嵌套对象) - 支持`null`值转换(引用[4]中的`"tag":null`) - 可直接输出到Excel(引用[5]) 2. **正则表达式局限性**: - 无法处理多层嵌套JSON - 值中包含逗号时会解析错误 - 需手动处理数据类型转换 > **推荐场景**: > - 复杂JSON → 使用VBA-JSON解析器 > - 简单键值对 → 正则表达式快速处理 解析后的数组可直接用于: - 填充Excel表格(如引用[5]) - 数据筛选和分析 -数据库交互
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值