Excel 使用VBA代码将Excel转换为JSON

不必下载Excel转换为JSON的软件，您还可以使用VBA代码编辑器将Excel数据转换为JSON格式。您可以在VBA编辑器中创建自定义代码并运行它来完成任务。

VBA代码编辑器是Excel的开发工具或内置功能。当您下载MS Excel时，它默认随Excel一起提供。在VBA代码编辑器中，您可以编写自己的代码并执行特定任务。如果您习惯于进行编码，那么这是一个不错的方式。

通过将Excel列映射到JSON对象键创建Excel转换为JSON的代码。然后执行该代码将数据转换为JSON格式。如果您了解Visual Basic编程，则可以使用此工具。

为什么选择VBA代码编辑器

如果您擅长编码并创建逻辑代码，那么为什么要使用工具呢？在VBA代码编辑器中创建自己的自定义代码，并通过运行该代码将任意数量的Excel文档转换为JSON格式。不需要下载或互联网连接等。

您不需要明确下载任何工具来将Excel数据转换为JSON格式。您可以在使用Excel VBA代码编辑器时无需在系统上下载任何软件。如果您擅长进行编码，这是一个不错的方式。甚至您不需要安装VBA编辑器，因为它默认随Excel一起提供。

提示：您的Excel数据必须具有列名，因为第一行始终被视为标题。

打开VBA编辑器

您可以从开发者选项卡中在Excel中打开VBA代码编辑器。
如果您的Excel功能区中没有开发者选项卡，请右键单击功能区并从列表中选择开发者选项卡以自定义功能区。
然后，点击第一个选项 Visual Basic 以打开VBA代码编辑器。
在VBA代码编辑器中，在您当前打开的Excel文件下创建一个新模块。
在这里编写Excel转换为JSON的代码。

VBA代码

以下是将Excel文件数据转换为JSON格式的完整代码。复制以下代码并粘贴到相应的VBA代码编辑器中。

Public Function ExcelToJSON(rng As Range) As String
    ' Check there must be at least two columns in the Excel file
    If rng.Columns.Count < 2 Then
        ExcelToJSON = CVErr(xlErrNA)
        Exit Function
    End If
    Dim dataLoop, headerLoop As Long
    ' Get the first row of the Excel file as a header 
    Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)   
    ' Count the number of columns of targeted Excel file
    Dim colCount As Long: colCount = headerRange.Columns.Count   
    Dim JSON As String: JSON = "["    
    For dataLoop = 1 To rng.Rows.Count
        ' Skip the first row of the Excel file because it is used as header
        If dataLoop > 1 Then
            ' Start data row
            Dim jsonData As String: jsonData = "{"            
            ' Loop through each column and combine with the header
            For headerLoop = 1 To colCount
                jsonData = jsonData & """" & headerRange.Value2(1, headerLoop) & """" & ":"
                jsonData = jsonData & """" & rng.Value2(dataLoop, headerLoop) & """"
                jsonData = jsonData & ","
            Next headerLoop            
            ' Strip out the comma in last value of each row
            jsonData = Left(jsonData, Len(jsonData) - 1)            
            ' End data row
            JSON = JSON & jsonData & "},"
        End If
    Next    
    ' Strip out the last comma in last row of the Excel data
    JSON = Left(JSON, Len(JSON) - 1)   
    JSON = JSON & "]"    
    ExcelToJSON = JSON
End Function

在这个VBA代码中，我们定义了一个名为 ExcelToJSON 的函数，该函数将在Excel文件中用于将Excel转换为JSON。让我们看看它的执行过程。

语法

=ExcelToJSON(range)

此函数将使用您想要转换为JSON格式的Excel单元格范围，例如A1:F9。

将Excel数据转换为JSON格式

现在，我们将使用上述代码创建的函数 ExcelToJSON() 将Excel数据转换为JSON格式。请记住一件事-此函数仅适用于此特定文件。它不适用于其他Excel文件。

返回Excel文件，在您希望放置转换后的JSON结果的Excel单元格中编写以下ROUND()公式。例如， =ExcelToJSON(A1:D3)
按下 Enter 键，获取结果并查看数据是否成功转换为JSON格式。

代码说明

代码1

If rng.Columns.Count < 2 Then
        ExcelToJSON = CVErr(xlErrNA)
        Exit Function
End If

首先，该代码用于验证所选范围必须至少有两列才能进行Excel到JSON转换。如果不满足此条件，则会从该函数中退出。

代码2

Dim dataLoop, headerLoop As Long
' Get the first row of the Excel file as a header 
Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)

接下来我们创建了两个变量：dataLoop和headerLoop。dataLoop变量用于存储在Excel文件中的Excel数据，headerLoop用于存储列标题。这两个变量将在转换过程中使用。

代码3

' Count the number of columns of targeted Excel file
Dim colCount As Long: colCount = headerRange.Columns.Count

在这里，我们创建了一个变量 colCount。 我们使用这段代码来查找要将其数据转换为JSON格式的目标列的数量，该数据存储在Excel文件中。

代码4

Dim JSON As String: JSON = "["   
For dataLoop = 1 To rng.Rows.Count
       ' Skip the first row because it is used as header
        If dataLoop > 1 Then
             ' Start data row
             Dim jsonData As String: jsonData = "{"

现在，此代码开始从Excel文件创建JSON数据。这里，dataLoop变量在一个for循环中使用，从1到所选行的末尾。Excel文件数据的第一行将被跳过，因为在将Excel数据转换为JSON时，第一行始终被认为是标题。

JSON格式的数据将被放在花括号{}之间。

代码 5

            ' Loop through each column and combine with the header
            For headerLoop = 1 To colCount
                jsonData = jsonData & """" & headerRange.Value2(1, headerLoop) & """" & ":"
                jsonData = jsonData & """" & rng.Value2(dataLoop, headerLoop) & """"
                jsonData = jsonData & ","
            Next headerLoop            
            ' Strip out the comma in last value of each row
            jsonData = Left(jsonData, Len(jsonData) - 1)            
            ' End data row
            JSON = JSON & jsonData & "},"
        End If

循环将遍历每一列，并将列标题与行数据相结合。每个值将用逗号分隔。而这段代码（ jsonData = Left(jsonData, Len(jsonData) – 1) ）将跳过每行最后一个值后面的逗号。

在JSON文件格式中，每一行的数据将被大括号{}括起来。

代码6

    ' Strip out the last comma in last row of the Excel data
    JSON = Left(JSON, Len(JSON) - 1)    
    JSON = JSON & "]"    
    ExcelToJSON = JSON

所有连结的数据将被存储在一个 JSON name变量中，这个完整的数据将放置在[]方括号之间。转换后的JSON数据将显示在用户使用此用户定义的ExcelToJSON()函数的Excel单元格中。