PowerShellでExcelファイルのデータを読み書きする方法｜COMオブジェクトを使った自動化入門

本記事では、PowerShellスクリプトから直接Excelワークシートのデータを読み書きする方法を解説します。PowerShellとExcelを組み合わせることで、コンピューターやサーバー、インフラ、Active Directory(AD)などのインベントリ調査や各種レポート作成を自動化できます。

前提知識：COMオブジェクトとExcelオブジェクトモデル

PowerShellからExcelシートにアクセスするには、COM(Component Object Model)オブジェクトを使用します。この方法では、対象のコンピューターにExcelがインストールされている必要がある点に注意してください。

Excelのセルに格納されたデータへアクセスする前に、Excelファイルにおけるプレゼンテーション層の構造を理解しておきましょう。Excelオブジェクトモデルは、以下の4つの階層で構成されています。

• Applicationレイヤー – 実行中のExcelアプリケーションそのものを扱う
• WorkBookレイヤー – 複数のワークブック(Excelファイル)を同時に開くことができる
• WorkSheetレイヤー – 各XLSXファイルには複数のシートを含められる
• Rangeレイヤー – 特定のセルまたはセル範囲のデータにアクセスする

PowerShellでExcelスプレッドシートからデータを読み取る方法

まずは、従業員一覧を含むExcelファイルのデータにPowerShellからアクセスする簡単な例を見てみましょう。

1. Excelアプリケーションの起動

最初に、COMオブジェクト経由でExcelアプリケーション(Applicationレイヤー)を起動します。

$ExcelObj = New-Object -comobject Excel.Application

このコマンドを実行すると、Excelがバックグラウンドで起動します。Excelウィンドウを表示したい場合は、COMオブジェクトのVisibleプロパティを変更します。

$ExcelObj.visible=$true

Excelオブジェクトのすべてのプロパティは次のコマンドで確認できます。

$ExcelObj | fl

2. ワークブックを開く

続いて、Excelファイル(ワークブック)を開きます。

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")

3. ワークシートの一覧表示と選択

1つのExcelファイルには複数のワークシートを含めることができます。現在のワークブック内のシート一覧は以下のように表示できます。

$ExcelWorkBook.Sheets | fl Name, index

目的のシートを名前またはインデックスで開きます。

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")

現在アクティブなワークシートの名前を取得するには、次のコマンドを使用します。

$ExcelWorkBook.ActiveSheet | fl Name, Index

4. セルの値を取得する

ワークシート上のセル値は、範囲(Range)・セル(Cells)・列(Column)・行(Row)など、さまざまな方法で取得できます。同じセル(B4)からデータを取得する例を以下に示します。

$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(4, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text

用途に応じて使い分けるとよいでしょう。なお、.Textは表示形式に依存した文字列を返すのに対し、.value2は内部形式の値を返すという違いがあります。

実践例1:Active Directoryユーザー情報をExcelへエクスポートする

次に、PowerShellからExcelデータへアクセスする実践的な例を紹介します。ここでは、Excelファイルに記載された各ユーザーについて、Active Directoryから電話番号(telphoneNumber属性)、部署、メールアドレスの情報を取得して転記する処理を行います。

ADユーザー属性の情報取得には、PowerShellのActive Directoryモジュールに含まれるGet-ADUserコマンドレットを使用します。

# Active Directoryモジュールをセッションにインポート
import-module activedirectory
# まずExcelワークブックを開く
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# XLSXワークシート内の入力済み行数を取得
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# 2行目以降の1列目を順にループ(ドメインユーザー名が格納されている)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# ADからユーザー属性の値を取得
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# ADから取得したデータをセルに入力
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
# XLSXファイルを保存してExcelを終了
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

このスクリプトを実行すると、Excelファイルの各ユーザーの行に、ADから取得した情報の列が追加されます。

実践例2:ドメインサーバーのPrint Spoolerサービス状態レポートを作成する

もうひとつ、PowerShellとExcelを組み合わせたレポート作成の例を見てみましょう。ここでは、ドメイン内の全サーバーにおけるPrint Spoolerサービスの状態をまとめたExcelレポートを作成します。

サーバーの一覧取得にはGet-ADComputerコマンドレットを、各サーバー上のサービス状態のリモート確認にはWinRM経由のInvoke-Commandコマンドレットを使用します。

# Excelオブジェクトを作成
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# 新しいワークブックを追加
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# ワークシートの名前を変更
$ExcelWorkSheet.Name = 'Spooler Service Status'
# 表の見出しを入力
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# 見出しを太字にし、フォントサイズと列幅を設定
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=28
# ドメイン内の全Windows Serverのリストを取得
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# 各サーバーに接続してサービスの状態を取得
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# サーバーから取得したデータをExcelのセルに入力
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
$counter++
}
# レポートを保存してExcelを終了
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

見出し行の書式設定や列幅の調整もスクリプトから自動的に行えるため、そのまま提出できる体裁の整ったレポートを生成できます。

応用シナリオ：ADデータの更新自動化

PowerShellによるExcel操作は、さまざまな場面で活用できます。たとえば、Active Directoryの便利なレポートを定期的に生成したり、逆にExcelのデータをもとにADの情報を更新するPowerShellスクリプトを作成したりすることも可能です。

具体例として、人事部門の担当者にExcelでユーザー台帳を管理してもらい、その内容をPowerShellスクリプトとSet-ADUserコマンドレットによってADへ自動反映する仕組みを構築できます(担当者に対し、ADユーザー属性の変更権限を委任し、PowerShellスクリプトの実行方法を案内しておけばOKです)。こうすることで、電話番号・役職・所属部署などの最新情報を保つ社内アドレス帳を常に維持できます。