経緯
お仕事で Windows を使うようになり 1 ヶ月、 報告用 Excel に記述するのも面倒になり、PowerShell である程度作ってしまおうという気になってきていて、他の人にも同じような気持ちになって欲しいので、「お？簡単そうじゃね？」と思ってもらうために書いてみました。
サンプルコードをコピペで感覚をつかめると思いますので、少しでも興味持つ人が増えてくれると嬉しいです。
環境
- Windows 10
- PowerShell 5.0
- Excel 2016
1. PowerShell の起動
まずは、PowerShell を起動しましょう。
PowerShell ISE ではなく、PowerShell を起動してください。
Windows マーク(メニューボタン？)を右クリックしてファイル名を指定して実行、powershell と入力して OK を押すと起動します。
2. Excel を起動
起動した PowerShell に次のコマンドを一行づつ打ち込んでみましょう。
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $true
Excel が起動して画面に表示されたと思います。
最初の $excel = New-Object -ComObject Excel.Application で Excel が起動しており、次の $excel.Visible = $true で Excel を表示するようにしています。
タスクマネージャーで見るとわかりますが、Visible を True にしてない状態(非表示)でも Excel は起動していて、PowerShell から Excel を操作することはできますが、今回は Excel を操作することを実感するために表示しています。
3 行目の $excel.DisplayAlerts = $true は警告メッセージを表示するようにしています。2 行目と 3 行目は実際の運用では $false にしているところですので、敢えてここで紹介の意味も含めて載せています。
3. ワークブックを作成
Excel は起動しましたが、いつもの起動した画面とは違いますよね。
Excel でデータを作成する時の流れは、ワークブックを作り、その中のシートを選び、シートの中のセルを選んでデータを入力していきます。普段マウスポチポチでやっているのも同じ手順だと思います。
Excel を表示しながら、次の行を PowerShell に入力してみましょう。
$book = $excel.Workbooks.Add()
]
準備ができたので、ようやくこれから本番です。
ちなみに、Add() の引数はテンプレートの値らしく、省略せずにワークシートを追加するときにはこうやるようです。
$xlWBATWorksheet = -4167
$book = $excel.workbooks.add($xlWBATWorksheet)
4. シートを操作してみる
まずはシートに対して色々と操作してみましょう。
Excel と PowerShell を一緒に表示しながら行うと判りやすいですし、コマンドを打った瞬間に変わるのは、やはり楽しい物があります。
4-1. シート名の取得
Excel を見るとシート名はすぐ分かります... では面白くないですよね。
シート名を PowerShell で取得してみます。
$book.Sheets.Count # シートの数を取得
$book.Sheets(1).Name # 1 番目のシートの名前を取得
$book.ActiveSheet.Name # アクティブになっているシート名を取得
4-2. シート名の変更
シート名を「Test」に変更してみます。
Excel も見ながら変化を実感してくださいね。
$book.Sheets(1).Name = "hoge"
5. セルを操作してみる
シートでもっと遊べるのですが、それだけでご飯 2 杯ぐらい余裕で食べられると思うのでこれぐらいにして、そろそろセルを触って遊んで見たいと思います。
再三書いていますが、是非 Excel も表示しながら行って下さい。
5-1. セルに値を入れる
$sheet = $book.Sheets("hoge") # 扱いやすいようにシートを取得します
$sheet.Name # hoge と出るはずです
$sheet.Cells.Item(1, 1) = 100 # セル A1 に 100 を入れています
$sheet.Cells.Item(1, 2) = 50 # セル B1 に 50 を入れています
この方法はインクリメント(順次)で処理をするときに使いやすいですね。ただ、セルを直接指定する方が効果的なときもあります。
$sheet.Range("A2", "B2") = 50 # A2 から B2 まで 50 を入れています
$sheet.Range("A3", "B3") = 5,10 # A3 に 5、B3 に 10 を入れています
$sheet.Range("A2").Text # A2 のテキストを表示
Range メソッドを使うことで判りやすい感じで指定することができます。
5-2. セルに計算式を入れる
5-1 のセルの合計を取る計算式を入れて、値を確認してみます
$sheet.Range("C1") = "=SUM(A1:B1)"
$sheet.Range("C1").Text # C3 のテキストを表示
$sheet.Range("C1").Formula # C3 の計算式を表示
直感的に判ると思います。
5-3. セルのコピー
一度作った計算式は Excel っぽくそのままコピーして使っちゃいます。
$sheet.Range("C1").copy($sheet.Range("C2:C3"))
ここまででこのように表示されていると思います。
5-4. セルの装飾
合計のセルは判りやすく装飾しましょう。
$sheet.Range("C1:C3").Font.Bold = $true # 太字にする
$sheet.Range("C1:C3").interior.ColorIndex = 3 # セルを赤色にする
$sheet.Range("C1:C3").Font.ColorIndex = 2 # 文字を白色にする
ここまででこのように表示されていると思います。目に優しくない素晴らしい配色です。
Excel は用意されている色を ColorIndex として、直接数値として指定するのが一般的なようなので。(RGB でも指定できるのですが)
5-5. セルの罫線を引く
表らしく罫線を引こうと思います。
$sheet.Range("A1:C3").Borders.LineStyle = 1 # A1 から C3 まで罫線を引く
この数字も Excel 上で定義されていて、定数となっているようですね。
5-6. コメントを入れる
コメントを入れてみます。
$sheet.Range("A1").AddComment("misono マジ可愛い") # A1 にコメントを入れる
思い通りのコメントが入れられましたね。
6. 保存
一通り遊んだので、デスクトップに保存します。
$book.SaveAs("${HOME}\Desktop\hoge.xlsx")
7. Excel を閉じる
最後に Excel を閉じます。
$excel.Quit() # Excel の終了
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # 変数の破棄
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) # 変数の破棄
タスクマネージャーを見ていると判りますが、 $excel.Quit() だけではプロセスは落ちていません。利用した変数をキッチリ破棄する必要があります。
他にも、変数を Null にして GC を動かす方法もあるようです。
考察
ここまで見たら、後はググッて色々出来そうな気がしてきたと思います。
どこまでコマンドラインでできるかは判らないですが、私はオブジェクトを Get-Member で見ると色々出来そうだなという気分になりました。
実際仕事では、様々なログを正規表現をゴリゴリ書いて parse して、規定のレポーティング用にフォーマットし直して保存したり、Excel からデータを取得して突き合わせたりと大活躍してもらっています。