テンプレート
$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 更新の停止
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(ファイルパス)
シート選択を番号で指定
$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
最終行を取得
行にすべて値がある場合
$maxRow = $sheet.cells(1,1).end(-4121).row
行に空欄がある場合
$maxRow = $sheet.cells(1048576,1).end(-4162).row
やっていること
一度Excelの最大行まで移動し、Ctrl+↑を押下したと同じ動作
-4121や-4162はxlDownやxlUpの値になります。VBAであれば、xlDownと指定できますが、enumのデータがないので、数値で処理します
docs.microsoft.com
列の幅の調整
$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 = "ＭＳ Ｐゴシック"
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
VBAの場合は列挙が登録されているのでxlUnderlineStyleSingleなどで設定ができますが、PowerShellの場合は値を指定する
例)
$sheet.cells.item(1,1).Font.Underline = -4119
罫線を引く
罫線を引く場合は、セルを指定し、そのプロパティとして引く場所、線種、線の太さを指定します。
そのためプログラムが複雑化しやすいので注意して構築してください。
基本的な罫線を引く場合(4方を実線で引く)
セル.borders(罫線の引く場所).LineStyle = 罫線の種類
罫線の引く場所を省略すると4方を囲む罫線が引かれます
$cell = $sheet.cells(1,1)
$cell.borders.LineStyle = 1
もしくは
$range = $sheet.Range("A1")
$range.borders.LineStyle = 1
特定の辺に罫線を引く場合
$cell = $sheet.cells(1,1)
$cell.borders(9).LineStyle = 1
例のプログラムでは下辺に罫線を引きます。
他の場所に罫線を引く場合は下表のBordersプロパティを参考にしてください。
罫線の種類は罫線の種類を参考にしてください。
罫線の引く場所
| 名前 | 値 | 説明 |
|---|---|---|
| xlDiagonalDown | 5 | セルまたは範囲の左上から右下への斜め罫線 |
| xlDiagonalUp | 6 | セルまたは範囲の右上から左下への斜め罫線 |
| xlEdgeLeft | 7 | セルまたは範囲の左辺の罫線 |
| xlEdgeTop | 8 | セルまたは範囲の上辺の罫線 |
| xlEdgeBottom | 9 | セルまたは範囲の下辺の罫線 |
| xlEdgeRight | 10 | セルまたは範囲の右辺の罫線 |
| xlInsideVertical | 11 | 垂直の罫線を引く ※範囲選択のみ有効 |
| xlInsideHorizontal | 12 | 平行の罫線を引く ※範囲選択のみ有効 |
罫線の種類
| 名前 | 値 | 説明 |
|---|---|---|
| xlContinuous | 1 | 実線 |
| xlDash | -4115 | 破線 |
| xlDashDot | 4 | 一点鎖線 |
| xlDashDotDot | 5 | ニ点鎖線 |
| xlDot | -4118 | 点線 |
| xlDouble | -4119 | 2 本線 |
| xlLineStyleNone | -4142 | 線なし |
| xlSlantDashDot | 13 | 斜破線 |
表にはないが
下記の機能もある
| 名前 | 値 | 説明 |
|---|---|---|
| xlNone | 0 | 線なし |
罫線を消したい場合はxlNoneを使ったほうが体感的に使える
範囲選択をしたセルに対して罫線を引く場合
$cell1 = $sheet.cells.item(1,1)
$cell2 = $sheet.cells.item(10,10)
$range =$sheet.Range($cell1,$cell2)
$range.borders.LineStyle = 1
もしくは
$range = $sheet.Range("A1:J10")
$range.borders.LineStyle = 1
選択範囲の中のセルだけ罫線を引く場合
例
手順は垂直、平行方向に罫線を設定した後、枠に位置する罫線を消す
$sheet = $book.Sheets(1)
$cell = $sheet.cells(2,2)
$cell2 = $sheet.cells(10,10)
$range = $sheet.Range($cell,$cell2)
$range.borders(11).LineStyle = 1
$range.borders(12).LineStyle = 1
$range.borders(7).LineStyle = 0
$range.borders(8).LineStyle = 0
$range.borders(9).LineStyle = 0
$range.borders(10).LineStyle = 0
罫線の太さの変更
すでに罫線が引かれている事が条件
1つのセルに対して罫線を引く場合
$cell = $sheet.cells(1,1)
$cell.borders(5).LineStyle = 1
$cell.Borders.Weight = 4
太さの種類
| 名前 | 値 | 説明 |
|---|---|---|
| xlHairline | 1 | 細線 (最も細い罫線) |
| xlMedium | -4138 | 普通 |
| xlThick | 4 | 太線 (最も太い罫線) |
| xlThin | 2 | 極細 |
