Excel是一款非常常用的電子表格軟件，而JSON是一種常用的輕量級數(shù)據(jù)交換格式，很多情況下需要把Excel中的數(shù)據(jù)同步到JSON中，可以使用Excel VBA實現(xiàn)JSON格式的轉(zhuǎn)換。

'導(dǎo)入“Microsoft Scripting Runtime”引用
'json格式轉(zhuǎn)換函數(shù)
Function jsonEsc(str As String) As String
jsonEsc = Replace(str, "\", "\\")
jsonEsc = Replace(jsonEsc, """", "\""")
End Function
Sub excelToJson()
Dim dic As New Dictionary '使用字典輔助生成json
Dim json As String '最終生成的JSON格式字符串
Dim i As Integer, j As Integer '循環(huán)變量
Dim rowCnt As Integer, colCnt As Integer '行數(shù)、列數(shù)
Dim header() As String '保存表頭
Dim data As Variant '保存表格數(shù)據(jù)
Dim cellData As Variant '單元格數(shù)據(jù)
Dim cellValue As String '單元格值
Dim rowData As String '生成的一行數(shù)據(jù)
Dim lastCell As Range '最后一個單元格
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
'獲取表頭
colCnt = lastCell.Column
ReDim header(colCnt)
For j = 1 To colCnt
header(j) = Cells(1, j).Value
Next j
dic.Add "header", header '將表頭放入字典中
'獲取數(shù)據(jù)行數(shù)
rowCnt = lastCell.Row - 1
ReDim data(rowCnt - 1, colCnt - 1)
For i = 2 To rowCnt + 1 '從第2行開始遍歷
For j = 1 To colCnt
cellData = Cells(i, j).Value '獲取單元格數(shù)據(jù)
If VarType(cellData) = vbDate Then '處理日期數(shù)據(jù)
cellValue = Format(cellData, "yyyy-mm-dd")
Else
cellValue = CStr(cellData) '轉(zhuǎn)換為字符串
End If
rowData = rowData & """" & header(j) & """ : """ & jsonEsc(cellValue) & """, "
Next j
rowData = Left(rowData, Len(rowData) - 2) '去掉末尾逗號和空格
dic.Add CStr(i - 1), "{" & rowData & "}" '將一行數(shù)據(jù)放入字典中
rowData = "" '重置rowData
Next i
'生成json格式字符串
For i = 0 To rowCnt - 1
json = json & vbCrLf & dic(CStr(i)) & ","
Next i
json = "[" & Left(json, Len(json) - 1) & "]" '去掉末尾逗號和空格
'輸出
Debug.Print json
End Sub

在Excel中運行上述代碼后，通過調(diào)用excelToJson函數(shù)可以將當前工作表中的數(shù)據(jù)轉(zhuǎn)換為JSON格式的字符串，并在VBA窗口中輸出。