社内SEの話

日々起きたことの記録用

【PowerShell】Excel チートシート

テンプレート

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $true
$book = $excel.Workbooks.Open(ファイルパス)
$sheet = $book.Sheets(1)
$r = 1
$c = 1
$sheet.cells.item($r,$c).value() ="Hello Excel"
$book.save()
$excel.Quit()
$excel = $null

オブジェクト呼び出し

$excel = New-Object -ComObject Excel.Application

実行中Excelを表示するか

$excel.Visible = $true
$true 表示
$false 非表示

画面の更新の停止

$excel.ScreenUpdating = $false $true 更新 $false 更新の停止

learn.microsoft.com

VisibleをFalseにしている場合、ScreenUpdatingをFalseにしても実行時間に影響はなさそう

エラーメッセージの表示をするか

$excel.DisplayAlerts = $true
$true 表示
$false 非表示

保存

上書き保存

$book.save()

名前をつけて保存

$book.saveas(保存先)

Excelオブジェクトを閉じる

$excel.Quit()
$excel = $null
Quite()で終了できますが、COMオブジェクトを使っているのでオブジェクトの開放をしないとプロセスが残ってしまう不具合が発生してしまいます。
必ずガベージコレクションをしてあげましょう。

新規ファイルを作成

$book = $excel.Workbooks.Add()

既存のファイルを開く

$book = $excel.Workbooks.Open(ファイルパス)

docs.microsoft.com

シート選択を番号で指定

$sheet = $excel.Worksheets.Item(1)
もしくは
$sheet = $book.Sheets(1)

シート選択をシート名で指定

$sheet = $book.Sheets(シート名)

シートの存在確認

if($book.Sheets(シート名)){
#シートあり
}else{
#シートなし
}

指定のシートの前にシートの追加

$sheet = $book.Sheets(1)
[void]$book.Worksheets.Add()
現在指定されているシートの次にシートを追加する
開いた直後でシートを選択していない場合は、閉じる前に選択していたシートが選択される

指定のシートの後ろにシートの追加

$sheet = $book.Sheets(1)
[void]$book.Worksheets.Add([System.Reflection.Missing]::Value, $sheet)

最後のシートの後ろにシートを追加する場合

$sheet = $book.Sheets($book.Sheets.Count)
[void]$book.Worksheets.Add([System.Reflection.Missing]::Value, $sheet)

シートの削除

$sheet = $book.Sheets(シート番号もしくはシート名)
$sheet.Delete()
例)
$sheet = $book.Sheets(1)
$sheet.Delete()

複数のシートの削除

$sheet = $book.Sheets(配列で指定したシート番号、シート名)
$sheet.Delete()
例)
$sheet = $book.Sheets(@(1,2))
$sheet.Delete()
もしくは
$sheet = $book.Sheets(@("Sheet1","Sheet2"))
$sheet.Delete()

すべてのシートを削除しようとするとエラーになります。
1つ以上のシートを残す必要があります。

シート名の取得

$name = $sheet.Name
もしくは
$name = $book.Sheets(1).name

シート名を変更

$sheet = $book.Sheets(1)
$sheet.Name = "シート名を変更"

セルの値を取得

セルを数値で指定する

$v= $sheet.cells.item(行番号,列番号).value()

例)
$r = 1
$c= 2
write-host $sheet.cells.item($r,$c).value()

セルをセル番号で指定する

write-host $sheet.range(セル番号).value()
例)
write-host $sheet.range($rng).value()
もしくは
$rng = "A1"
write-host $sheet.range($rng).value()

セルに値を書き込む

セルを数値で指定する

$sheet.cells.item(行番号,列番号).value() = 書き込む値

例)
$r = 1
$c = 2
$sheet.cells.item($r,$c).value() ="Hello World"

セルをセル番号で指定する

$sheet.range(セル番号).value() = 書き込む値
例)
$sheet.range($rng).value() ="Hello World"
もしくは
$rng = "A1"
$sheet.range($rng).value() ="Hello World"

以降の解説ではセルは数値で指定していますが、Rnageではセル番号の指定でも同様に動作します。

セルに関数を書き込む

$sheet.cells.item(行番号,列番号)..Formula = "=関数名"
例)
$sheet.cells.item(1,1).Formula ="=TODAY()"

列の追加

$sheet.Columns.item(列番号).Insert()
指定した列番号に列を挿入するので、右に列がシフトする。
実行すると実行結果が出力されるので、非表示にするには先頭に[void]かOut-Null をパイプラインで渡す。
例)
$sheet.Columns.item(2).insert()

結果非表示

[void]$sheet.Columns.item(2).insert()
$sheet.Columns.item(2).insert() | Out-Null

行の追加

$sheet.Rows.item(行番号).Insert()
指定した行番号に行を挿入するので、下に行がシフトする。
実行すると実行結果が出力されるので、非表示にするには先頭に[void]かOut-Null をパイプラインで渡す。
例)
$sheet.Rows.item(2).insert()

結果非表示

[void]$sheet.Rows.item(2).insert()
$sheet.Rows.item(2).insert() | Out-Null

列の削除

$sheet.Columns.item(列番号).delete()
指定した列が削除されるので列が左にシフトします。
実行すると実行結果が出力されるので、非表示する場合は先頭に[void]かOut-Null をパイプラインで渡す。
例)
$sheet.Columns.item(2).delete()

結果非表示

[void]$sheet.Columns.item(2).delete()
$sheet.Columns.item(2).delete() | Out-Null

行の削除

$sheet.Rows.item(行番号).delete()
指定した行が削除されるので行が上へシフトします。
実行すると実行結果が出力されるので非表示する場合は先頭に[void]を付け加える
例) $sheet.Rows.item(2).delete()

結果非表示

[void]$sheet.Rows.item(2).delete()
$sheet.Rows.item(2).delete() | Out-Null

列の幅の調整

$sheet.columns(列番号).ColumnWidth = 幅数値
例)
$sheet.columns(1).ColumnWidth = 10

すべての列の幅を指定の数値に変更

$sheet.Range("1:1").EntireColumn.ColumnWidth = 10

行の高さの調整

$sheet.rows(行番号).RowHeight = 高さ数値
例)
$sheet.rows(1).RowHeight = 20

すべての行の高さを指定の数値に変更

$sheet.Range("A:A").EntireRow.RowHeight = 15

マクロの実行

$excel.run(マクロ名)
例)
$excel.run("runMacro")

フォントの変更

$sheet.cells.item(セル位置).Font.name = フォント名
例)
$sheet.cells.item(1,1).Font.name = "MS Pゴシック"
PCにインストールされているフォントであること

フォントサイズの変更

$sheet.cells.item(セル位置).Font.Size = フォントサイズ
例)
$sheet.cells.item(1,1).Font.Size = 10

フォントの色の変更

カラーインデックスを使用する

$sheet.cells.item(セル位置).Font.ColorIndex = カラー番号
例)
$sheet.cells.item(1,1).Font.ColorIndex = 3

背景の変更

$sheet.Cells.Item(セル位置).Interior.ColorIndex = カラー番号
例)
$sheet.Cells.Item(1,1).Interior.ColorIndex = 15

範囲選択して一括して背景変更

$sheet.Range("A1","C1").Interior.ColorIndex = 15

文字を太字に変更

$sheet.cells.item(セル位置).Font.Bold = $True
$Trueが太字
$Falseが太字解除
例)
$sheet.cells.item(1,1).Font.Bold = $True

範囲選択して一括して太字

$sheet.Range("A1","C1").Font.Bold = $True

文字を斜体に変更

$sheet.cells.item(セル位置).Font.Italic = $True
$Trueが斜体
$Falseが斜体解除
例)
$sheet.cells.item(1,1).Font.Italic = $True

範囲選択して一括して斜体

$sheet.Range("A1","C1").Font.Italic = $True

文字に下線を追加

$sheet.cells.item(セル位置).Font.Underline = $True
$Trueが下線あり
$Falseが下線なし
例)
$sheet.cells.item(1,1).Font.Underline = $True

1重下線以外の下線を引く場合

$Trueの場合は1本線下線が設定されます。

下線なし xlUnderlineStyleNone -4142
1重下線 xlUnderlineStyleSingle 2
2重下線(太い) xlUnderlineStyleDouble -4119
2本下線(細い) xlUnderlineStyleDoubleAccounting 5

learn.microsoft.com

VBAの場合は列挙が登録されているのでxlUnderlineStyleSingleなどで設定ができますが、PowerShellの場合は値を指定する
例)
$sheet.cells.item(1,1).Font.Underline = -4119