Excel是一款非常常見的數(shù)據(jù)處理軟件，我們可以使用它來處理各種各樣的數(shù)據(jù)。有時(shí)候我們需要將Excel中的數(shù)據(jù)轉(zhuǎn)換成json文件，這就需要用到Excel VBA來編寫轉(zhuǎn)換代碼。

Sub ExcelToJson()
Dim jsonString As String
Dim lastRow As Long
Dim lastColumn As Long
Dim row As Long
Dim column As Long
Dim key As String
Dim value As Variant
'根據(jù)需求修改Sheet名稱
Sheets("Sheet1").Activate
'獲取最后一行和最后一列
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
'構(gòu)建JSON字符串
jsonString = "{"
'遍歷Excel表格的每一行和每一列
For row = 2 To lastRow
jsonString = jsonString & Chr(10) & "  {"
For column = 1 To lastColumn
key = ActiveSheet.Cells(1, column).Value
value = ActiveSheet.Cells(row, column).Value
jsonString = jsonString & Chr(10) & "    """ & key & """: "
If IsNumeric(value) Then
jsonString = jsonString & value
ElseIf IsDate(value) Then
jsonString = jsonString & """" & Format(value, "yyyy-mm-dd") & """"
Else
jsonString = jsonString & """" & value & """"
End If
If column< lastColumn Then
jsonString = jsonString & ","
End If
Next column
jsonString = jsonString & Chr(10) & "  }"
If row< lastRow Then
jsonString = jsonString & ","
End If
Next row
jsonString = jsonString & Chr(10) & "}"
'將JSON字符串輸出到控制臺(tái)或保存為文件
Debug.Print jsonString
'Open "test.json" For Output As #1
'Print #1, jsonString
'Close #1
End Sub

以上的Excel VBA代碼可以將表格中的數(shù)據(jù)轉(zhuǎn)換成符合JSON格式的字符串。我們需要根據(jù)實(shí)際需求修改Sheet名稱以及輸出方式等參數(shù)。

通過使用這個(gè)Excel VBA代碼，我們就可以輕松地將Excel中的數(shù)據(jù)轉(zhuǎn)換成JSON格式，方便地在各種應(yīng)用中使用。