PowerShell を使ってExcel を操作する

Windows Excel PowerShell

Table of Contents

  1. はじめに
    1. 参考
  2. Excel の起動
  3. シートの追加
  4. セルの編集
  5. 表の作成
  6. グラフの作成
  7. 保存、終了

はじめに

PowerShell を使った Excel 操作のコマンドのメモ。

▼ メリット

  • Input – Output を明確化できる
  • 1つ1つのコマンド実行結果を視覚的に確認できる
  • VBA を触らずに済む

参考

PowerShellをはじめよう　Powershell入門

Windows PowerShell を使ってExcel を操作する -セル操作編 vol.1

Excel の起動

### Excel の起動
$excel = New-Object -Com Excel.Application

# 可視化 (バッチファイルにするときは $Falseに)
$excel.Visible = $True

# アラートを無効に
$excel.DisplayAlerts = $False

可視化を True にすることで、これから流すコマンドの結果を1つ1つ目視確認できる。

シートの追加

### 新規シートを追加
$book = $excel.Workbooks.add()

# アクティブなシート(Sheet1) を取得
# ※ $sheet = $book.Sheets("Sheet1") でも可
$sheet = $book.ActiveSheet

# シート名を変更
$sheet.Name = "表紙"

既存ワークブックを開く場合は open() を使用する。

### AAA.xlsx を開く
$book = $excel.Workbooks.Open("C:\TEST\AAA.xlsx")

セルの編集

### A1セルに値を登録
$sheet.Cells.Item(1,1) = "報告書サンプル"

# A1セルを太字、サイズ30に
$sheet.Cells.Item(1,1).Font.Bold = $True
$sheet.Cells.Item(1,1).Font.Size = 30

# A2セルに作成日時を登録
$sheet.Cells.Item(2,1) = "　作成日：" + (Get-Date).toString()

A1、A2 といった書き方もできる。

# A2のセルに値を設定
$sheet.Range("A2") = "200"

表の作成

# $tableRange = 登録した表の範囲
#   $sheet.Cells.Item($x1,$y1) ... 表の左上
#   $sheet.Cells.Item($x2,$y2) ... 表の右下
$tableRange = $sheet.Range($sheet.Cells.Item($x1,$y1),
               $sheet.Cells.Item($x2,$y2))
# 表の罫線を表示
$tableRange.Borders.LineStyle = $True

# 表の幅を自動調整
$tableRange.Columns.AutoFit() | Out-Null

# 表ヘッダ部分を太字、背景灰色(15) に (参考) https://www.sejuku.net/blog/32288
$sheet.Range("A1","C1").Font.Bold = $True
$sheet.Range("A1","C1").Interior.ColorIndex = 15

グラフの作成

先程作成した表 (範囲 $tableRange) をグラフ化する。

### グラフを描画するための領域を作成
# $posX, $posY    ... 描画領域の左上座標
# $width, $height ... 描画領域の幅、高さ
$posX, $posY = 200, 10
$width, $height = 600, 400
$chart = $sheet.ChartObjects().Add($posX, $posY, $width, $height).Chart

# グラフを描画
$chart.SetSourceData($tableRange) | Out-Null

# 折れ線グラフに (参考: https://www.relief.jp/docs/excel-vba-xlchartype-list.html )
$chart.ChartType = 4 # -> xlLine

# タイトルを表示
$chart.HasTitle = $True
$chart.ChartTitle.Text = "アクセス数推移グラフ"

保存、終了

### 保存、終了
$book.SaveAs("$workdir\報告書サンプル_2018-05.xlsx")
$excel.Quit()
$excel = $Null
[GC]::collect()

上書きの場合は $book.Save() で良い。
最後2行はプロセス解放のために必要。