利用VBA将excel数据表生成JSON文件（utf8）

ADODB.Stream创建UTF-8+BOM编码的文本文件。
然后遍历数据区，格式化数据，输出即可。
小数据还行，大数据没测试。

另，使用fso创建的文本文件编码为ANSI，ajax解析json时出现乱码无法正常解析。

SubToJson() '创建UTF8文本文件

 myrange = Worksheets("sheet1").UsedRange '通过有效数据区来选择数据

 'myrange = ActiveWorkbook.Names("schoolinfo").RefersToRange '通过定义的名称来选择数据

 'myrange = Range(Worksheets("sheet1").Range("a1").End(xlDown), Worksheets("sheet1").Range("a1").End(xlToRight)) '通过标题行的最大行最大列来选择数据

 

Total = UBound(myrange, 1) '获取行数

Fields = UBound(myrange, 2) '获取列数

 

     DimobjStream AsObject

     SetobjStream = CreateObject("ADODB.Stream")

      

     WithobjStream

            .Type = 2

            .Charset = "UTF-8"

            .Open

            .WriteText"{""total"":"& Total & ",""contents"":["

     

            Fori = 2 ToTotal

                .WriteText"{"

                Forj = 1 ToFields

                    .WriteText""""& myrange(1, j) & """:"""& Replace(myrange(i, j), """","\""") & """"

                     Ifj <> Fields Then

                        .WriteText","

                     EndIf

                Next

                Ifi = Total Then

                        .WriteText"}"

                Else

                        .WriteText"},"

                EndIf

            Next

 

            .WriteText"]}"

            .SaveToFile ActiveWorkbook.FullName & ".json", 2

     EndWith

     SetobjStream = Nothing

EndSub