💙
PowerShellでExcelを操作する
はじめに
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オブジェクトの解放が必要なことに注意しましょう。
参考資料
- Workbooks.Open method (Excel)
- .NETを使った別プロセスのOfficeの自動化が面倒なはずがない―そう考えていた時期が俺にもありました。
- OfficeをCOM Object経由でPowershellから扱うときの面倒を少しマシにする
- Office オートメーションで割り当てたオブジェクトを解放する – Part1
- Office オートメーションで割り当てたオブジェクトを解放する - Part2
Discussion