vba如何处理json格式的数据

VBA如何处理JSON格式的数据：从入门到实战

在数据交互日益频繁的今天,JSON（JavaScript Object Notation）已成为轻量级数据交换的主流格式，VBA（Visual Basic for Applications）作为Office内置的编程工具，原生并不支持JSON解析，这给处理来自API、Web服务或外部文件的JSON数据带来了挑战，本文将详细介绍VBA处理JSON数据的多种方法，从基础概念到实战应用，帮助读者高效实现JSON与VBA数据的双向转换。

JSON与VBA的基础认知

1 JSON数据结构

JSON是一种基于文本的数据格式,采用键值对（Key-Value）结构，主要包括两种类型：

对象（Object）：用表示，包含多个键值对，如 {"name":"张三", "age":30}。
数组（Array）：用 [] 表示，包含有序值列表，如 [{"name":"李四"}, {"name":"王五"}]。

键（Key）必须是字符串，值（Value）可以是字符串、数字、布尔值、数组、对象或null。

2 VBA与JSON的交互痛点

VBA的数据结构主要是数组、集合（Collection）和字典（Dictionary），与JSON的嵌套结构无法直接匹配，处理JSON数据的核心需求是：

解析（Parse）：将JSON文本转换为VBA可识别的对象（如字典、集合）。
序列化（Serialize）：将VBA对象转换为JSON文本，方便输出或传输。

VBA处理JSON的常用方法

方法1：使用ScriptControl（Windows内置，无需额外引用）

ScriptControl是Windows提供的组件,可执行JavaScript代码，通过调用JSON.parse和JSON.stringify实现解析与序列化。

示例代码：

Function ParseJson(jsonString As String) As Object
    Dim sc As Object
    Set sc = CreateObject("MSScriptControl.ScriptControl")
    sc.Language = "JScript"
    sc.AddCode "function parseJSON(json) { return JSON.parse(json); }"
    Set ParseJson = sc.Run("parseJSON", jsonString)
End Function
Function SerializeJson(vbaObject As Object) As String
    Dim sc As Object
    Set sc = CreateObject("MSScriptControl.ScriptControl")
    sc.Language = "JScript"
    sc.AddCode "function stringifyJSON(obj) { return JSON.stringify(obj); }"
    SerializeJson = sc.Run("stringifyJSON", vbaObject)
End Function
' 使用示例
Sub TestScriptControl()
    Dim jsonText As String
    Dim parsedData As Object
    jsonText = "{""name"":""张三"",""age"":30,""hobbies"":[""阅读"",""编程""]}"
    Set parsedData = ParseJson(jsonText)
    ' 访问数据
    Debug.Print "姓名：" & parsedData.name ' 输出：姓名：张三
    Debug.Print "年龄：" & parsedData.age   ' 输出：年龄：30
    Debug.Print "爱好1：" & parsedData.hobbies(0) ' 输出：爱好1：阅读
    ' 序列化VBA对象
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "key1", "value1"
    dict.Add "key2", 123
    Debug.Print "序列化结果：" & SerializeJson(dict) ' 输出：序列化结果：{"key1":"value1","key2":123}
End Sub

优缺点：

优点：无需安装额外组件，Windows系统自带。
缺点：仅支持Windows，Mac系统不兼容；性能较低，处理复杂JSON时可能卡顿。

方法2：使用VBA-JSON库（第三方库，推荐）

VBA-JSON（GitHub开源库）是专门为VBA设计的JSON处理工具，通过解析JSON文本生成字典和集合的嵌套结构，支持复杂嵌套和数组操作。

安装步骤：

下载VBA-JSON库文件（json.bas），地址：https://github.com/VBA-tools/VBA-JSON。
在VBA编辑器中,通过“文件”→“导入文件”将json.bas导入到当前工程。

示例代码：

' 需先引用 VBA-JSON 库
Function ParseJsonWithLibrary(jsonString As String) As Object
    Set ParseJsonWithLibrary = JsonConverter.ParseJson(jsonString)
End Function
' 使用示例
Sub TestVBAJson()
    Dim jsonText As String
    Dim parsedData As Object
    Dim hobbies As Collection
    jsonText = "{""name"":""李四"",""address"":{""city"":""北京"",""district"":""海淀""},""scores"":[85,90,78]}"
    Set parsedData = ParseJsonWithLibrary(jsonText)
    ' 访问对象
    Debug.Print "姓名：" & parsedData("name") ' 输出：姓名：李四
    Debug.Print "城市：" & parsedData("address")("city") ' 输出：城市：北京
    ' 访问数组
    Debug.Print "成绩1：" & parsedData("scores")(1) ' 输出：成绩1：85（VBA数组从1开始）
    ' 遍历数组
    Debug.Print "所有成绩："
    For Each score In parsedData("scores")
        Debug.Print score;
    Next ' 输出：85 90 78
    ' 序列化VBA对象
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "product", "笔记本电脑"
    dict.Add "price", 5999
    Debug.Print "序列化结果：" & JsonConverter.ConvertToJson(dict) ' 输出：{"product":"笔记本电脑","price":5999}
End Sub

优缺点：

优点：功能强大，支持复杂嵌套、数组、日期类型；代码简洁，易读性高；跨平台（Windows/Mac）。
缺点：需额外导入库文件（但一次配置永久可用）。

方法3：使用MSXML库（处理简单JSON）

MSXML（Microsoft XML Core Services）库支持解析XML，但可通过技巧处理简单JSON（如键值对不含嵌套）。

示例代码：

' 需引用 Microsoft XML, v6.0
Function ParseSimpleJson(jsonString As String) As Object
    Dim xmlDoc As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    ' 将JSON转换为XML格式（仅适用于简单键值对）
    Dim xmlString As String
    xmlString = "<root>" & Replace(Replace(jsonString, "{", ""), "}", "") & "</root>"
    xmlString = Replace(xmlString, """, "'")
    xmlString = Replace(xmlString, ":", "<value>")
    xmlString = Replace(xmlString, ",", "</value><key>")
    xmlDoc.LoadXML xmlString
    Set ParseSimpleJson = xmlDoc
End Function
' 使用示例
Sub TestMSXML()
    Dim jsonText As String
    Dim xmlDoc As Object
    Dim nodes As Object
    jsonText = "{""name"":""王五"",""age"":25}"
    Set xmlDoc = ParseSimpleJson(jsonText)
    Set nodes = xmlDoc.SelectNodes("//root/key")
    Dim node As Object
    For Each node In nodes
        If node.Text = "name" Then
            Debug.Print "姓名：" & node.NextSibling.Text
        ElseIf node.Text = "age" Then
            Debug.Print "年龄：" & node.NextSibling.Text
        End If
    Next ' 输出：姓名：王五，年龄：25
End Sub

优缺点：

优点：无需额外库，适合处理简单JSON。
缺点：仅支持无嵌套的JSON，复杂JSON需手动解析，代码冗余。

方法4：调用Python脚本（高级场景）

若系统已安装Python,可通过VBA调用Python的json库处理复杂数据，适合需要高性能或复杂逻辑的场景。

步骤：

编写Python脚本（如json_handler.py）：

import json
import sys
def parse_json(json_string):
    return json.loads(json_string)
def serialize_json(vba_data):
    return json.dumps(vba_data)
if __name__ == "__main__":
    json_string = sys.argv[1]
    result = parse_json(json_string)
    print(result["name"])  # 示例：返回JSON中的name字段

VBA调用Python脚本：

Sub CallPythonScript()
    Dim shell As Object
    Set shell = CreateObject("WScript.Shell")
    Dim jsonText As String
    jsonText = "{""name"":""赵六"",""age"":28}"
    ' 执行Python脚本并传递参数
    Dim command As String
    command = "python C

企鹅直播企鹅直播足球直播爱奇艺直播爱奇艺足球直播足球直播足球直播