このところ、JSONを扱うことが多くなってきました。
JavaScript を操る人にはお馴染みの JSON ですが、データ作成はどうしているのでしょうか?
・CSVからの変換で間に合っている
・Web上のツールで基本を作って後はText Editor
・システム化しているからあまり関心がない…
ちょっとしたデータなら Text Editor でも間に合いますが、大規模なものだと大変だと思います。
ExcelやCSVからの変換ツールも多数存在しますが、一つのテーブルを JSON にするだけのものが殆どです。
これでは CSV を利用しているのと大差ありません。
私が考えるに、JSON の最大の特徴はレコードとテーブルをオブジェクトとして扱い、それらを階層化できることです。
CSVのような単純な構造を JSON にするだけでは勿体無いないのです。
そこで以下のルールに従い、Excel の「複数テーブル」から一つのJSONデータを作る新API GetJsonFromTable() を
Excel PowerShell Tool に追加しました。
[テーブル]
テーブルの各一行(=レコード)がオブジェクト単位である
テーブル全体も一つのオブジェクトで、オブジェクトの配列である
ただし、一行だけのテーブルは配列として扱わない
テーブルの列名がキー
ただし、一列だけのテーブルはキーの存在は無意味なので値のみとする
[セル]
改行コードは "\n" に置換
数値の場合は Value2 を採用
・セルが文字列書式
自動的に "" を付加
・セルが標準書式
TRUEは true、FALSEは false、空セルは null として扱う
数値として判定できるものは数値、
"#"で始まる文字列はテーブルリンク
"["で始まる文字列は配列として、加工しない
それ以外は文字列として自動的に "" を付加
・それ以外の書式
Range.Textで評価(※幅が足りない場合は ##### 等で評価されるので注意)
数値として判定できるものは数値、
それ以外は文字列として自動的に "" を付加
[テーブルリンク]
#テーブル名 [範囲]
範囲の書式
数値指定された一行
数値:数値指定された複数行
列名=値テーブル名の列で、値により見つかる最初の一行
最大の特徴は、"テーブルリンク" と呼ぶ、テーブル間の結合です。 #テーブル名 [範囲]で指定します。
これにより、複数のテーブルをまとめて1つのJSONにまとめることが可能になっています。
リンクするテーブとリンクされるテーブルは階層構造をもち、プロパティによるアクセスが可能です。
例えば、メンバーを管理するテーブルを用意しましょう。
Security に関しては項目が多数ありそうなので、別のテーブル"Security"で管理したいところです。
Security Level が高い人の MemverOf は沢山ありそうなので、またまた別のテーブルで管理したいところです。
これくらい用意すれば、将来の性能インフレにも耐えられそうです(笑)
では、これらの3つのテーブルをリンクさせてみましょう。"メンバー" と "Security" にテーブルリンクを記入します。
このシートからJSONを得るためのスクリプトを示します。
#@PSTOOL
$addin = $_Excel.ComAddins.Item("ExPSTool").Object
# "メンバー"テーブルを指定して、JSON文字列を取得
$json = $addin.GetJsonFromTable("メンバー")
# $json は単純なStringなのでそのままだと一つのCellに表示してしまう
# 改行コードで Split してやればOK
$json.split("`n")
…これだけですww
得られる JSONのデータです。実際には改行コードは入っていますが、インデントは手抜きしています。
整形されたJSONを得たい場合には JSONLint がお勧めです。
[
{
"ID": 1,
"Name": "伊藤",
"Tel": "000-111",
"GropuName": "営業",
"Security": {
"Level": 1,
"MemberOf": "あいうえお",
"Description": "初心者"
}
},
{
"ID": 2,
"Name": "加藤",
"Tel": "000-112",
"GropuName": "開発",
"Security": [
{
"Level": 2,
"MemberOf": [
"C",
"B"
],
"Description": "中堅"
},
{
"Level": "ABC",
"MemberOf": [
"C",
"B",
"A"
],
"Description": "ベテラン"
}
]
},
{
"ID": 3,
"Name": "鈴木",
"Tel": "000-114",
"GropuName": "マーケティング",
"Security": {
"Level": "ABC",
"MemberOf": [
"C",
"B",
"A"
],
"Description": "ベテラン"
}
},
{
"ID": 4,
"Name": "田中",
"Tel": "000-119",
"GropuName": "経理",
"Security": {
"Level": "ABC",
"MemberOf": [
"C",
"B",
"A"
],
"Description": "ベテラン"
}
},
{
"ID": 5,
"Name": "佐藤",
"Tel": "000-222",
"GropuName": "営業",
"Security": {
"Level": 4,
"MemberOf": [
"C",
"B",
"A",
"S",
"SS",
"SSS",
"SSSS",
"SSSSS"
],
"Description": "神"
}
}
]
複数のテーブルをまとめることができると、とても便利です。
これは RDB と同一の思想で、Excel の応用範囲が広がります。
もちろん本格的な SQL が使えるわけでなないですが、 多くの場合これくらいの機能で十分ではないでしょうか?
Excel + Json + PowerShell で大抵のデータを扱える気がして来ました。
GetJsonFromTable() を追加したことで、PS Tool は Version 1.1 になります。
それも、まだ私が JSON の経験不足な点もあるのでβ版としてテスト公開します。
Ver 1.1 からは PowerShell 3.0 が必須になります。
Win7 の環境では Windows Update のオプション扱いなので注意してください。
※もしかすると、もう必須扱いか?
Windows 7 SP1 および Windows Server 2008 R2 SP1 用の Windows Management Framework 3.0 について
日頃 JSON のデータ編集や管理で苦労されている方、是非使ってみてください。
そしてご意見等お願いします。
ダウンロード↓↓↓
ExPSTool-1-1-beta.zip (470.35 kb)