PowerShell を使ってExcel を操作する

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行はプロセス解放のために必要。