导航：首页 > IT资讯 >

看了这篇，EXCEL 导出 JSON 再也不用求人啦

发表于：2024-09-21 作者：千家信息网编辑

千家信息网最后更新 2024年09月21日，实际开发过程中，策划习惯使用 Excel 填写游戏数据，而程序使用的配置数据一般是 xml,json,lua 或者某种自定义的 DSL (Domain Specific Language) 等，或是

千家信息网最后更新 2024年09月21日看了这篇，EXCEL 导出 JSON 再也不用求人啦

实际开发过程中，策划习惯使用 Excel 填写游戏数据，而程序使用的配置数据一般是 xml,json,lua 或者某种自定义的 DSL (Domain Specific Language) 等，或是 scriptable 等二进制文件，所以产生了一个把 Excel 里的数据转换成程序所需要的 "导表" 的过程.

一般团队的导表工具都是程序写的，有好的方面，也有坏的方面.好的方面是: 程序会对比 MD5 码来确定文件一致.坏的方面是: 要规定文件路径，只能一次性转换所有表格，或者选择所需转换表格时难用的 UI, 表格 sheet 名和表的某一行必须有特殊规定等.最大的问题还是这个工具对策划并不开源，加上导表工具里还有上传 SVN, 重启服务器等一系列自动化操作，出了错误之后无法确定是配置数据问题，还是工具问题，网络问题，在这条管线里面，就有了巨大的甩锅空间.

所以策划要牢牢把握住这口锅，出了错就是你配置的错，谁让你离开了 Excel 连游戏都不会做了呢? 下面以 json 格式举例，讲几种策划也能学会的方法，制作自己的导表工具.

题外话，非常不推荐直接把 Excel 导入进数据库的操作.

方法一: 手动转换

最简单的就是复制粘贴手动存成 json, 对，就这样干，因为程序要的就是个 json 文件而已.沙塔斯城里的商人说过一句话:

"你要战争，我就给你战争，水果贩."

我们先分析下 json 格式一般是什么样的，以下面这段为例:

可以看出，整个文件是用大括号 {} 包裹起来，左边引号里的是 key, 右边是对应的 value. Value 有多种类型: 是个数字的 / 用引号圈起来的 / 用大括号圈起来的 / 和用中括号圈起来的，那么我们只要弄成这样就好了.

下面看 excel 表格里面的格式一般是这样的:

第一行是字段名，下面是字段的值，一般用 id 作为数据库里面的 key, 或者还有自动生成的 uid, 增加一行用作标记改字段的类型等，大差不差的内容.我们需要的就是把每一行都通过 "字段: 值，字段: 值..." 的形式连接起来，然后用个大括号来包裹即可.

我们后面加一列，通过 Excel 的函数，来获取我们需要的数据.给策划新人们一个建议，就是函数要一步一步地写，哪怕一共有十几步，每一步的结果都放在一列里，确认结果正确后，再拼接到一起，写一个超长的函数 (不能超过 255 个字符), 然后删掉中间的步骤，云淡风轻地对旁边的人说:"不就是这样简单吗?"

第一步:

=INDEX($A$1:$F$1,COLUMN(A2))

index 函数用来获得字段名，第一个参数 $A$1:$F$1 是第一行需要的字段范围，注意加上 $ 符号，快捷键是 F4...(自己搜索吧，有一种教人 ' 这是冰箱 ' 的感觉), 第二个参数是当前值所在的列号，如果前面还有空行的话，这里要减去相应的数量.

横着一拖，竖着一拖，看到了需要的内容是我们要的 key.

接着连接冒号和每一行的值:

=INDEX($A$1:$F$1,COLUMN(A2))&":"&A2

然后使用 textJoin 函数，连接在一起，外面接上大括号就好了.

="{"&TEXTJOIN(",",TRUE,G2:L2)&"}"

有人问了，所需要的 json 的 key 有引号的啊，要怎么办?

最简单的就是字段名字就给他加上引号!

到这里主体部分就完成了，每一条 {} 大括号里面的，都是个 json 的对象.对象之间用 [] 包裹起来，就是一个 json 的列表，或者还有 {} 包裹组成 kv 对，那么就只需要在把拼接的内容再次按照要求拼接就好了.

有人可能会问了，最重要的云淡风轻，怎么还没讲? 这么多辅助列，明明是手忙脚乱啊? 怎么能写到一个函数里面啊?

别急，这里我们要用到数组公式.直接上公式:

{="{"&TEXTJOIN(",",TRUE,INDEX($A$1:$F$1,COLUMN(A2:F2))&":"&A2:F2)&"}"}

只要把值从一个格子 A2, 变成一个范围 A2:F2, 然后按 CTRL+SHITF+ENTER 输入数组公式就好了.(数组公式最外面的大括号不是打字打上去的...)

至此，终于可以云淡风轻了，因为一般程序猿也不太会用 Excel, 这样就显得你很专业了.

方法二: 自定义函数加载项

上面的例子中，有的字段的值是数组，用 [] 中括号引起来，例如

"keywords":["小猪","小肚","小鸡"]

或者自定义的类似 lambda 表达式的东西

scripts:(Count(Unit))=100&Count(Wonder)=3IsDead(Bob)

这个时候为了策划填表方便，可能每一列都有特殊的拼接方法，我们在第二行里面写上值的类型，示例如下:

这种会有很多特殊的，定制的内容，例如看到 lambda 的时候，赋值给一个临时变量名，例如

lambda1=(Count(Unit))=100&Count(Wonder)=3IsDead(Bob)

array 字段为了策划填写方便，用逗号分割起来，生成的时候还是要分别加上引号，并用中括号括起来

"keyword":["小猪","小肚","小鸡"]

面对这些定制化的需求，直接用 Excel 里的函数，就捉襟见肘了.那么我们来自己写一个，高度定制化的函数。

打开 Visual Basic 的 IDE，如果你的 Excel 不显示开发工具选项卡，则需要在自定义功能区里勾选一下。

插入一个模块，写入下列代码：

Function textToJson(ByVal s As Variant)Dim myKey,myValueDim valueTypeDim output'将单元格范围作为选中范围Dim mr As RangeSet mr = s'读取第一行的key，和当前的value组成一对For Each i In mr         If Not IsEmpty(i) And i <> 0 Then                       '通过第二行的类型来处理对应的值                valueType = Cells(2, i.Column)                myKey = Cells(1, i.Column)                myValue = i.value                                Select Case valueType                        'lambda把key特殊处理，加一个用行号作为序列号的变量                Case "lambda"                     myKey = "lambda" & i.Row - 2                       output = output & myKey & "=" & myValue & ","                                  'array把值特殊处理，将逗号分隔的字符串放在一个数组里               Case "array"                    temp = ""                       tempString = Split(i.value, ",")                       For Each k In tempString                            temp = temp & Chr(34) & k & Chr(34) & ","                       Next k                       temp = Left(temp, Len(temp) - 1)                       temp = "[" & temp & "]"                       myValue = temp                       output = output & myKey & ":" & myValue & ","                                 '情况不做处理              Case Else                  output = output & myKey & ":" & myValue & ","                      End Select      End IfNext i'最后拼接一下If IsError(output) Or Len(output) <= 1 Then    textToJson = ""Else    output = Left(output, Len(output) - 1)       textToJson = "{" & output & "}"  End IfEnd Function

这样我们定义了 textToJson（）这个函数，在最后一列里面输入 = textToJson (A3:F3) 即可转换。

如果其他表格也想使用该函数，但是不想转换成 xlsm 这带宏的格式，怎么办？

我们可以把这个文件另存为 xlam，作为加载宏，给其他表格使用。

其他表格使用时，通过开发工具》Excel 加载项》浏览找到该文件，即可使用 textToJson 这个自定义函数。

方法三：VBA

上面既然已经用了自定义函数，还要另存为等手动操作，那么不如使用 VBA 直接导出。写法基本一样，只不过创建了一个 json 文件作为 object 来承载导出的内容。注意，如果报出找不到对象的错误的话，那么可以去人民公园试试。

Sub toJson()Dim i, j, k As IntegerDim myString, output As StringDim myRange As RangeDim myArr()Dim myTitle()Dim WriteStream As ObjectSet MyFile = CreateObject("Scripting.FileSystemObject").OpenTextFile("D:\testjson.json", 8, True)myString = ""output = ""i = 0j = 0k = 0Set myRange = SelectionmyArr = myRangeReDim myTitle(20)For k = 0 To myRange.Columns.Count - 1    myTitle(k) = myArr(1, k + 1)    Next kFor i = 2 To myRange.Rows.Count      output = output & "{"                      For j = 1 To myRange.Columns.Count                                       If myTitle(j - 1) = "truth" Then                                myString = Trim(myArr(i, j))                                                          output = output & Chr(34) & myTitle(j - 1) & Chr(34) & ":" & LCase(myString) & ","                                    ElseIf myTitle(j - 1) = "tag" Or myTitle(j - 1) = "falseWord" Then                                            myString = Trim(myArr(i, j))                                              output = output & Chr(34) & myTitle(j - 1) & Chr(34) & ":[" & mySubString(myString) & "],"                                    ElseIf myTitle(j - 1) = "difficulty" Then                                        myString = Trim(myArr(i, j))                                           output = output & Chr(34) & myTitle(j - 1) & Chr(34) & ":" & myString & ","                                      Else                            myString = Trim(myArr(i, j))                                                          output = output & Chr(34) & myTitle(j - 1) & Chr(34) & ":" & Chr(34) & myString & Chr(34) & ","                             End If                                Next j                    output = Mid(output, 1, Len(output) - 1)           output = output & "}," & Chr(10)               Next i     output = Mid(output, 1, Len(output) - 2)    '    Set WriteStream = CreateObject("ADODB.Stream")''    With WriteString'         .Type = 2'                 .Charset = "UTF-8"''    End With              MyFile.WriteLine (output)                  MyFile.Close         Set MyFile = Nothing                  MsgBox "成功！！"         'UserForm1.TextBox1.Text = output         'UserForm1.Show              End Sub

以上代码是我们做过的一个答题游戏的例子，超过几万条有趣的问题。配置表可以稍微露一下：