何がしたいか
Excelがインストールされていないマシン上でPowerShellを使ってExcelファイルを生成したいんです。
今どきと思われるかもしれませんが、サーバー上のバッチ処理で部署別に何かをチェックしてもらうためのリスト（例：在庫、未処理データ明細、Active Directoryアカウント棚卸）を作成したいとします。
CSVに書き出すだけなら簡単にできますが、Excelにしたい…。
なぜExcelにする必要があるのか。
忙しい現場の担当者からすれば、CSVをExcelで開いて、隠れて見えないデータを見るために手でいちいち列幅を広げたり、データを絞り込むためにフィルターを追加したり、そんな手間はかけたくない。
最初から列幅が自動調整されたフィルタ付きのExcel形式で送ってくれよ、と言いたくなるはず。
おまけにピボットテーブまで付いていれば、単なるチェックリストではなく管理資料として使えるかも。
えっ？
それってサーバーにExcelをインストールしてCOMオブジェクトを生成すればいいだけの話では？
とお思いの方。
ユーザーがインタラクティブにログオンして使う前提ではないサーバーに、Microsoft 365ライセンスを購入するのは実はややこしいです。少なくとも筆者が経験した限りではエンタープライズ向けライセンスを購入できませんでした (交渉が足りなかった可能性はありますが)。
そういうわけで、ExcelがインストールされていなくてもExcel形式ファイルを生成できる ImportExcel というPowerShellモジュールを使いましょう。
ImportExcelモジュールのインストール
モジュール名はImportExcelですが、Excelファイルのインポートしかできないわけではありません。ご安心を。
ImportExcelモジュールのPowerShell Gallery、GitHubリンクは以下のとおりです。
ImportExcel (PowerShell Gallery)
インストールはPowerShellコマンドラインを「管理者として実行する」で起動して、以下のように入力するだけ。
Install-Module -Name ImportExcel
使用例
例1 : データベースの出力を直接Excelにする
Invoke-SqlCmd -ServerInstance "(データベースサーバー名)" -UserName "(データベースユーザー名)" -Password "(パスワード)" -Query "(SQL文)" |
Select-Object (列1), (列2), (列3), (列4), (以下略) |
Export-Excel -Path "(Excelファイル名)" -AutoSize -AutoFilter
これだけです。
これだけで列幅が自動調整され(-AutoSize)、フィルターが追加された(-AutoFilter) ExcelシートがSQL文の実行結果から生成されます。
Select-Object の行は余計なように見えますが、ここで出力したい列を明示しないと、結果のExcelにRowError、RowState、Table、ItemArray、HasErrorsという5つの不要な列が出力されてしまいます。これはInvoke-SqlCmdコマンドレットの仕様とのことです。
'Getting additional rows from power-shell Export-Excel command' (Stack overflow)
例2 : CSVファイルをExcelに変換する
Import-Csv -Path "(CSVファイル名)" -Encoding SJIS |
Export-Excel -Path "(Excelファイル名)" -AutoSize -AutoFilter
CSVファイルもこれだけで列幅自動調整済み、フィルター追加済みのExcelファイルに変換されます。
(Import-CsvのEncodingにSJISを指定していますが、当然ながらCSVファイルの実際のエンコードにあわせて変更してください）
例3 : セル幅の微調整とセルの色
せっかくなのでセル幅の微調整とセルの色付けもしてみます。
# Excelファイルを開く
$xlsFile = Open-ExcelPackage -Path "Excelファイル名"
# 加工したいシートを取得
$sheet1 = $xlsFile.Workbook.Worksheets["シート名"]
# 1列目の幅を100に
Set-ExcelRange -Range $sheet1.Cells["A:A"] -Width 100
# 1行目の色をカスタムカラーに
Set-ExcelRange -Range $sheet1.Cells["1:1"] -BackgroundColor ([System.Drawing.Color]::FromArgb(255, 0, 240, 255))
# 最後に保存
Close-ExcelPackage -ExcelPackage $xlsFile
まずExcelファイルを Open-ExcelPackage で開きます。
そして加工したいシートを取り出し、シートのセルを指定して、セルのプロパティを変更するという手順。Visual Basic for Applicationsの経験があれば直感的に分かると思います。
最後に変更を保存するために Close-ExcelPackage を呼び出して終了です。
この例ではA列の幅を100に変更。そして1列目はたいていヘッダになっているはずですが、そこだけセルの色(BackgroudColor)を変更しています。
範囲を矩形で指定したい場合は $sheet1.Cells["A1:F5"] などになります。
セルの色指定には.NETのColor構造体が使えます。
'Color 構造体' (Microsoft .NETドキュメント)
この例ではFromArgbメソッドで、透明度、R、G、Bを指定してカスタムカラーを作っています（Excelでは透明度を指定しても反映されません）。
[System.Drawing.Color]::LightBlueなど色名で指定することもできます。
例4 : ピボットテーブルを追加する
そうです。何とピボットテーブルの追加まで出来てしまいます。
下記の例は「地域経済分析システム（RESAS：リーサス）」の「人口マップ > 人口構成 > 人口推移」からダウンロードできる「人口_人口構成_人口推移_市区町村.csv」というCSVファイルの場合です。
このファイルにある「都道府県名」列と「市区町村名」列をピボットテーブルの行方向に、「集計年」を列方向に、「総人口（人）」の合計値をデータフィールドにするとこうなります。
- 行方向 : 都道府県名、市区町村名
- 列方向 : 集計年
- データ : 総人口（人）の合計
# CSVファイルをExcelに変換
Import-Csv -Path "人口_人口構成_人口推移_市区町村.csv" -Encoding SJIS |
Export-Excel -Path "人口_人口構成_人口推移_市区町村.xlsx" -AutoSize -AutoFilter
# 生成したExcelファイルを開き直す
$xlsFile = Open-ExcelPackage -Path "人口_人口構成_人口推移_市区町村.xlsx"
$sheet1 = $xlsFile.Workbook.Worksheets["Sheet1"]
# 変換後のCSVの行数を取得 (後で使います)
$rows = $sheet1.Dimension.Rows
# ピボットテーブル用のシートを追加
$sheet2 = Add-Worksheet -ExcelPackage $xlsFile
$pivotTableParams = @{
# ピボットテーブル名 (適当につける)
PivotTableName = "人口"
# ピボットテーブルを書き出すシートと位置の指定
Address = $sheet2.Cells["A1"]
# データソースとなるシート
SourceWorksheet = $sheet1
# データソースの範囲 (ここで行数が必要になる)
SourceRange = $sheet1.Cells["A1:I" + $rows]
# ピボットテーブルの行方向
PivotRows = @("都道府県名", "市区町村名")
# ピボットテーブルの列方向
PivotColumns = "集計年"
# ピボットテーブルのデータ部分。= "sum" が「合計」の指定部分
PivotData = @{'総人口（人）' = "sum"}
# 有効化
Activate = $true
}
# ピボットテーブルの追加
Add-PivotTable @pivotTableParams
# 最後に保存
Close-ExcelPackage -ExcelPackage $xlsFile
ImportExcelをもっと深掘りしたい
ImportExcelモジュールをもっと深掘りしたい方は、説明は英語ですがGitHubに使用例がたくさんあります。グラフ（チャート）の追加もできるようです。
'ImportExcel/Examples at master · dfinke/ImportExcel'
「退屈なことはPowerShellにやらせて」、かつ、エンドユーザーの方々にも喜んでもらって、社内情シスの皆さんは幸せになりましょう。