excel export json vba

您可以使用 VBA 编写一个能够将 Excel 表格转换为 JSON 格式数据的 Excel 宏来导出 JSON。下面是示例代码：

Sub ExportToJson()
    Dim i As Long, j As Long
    Dim jsonStr As String
    Dim rowStr As String
    Dim cellValue As Variant
    '获取第一个工作表的行数和列数
    Dim rowCount As Long
    Dim columnCount As Long
    rowCount = Worksheets(1).UsedRange.Rows.Count
    columnCount = Worksheets(1).UsedRange.Columns.Count
    '遍历所有行和列，生成 JSON 字符串
    jsonStr = "{""data"": ["
    For i = 1 To rowCount
        rowStr = "{ "
        For j = 1 To columnCount
            cellValue = Worksheets(1).Cells(i, j).Value
            rowStr = rowStr & """" & Worksheets(1).Cells(1, j) & """: """ & cellValue & """"
            If j < columnCount Then
                rowStr = rowStr & ", "
            End If
        Next j
        rowStr = rowStr & " }"
        If i < rowCount Then
            rowStr = rowStr & ","
        End If
        jsonStr = jsonStr & rowStr
    Next i
    jsonStr = jsonStr & "]}"
    '将 JSON 保存到文件中
    Open "C:\output.json" For Output As #1
    Print #1, jsonStr
    Close #1
End Sub
此代码将 Excel 表单的数据转换为带有"data"字段的 JSON 对象。您可以根据实际需求修改代码来适应不同的 Excel 表格格式和 JSON 结构。