JSON

Javascript object Notation

Learn from W3C
JSON

- 数据名称和值对中

"firstName":"Cemon"

- 数据之间逗号分隔

- {}用于保存对象,可以嵌套

- []用于保存数组,多个对象

var employees = [
{ "firstName":"Bill" , "lastName":"Gates" },
{ "firstName":"George" , "lastName":"Bush" },
{ "firstName":"Thomas" , "lastName": "Carter" }
];

- Json的校验

当用于数据传输时,使用schema进行校验,类似于XML的XTD校验
SchemaValidator

VBA 生成JSON格式TXT档案

Dim strS, strE, strTempD, strTempO, strQ, strJson As String
Sub JsonLog()
Range("AA1") = Now()
Range("AB1") = WorksheetFunction.Text(Range("AA1"), "YYYYMMDDHHmmSS")
sDateTime = Range("AB1").Value
Set fs = CreateObject("Scripting.FileSystemObject")
sFilename = "C_CONTROL_LOG_" & sDateTime & ".txt"
Set A = fs.CreateTextFile("d:\" & sFilename, True)
strS = "{""controlLogs"":[ "
strE = "]}"
strQ = """"
A.WriteLine (strS)
LR = Range("A65535").End(xlUp).Row
For i = 2 To LR
strTempD = "{" + strQ + "device" + strQ + ":{" + GetData(Range("A1"), Range("A" & i)) + "," _
                                               + GetData(Range("B1"), Range("B" & i)) + "," _
                                               + GetData(Range("C1"), Range("C" & i)) + "," _
                                               + GetData(Range("D1"), Range("D" & i)) + "," _
                                               + GetData(Range("E1"), Range("E" & i)) + "," _
                                               + GetData(Range("F1"), Range("F" & i)) + "," _
                                               + GetData(Range("G1"), Range("G" & i)) + "," _
                                               + GetData(Range("H1"), Range("H" & i)) + "," _
                                               + """radios""" + ":{" + GetData(Range("I1"), Range("I" & i)) + "}" + "," _
                                               + """partDetails""" + ":{" + GetData(Range("J1"), Range("J" & i)) + "," + GetData(Range("K1"), Range("K" & i)) + "}" _
            + "},"


strTempO = strQ + "order" + strQ + ":{" + GetData(Range("L1"), Range("L" & i)) + "," _
                                               + GetData(Range("M1"), Range("M" & i)) + "," _
                                               + GetData(Range("N1"), Range("N" & i)) + "," _
                                               + GetData(Range("O1"), Range("O" & i)) + "," _
                                               + GetData(Range("P1"), Range("P" & i)) + _
            "}}"

If i < LR Then
strJson = strTempD + strTempO + ","
Else
strJson = strTempD + strTempO
End If
A.WriteLine (strJson)
Next
A.WriteLine (strE)
MsgBox "Generate OK"
End Sub

Function GetData(name As String, Datavalue As String)
GetData = strQ + name + strQ + ":" + strQ + Datavalue + strQ
End Function.

主要总结

1. txt生成方式通过createobject
2. 调用命令writeline
3. 由于VBA输入双引号不够方便，所以单独定义变量定义单引号
4. 针对名称和值定义一个function GetData从excel里面循环获取值
5. 注意逗号的分隔，最后一个对象后面没有逗号