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

2022-11-29 13:09 来源：网络作者：佚名

用手机看

扫描二维码随身看资讯使用手机二维码应用扫描右侧二维码，您可以
1.在手机上细细品读~
2.分享给你的微信好友或朋友圈~

实际开发过程中，策划习惯使用 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,myValue
Dim valueType
Dim output
'将单元格范围作为选中范围
Dim mr As Range
Set 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 If
Next i

'最后拼接一下
If IsError(output) Or Len(output) <= 1 Then
    textToJson = ""
Else
    output = Left(output, Len(output) - 1)   
    textToJson = "{" & output & "}"  
End If
End Function

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

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

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

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

方法三：VBA

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

Sub toJson()

Dim i, j, k As Integer
Dim myString, output As String
Dim myRange As Range
Dim myArr()
Dim myTitle()
Dim WriteStream As Object


Set MyFile = CreateObject("Scripting.FileSystemObject").OpenTextFile("D:\testjson.json", 8, True)

myString = ""
output = ""

i = 0
j = 0
k = 0

Set myRange = Selection
myArr = myRange

ReDim myTitle(20)

For k = 0 To myRange.Columns.Count - 1

    myTitle(k) = myArr(1, k + 1)
    
Next k


For 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

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