Zenn
💙

PowerShellでExcelを操作する

2021/07/18に公開
Excel
PowerShell
#
COM
tech

はじめに

Excel で VBA などでマクロを書いてやりたいことを実現することがあります。
しかし、データとコードが結合して管理され、また、バイナリファイルであるため、管理性はよくありません。
そこで、 Excel を PowerShell 5.1 で操作してみましょう。

コード1: COMの明示的な解放なし

このコードは問題がありますが、おそらく動きます。
問題とその対処は後述します。

# Excel を開く。
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Excel.DisplayAlerts = $False

# ブックを開く。
$Item = Get-Item .\book.xlsx
$UpdateLinks = 0 # リンクを更新しない。
$ReadOnly = $True # 読み込み専用で開く。
$Books = $Excel.Workbooks
$Book = $Books.Open($Item.FullName, $UpdateLinks, $ReadOnly)

# ブックに何かする。
# ...

# ブックを閉じる。
$SaveChanges = $False # 変更を保存しない。
$Book.Close($SaveChanges)

# Excel を閉じる。
$Excel.Quit()

コード1の問題と対処

このコードは動作させると期待通りに動作しますが、COMの解放を行っていません。
そのため、この記事によれば以下の影響が発生しうる状況です。

  • 意図とは異なる COM オブジェクトに接続され、想定とは異なる結果を返し、アプリケーションの動作に予期せぬ影響を与えます。
  • 通常のメソッド実行にも関わらず COMException 等の例外が返さることがあります。
  • イベント呼び出し時にフリーズすることがあります。
  • Office の内部処理 (例. 終了時の処理など) に予期せぬ影響を及ぼすことがあります。
  • 解放漏れのオブジェクトがメモリを圧迫します。

対処としては、上記の影響を受け入れるか、または、ベストプラクティスに従いCOMオブジェクトを解放するか、です。

コード2: COMの明示的な解放あり

コード1に解放処理を加えたコードです。
コード1からの変更点は次の通りです:

  • COMオブジェクトを生成したらスタックに格納する。
  • COMオブジェクトを解放する処理を加える。
# 後でCOMオブジェクトを解放するためのスタックを作る。
# COMオブジェクトを生成したら、このスタックに都度格納する。
$Refs = [System.Collections.Generic.Stack[System.WeakReference]]::new()

# Excel を開く。
$Excel = New-Object -ComObject Excel.Application
$Refs.Push([System.WeakReference]::new($Excel))
$Excel.Visible = $True
$Excel.DisplayAlerts = $False

# ブックを開く。
$Item = Get-Item .\book.xlsx
$UpdateLinks = 0 # リンクを更新しない。
$ReadOnly = $True # 読み込み専用で開く。
$Books = $Excel.Workbooks
$Refs.Push([System.WeakReference]::new($Books))

$Book = $Books.Open($Item.FullName, $UpdateLinks, $ReadOnly)
$Refs.Push([System.WeakReference]::new($Book))

# ブックに何かする。
# ...

# ブックを閉じる。
$SaveChanges = $False # 変更を保存しない。
$Book.Close($SaveChanges)

# COMオブジェクトを破棄し、アプリケーションを終了する。
while ($Refs.Count) {
    $ComRef = $Refs.Pop()

    # 全変数からCOMオブジェクトと変数値の参照が等しい変数を得る。
    $ComVar = Get-Variable |
    Where-Object { [Object]::ReferenceEquals($ComRef.Target, $_.Value) }

    # Applicationオブジェクトか判定する。
    $IsApp = $ComRef.Target -is [Microsoft.Office.Interop.Excel.Application]

    # ガベージコレクト後にアプリケーションを終了する。
    if ($IsApp) {
        # https://social.msdn.microsoft.com/Forums/ja-JP/5deec897-a897-404b-a610-f7d894fde1b3
        # > アプリケーションの終了時には、Excel からの COM オブジェクトの参照解放処理が行われます。
        # > このタイミングまでに適切に COM オブジェクトが解放されていないと、
        # > ガベージコレクトのタイミングによっては予期せぬエラーが生じる場合があります。
        # > このため、アプリケーションの終了前にまずガベージコレクトを実行します。
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
        $ComRef.Target.Quit()
    }

    # ComObjectを解放する。
    while ([Runtime.Interopservices.Marshal]::ReleaseComObject($ComRef.Target)) { }
    $ComRef.Target = $null

    # 変数を消す。
    $ComVar | Remove-Variable
    Remove-Variable ComRef

    # Application オブジェクトのガベージコレクトする。
    if ($isApp) {
        # https://social.msdn.microsoft.com/Forums/ja-JP/5deec897-a897-404b-a610-f7d894fde1b3
        # > さらに、Application インスタンスを解放する際なのですが、
        # > Marshal.ReleaseComObject メソッドを使用して参照カウンタをデクリメントしただけでは、
        # > プロセスが終了することを保証できませんので、
        # > GC.Collect メソッドでガベージコレクトを強制してオブジェクトを解放しています。
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
    }
}

動作環境

> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      5.1.19041.1023
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.19041.1023
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

まとめ

  • Excel を PowerShell から操作できました。
  • COMオブジェクトの解放が必要なことに注意しましょう。

参考資料

Discussion