マイナー・マイナー

隠れた名作の発掘が生きがい。

【VBA】JSONファイルの内容をEXCELファイルに読み込む


スポンサードリンク

JSONファイルから各キーの値を読み込んでEXCELファイルに出力するサンプルプログラムを書きました。VBA-JSONを利用してJSONデータをパースします。

f:id:yosinoo:20161010233251p:plain

準備

下記よりVBA-JSONをダウンロードします。ここではVBA-JSON-2.2.1.zipをダウンロードしました。
https://github.com/VBA-tools/VBA-JSON/releases


解凍すると「JsonConverter.bas」が含まれているので、これをインポートします。VBAの開発ツールを開き、File -> Import Fileより「JsonConverter.bas」を選択します。


また、Tools -> Referencesより「Microsoft Scripting Runtime」にチェックを入れます。

データモデル

上図のようなJSONデータを保持するTypeを用意します。

JsonTypes

Option Explicit

' メンバタイプ
Public Type Member
    id As Integer
    name As String
    age As Integer
End Type

' チームタイプ
Public Type Team
    teamName As String
    members() As Member
End Type

JSONファイルリーダー

JSONを読み込むクラスモジュールを次のように書きました。

JsonReader

Option Explicit

Dim jsonObj As Object

' JSONファイルをロードする
Public Sub LoadJsonFile(ByVal fileName As String)
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
   
    Dim jsonTS As textStream
    Set jsonTS = fso.OpenTextFile(fileName, ForReading)
   
    Dim jsonText As String
    jsonText = jsonTS.ReadAll
   
    jsonTS.Close
   
    Set jsonObj = JsonConverter.ParseJson(jsonText)
End Sub

' JSONデータをロードする
Public Function GetTeam(ByRef teamObj As JsonTypes.Team)
   
    ' 項目の読み込み
    teamObj.teamName = jsonObj("team_name")
   
    ' 配列の読み込み
    Dim Values As Variant
    ReDim teamObj.members(jsonObj("members").count - 1)
   
    Dim i As Integer
    For i = 1 To jsonObj("members").count
        ' メンバの作成
        Dim memberObj As JsonTypes.Member
        memberObj.id = jsonObj("members")(i)("id")
        memberObj.name = jsonObj("members")(i)("name")
        memberObj.age = jsonObj("members")(i)("age")
        teamObj.members(i - 1) = memberObj
    Next
   
End Function

' デストラクタ
Public Sub Class_Terminate()
    If Not jsonObj Is Nothing Then Set jsonObj = Nothing
End Sub


LoadJsonFIleでJSONファイルからデータを読み込み、JSONオブジェクトとして内部に保持します。JSONファイルを文字列として読み込み、JsonConverter.ParseJsonでその文字列をパースします。


GetTeamでは、JSONオブジェクトをデータモデルに従ったオブジェクトに変換します。JSONオブジェクトにキーを指定と、そのキーに対する値を取得できます。配列を読み込むときは、オブジェクトのcountが要素数を示すことを利用してループ処理で値を取得します。ただし、JSONオブジェクトの配列のindexは1から始まりのようなので、注意。

メイン処理

JsonReaderを利用して、JSONファイルから読み込んだ内容をExcelのセルに出力するモジュールを次のように書きました。

SampleJsonReaderModule

Option Explicit

Sub ReadSample()

    ' 入力ファイル
    Dim inputFileName As String
    inputFileName = "C:\sample.json"
   
    ' JSONファイルの読み込み準備
    Dim jr As JsonReader
    Set jr = New JsonReader
    jr.LoadJsonFile (inputFileName)
   
    ' JSONデータよりデータを読み込む
    Dim teamObj As JsonTypes.Team
    Call jr.GetTeam(teamObj)
   
    ' 結果の表示
    Cells(1, 1) = "team_name"
    Cells(1, 2) = "id"
    Cells(1, 3) = "name"
    Cells(1, 4) = "age"
   
    Dim rowIndex As Integer
    rowIndex = 2
    If Sgn(teamObj.members) <> 0 Then
        Dim i As Integer
        For i = 0 To UBound(teamObj.members)
            Cells(rowIndex, 1) = teamObj.teamName
            Cells(rowIndex, 2) = teamObj.members(i).id
            Cells(rowIndex, 3) = teamObj.members(i).name
            Cells(rowIndex, 4) = teamObj.members(i).age
            rowIndex = rowIndex + 1
        Next i
    End If
   
    Set jr = Nothing
   
End Sub


実行すると、team_name、id、name、ageが1〜4列目に出力されました。