PowershellでExcelファイルを作る

はじめに

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

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

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

前提条件

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

Powershellバージョンの確認

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

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を起動します。

main.ps1

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

表示の有無

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

main.ps1

    # $excel.Visible = $true

高速化設定

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

main.ps1

    $excel.DisplayAlerts = $false
    $excel.ScreenUpdating = $false
    $excel.EnableEvents = $false

ブックを作成

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

main.ps1

    [__ComObject]$book = $excel.Workbooks.Add()

シートを作成

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

main.ps1

    [__ComObject]$sheet = $book.WorkSheets(1)

シート名を変更

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

main.ps1

    $sheet.Name = "CookingSheet"

データを用意

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

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)

配列を書き込む

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

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
    }

セルの幅を変更

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

main.ps1

    $sheet.Columns.ColumnWidth = 3

保存先のフォルダ

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

main.ps1

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

保存

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

main.ps1

    $book.SaveAs("$(Get-Location)\output\generated_from_posh_$(Get-Date -Format yyyyMMdd_HHmmss).xlsx")

高速化設定解除

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

main.ps1

    $excel.DisplayAlerts = $true
    $excel.ScreenUpdating = $true
    $excel.EnableEvents = $true

ウィンドウを閉じる

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

main.ps1

    $excel.Quit()
}

エラーハンドリング

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

main.ps1

catch {
    Write-Host "Error" -ForegroundColor Red
}

開放

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

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

まとめ

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

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スクリプトの実行結果です。

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

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

Excelsior!