PowerShell Excel、CSVの詳細紹介

Powershell for Excelのいくつかの簡単な操作

　　少し高度な言語ではCOMオブジェクトの操作があり、マイクロソフト独自の進化したスクリプト言語であるpowershellがこの機能を持つことは珍しいことではありません。WMIやCOMの管理はpowershellを使う方が簡単です。

　　powershellをマイクロソフトの公式な言葉で要約すると、こうなる。

　　Windows PowerShell™ は、システム管理のために特別に設計されたタスクベースのコマンドラインシェルおよびスクリプト言語です。.NET Framework上に構築されたWindows PowerShell™は、IT専門家やパワーユーザーがWindowsオペレーティングシステムとWindows上で実行するアプリケーションの管理を制御し、自動化するのに役立ちます。

　　 Windows PowerShellに組み込まれたコマンド（コマンドレットと呼ばれる）を使用すると、組織内のコンピュータをコマンドラインから管理することができます。Windows PowerShell™ プロバイダーを使用すると、ファイルシステムにアクセスするのと同じように、レジストリや証明書ストアのようなデータストアに簡単にアクセスすることができます。さらに、Windows PowerShell™は、機能豊富な式パーサーと、完全に開発されたスクリプト言語を備えています。

　　パワーシェルでエクセルに何ができるかを簡単に説明します。

　　まず、システム上で現在動作しているすべてのプロセスを取得するコマンドを実行します。

Get-Processコマンドレットは、オープンハンドルの数、様々なメモリ使用量の表示、CPU使用量のスナップショットなど、現在のローカルプロセスの状態を表示します。powershellとLinuxシェルの大きな違いは、システム関数とコマンドのいずれにおいても、大文字と小文字が区別されないということです。これにより、間違いを犯す確率が低くなります。

　　なぜこんなことを言われるのかと思われるかもしれません。システム管理者にとって、長いデータの裏には多くの詳細があります。get-processはローカルプロセスを取得するもので、もちろん-computernameパラメータを使えばリモートコンピュータにアクセスできますが、現時点ではネットワーク管理者にとってこれは幻です。そのため、リモートシステムを監視して情報を実用的に表示するには、Windows WMIやWin32_Process WMIクラスを使って行う必要があります。Get-Processの出力が非常に有益だと思うのなら、Win32_Processの出力をよく見てみてください。

　明らかに、この出力は詳細であるが、あまり読みやすいものではない。管理者が必要とするのは、読みやすいメモリ使用量のレポートである。これを自動化するために、Excelに切り替えることができます。

　Excelを自動化するのは難しいですか？実はとても簡単です。MicrosoftはExcelを操作するために特別に自動化モデルを作成したからです。プログラムIDはExcel.Applicationで、これはCOMオブジェクトである。Applicationオブジェクトは、デフォルトでExcelが起動し、実行されますが、あなたはそれを見ることはできません。しかし、visibleプロパティを使えば、Excelを見えるようにすることができます。

Applicationオブジェクトを作成し、visibleプロパティの状態を問い合わせ、visibleプロパティをtrueに設定してください。

　　すると、Excelアプリケーションのシェルのような、とても奇妙なExcelビューが表示されます（下図）。ワークブックもスプレッドシートもない、むき出しのExcelです。

もちろん、これは確かに使えないが、ワークブックを作成するための前提条件であり、ワークブックを収納するコンテナである。では、アプリケーションにワークブックを追加する必要があります。これを行うには、ワークブックオブジェクトのaddメソッドに頼る必要があります。ワークブックオブジェクトは、メインのExcelからアクセスします。

さて、特定のスプレッドシートを接続する必要があります。デフォルトでは、Excelにワークブックが追加されると、3つのスプレッドシート（sheet1、sheet2、sheeet3）が追加されます。これらのスプレッドシートは番号で識別することができます。次のコード行では、最初のスプレッドシートがリンクされ、返されたスプレッドシートオブジェクトが $sheet という変数に格納されています。

ここで、もう一度 $excel.Visible = $true を実行して、excel がどのように見えるかを確認します（ワークブックが構築され、sheet1 が選択されている状態）。

これで、スプレッドシートにデータを書き込むことができるようになりました。Excelのスプレッドシート内の情報は、セルに格納されています。セルはスプレッドシートの中にあるので、変数 $sheet に格納されているスプレッドシートオブジェクトを使って、特定のセルにアクセスすることができます。これは、スプレッドシート内の行と列を参照する数値を使用することで実現します。Excelのスプレッドシートでは、行が数字で、列が文字であることがやや紛らわしいです。しかし、自動化モデルを使用する場合、行と列の両方が数字になります。最初の数字が行で、2番目の数字が列です。セルは、特定のセルに値を代入するだけで書くことができる：。

この時点で、再度 $excel.Visible = $true を実行し、excel がどのように見えるかを確認します。

幸いなことに、余分なスプレッドシートはオートメーションで削除することができます。同じ操作で、2 番目のスプレッドシートを削除します。　

　　 これでsheet1だけが残りました。次に、残りのスプレッドシートの名前を変更します。これは重要です。なぜなら、Excelスプレッドシートを照会するためにADO（ActiveX Data Object）を使用することにした場合、このスプレッドシート名を接続文字列で使用することになるからです。したがって、コードが読みやすく直感的に理解できるように、スプレッドシートには論理的な名前を付けておく必要があります。スプレッドシートの名前を変更するには、特定のスプレッドシートの name プロパティに値を代入するだけです。以下のコードでは、最初のスプレッドシートの名前を "Processes"に変更します。　

　　ここで、名前を変更したスプレッドシートを接続する必要があります。WorksheetオブジェクトのItemメソッドを使用し、スプレッドシートの名前を代入してください。　

　  この時点で、次にエクセルがどのように見えるかを見てみます。

上記のテスト手順のコードは以下の通りです。

ここで、プロセスからエクセルへのコンテンツの移植を開始します。
　　スプレッドシートの最初の行には、ヘッダー情報が表示されます。境界線が引かれ、属性名が太字で表示されます。データは 2 行目から始まるので、カウンタ変数 $x に値 2 が代入されます。

　　　次の4行のコードでは、4つの列挙型を作成しています。列挙型は、特定のオプションタイプでどのような値の記入が許されるかをExcelに伝えるために使用されます。たとえば、xlLineStyle列挙型は、描画される線の種類（二重線、破線など）を決定するために使用されます。これらの列挙型の値については、MSDNで詳しく説明されています。
コードを読みやすくするために、使用する列挙型ごとにショートカットのエイリアスを作成します。事実上、列挙名を表す文字列を[type]に変換することになります。　

　　さて、最初の行は書式を整える必要があります。フォントを太字にし、線をxlDashDotと定義し、色の自動割り当てを可能にし、ボーダーの幅を中太に設定します。

終わったら、itemメソッドでセルを選択し、行と列の座標を指定して、最初の行に値を割り当てます。次に、列の見出しを直接代入で書きます。

　　さて、WMIクエリによって生成された$processes変数に格納されているプロセス情報を、適切なセルに入れる必要があります。プロセス情報のコレクションを繰り返し処理するには、foreach ステートメントを使用します。変数 $process をコレクション列挙子 (プレースホルダ) として定義し、name および workingSetSize プロパティをそれぞれ 1 列目および 2 列目に書き込むように選択します。ここで、変数$xが登場します。2 行目からプロセス・コレクションを走査しながら、$x 変数の値をインクリメントして、コレクション内の現在の行を常に指すようにします。プロセス情報コレクション $processes に格納されているすべてのデータは、次のコードでソートできます。　

　　Excelのスプレッドシートに記入した後、列のサイズを変更して、セルに格納されているデータと同じサイズにすることができます。これを行うには、使用する列の座標を指定して範囲を作成します。ただし、スプレッドシートのusedRangeプロパティをそのまま使用することもできます。範囲オブジェクトを作成したら、EntireColumnプロパティを選択し、AutoFitメソッドを使用して列のサイズを変更します。このメソッドは常にデータを返すので、結果を Out-Null コマンドレットに渡すことで、コンソールが無駄な情報で散らかるのを防いでいます。以下は、使用したコードです。

　　この時点で、すべてのタスクが完了しました。この時点のスプレッドシートには、すべてのプロセスの名前とメモリワーキングセットが含まれており、非常に素晴らしいです。完成した結果を見るには

上記の結果は、私のコンピュータで実行されているプロセスです。表示するフィールドは2つだけ選んでいます。

スクリプトのコード全体については、以下の通りです。

実際、実行するとわかりますが、処理中の各フィールドは、後の記事で説明するように、一度にエクセルにコピーされるのではなく、セルごとに記入されるため、非常にゆっくりとエクセルに記入されます。

　　注）この記事は、マイクロソフトの公式ブログを参考に、私なりに理解して書いたもので、この後、powershellによるexcel&CSVの操作の他の応用例を簡単に紹介する予定です。

　　もし間違いがあれば、遠慮なく訂正してください！ありがとうございました。