Excel の複数テーブルから JSONデータを作る

by manamana 12. July 2013 18:00

このところ、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)

Tags:

PowerShell

Comments (1) -

名前無し
1/23/2014 7:53:24 PM #

テストデータ自動作成ツール
Excelを利用して直接シートにデータを自動生成します。
作ったデータをExcelから直接データベースへ登録できます。
www.vector.co.jp/soft/winnt/business/se475115.html

Reply

Add comment


スポンサーリンク

Calendar

<<  July 2019  >>
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar

Month List

Twitter