Hey, Scripting Guy!Excel の活用方法を学習する

Microsoft Scripting Guys

先月からこの Scripting Guy は、オーストラリアで暮らしながら仕事をしていますが、オーストラリアの同僚たちの創造性とあらゆる面でのたくましさに常に驚かされています。数日前の晩、そこに住んでいる友人が Scripting Guy と彼の奥さんを夕食に誘ってくれました。彼は、レストランは宿泊先のホテルから歩いてすぐだと言いました。

40 分後、素敵なレストランに到着しました。そのレストランからはシドニー湾を一望することができ、シドニー オペラ ハウスがカラフルな浜に打ち上げられた貝殻のようにきらめいていました。世界で最も長い単一アーチの橋であるハーバー ブリッジは、2 枚の曲がった板を大きな留め金で押さえるように 2 つの半島を結び付けて、その地位を誇示していました。

米国では、40 分間のウォーキングを夕食までのちょっとした散歩とは呼びません。これは、15 分間の準備運動とストレッチから始まる日常的なトレーニングです。

この Scripting Guy がオーストラリア旅行を楽しんでいる理由の 1 つは、オーストラリアの同僚がさまざまな考え方を持っているからです。彼らは独特な物の見方をするようで、その見方は私がこうあるべきと思っている先入観とよく対立します。

スクリプトを作成する場合、実証済みの方法に忠実に従えば、パターンどおりの結果を得ることはとても簡単です。同じ手法を使用して同じ結果を得ることが必要な状況は数多く存在します。

監視という視点で考えると、たとえば Windows PowerShell を使用すると、ローカル コンピュータのプロセス使用率をキャプチャしたすばらしいスナップショットを簡単に取得できます。Get-Process コマンドレットを使用すると、図 1 のようなきちんと整理された結果が返されます。

fig01.gif

図 1 Get-Process を使用したローカル プロセスの確認

Get-Process コマンドレットの結果は、多くの状況で役立ちます。この結果には、開いているハンドルの数、いくつかの異なる形式で表されたメモリ消費量、CPU 使用率のスナップショットなどが含まれます。また、Windows PowerShell 2.0 がリリースされれば、Get-Process と –computername パラメータを使用して、リモート コンピュータからこのような概要情報を取得できるようになります。このような便利な方法が存在するのにもかかわらず、なぜわざわざ他の方法を吟味する必要があるのでしょうか。

問題は、長いデータ列の中に多くの詳細情報が隠れていることです。このような大量のデータは、より重要な詳細情報を隠してしまう傾向があります。今後 Windows PowerShell 2.0 で –computername パラメータがサポートされるのはすばらしいことですが、今それがネットワーク管理者にとって役に立つかと言われれば、何の役にも立ちません。そのため、Windows Management Instrumentation (WMI) と WMI クラス Win32_Process を使用して、リモート システムの監視を実行し、本当に役に立つ形式で情報を提供する必要があります。Get-Process からの出力は気前が良すぎると思った皆さんは、図 2 に示された Win32_Process からの出力をご覧ください。

fig02.gif

図 2 WMI を使用したプロセスの確認

では、消費されるメモリの量をわかりやすくまとめたレポートがあれば十分という欲のないネットワーク管理者は、どうすればよいでしょうか。このような場合は、既成概念にとらわれずに物事を考え、自分の殻を破り、Excel オートメーションに取り掛かる必要があります。おそらく、皆さんのコンピュータには Microsoft Office Excel がインストールされていると思います。私たちと同じように皆さんも専門家ではありませんが、Excel は Microsoft Office system の一部なので、これを活用しても問題ないでしょう。

Excel のオートメーションはどれくらい大変なのでしょうか。実際は、マイクロソフトから Excel 専用のオートメーション モデルが提供されているので、思ったより簡単です。このオートメーション モデルは COM オブジェクトで、プログラム ID は Excel.Application です。Excel.Application オブジェクトのインスタンスを作成すると、既定で Excel が起動および実行されますが、表示されることはありません。ただし、Visible プロパティを使用して、Excel を表示できます。

次のように、Excel.Application オブジェクトを作成し、Visible プロパティの状態を照会してから、Visible プロパティの値を true に設定します。

PS C:\> $excel = New-Object -ComObject Excel.Application
PS C:\> $excel.Visible
False
PS C:\> $excel.Visible = $true

処理が完了すると、少し奇妙な Excel が表示されます。これは Excel アプリケーションの外枠にそっくりです (図 3 参照)。ブックもスプレッドシートもなく、Excel の外枠の他には何もありません。

fig03.gif

図 3 ブックやスプレッドシートがない外枠だけの Excel

このアプリケーションにブックを追加する必要があります。これを行うには、workbook オブジェクトの add メソッドを使用します。workbook オブジェクトには、メインの Excel.Application オブジェクトからアクセスできます。ご覧のとおり、ここでは workbook オブジェクトを $workbook という変数に格納します。

$workbook = $excel.Workbooks.add()

ここで、特定のスプレッドシートに接続する必要があります。既定では、ブックが Excel に追加されると、ブックに 3 つのスプレッドシートが追加されます。各スプレッドシートは、数字を使用して指定できます。次のコード行では、1 つ目のスプレッドシートに接続し、返された spreadsheet オブジェクトを $sheet という変数に格納します。

$sheet = $workbook.worksheets.Item(1)

次に、このスプレッドシートにデータを書き込みます。Excel スプレッドシート内の情報はセルに格納されます。セルはスプレッドシート内にあるので、$sheet 変数に格納されている spreadsheet オブジェクトを使用して、特定のセルにアクセスします。この操作を行うには、スプレッドシートの行と列を示す数字を使用します。少し厄介なのは、Excel スプレッドシートでは、行が数字で、列が英字で示されることです。ただし、オートメーション モデルを使用すると、行と列の両方を数字で示すことができます。1 つ目の数字は行、2 つ目の数字は列を表します。セルに値を書き込むには、そのセルに値を代入するだけです。

$sheet.cells.item(1,1) = "Test"

ブックを Excel.Application オブジェクトに追加し、データをスプレッドシート内のセルに追加すると、Excel ブックは図 4 のようになります。

fig04.gif

図 4 セルへの値の追加

以上のことを念頭に置いて、情報を役立つ形式にまとめましょう。WMI からプロセス情報のコレクションを取得し、各プロセスの名前とメモリ消費量を Excel スプレッドシートに書き込んだ後、グラフを作成し、使用されたメモリを強調表示します。これが WriteProcessInformationToExcel.ps1 で行われる処理です。スクリプト全体は TechNet Magazine Web サイトで確認できます。

このスクリプトでは、まず Get-WmiObject コマンドレットを使用して、プロセスに関する情報のコレクションを取得します。WMI クラス Win32_Process を使用してこの情報を取得し、$processes 変数に格納します。

$processes = Get-WmiObject -class Win32_Process

次に、Excel.Application オブジェクトのインスタンスを作成し、そのインスタンスを $excel 変数に格納したら、アプリケーションを表示してブックを追加します。Excel オートメーションの場合、通常は必ずこの手順を実行します。コードは次のとおりです。

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()

Excel で困ることの 1 つは、必ずブック内に 3 つのスプレッドシートが作成されることです。これは無駄な動作だと思います。なぜなら、スプレッドシートは 1 つ使用するのがやっとで、とても 3 つは使用できないからです。さいわい、オートメーションでは余分なスプレッドシートを簡単に削除できます。具体的には、worksheets コレクションを使用して 3 つ目のスプレッドシートに接続し、delete メソッドを呼び出します。同じ方法を使用して、2 つ目のスプレッドシートも削除できます。

$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

次に、残ったスプレッドシートの名前を変更します。ActiveX データ オブジェクト (ADO) を使用して Excel スプレッドシートを照会する場合、接続文字列内でそのスプレッドシートの名前を指定するので、この作業は重要です。このため、コードを読みやすく、かつわかりやすくするために、スプレッドシートに論理名を付ける必要があります。スプレッドシートの名前を変更するには、そのスプレッドシートの Name プロパティに新しい値を代入します。ここでは、1 つ目のスプレッドシートの名前を "Processes" に変更します。

$workbook.WorkSheets.item(1).Name = "Processes"

ここで、名前が変更されたスプレッドシートに接続する必要があります。worksheets オブジェクトの Item メソッドを使用して、スプレッドシートの名前を指定します。

$sheet = $workbook.WorkSheets.Item("Processes")

スプレッドシートの 1 行目には、見出しとなる情報が格納されます。罫線を引き、プロパティ名を太字にします。その結果、データが行 2 から始まることになるので、値 2 をカウンタ変数 $x に代入します。

$x = 2

次に、4 行のコードを使用して 4 つの列挙型を作成します。列挙型は、特定の種類のオプションに使用できる値を Excel に指示するために使用されます。例を挙げると、xlLineStyle 列挙型は、描画する線の種類 (二重線、破線など) を指定するために使用されます。MSDN では、このような列挙値に関するドキュメントが提供されています。

コードを読みやすくするために、使用する 4 つの列挙型それぞれに対応するショートカット エイリアスを作成します。基本的には、列挙名を表す文字列を [type] にキャストします。これはとてもすばらしい手法です。

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

ここで、1 行目の書式を設定する必要があります。フォントを太字にし、線を xlDashDot として定義し、色が自動的に指定されるようにします。また、罫線の太さを中ぐらいに設定します。

For($b = 1 ; $b -le 2 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
 $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
 $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

それが完了したら、item メソッドを使用してセルを選択し、行と列の座標を指定することによって、値を 1 行目に代入します。また、それに続けて値を代入し、列見出しとなる情報を書き込みます。

$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

次に、WMI クエリによって作成され、$processes 変数に格納されるプロセス情報を、適切なセルに入力します。プロセス情報のコレクションを処理するには、foreach ステートメントを使用します。$process 変数をコレクションの列挙子 (プレースホルダ) として定義し、name プロパティと workingSetSize プロパティを選択して、1 つ目と 2 つ目の列にそれぞれ書き込みます。

ここで活躍するのが $x 変数です。2 行目から処理を開始し、プロセスのコレクションに格納されたデータを処理するたびに $x 変数の値をインクリメントすることによって、常にその変数がコレクション内の現在の行を表すようにします。このようにして、プロセス情報の $processes コレクションに格納されたすべてのデータを処理します。

Foreach($process in $processes)
{
 $sheet.cells.item($x, 1) = $process.name
 $sheet.cells.item($x,2) = $process.workingSetSize
 $x++
} #end foreach

Excel スプレッドシートにデータを入力したら、セルに格納されたデータとセルのサイズが同じになるように、列のサイズを調整します。これを行うには、使用する列の座標を指定して、範囲を作成します。この処理には単純に、スプレッドシートの usedRange プロパティを使用できます。range オブジェクトを作成したら、EntireColumn プロパティを選択し、AutoFit メソッドを使用して列のサイズを変更します。このメソッドからは常にデータが返されるので、その結果を Out-Null コマンドレットにパイプします。これで、役に立たない情報がコンソールに表示されなくなります。使用するコードは次のとおりです。

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

ここで作業を終了し、すべてのプロセスの名前とメモリのワーキング セットが記入された便利なスプレッドシートを手に入れることもできます。しかし、もう一歩踏み込んで、グラフを作成してみましょう。簡単ですから。これを行うには、workbook の charts オブジェクトで提供される add メソッドを使用します。このメソッドからも望ましくない形式で情報が返されるので、次のように、結果を Out-Null コマンドレットにパイプします。

$workbook.charts.add() | out-null  

上記のコマンドを実行すると、折れ線グラフが追加されます。他の種類のグラフを定義する必要がある場合は、グラフの種類を指定する列挙値を使用します。この操作を行うには、microsoft.office.interop.excel.xlChartType 列挙値 (xl3DPieExploded 型など) を使用します。xl3DPieExploded 型では、予想どおり 3-D の分割円グラフが作成されます。この列挙型を ActiveChart オブジェクトの chartType プロパティに代入します。その後、グラフのデータ ソースの範囲が $range 変数で定義した範囲になるように指定します。その結果、折れ線グラフが一瞬表示された後、3-D の分割円グラフが表示されます。これを行うためのコードは、次のとおりです。

$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

試しに、円グラフを回転させてみましょう。これを行うには、ActiveChart オブジェクトの rotation プロパティを使用します。for ステートメントを使用して、数が 360 に達するまで 15 ずつインクリメントします。360 は円の度数です。グラフは一度に 15 度ずつ回転し、最終的に 1 周します。かなりかっこいい動きが実現されるでしょう。このすばらしい処理を実行するコードは次のとおりです。

For($i = 1 ; $i -le 360 ; $i +=15)
{
 $workbook.ActiveChart.rotation = $i
}

最後に、スプレッドシートを保存する必要があります。これを行うには、Test-Path コマンドレットを使用して、このスプレッドシートが既に存在するかどうかを確認します。既に存在する場合は、Remove-Item コマンドレットを使用して古いスプレッドシートを削除した後、$strPath 変数の値が示す場所に現在のブックを保存します。Excel.Application オブジェクトの ActiveWorkbook オブジェクトと SaveAs メソッドを使用して、ブックを保存します。まだ保存されたスプレッドシートが存在しない場合は、ActiveWorkbook オブジェクトの SaveAs メソッドを使用してそのまま保存します。

IF(Test-Path $strPath)
  { 
   Remove-Item $strPath
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }
ELSE
  {
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }

スクリプトを実行すると、図 5 のようなグラフが表示されます。

fig05.gif

図 5 Processes の分割円グラフ

スプレッドシート自体は [Processes] タブ上にあります。図 6 では、罫線用に選択した一点鎖線のスタイル、および太字が列見出しに適用されています。表示した 2 つのデータ列は、プロセス名とワーキング セット サイズのプロパティです。

fig06.gif

図 6 完成したスプレッドシート

おわかりのとおり、Excel.Application オートメーション モデルを使用すると、強力で優れた Excel アプリケーションの分析ツールとグラフ作成ツールを活用して、サーバーのデータを処理できます。

Ed Wilson はマイクロソフトのシニア コンサルタントであり、有名なスクリプトの専門家です。マイクロソフト認定トレーナーでもあり、彼が世界中のマイクロソフト プレミア サポートの顧客向けに開催している Windows PowerShell のワークショップは好評です。これまでに Windows スクリプトに関するいくつかの書籍を含む 8 冊の書籍を執筆し、その他にも多くの書籍の執筆に携わっています。Ed は 20 種類を超えるこの業界の資格を持っています。

Craig Liebendorfer は言葉を巧みに操る、マイクロソフトのベテラン Web 編集者です。Craig は毎日言葉にかかわって給料を受け取ることができる仕事が存在することが、いまだに信じられないと思っています。彼が大好きなことの 1 つは場違いなユーモアなので、まさにこのコラムにうってつけの人物です。彼は自分の立派な娘が、自分の人生における最も偉大な功績であると考えています。