ImportExcel PowerShell Module Worthwhile Add to Microsoft 365 Admin Toolkit

2022年5月10日

CSV出力をより見栄えよくする

ザ エクセルのインポート モジュールは PowerShell ギャラリーで入手できます。.メンテナンス者 MVP ダグ・フィンクこのモジュールを使用すると、開発者はワークステーションにアプリケーションをインストールすることなく、Excel にデータをインポートおよびエクスポートできます。

Microsoft 365 にはいくつかの基本的なレポートが付属していますが、多くの組織では PowerShell スクリプトを使用して、ユーザーのアクティビティからメールボックスの一覧まで、あらゆるものをカバーするレポートを生成します。多くの場合、スクリプトは エクスポート – CSV レポートを CSV ファイルにエクスポートするコマンドレット。後で、CSV ファイルを使用して、Excel でより優れたレポートを作成したり、データを Power BI にインポートしたりする可能性があります。私はこのルーチンを経験した回数を数えることができません。入力側では、 インポート – CSV コマンドレットは、チェックするメールボックスの一覧など、処理のために PowerShell にデータをもたらします。

エクセルへのエクスポート

チェックアウトするには エクセルのインポート モジュールでは、長年にわたってここで議論してきたレポートスクリプトのいくつかを使用しました。後 FAQの参照では、このスクリプトを使用して、OneDrive for Business サイトがレポートを生成するために使用する記憶域を報告することから始めました。このストレージは、$Report という PowerShell リストにあります。Excelにエクスポートするには(そして、アプリケーションが利用可能な場合はロードされたデータでExcelを起動するには)、私がする必要があるのは:

$Report | Export-Excel

Excel は、既定の表スタイルを適用した後、データを読み込みます (図 1)。

インポートExcel モジュールで使用される既定の Excel スタイル — 図 1: ImportExcel モジュールで使用される既定の Excel スタイル

名前付きスプレッドシートにエクスポートするには、その名前をコマンドに追加します。

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx

この種のエクスポートでは書式設定が適用されないため、図 2 のような結果になります。

名前付きスプレッドシートファイルへのエクスポートでは、別のスタイルが使用されます — 図2:名前付きスプレッドシートファイルへのエクスポートで別のスタイルを使用する

Excel ではブック内の複数のワークシートがサポートされているため、既存のブックに新しいワークシートを追加できます。例えば：

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx -Append -WorksheetName "New data"

このコマンドのより高度なバージョンでは、名前付きシート、名前付きテーブル、タイトル、および別のスタイルを持つブックが作成されます。使用可能な表スタイルの名前は、Excel に表示されているスタイルにカーソルを合わせると表示されます。 表として書式設定 オプション。図 3 に、このコマンドの出力を示します。

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx -WorksheetName "May 2022" -Title "OneDrive for Business Storage Consumption" -TitleBold -TableName "OneDriveStorage" -TableStyle Dark1

グラフ

Excel で最も人気のある機能の 1 つは、スプレッドシートデータからさまざまなグラフを生成する機能です。ワークシートにグラフを作成するには、まず変数で生成するグラフの種類を定義し、次にグラフ定義を使用してデータをエクスポートします。OneDrive ストレージデータの円グラフ (図 4) を作成する方法を次に示します。

$ExcelChart = New-ExcelChartDefinition -XRange Owner -YRange UsedGB -ChartType Pie -Title "OneDrive for Business Storage"
$Report | Export-Excel -Path c:\temp\OneDrive.xlsx -Append -WorksheetName "Pie Chart" -ExcelChartDefinition $ExcelChart -AutoNameRange -Show -Title "OneDrive for Business Storage"

Excel でサポートされているセットからグラフの種類を選択するには、いくつかの試行錯誤が必要ですが、理解するのは簡単です。

チームメンバーシップ

ザ エクセルのインポート モジュールは、入力として使用したレポートのいずれかを処理しました。たとえば、Microsoft 365 および Teams メンバーシップレポートスクリプトからの出力はかなり大きくなる可能性がありますが、 エクセルのインポート 何の問題もなかった。このコマンドを使用して、図 5 に示すレポートを生成しました。

$SummaryData | Export-Excel -Path c:\Temp\TeamMembership.xlsx -WorksheetName "Groups May 2022" -Title "Microsoft 365 Groups and Teams Membership" -TitleBold -TableName "GroupsMembership" -TableStyle Medium28

図 5: Excel バージョンの Microsoft 365 グループとチームのメンバーシップレポート

エクセルからのインポート

Excelからのインポートは、 インポート – CSV コマンドレットですが、いくつかの追加のコントロールがあります。この例では、インポートする列と開始行を指定します。

[array]$Data = Import-Excel -Path c:\temp\TeamMembership.xlsx -ImportColumns @(1,2,3,4,5) -StartRow 2

配列に読み込まれたら、標準の PowerShell コマンドを使用してデータを操作できます。たとえば、次のコードはデータをループ処理して、名前に “red" を含むユーザーを強調表示します。

ForEach ($D in $Data) {
   If ($D.User -like "*Red*") {
      Write-Host ("{0} is a member of {1} groups..." -f $D.User, $D."Groups count" ) }
}

エクセルの更新

PowerShell にデータを読み込む方法をさらに制御することは興味深いことです。Excel を介して Excel データを操作できる方が興味深いです。これを行うには、 エクセルのインポート ターゲットスプレッドシートのワークシートの情報を変数に読み込み、それを使用してワークシート内のセルからの情報を配列に入力するには、次のようにします。

$ExcelData = Open-ExcelPackage -path c:\temp\TeamMembership.xlsx
$Data = $ExcelData.Workbook.WorkSheets["Groups May 2022"].Cells

これで、行/列表記を使用してセルをナビゲートできるようになりました。たとえば、セル 3,1 (3 行目、最初の列) にはこの information:

$Data[3,1]

Style                 : OfficeOpenXml.Style.ExcelStyle
StyleName             : Normal
StyleID               : 0
Value                 : Tony Redmond
Text                  : Tony Redmond
Formula               :
FormulaR1C1           :
Hyperlink             :
Merge                 : False
AutoFilter            : False
IsRichText            : False
IsArrayFormula        : False
RichText              : {}
Comment               :
Worksheet             : Groups May 2022
FullAddress           : 'Groups May 2022'!A3
FullAddressAbsolute   : 'Groups May 2022'!$A$3
ConditionalFormatting : OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting
DataValidation        : OfficeOpenXml.DataValidation.RangeDataValidation
Current               :
Address               : A3
Start                 : OfficeOpenXml.ExcelCellAddress
End                   : OfficeOpenXml.ExcelCellAddress
Table                 :
IsName                : False
Rows                  : 1
Columns               : 1

セルを更新するには、セルに新しい値を書き込み、値が正しいことを確認したら、Excel スプレッドシートを閉じてデータを保存します。

$Data[3,1].Value = “The Fool on the Hill”
$Data[3,1] | Select -ExpandProperty Value
The Fool on the Hill
Close-ExcelPackage $ExcelData

Excel の通常の自動保存機能は、スプレッドシートの場所が OneDrive for Business または SharePoint Online サイトにある場合でも機能しないため、ローカルドライブに保存されているファイルを操作する場合と同様に、変更を加えてからコミットするために保存することを忘れないでください。