しばらく更新を怠っていたら Windows 8.1 が発売されてしまいましたww

hp 2760p を使っているので早速入れてみたわけですが…良いではないですか！
例の Start Screen に関しては、やはり Classic Shell が神ソフトであると再認識しました。
でもまぁ、これで Windows 8.x は普及期に入って行くと思います。

エクスペリエンスインデックスとは、Vista から導入された簡単な性能評価システムです。
ところが、Win 8.1 では何故か GUI が廃止されてしまいました。便利なんですけどね…

参考： Windows 8.1で消えたマシン性能測定機能、実はある？（ASCII.JP)

エクスペリエンスインデックスの実体は WinSAT.exe コマンドなのですが、これは残っています。
ということで、 PS Tool を使って Excel 上でエクスペリエンスインデックスを再現してみました。 
管理者として Excel を起動してから実行してみてください。  

 ダウンロード ↓ ↓ ↓ 

エクスペリエンスインデックス.xlsx (12.21 kb)

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

Yahoo! の Web API 一覧を見ていたらショッピングWeb APIがありました。

Yahoo! JAPANが提供するショッピングWebAPI

以前、VSTOで Amazonや楽天のサービスを使い画像を取得するアドインを組んだことがありますが、
それと同様のことが Excel PowerShell Tool でも簡単に構築できそうです。 
…ということで作ってみました。 サンプルとして机上にあった商品を検索してみました。

簡単に出来るつもりでしたが、結構ハマりました。
Invoke-RestMethod で XMLデータを取得すると何故か文字化けをするのです。
Encode には UTF-8 を利用しているし、これといった問題は見つかりません。
念の為に UTF-8 -> UTF-16(=Unicode) への手動変換とかしてみましたが効果なし。
真面目に調べると時間がかかるので、JSONにて処理をしたところ普通に動きました。 
※ただし、ドキュメントとは微妙にエントリー位置が異なったりする
Invoke-RestMethod + XML に関しては後日検証しなければ… 

MSはWebサービスに対してSOAP形式を推していましたが、世の中はREST+JSONが主流です。
REST+JSONは必要な機能を備えているし、テキストベースでも開発できますからね。
ExcelでJSONデータの編集をするツールでも作ってみるかな～

天気予報が簡単だったので、調子にのって「でんき予報」も作ってみました。
使えそうなサービスはないかと探した所、Yahoo! Japan のWebサービスが充実しています。 

YAHOO! Japan デベロッパーネットワーク

いろいろなサービスがありますが、それらを使うには「アプリケーションID」を取得する必要があります。
このIDはおいそれと公開するものではないので、今回はスクリーンショットにとどめておきましょう…

先日からの PS Tool 用ソースの使い回しなので、2時間程度で形になりました。
興味の有る方はご連絡ください。 

ブラウザで得られる情報をExcelに取得するのに意味があるのか? という質問がありましたが、大いにあります。
Excel 上で行われる作業は個人や会社のノウハウの塊です。
いくらブラウザが進化しても、Excel の必要性が無くなるとは思えません。

Excel で重要なのは、必要なデータを素早く正確に取得することです。手打ちは出来るだけ避けたいところです。
PS Tool を使ってデータ取得の自動化に挑戦してみてください。

昨日の気象情報取得で使ったのは、WebServiceX.Net という海外のサイトです。
データが英語なのでちょっと使いにくいかもしれません。
もう少し一般的な情報源はないかとさがしたところ、ライブドアの天気予報を見つけました。

ライブドアお天気Webサービス

このサービスは XML ではなく JSON でデータを返してくれます。
…実は JSON を扱った事がなかったのですが、便利なコマンドレットがありました。

Invoke-RestMethod

このコマンドレットは、PowerShell 3.0から追加された Webサービス用のものです。
特筆すべきは、XML/JSONのパースを自動的に行う点で、何も考えずにサービスが利用できます。 
便利過ぎます。 

#@PSTOOL

$ws = $_Excel.Activesheet
$ws_master = $_Excel.ActiveWorkbook.Worksheets.Item("Master")
$addin = $_Excel.ComAddins.Item("ExPSTool").Object

#==== 天気情報格納用の型
$MyType = @"
    public class OtenkiType {
        public string dateLabel;
        public string date;
        public string telop;
        public string imageurl;
        public string temperature;
    }
"@
#--- 型の追加(※既存の場合はエラーになるので SilentlyContinue)
Add-Type -TypeDefinition $MyType -ErrorAction:SilentlyContinue

$Otenki = New-Object -TypeName OtenkiType

$city = $ws.Range("LWWSId").Value2
$json = Invoke-RestMethod -uri "http://weather.livedoor.com/forecast/webservice/json/v1?city=$city"

#--- 概況等は直接出力
$ws.Range("LWWS_URL").Value2 = $json.link
$ws.Range("LWWS_Time").Value2 = $json.publicTime
$ws.Range("LWWS_Description").Value2 = $json.description.text

#--- 3日分のデータはMasterシートのテーブルに出力して参照する
$List = $ws_master.ListObjects.Item("LWWSResultTable")
$rg = $List.HeaderRowRange
$addin.SetStartRange($rg)

$json.forecasts | foreach {

  $Otenki.dateLabel = $_.dateLabel
  $Otenki.date = $_.date
  $Otenki.telop = $_.telop
  $Otenki.imageurl = $_.image.url

  $min = $_.temperature.min.celsius
  $max = $_.temperature.max.celsius
  $Otenki.temperature = "最低:"+$min +"  最高:"+$max

  $Otenki

}

#--- せっかくだからお天気アイコンも付ける
$rg = $List.ListColumns.Item("imageurl").Range
$shape = $ws.Shapes.Item("LWWS_Img0")
$shape.Fill.UserPicture($rg.Cells.Item(2, 1).Value2)
$shape = $ws.Shapes.Item("LWWS_Img1")
$shape.Fill.UserPicture($rg.Cells.Item(3, 1).Value2)
$shape = $ws.Shapes.Item("LWWS_Img2")
$shape.Fill.UserPicture($rg.Cells.Item(4, 1).Value2)

PowerShell環境からExcelのオブジェクトを操作する際のコストは非常に大きなものがあります。
詳しい理由は知りませんが、COMオブジェクトとPSObjectの変換コストが原因だと思われます。
よく使う機能は DLL 化するなりの工夫が必要ですが、PS Tool の表出力はそこそこ速度があるので
直接Cellに書くような事をしないで、表形式で出力をしてやり、Excel上では式による参照をするのが効果的です。
今回は天気予報用の型を用意し、3日分のデータを表形式で出力しています。
文字だけでは芸がないので、Excelの図形に天気アイコンを埋め込みそれっぽくしてみました。 

東京の天気予報

那覇の天気予報。あぁ、沖縄に行きたいww

PS Tool を使えば、Excel上でWebサービスの恩恵をうけることが可能になります。
天気予報などは一般的な例ですが、企業の内部データを直接Excel上に展開するといった使い方は便利でしょう。
何か案件がありましたご連絡ください。

ダウンロード ↓↓↓
天気予報.xlsx (31.31 kb)