PowershellでExcelファイルを作る

# はじめに

ExcelVBAから脱出したいですが、なかなかそうもいきません。そんななか、「PowershellでExcel操作ができるらしい。」という情報を得たものの実際に利用するまでには至らない。そんなケースが多いのではないでしょうか？

その背景には、情報が少なかったり、VS Code上でのインテリセンスも効きかなかったり、様々な事情があると思います。

ニッチではありますが、そういった苦悩がありますので、非常に有用な内容だと思います。そこで、今回は私がPowershellでExcelを操るときの基本的なコードをご紹介します。

# 前提条件

- OS : Windows
- Excel : 2007 or Later
- Powershell : 6 or Later

# Powershellバージョンの確認

ひとまず、Powershellのバージョンが、6以上であるか確認します。
Excelのバージョン確認もあったほうが良いかもしれませんね。

```posh:main.ps1
using namespace System.Collections.Generic

if ([int]$psversiontable.psversion.major -lt 6) {
    Write-Host "PowerShell version need 6 or later" -BackgroundColor Red -ForegroundColor White
}
else {
    Write-Host "PowerShell version is Fit" -BackgroundColor Green -ForegroundColor White
}
```

# Excelの起動

まずは、ExcelのWindowを起動します。

```posh:main.ps1

try {
    [System.__ComObject]$excel = New-Object -ComObject Excel.Application
```

# 表示の有無

このとき、画面を表示したければ、コメントアウトを解除して、表示します。
デフォルトでは非表示です。高速に動作させたい場合はそのままで良いでしょう。

```posh:main.ps1
    # $excel.Visible = $true

```

# 高速化設定

VBAでもよく見かける、一時的な高速化設定をします。

```posh:main.ps1
    $excel.DisplayAlerts = $false
    $excel.ScreenUpdating = $false
    $excel.EnableEvents = $false
```

# ブックを作成

ウィンドウを立ち上げましたが、ブックは開いていないので、ウィンドウ枠内は灰色１色で何もありません。
（※先ほどの高速化設定をしなくても灰色１色です。）
まずは、ブックを新規追加します。

```posh:main.ps1
    [__ComObject]$book = $excel.Workbooks.Add()
```

# シートを作成

ブックの中は空なので、新しくシートを追加します。

```posh:main.ps1
    [__ComObject]$sheet = $book.WorkSheets(1)
```

# シート名を変更

デフォルトでシート名は`Sheet[0-9]+`ですが、ナンセンスなので命名してあげます。

```posh:main.ps1
    $sheet.Name = "CookingSheet"
```

# データを用意

ここから、ようやくシートにデータを書き込めるようになります。
まずは、セルに書き込むためのデータを用意しましょう。

```posh:main.ps1
    [Microsoft.VisualBasic.VariantType]$Empty = [Microsoft.VisualBasic.VariantType]::Empty
    [Microsoft.VisualBasic.VariantType[]]$array1 = (1, 2, $Empty, 3, $Empty, $Empty, $Empty, 4, $Empty, 5, $Empty, 6, $Empty, $Empty, $Empty, 7, $Empty, 8, $Empty, 9, $Empty, $Empty, $Empty, 10, $Empty, 11)
```

# 配列を書き込む

１セルずつ書き込んでいては遅いので、配列で一気に書き込みます。
今回は１次元配列で書き込みました。２次元配列でも同様に書き込むことができます。

```posh:main.ps1
    for ($i = 0; $i -lt 10000; $i++) {
        $sheet.Range($sheet.Cells($i + 1, 1), $sheet.Cells($i + 1, $array1.Count)).Value(10) = $array1
    }
```

# セルの幅を変更

見やすくなるように、セルの列幅を一括変更します。

```posh:main.ps1
    $sheet.Columns.ColumnWidth = 3
```


# 保存先のフォルダ

出来上がったExcelファイルを保存するフォルダがあるか確認します。
もちろん、無ければ作成します。

```posh:main.ps1
    if (!(Test-Path("output"))) {
        New-Item -Path "output" -ItemType Directory
    }
```

# 保存

名前を付けて保存します。カレントディレクトリに「output」フォルダを作成し、その中でファイル名に日付で連番を付けて保存しています。

```posh:main.ps1
    $book.SaveAs("$(Get-Location)\output\generated_from_posh_$(Get-Date -Format yyyyMMdd_HHmmss).xlsx")
```

# 高速化設定解除

先ほど行った高速化設定は、次回のExcel起動時に残っていると困ります。
そのため、処理の最後に元通りにしておきます。

```posh:main.ps1
    $excel.DisplayAlerts = $true
    $excel.ScreenUpdating = $true
    $excel.EnableEvents = $true
```

# ウィンドウを閉じる

Excelのウィンドウを閉じます。

```posh:main.ps1
    $excel.Quit()
}
```

# エラーハンドリング

特に何も処理していませんが、もし何かエラーがあったらcatchして知らせてくれます。

```posh:main.ps1
catch {
    Write-Host "Error" -ForegroundColor Red
}
```

# 開放

最後の後片付けです。
Comオブジェクトを開放してあげます。
ガベージコレクションします。

```posh:main.ps1
finally {
    # $excel = $Null

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
    [GC]::collect()
}

Write-Host "Finish" -ForegroundColor Green
# [Console]::ReadKey($true) | Out-Null

```

# まとめ

まとめるとこうなります。

```posh:main.ps1
using namespace System.Collections.Generic

if ([int]$psversiontable.psversion.major -lt 6) {
    Write-Host "PowerShell version need 6 or later" -BackgroundColor Red -ForegroundColor White
}
else {
    Write-Host "PowerShell version is Fit" -BackgroundColor Green -ForegroundColor White
}

try {
    [System.__ComObject]$excel = New-Object -ComObject Excel.Application
    # $excel.Visible = $true

    $excel.DisplayAlerts = $false
    $excel.ScreenUpdating = $false
    $excel.EnableEvents = $false
    [__ComObject]$book = $excel.Workbooks.Add()
    [__ComObject]$sheet = $book.WorkSheets(1)
    $sheet.Name = "CookingSheet"
    [Microsoft.VisualBasic.VariantType]$Empty = [Microsoft.VisualBasic.VariantType]::Empty
    [Microsoft.VisualBasic.VariantType[]]$array1 = (1, 2, $Empty, 3, $Empty, $Empty, $Empty, 4, $Empty, 5, $Empty, 6, $Empty, $Empty, $Empty, 7, $Empty, 8, $Empty, 9, $Empty, $Empty, $Empty, 10, $Empty, 11)
    for ($i = 0; $i -lt 100000; $i++) {
        $sheet.Range($sheet.Cells($i + 1, 1), $sheet.Cells($i + 1, $array1.Count)).Value(10) = $array1
    }
    $sheet.Columns.ColumnWidth = 3

    if (!(Test-Path("output"))) {
        New-Item -Path "output" -ItemType Directory
    }

    $book.SaveAs("$(Get-Location)\output\generated_from_posh_$(Get-Date -Format yyyyMMdd_HHmmss).xlsx")
    $excel.DisplayAlerts = $true
    $excel.ScreenUpdating = $true
    $excel.EnableEvents = $true
    $excel.Quit()
}
catch {
    Write-Host "Error" -ForegroundColor Red
}
finally {
    # $excel = $Null

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null
    [GC]::collect()
}

Write-Host "Finish" -ForegroundColor Green
# [Console]::ReadKey($true) | Out-Null

```

# 結果

このPowershellスクリプトの実行結果です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/0da64597-4d8b-82cd-e6e8-e1025f570ed6.png)

ComObjectに対するインテリセンスの効きが弱いのがデメリットですが、
Powershellならではの記法や、VScodeのデバッガが使える利点は大きいです。

この記事で、Powershell x Excelの組み合わせを実践する人が増えるといいですね。

**Excelsior!**