Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

ブログからの転記

経緯

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

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

環境

  • Windows 10
  • PowerShell 5.0
  • Excel 2016

1. PowerShell の起動

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

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

2. Excel を起動

起動した PowerShell に次のコマンドを一行づつ打ち込んでみましょう。

example
$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 に入力してみましょう。

example
$book = $excel.Workbooks.Add()

]

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

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

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

4. シートを操作してみる

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

4-1. シート名の取得

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

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

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

4-2. シート名の変更

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

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

5. セルを操作してみる

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

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

5-1. セルに値を入れる

example
$sheet = $book.Sheets("hoge") # 扱いやすいようにシートを取得します
$sheet.Name                   # hoge と出るはずです
$sheet.Cells.Item(1, 1) = 100 # セル A1 に 100 を入れています
$sheet.Cells.Item(1, 2) = 50  # セル B1 に 50 を入れています

この方法はインクリメント(順次)で処理をするときに使いやすいですね。ただ、セルを直接指定する方が効果的なときもあります。

example
$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 のセルの合計を取る計算式を入れて、値を確認してみます

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

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

5-3. セルのコピー

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

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

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

5-4. セルの装飾

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

example
$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. セルの罫線を引く

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

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

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

5-6. コメントを入れる

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

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

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

6. 保存

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

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

7. Excel を閉じる

最後に Excel を閉じます。

example
$excel.Quit()  # Excel の終了

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # 変数の破棄
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) # 変数の破棄

タスクマネージャーを見ていると判りますが、 $excel.Quit() だけではプロセスは落ちていません。利用した変数をキッチリ破棄する必要があります。

他にも、変数を Null にして GC を動かす方法もあるようです。

考察

ここまで見たら、後はググッて色々出来そうな気がしてきたと思います。

どこまでコマンドラインでできるかは判らないですが、私はオブジェクトを Get-Member で見ると色々出来そうだなという気分になりました。

実際仕事では、様々なログを正規表現をゴリゴリ書いて parse して、規定のレポーティング用にフォーマットし直して保存したり、Excel からデータを取得して突き合わせたりと大活躍してもらっています。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした