Excel VBA开发：批量替换Sheet数据生成JSON并写入Sheet2

先修正JSON模板的语法问题 #

你提供的原始JSON模板存在几处语法错误（会导致生成的JSON不合法），先修正为标准JSON格式：

{
    "apiOperation": "CREATE_OR_UPDATE_MERCHANT",
    "merchant": {
        "cardMaskingFormat": "DISPLAY_6_4",
        "systemCapturedCardMaskingFormat": "DISPLAY_6_4",
        "categoryCode": "***enterhere***",
        "goodsDescription": "***enterhere***",
        "locale": "en_US",
        "name": "***enterhere***",
        "timeZone": "Asia",
        "address": {
            "city": "***enterhere***",
            "countryCode": "SAR",
            "postcode": "***enterhere***",
            "stateProvince": "***enterhere***",
            "street1": "***enterhere***"
        },
        "service": [
            "ENABLE_DEVICE_PAYMENTS",
            "ENABLE_DECRYPT_APPLE_PAY",
            "ENABLE_DECRYPT_GOOGLE_PAY"
        ],
        "acquirerLink": {
            "aaS2I01": {
                "terminalId": [
                    "aaa00056",
                    "aaa00057",
                    "aaa00058",
                    "aaa00059"
                ]
            }
        }
    }
}

修正点：

• 给merchant、service、aaS2I01添加双引号
• 给service数组里的枚举值添加双引号
• 移除terminalId数组最后一个元素后的多余逗号

VBA代码实现 #

直接复制以下代码到Excel的VBA编辑器（按Alt+F11打开），运行即可：

Sub GenerateJSONFromSheet()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, i As Long
    Dim jsonTemplate As String, generatedJSON As String
    
    ' 设置数据源工作表和目标工作表
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsTarget = ThisWorkbook.Sheets("Sheet2")
    
    ' 定义修正后的JSON模板
    jsonTemplate = "{" & vbCrLf & _
        "    ""apiOperation"": ""CREATE_OR_UPDATE_MERCHANT""," & vbCrLf & _
        "    ""merchant"": {" & vbCrLf & _
        "        ""cardMaskingFormat"": ""DISPLAY_6_4""," & vbCrLf & _
        "        ""systemCapturedCardMaskingFormat"": ""DISPLAY_6_4""," & vbCrLf & _
        "        ""categoryCode"": ""***enterhere***""," & vbCrLf & _
        "        ""goodsDescription"": ""***enterhere***""," & vbCrLf & _
        "        ""locale"": ""en_US""," & vbCrLf & _
        "        ""name"": ""***enterhere***""," & vbCrLf & _
        "        ""timeZone"": ""Asia""," & vbCrLf & _
        "        ""address"": {" & vbCrLf & _
        "            ""city"": ""***enterhere***""," & vbCrLf & _
        "            ""countryCode"": ""SAR""," & vbCrLf & _
        "            ""postcode"": ""***enterhere***""," & vbCrLf & _
        "            ""stateProvince"": ""***enterhere***""," & vbCrLf & _
        "            ""street1"": ""***enterhere***""" & vbCrLf & _
        "        }," & vbCrLf & _
        "        ""service"": [" & vbCrLf & _
        "            ""ENABLE_DEVICE_PAYMENTS""," & vbCrLf & _
        "            ""ENABLE_DECRYPT_APPLE_PAY""," & vbCrLf & _
        "            ""ENABLE_DECRYPT_GOOGLE_PAY""" & vbCrLf & _
        "        ]," & vbCrLf & _
        "        ""acquirerLink"": {" & vbCrLf & _
        "            ""aaS2I01"": {" & vbCrLf & _
        "                ""terminalId"": [" & vbCrLf & _
        "                    ""aaa00056""," & vbCrLf & _
        "                    ""aaa00057""," & vbCrLf & _
        "                    ""aaa00058""," & vbCrLf & _
        "                    ""aaa00059""" & vbCrLf & _
        "                ]" & vbCrLf & _
        "            }" & vbCrLf & _
        "        }" & vbCrLf & _
        "    }" & vbCrLf & _
        "}"
    
    ' 获取Sheet1的最后一行数据
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' 清空Sheet2原有内容
    wsTarget.Cells.Clear
    
    ' 遍历每行数据（从第2行开始，假设第1行是表头）
    For i = 2 To lastRow
        ' 复制模板到临时变量
        generatedJSON = jsonTemplate
        
        ' 依次替换模板中的占位符为对应单元格的值
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "A").Value, , 1) ' A列：categoryCode
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "B").Value, , 1) ' B列：goodsDescription
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "C").Value, , 1) ' C列：name
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "D").Value, , 1) ' D列：city
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "E").Value, , 1) ' E列：postcode
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "F").Value, , 1) ' F列：stateProvince
        generatedJSON = Replace(generatedJSON, "***enterhere***", wsSource.Cells(i, "G").Value, , 1) ' G列：street1
        
        ' 将生成的JSON写入Sheet2的对应行（A列）
        wsTarget.Cells(i - 1, "A").Value = generatedJSON
        ' 自动调整列宽
        wsTarget.Columns("A").AutoFit
    Next i
    
    MsgBox "JSON生成完成，已写入Sheet2！", vbInformation
End Sub

代码说明 #

1. 工作表设置：指定Sheet1为数据源，Sheet2为输出目标
2. 模板定义：用字符串拼接的方式定义标准JSON模板，避免语法错误
3. 遍历数据：从第2行开始遍历Sheet1的所有数据行（如果你的数据从第1行开始，把i=2改成i=1）
4. 占位符替换：按顺序替换7个***enterhere***为A-G列的单元格值
5. 输出写入：将生成的JSON写入Sheet2的A列，自动调整列宽

使用注意事项 #

• 如果单元格内容包含双引号，生成的JSON会出错，可在替换时添加转义：比如把wsSource.Cells(i, "A").Value改成Replace(wsSource.Cells(i, "A").Value, """", """""")
• 确保Sheet1和Sheet2存在，否则会报错

内容的提问来源于stack exchange，提问作者Akshay