PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい

2020/04/18

経緯

お仕事で Windows を使うようになり 1 ヶ月、 報告用 Excel に記述するのも面倒になり、PowerShell である程度作ってしまおうという気になってきていて、他の人にも同じような気持ちになって欲しいので、「お？簡単そうじゃね？」と思ってもらうために書いてみました。

サンプルコードをコピペで感覚をつかめると思いますので、少しでも興味持つ人が増えてくれると嬉しいです。

環境

  • Windows 10
  • PowerShell 5.0
  • Excel 2016

PowerShell の起動

まずは、PowerShell を起動しましょう。
PowerShell ISE ではなく、PowerShell を起動してください。

Windows マーク(メニューボタン？)を右クリックしてファイル名を指定して実行、powershell と入力して OK を押すと起動します。

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 にしているところですので、敢えてここで紹介の意味も含めて載せています。

ワークブックを作成

Excel は起動しましたが、いつもの起動した画面とは違いますよね。

Excel でデータを作成する時の流れは、ワークブックを作り、その中のシートを選び、シートの中のセルを選んでデータを入力していきます。普段マウスポチポチでやっているのも同じ手順だと思います。

Excel を表示しながら、次の行を PowerShell に入力してみましょう。

$book = $excel.Workbooks.Add()

]

準備ができたので、ようやくこれから本番です。

ちなみに、Add() の引数はテンプレートの値らしく、省略せずにワークシートを追加するときにはこうやるようです。

$xlWBATWorksheet = -4167
$book = $excel.workbooks.add($xlWBATWorksheet)

シートを操作してみる

まずはシートに対して色々と操作してみましょう。
Excel と PowerShell を一緒に表示しながら行うと判りやすいですし、コマンドを打った瞬間に変わるのは、やはり楽しい物があります。

シート名の取得

Excel を見るとシート名はすぐ分かります… では面白くないですよね。

シート名を PowerShell で取得してみます。

$book.Sheets.Count     # シートの数を取得
$book.Sheets(1).Name   # 1 番目のシートの名前を取得
$book.ActiveSheet.Name # アクティブになっているシート名を取得

シート名の変更

シート名を「Test」に変更してみます。
Excel も見ながら変化を実感してくださいね。

$book.Sheets(1).Name = "hoge"

セルを操作してみる

シートでもっと遊べるのですが、それだけでご飯 2 杯ぐらい余裕で食べられると思うのでこれぐらいにして、そろそろセルを触って遊んで見たいと思います。

再三書いていますが、是非 Excel も表示しながら行って下さい。

セルに値を入れる

$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 メソッドを使うことで判りやすい感じで指定することができます。

セルに計算式を入れる

先ほどのセルの合計を取る計算式を入れて、値を確認してみます

$sheet.Range("C1") = "=SUM(A1:B1)"
$sheet.Range("C1").Text         # C3 のテキストを表示
$sheet.Range("C1").Formula      # C3 の計算式を表示

直感的に判ると思います。

セルのコピー

一度作った計算式は Excel っぽくそのままコピーして使っちゃいます。

$sheet.Range("C1").copy($sheet.Range("C2:C3"))

ここまででこのように表示されていると思います。

セルの装飾

合計のセルは判りやすく装飾しましょう。

$sheet.Range("C1:C3").Font.Bold = $true       # 太字にする
$sheet.Range("C1:C3").interior.ColorIndex = 3 # セルを赤色にする
$sheet.Range("C1:C3").Font.ColorIndex = 2     # 文字を白色にする

ここまででこのように表示されていると思います。目に優しくない素晴らしい配色です。

Excel は用意されている色を ColorIndex として、直接数値として指定するのが一般的なようなので。(RGB でも指定できるのですが)

セルの罫線を引く

表らしく罫線を引こうと思います。

$sheet.Range("A1:C3").Borders.LineStyle = 1 # A1 から C3 まで罫線を引く

この数字も Excel 上で定義されていて、定数となっているようですね。

コメントを入れる

コメントを入れてみます。

$sheet.Range("A1").AddComment("misono マジ可愛い") # A1 にコメントを入れる

思い通りのコメントが入れられましたね。

保存

一通り遊んだので、デスクトップに保存します。

$book.SaveAs("${HOME}\Desktop\hoge.xlsx")

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 からデータを取得して突き合わせたりと大活躍してもらっています。

ProgrammingExcel, PowerShell

Posted by みやみや