Excel VBA提交含空字段JSON至Flask后端解析失败求助

问题场景 #

从Excel通过VBA向Python Flask后端提交数据时，VBA生成的JSON存在不符合标准的空字段（表现为连续逗号,,），导致Flask无法正常解析JSON。

VBA生成的JSON示例 #

[
    [
        "name",
        "RTX",
        "SPX",
        "NDX"
    ],
    [
        "date",
        "close",
        "close",
        "close"
    ],
    [
        "2020-01-10T05:00:00.000Z", ,
        200.1,
        300.1
    ],
    [
        "2020-01-11T05:00:00.000Z",
        100.1,
        200.2,
        300.2
    ]
]

VBA发送请求代码 #

Set http = CreateObject("Msxml2.ServerXMLHTTP")
With http
    .Open "POST", url, False
    .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    .setRequestHeader "Content-Type", "application/json; charset=utf-8"
    .setTimeouts ONE_MIN_MS, 5 * ONE_MIN_MS, ONE_MIN_MS, 5 * ONE_MIN_MS
    .send to_json
    Log "response status: " & .Status
    resp = .responseText
End With

Flask接收代码及错误 #

执行data = req.get_json()时触发错误：

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.11/site-packages/werkzeug/wrappers/request.py", line 620, in get_json
    rv = self.on_json_loading_failed(e)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/flask/wrappers.py", line 131, in on_json_loading_failed
    return super().on_json_loading_failed(e)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/werkzeug/wrappers/request.py", line 645, in on_json_loading_failed
    raise BadRequest(f"Failed to decode JSON object: {e}")
werkzeug.exceptions.BadRequest: 400 Bad Request: Failed to decode JSON object: Expecting value: line 1 column 90 (char 89)

调用req.get_data()获取的原始字节数据：

b'[["name","RTX","SPX","NDX"],["date","close","close","close"],["2020-01-10T05:00:00.000Z",,200.1,300.1],["2020-01-11T05:00:00.000Z",100.1,200.2,300.2]]'

解决方案 #

方案1：Python后端预处理修复非标准JSON #

由于原始JSON中,,是不符合JSON规范的空值表示，可先将其替换为标准的null后再解析：

from flask import request, jsonify
import json

@app.route('/your-endpoint', methods=['POST'])
def handle_excel_data():
    # 获取原始数据并转为字符串
    raw_str = request.get_data().decode('utf-8')
    # 替换连续逗号为带null的格式
    fixed_str = raw_str.replace(',,', ',null,')
    try:
        # 解析修复后的JSON
        data = json.loads(fixed_str)
        # 后续业务逻辑处理
        return jsonify({"status": "success", "data": data})
    except Exception as e:
        return jsonify({"status": "error", "message": str(e)}), 400

注意：该方案仅适用于空字段表现为连续逗号的场景，若存在其他非标准格式需调整替换规则。

方案2：VBA端生成标准JSON（推荐） #

从根源解决问题，在VBA生成JSON时，将空单元格值替换为标准的null，避免生成,,：

' 示例：遍历单元格生成JSON元素时处理空值
Function GetJSONElement(cell As Range) As String
    If IsEmpty(cell.Value) Or cell.Value = "" Then
        GetJSONElement = "null"
    Else
        Select Case VarType(cell.Value)
            Case vbString
                ' 转义双引号（如果需要）
                GetJSONElement = """" & Replace(cell.Value, """", "\""") & """"
            Case vbDate
                ' 日期转为ISO格式字符串
                GetJSONElement = """" & Format(cell.Value, "yyyy-mm-ddThh:mm:ss.000Z") & """"
            Case vbInteger, vbLong, vbSingle, vbDouble
                GetJSONElement = CStr(cell.Value)
            Case Else
                GetJSONElement = "null"
        End Select
    End If
End Function

使用该函数生成每个单元格对应的JSON元素，再拼接成完整的JSON数组，确保输出符合JSON规范。

方案对比 #

• Python端修复：快速临时解决，无需修改VBA代码，但依赖特定格式的错误，通用性弱。
• VBA端修复：从源头保证JSON标准性，避免后续任何解析问题，是长期可靠的方案。

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