PowerShellを使用したExcelファイルへのデータの読み取りと書き込み

PowerShellを使用して従業員のリストを含むExcelファイルのデータにアクセスする方法の簡単な例を見てみましょう。

まず、COMオブジェクトを使用してコンピューターでExcelアプリ（アプリケーションレイヤー）を実行します。
$ExcelObj = New-Object -comobject Excel.Application

コマンドを実行すると、Excelがバックグラウンドでコンピューター上で起動されます。 Excelウィンドウを表示するには、表示を変更します COMオブジェクトのプロパティ：

$ExcelObj.visible=$true

$ExcelObj| fl

次に、Excelファイル（ワークブック）を開くことができます：

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

各Excelファイルには、複数のワークシートを含めることができます。現在のExcelブックのワークシートのリストを表示しましょう：

$ExcelWorkBook.Sheets| fl Name, index

次に、必要なシートを（名前またはインデックスで）開くことができます：

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

次のコマンドを使用して、現在の（アクティブな）Excelワークシートの名前を取得できます。

$ExcelWorkBook.ActiveSheet | fl Name, Index

次に、Excelワークシートのセルから値を取得できます。範囲、セル、列、または行を使用して、さまざまな方法を使用して現在のExcelワークシートのセル値を取得できます。以下の同じセルからデータを取得する方法の例を参照してください。

$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

PowerShellを使用したActiveDirectoryユーザー情報のExcelスプレッドシートへのエクスポート

PowerShellからExcelデータにアクセスする方法の実際的な例を見てみましょう。 Excelファイルで各ユーザーのActiveDirectoryからいくつかの情報を取得するとします。たとえば、電話番号（telephoneNumber属性）、部門、電子メールアドレスなどです。

# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all rows in Column 1 starting from Row 2 (these cells contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Get the values of user attributes in AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Fill in the cells with the data received from 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
}
# Save the XLS file and close Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

その結果、AD情報を含む列がExcelファイルのユーザーごとに追加されました。

PowerShellとExcelを使用してレポートを作成する別の例を考えてみましょう。すべてのドメインサーバーでのPrintSpoolerサービスの状態に関するExcelレポートを作成するとします。

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Rename a worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the column width
$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
# Get the list of all Windows Servers in the domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# Fill in Excel cells with the data obtained from the server
$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++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

PowerShellを使用して、さまざまなシナリオでExcelにアクセスできます。たとえば、便利なActive Directoryレポートを作成したり、PowerShellスクリプトを作成してExcelからADデータを更新したりできます。