Excel单元格内容转为JSON格式

'从Excel单元格取出上送数据并转为JSON格式【bizContent】

Function ExcelToJSON()

    Dim sht As Worksheet

    Set sht = Worksheets("Sheet1")

    usedRng = sht.UsedRange

    rowCnt = UBound(usedRng, 1)

    colCnt = UBound(usedRng, 2)

    Dim colArr()

    ReDim colArr(1 To colCnt)

    For c = 1 To colCnt

        Dim rowArr()

        ReDim rowArr(1 To rowCnt)

        For r = 1 To rowCnt

            rowArr(r) = usedRng(r, c)

        Next

        colArr(c) = "" + "" + arrToStr(rowArr)

    Next

    Dim data$

    data = arrToStr2(colArr)

    ExcelToJSON = "bizContent=" + data

End Function

Function arrToStr(ByVal arr)

    arrStr = Join(arr, """:""")

    arrToStr = """" + arrStr + """"

End Function

Function arrToStr2(ByVal arr)

    arrStr = Join(arr, ",")

    arrToStr2 = "{" + arrStr + "}"

End Function

单元格内容格式 转换后的JSON数据