excel转成json格式 用vba转成json

Sub excelToJsonFileExample()
Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim jsonFileObject As New FileSystemObject
Dim jsonFileExport As TextStream
Dim i As Long
Dim cell As Variant

Set excelRange = Cells(1, 1).CurrentRegion

For i = 2 To excelRange.Rows.Count
    jsonDictionary("id") = Cells(i, 1)
    jsonDictionary("name") = Cells(i, 2)
    jsonDictionary("username") = Cells(i, 3)
    jsonDictionary("email") = Cells(i, 4)
    jsonDictionary("street") = Cells(i, 5)
    jsonDictionary("suite") = Cells(i, 6)
    jsonDictionary("city") = Cells(i, 7)
    jsonDictionary("zipcode") = Cells(i, 8)
    jsonDictionary("phone") = Cells(i, 9)
    jsonDictionary("website") = Cells(i, 10)
    jsonDictionary("company") = Cells(i, 11)

    jsonItems.Add jsonDictionary
    Set jsonDictionary = Nothing
Next i

‘change the path below to save where you desire
Set jsonFileExport = jsonFileObject.CreateTextFile("C:\Users\username\Desktop\jsonExample.json", True)
jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))

End Sub

参考
http://excelerator.solutions/2017/08/24/convert-excel-to-json-using-vba/