なんかふと気になったのでExcelでJSONを読み取る方法を探した。
JSONパース自体はこちらを参考にさせてもらった。
ExcelでJSONを扱う場合のつまずきポイント
まず前提として、外部ファイルであるJSONファイルを開き読み込んでシートに一覧出力する、
という処理を行いたい。
##JSONの構造
JSONの構造は下記。
{"tweet":[
{
"id":"0001",
"daytime":[
{
"year":"2019",
"month":"01",
"day":"01",
"hour":"12",
"minute":"00"
}
],
"contents":"テストテストテスト"
},
{
"id":"0002",
"daytime":[
{
"year":"2019",
"month":"01",
"day":"01",
"hour":"12",
"minute":"00"
}
],
"contents":"hogehoge"
}
]}
##JSONファイルを読み込む
ということで、以下がファイル入力のコード。
Dim buf as String
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile "./tweet.json"
buf = .ReadText
.Close
End With
JSONファイルには日本語も含まれているので、
文字コードを選択できるADODBを呼び出す方法を使った。
##読み込んだ内容をパースする
上のリンク先コードを活用させてもらった。
Dim obj as Object
Dim json as Object
Set obj = CreateObject("ScriptControl")
obj.Language = "JScript"
obj.addcode "function jsonParse(s){ return eval('('+s+')');}"
Set json = obj.CodeObject.jsonParse(buf)
これで読み込んだJSONファイルを使う準備は整った。
##読み込んだJSONファイルを使う
まず先ほども参照させてもらった上のリンク先の方法。
Set json = obj.CodeObject.jsonParse("{id:5,name:'山田',age:20}")
MsgBox CallByName(json, "name", VbGet)
値として「name」を渡してやることで、「name」の右辺である「山田」という値を取り出している。
「id」を渡すと「5」、
「age」を渡すと「20」が、
返ってくるのは想像できた。
何となく既視感あるなと思っていたが、やっと似たものを思い出す。
そう、連想配列だ。
ちなみに個人的にPHPでJSONを扱うときの連想配列を想像するのがとても理解しやすかった。
先ほどの例で行くと、PHPでは下記のようになる。
echo $json["name"];
つまり、一個のCallByNameがPHPでの[]になるのだ。
それを踏まえて。
最初に前提として提示したJSONで、一つ目のcontentsの中身を取り出したい場合、
PHPでは下記のようになる。
echo $json['tweet'][0]['contents'];
つまりマクロでは[]の個数分CallByNameを呼べばいいということになる。
contents = CallByName(CallByName(CallByName(json, "tweet", VbGet), 0, VbGet), "contents", VbGet)
「day」の値を取る場合は下記。
day = CallByName(CallByName(CallByName(CallByName(CallByName(json, "tweet", VbGet), i, VbGet), "daytime", VbGet), 0, VbGet), "day", VbGet)
##個数を取得したい場合
上記のJSONで配列値の個数を知りたい時。
splt = Split(CallByName(json, "tweet", VbGet), ",")
For i = 0 To UBound(splt)
MsgBox CallByName(CallByName(CallByName(json, "tweet", VbGet), 0, VbGet), "id", VbGet)
Next i
配列値の上の階層?までをCallByNameして、その値を「,」でSplitで分割する。
そしてその分けられた配列数をUBound関数で調べて、その値を使ってfor文を回せばいい。