PowerShellとExcel：ImportExcelのパワーを活用する

チュートリアル
PowerShell

マイクロソフトエクセルは、私たちの多くが逃れることができない普及したツールの一つです。多くのITプロフェッショナルは、Excelを小さなデータベースとして使用し、さまざまな自動化ルーチンで大量のデータを保存しています。Excelを自動化するための最適なシナリオは何でしょうか？それはPowerShellとExcelです！

Excelスプレッドシートは、スクリプトや自動化が難しいことで有名です。機能が少なく（そしてシンプルな）CSVファイルの代替物とは異なり、Excelワークブックは単なるテキストファイルではありません。Excelワークブックを操作するには、PowerShellが必要で、Excelがインストールされている必要がありました。しかし、もう心配ありません。

幸いにも、賢明なPowerShellコミュニティのメンバーであるDoug Finkeさんが、私たち一般のユーザーのために「ImportExcel」というPowerShellモジュールを作成してくれました。ImportExcelモジュールは、すべての複雑さを抽象化してくれます。これにより、Excelワークブックを簡単に管理し、PowerShellスクリプトを実行することが可能になります。

この記事では、ImportExcelモジュールといくつかの人気のあるユースケースを使用して、PowerShellとExcelで何ができるかを探ってみましょう。

前提条件

WindowsシステムでImportExcelモジュールを実行する際は、別個の依存関係は必要ありません。ただし、macOSで作業する場合は、brew install mono-libgdiplus を使用して mono-libgdiplusパッケージをインストールする必要があります。この記事のすべての例はmacOSを使用して構築されますが、すべての例はクロスプラットフォームで動作するはずです。

macOSを使用している場合は、続行する前にPowerShellセッションを再起動してください。

ImportExcelモジュールのインストール

Install-Module ImportExcel -Scope CurrentUserを実行して、PowerShellギャラリーからモジュールをダウンロードしてインストールします。数分後には準備が整います。

PowerShellとExcelを使用してワークシートにエクスポートする

おそらく、標準のPowerShellコマンドレット Export-Csv と Import-Csvには慣れているでしょう。これらのコマンドレットを使用すると、PowerShellオブジェクトをCSVファイルに読み込みおよびエクスポートできます。残念ながら、Export-ExcelとImport-Excelのコマンドレットはありません。しかし、ImportExcelモジュールを使用することで、独自の機能を作成できます。

関連記事: Import-Csvを使用してPowerShellでCSVファイルを管理する

システム管理者が最も一般的に要求することの1つは、PowerShellオブジェクトをExcelワークシートにエクスポートすることです。ImportExcelモジュールのExport-Excelコマンドレットを使用すると、簡単に実現できます。

例えば、ローカルコンピュータ上で実行されているいくつかのプロセスを見つけ、それらをExcelワークブックに取得する必要があるかもしれません。

Export-Excelコマンドレットは、Export-Csvと同様に、任意のオブジェクトを受け入れます。任意の種類のオブジェクトをこのコマンドレットにパイプで渡すことができます。

PowerShellを使用してシステム上で実行されているプロセスを検索するには、Get-Processコマンドレットを使用します。このコマンドレットは各実行中のプロセスと各プロセスに関するさまざまな情報を返します。その情報をExcelにエクスポートするには、Export-Excelコマンドレットを使用し、作成されるExcelワークブックへのファイルパスを指定します。以下に、コマンドと生成されたExcelファイルのスクリーンショットの例を示します。

Get-Process | Export-Excel -Path './processes.xlsx'
Worksheet created with PowerShell and Excel

おめでとうございます！これで、Export-Csvと同様にすべての情報をエクスポートしましたが、Export-Csvとは異なり、このデータをより洗練されたものにすることができます。ワークシートの名前をProcessesとし、データをテーブルにし、行を自動サイズにしましょう。

AutoSizeスイッチパラメータを使用してすべての行のサイズを自動調整し、TableNameを指定してすべてのデータが含まれるテーブルの名前を指定し、WorksheetNameパラメータ名にはProcessesを指定して、以下のスクリーンショットで構築できる内容が表示されます。

Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName Processes -WorksheetName Proccesses
Autosize Switch Parameter Result

Export-Excelコマンドレットには、さまざまな種類のExcelワークブックを作成するために使用できる多くのパラメータがあります。 Export-Excelの機能についての詳細な説明については、Get-Help Export-Excelを実行してください。

ExcelへのインポートにPowerShellを使用する

前のセクションでファイル名processes.xlsxで情報をエクスポートしました。おそらく、このファイルを別のコンピュータに移動し、PowerShellとExcelでこの情報をインポート/読み取りする必要があります。問題ありません。使用できるImport-Excelがあります。

基本的な使用法では、Pathパラメータを使用してExcelドキュメント/ワークブックへのパスを指定するだけです。以下のように、最初のワークシート（この場合はProcessesワークシート）を読み取り、PowerShellオブジェクトを返します。

Import-Excel -Path './processes.xlsx'
Path Parameter

おそらくExcelワークブックに複数のワークシートがある場合はどうでしょうか？ WorksheetNameパラメータを使用して特定のワークシートを読み取ることができます。

Import-Excel -Path './processes.xlsx' -WorkSheetname SecondWorksheet

Excelワークシートから特定の列のみを読み取る必要がある場合はどうでしょうか？ HeaderNameパラメータを使用して読み取りたい列のみを指定します。

Import-Excel -Path './processes.xlsx' -WorkSheetname Processes -HeaderName 'CPU','Handle'

Import-Excelコマンドレットには、さまざまな種類のExcelワークブックを読み取るために使用できる他のパラメータがあります。 Import-Excelができることの完全な説明については、Get-Help Import-Excelを実行してください。

PowerShellを使用してExcelのセル値を取得（および設定）する

PowerShellとExcelを使用してワークシート全体を読み取る方法はわかりましたが、単一のセル値のみが必要な場合はどうでしょうか？ 厳密には、Import-Excelを使用して必要な値をフィルタリングすることもできますが、それはあまり効率的ではありません。

代わりに、Open-ExcelPackageコマンドレットを使用して、ExcelワークブックをPowerShellオブジェクトに「変換」し、それを読み取って操作することができます。 セルの値を見つけるためには、まずExcelワークブックをメモリに読み込むために開きます。

$excel = Open-ExcelPackage -Path './processes.xlsx'

Open-ExcelPackageは、COMオブジェクトと直接操作する場合のNew-Object -comobject excel.applicationの使用方法と似ています。

次に、ワークブック内のワークシートを選択します。

$worksheet = $excel.Workbook.Worksheets['Processes']

このプロセスは、excel.workbooks.openを使用してワークブックを開くCOMオブジェクトの方法と似ています。

ワークシートを変数に割り当てたら、個々の行、列、セルに進むことができます。 たとえば、A1行のすべてのセル値を見つける必要がある場合、以下に示すようにインデックスA1を指定してCellsプロパティを参照するだけです。

$worksheet.Cells['A1'].Value

ワークシートのセルの値を変更するには、異なる値を割り当てることもできます。例：$worksheet.Cells['A1'] = '異なる値'

メモリに読み込んだ後、Excelパッケージを解放するためにClose-ExcelPackageコマンドレットを使用することが重要です。

Close-ExcelPackage $excel

PowerShellとExcelを使用してワークシートをCSVファイルに変換する

PowerShellオブジェクトを介してExcelワークシートの内容を取得したら、ExcelワークシートをCSVに「変換」するには、これらのオブジェクトをExport-Csvコマンドレットに送信するだけです。

関連記事：Export-Csv：オブジェクトをCSVファイルに変換する

以前に作成したprocesses.xlsxワークブックを使用して、最初のワークシートを読み取り、すべてのデータをPowerShellオブジェクトに取得し、次に以下のコマンドを使用してオブジェクトをCSVにエクスポートします。

Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation

これで、結果のCSVファイルを開くと、Processesワークシート（この例では）内の同じデータが表示されます。

複数のワークシートを変換する

複数のワークシートを持つExcelワークブックがある場合、各ワークシートに対してCSVファイルを作成することもできます。これを行うには、Get-ExcelSheetInfoコマンドレットを使用してワークブック内のすべてのシートを取得します。ワークシートの名前を取得したら、その名前をWorksheetNameパラメーターに渡し、シート名をCSVファイルの名前として使用することもできます。

以下に、PowerShellとExcelを使用した必要な例のコードが示されています。

## ワークブック内の各シートを検索します
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## 各シートを読み込み、同じ名前のCSVファイルを作成します
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

結論

PowerShellとExcelを使用すると、Excelワークブック内のデータをCSVと同様にインポート、エクスポート、管理できます。Excelのインストールは必要ありません！

この記事では、Excelワークブックのデータの読み書きの基本を学びましたが、これは表面の一部です。PowerShellとImportExcelモジュールを使用すると、グラフやピボットテーブルの作成、Excelの他の強力な機能を活用することができます！

Source:
https://adamtheautomator.com/powershell-excel/