Excel の表から Json を出力する関数

つまり

ID	NumberProp	ArrayProp	StrArrayProp	BoolProp
Areare	1	[1,2,3]	["aaa", "bbb"]	TRUE
Hugahuga	2	[4,5,6,7]	["ccc", "ddd", "eee"]	FALSE

こんな表を

[
  {
    "ID": "Areare",
    "NumberProp": 1,
    "ArrayProp": [1,2,3],
    "StrArrayProp": ["aaa", "bbb"],
    "BoolProp": true
  },
  {
    "ID": "Hugahuga",
    "NumberProp": 2,
    "ArrayProp": [4,5,6,7],
    "StrArrayProp": ["ccc", "ddd", "eee"],
    "BoolProp": false
  }
]

こうしたい。
関数として独立した処理にしましょうね。責任分界点はちゃんとしようね。シートの中でも使えるよ。

ほかに試した方法

Excel から CSV を出力してそれを Json にする

値がぜーんぶ文字列になる。そらそうだ。 CSV 上じゃ数字も真偽値も全部文字列だ。

コード

• 上に書いたみたいなフラットなJsonしか出てこないよ
• ファイルに出力したい時とかはこれ挟んだマクロまた書いてね
• 「[ で始まる文字列として扱いたいもの」が来たらアウトだね。そもそも DynamoDB の設定ファイル量産するためのマクロだから、多少はね。

Function rangeToJsonString(rangeP)
    '入力は二次元配列とかセル範囲とか行けるはず
    Dim range
    range = rangeP 'なんかこうするとセル範囲オブジェクトが渡ってきた時に .Value2 がいいかんじに入ってくれる。
    
    len_row = UBound(range, 1) '行数
    len_col = UBound(range, 2) '列数

    ReDim heads(len_col - 1) '見出し、0から列数-1まで
    ReDim recs(len_row - 2) 'レコード、0から行数-2まで
   
    'データの取得とJSONの生成
    For c = 1 To len_col
        '1行目の各セルを見出しとして取得。
        heads(c - 1) = range(1, c)
    Next c
    For r = 2 To len_row
        '2行目から最下行まで処理。
        ReDim temps(len_col - 1) '0から列数-1まで
        For c = 1 To len_col
            cellData = range(r, c)
            dataStr = ""
            'データ種別の判定
            dataKind = VarType(cellData)
            Select Case dataKind
                Case 8 'Stringの時
                    If Left(cellData, 1) = "[" Then '配列ならそのまま
                        dataStr = cellData
                    Else
                        dataStr = Chr(34) & cellData & Chr(34) '文字列ならダブルクォーテーションをつける
                    End If
                Case 11 'bool
                    If cellData Then
                        dataStr = "true"
                    Else
                        dataStr = "false"
                    End If
                Case Else 'そのほかはそのまま
                    dataStr = cellData
            End Select
            
            '各行の各セルを取得し見出しと組み合わせる。
            temps(c - 1) = "    " & Chr(34) & heads(c - 1) & Chr(34) & ": " & dataStr
        Next c
        recs(r - 2) = "  {" & vbCrLf & Join(temps, "," & vbCrLf) & vbCrLf & "  }"
    Next r
    rangeToJsonString = "[" & vbCrLf & Join(recs, "," & vbCrLf) & vbCrLf & "]"
   

End Function