Powershellのexcel操作でCom終了？

質問

PowershellでCSVファイルをExcelに書き出すコードを正解かどうか分かりませんが他のサイト等を参考に継ぎ接ぎで下記のようにしましたが、COMオブジェクトの終了の方法がこれで正しいのかお伺いいたします。
#QueryTableオブジェクトを使ってCSVを取り込む方法
# Excelを操作する為の宣言
$excel = New-Object -ComObject Excel.Application
# 起動直後は非表示（最小化ではない）になっているので表示する
$excel.Visible = $true
#新規ブックを作成する
$book = $excel.Workbooks.Add()
# シートを取得する
$sheet = $excel.Worksheets.Item(1)
# CSVファイル
$csvFile = "D:\test\pp002.csv"
# 書き出し開始セル
$startRange = "B2"
# 出力列(書き出し開始セルの列)
$outputColumn = $sheet.Range($startRange).Column
# 出力行(書き出し開始セルの行)
$outputRow = $sheet.Range($startRange).Row

# 読み込むファイルの形式を【2:文字列】と指定するための配列を作成
$arrDataType = @()
for ($i=0; $i -lt 255; $i++){
$arrDataType += 2
}
# QueryTableオブジェクトを作成
$QueryTable = $sheet.QueryTables.Add("TEXT;$csvFile",$sheet.cells($outputRow,$outputColumn))
# 区切り文字に「カンマ区切り」を指定
$QueryTable.TextFileCommaDelimiter = $True
# 文字コードに「Shift_JIS」を指定
#$QueryTable.TextFilePlatform = 932
# 文字コードに「UTF-8」を指定
$QueryTable.TextFilePlatform = 65001
# 読み込み開始行を指定
$QueryTable.TextFileStartRow = 1
# 読み込むファイルの形式を指定
$QueryTable.TextFileColumnDataTypes = $arrDataType[0..255]
# 読み込み実行
$QueryTable.Refresh($false)
# 名前を指定(後続処理で削除できるようにするため)
$QueryTable.Name = "仮テーブル"
# 作成された「QueryTableオブジェクト(＝クエリと接続)」を削除
$QueryTable.Delete()
# 上記で作成されてしまう名前定義(仮テーブル)を削除
foreach($n in $book.Names){
If ($n.Name -Like $loadSheetName + "!" + "仮テーブル*") {
$n.Delete()
}
}
#COMオブジェクトの破棄とメモリの解放
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null
[GC]::Collect()
全体のコードの是非も問いたいところですが特に最後の2行で行う処理についてです。
いままで、コードが不十分だったためかどうか分かりませんが読み取り専用になったりプロセスが残ったままになったりメモリの問題等で再三PCの再起動を余儀なくされる事に陥っているため宜しくお願いいたします。
ちなみにいままでは
$excel = $null
[GC]::Collect()
で処理していました。

回答No.5

luka3

VBAのDoEventsみたいのがあれば、と思って検索すると
[Application]::DoEvents()
[System.Windows.Forms.Application]::DoEvents()
などがヒットするのですが、
自分でも試してみましたが今は使えないみたいですね。
失礼しました。

補足

前の補足で意味のない事をしていた。csvの列数を云々といっていたがこれはデータタイプに適用されているもので補足コードでも1441列中255迄しか適用されていなかった。そこで考えてみたがデータタイプ部分が不要ではないかと思い下記のようにしてみると
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$csvFile = "D:\test\pp003.csv"
$startRange = "B2"
$outputColumn = $sheet.Range($startRange).Column
$outputRow = $sheet.Range($startRange).Row
$QueryTable = $sheet.QueryTables.Add("TEXT;$csvFile",$sheet.cells($outputRow,$outputColumn))
$QueryTable.TextFileCommaDelimiter = $True
$QueryTable.TextFilePlatform = 65001
$QueryTable.TextFileStartRow = 1
$QueryTable.Refresh($false)
$QueryTable.Name = "仮テーブル"
$QueryTable.Delete()
foreach($n in $book.Names){If ($n.Name -Like $loadSheetName + "!" + "仮テーブル*") {$n.Delete()}}
gv | ? Value -is [__ComObject] | clv
[gc]::Collect()
[gc]::WaitForPendingFinalizers()
1|%{$_} > $null
[gc]::Collect()
今回のCSVデータは、文字列と数字と明確に分かれているため特段の支障がなく書き出された。項目の数字を文字列とする場合は考慮の必要があるが全体を文字列として扱うことはないので後処理で十分かも。
なお、何度か繰り返しているとどこかのタイミングで解放等の処理が不十分状態（内部的に）となりPowershellの繰り返し実行がメモリの関係で出来なくなりPCの動作がおかしくなるみたいに感じられる・・・推測でしかない

回答No.4

UY-Scuti

さらに続きで、結局は、
「OfficeをCOM Object経由でPowershellから扱うのはそれほど面倒じゃありませんでした」
https://qiita.com/SilkyFowl/items/b4b6271619bd6d3824f7

お礼

有り難うございます。
このサイトも初めて見ました。残念ながら良く理解できない・・・
一応
<#セルA1にNullを代入して書き出しを確定#>
$sheet.Range("A1").Value = $empty
<#COMオブジェクトの破棄とメモリの解放#>
gv | ? Value -is [__ComObject] | clv
[gc]::Collect()
[gc]::WaitForPendingFinalizers()
1|%{$_} > $null
[gc]::Collect()
で試してみたところ最初のところは書き出せているが、さらに繰り返し試してみます。

補足

下記のコードで繰り返しターミナル他で実行しましたが今のところ問題は発生していません。
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$csvFile = "D:\test\pp004.csv"
$numberOfColumns = Get-Content $csvFile | ForEach-Object{($_.split(",")).Count} | Measure-Object -Maximum | Select-Object -ExpandProperty Maximum
$startRange = "B2"
$outputColumn = $sheet.Range($startRange).Column
$outputRow = $sheet.Range($startRange).Row
$arrDataType = @()
for ($i=0; $i -lt $numberOfColumns; $i++){ $arrDataType += 2}
$QueryTable = $sheet.QueryTables.Add("TEXT;$csvFile",$sheet.cells($outputRow,$outputColumn))
$QueryTable.TextFileCommaDelimiter = $True
$QueryTable.TextFilePlatform = 65001
$QueryTable.TextFileStartRow = 1
$QueryTable.TextFileColumnDataTypes = $arrDataType
$QueryTable.Refresh($false)
$QueryTable.Name = "仮テーブル"
$QueryTable.Delete()
foreach($n in $book.Names){If ($n.Name -Like $loadSheetName + "!" + "仮テーブル*") {$n.Delete()}}
$sheet.Range("A1").Value = $empty
gv | ? Value -is [__ComObject] | clv
[gc]::Collect()
[gc]::WaitForPendingFinalizers()
1|%{$_} > $null
[gc]::Collect()
最初のコードではExcel2003の最大列数を使っていたので、その部分を
$csvFile = "D:\test\pp004.csv"
$numberOfColumns = Get-Content $csvFile | ForEach-Object{($_.split(",")).Count} | Measure-Object -Maximum | Select-Object -ExpandProperty Maximum
で取得した値に変えました。
基本このコードが正しいのか分かりませんが経過報告です。

回答No.3

luka3

＞ここでベストプラクティスとしている方法みたいに長い処理が必要なのでしょうか？

私は実際にPowerShellでExcelの操作はしておらず、ここの質問に答えるためにサンプル的に試す程度で、メモリの解放まで追求したことはないです。
ただ、そのサイトによればマイクロソフトの中の人を参考にしたとありますので、信頼性はかなり高いと思いますので、あとはどこまで忠実に実行するか、になりますでしょうか。

なんとなく、そこまで問題が出るのであれば、餅は餅屋で、エクセルマクロで処理した方がいいのではないかと思ってしまいます。

＞注意マークが示されません

[Application]::DoEvents()
をどこかのタイミングで呼び出してみたらどうなりますか？

お礼

ご回答ありがとうございます。
>なんとなく、そこまで問題が出るのであれば、餅は餅屋で、エクセルマクロで処理した方がいいのではないかと思ってしまいます。
これに関しては至極真っ当なご意見だと思います。
今回は、powershellで配列データをexcelに書き出す流れでCSVの場合どうすれば出来るのかと思い立ち試しているところです。ちなみに再起動等をを要するような状況に陥ったのは、QueryTableオブジェクトを使ってCSVを取り込む方法を試すようになってからです。同じようにComObjectを使ってその他のExcel操作をする場合（但しその場合は他のアプリからコマンドラインで実行することが多いが）は、このようなトラブルに見舞われたことはありませんでした。そこでComObjectの解放等に問題があるのかなとお思い質問した次第です。ちなみに起こるタイミングは他のアプリから実行した後ターミナルで実行した場合に不具合が発生すろことが多い・・・但し常時ではないのでなんとも言えないが
[Application]::DoEvents()・・使い方が分からないので
[Application]::DoEvents()
$QueryTable = $null
$excel = $null
$book = $null
$sheet = $null
[GC]::Collect()
で試したところ注意マークはつきましたが下記のエラーだ出ました。
Unable to find type [Application].

回答No.2

luka3

・【PowerShell】Excel操作処理後にExcelのプロセスが残る
https://boonx4m312s.hatenablog.com/entry/2022/12/05/180000

・PowerShell①Excel自動操作編
https://www.any.co.jp/staff/2020/09/16/powershell%E2%91%A0excel%E8%87%AA%E5%8B%95%E6%93%8D%E4%BD%9C%E7%B7%A8/

・.NETを使ったOfficeの自動化が面倒なはずがない―そう考えていた時期が俺にもありました。
https://needtec.sakura.ne.jp/wod07672/2020/03/29/net%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%9Foffice%E3%81%AE%E8%87%AA%E5%8B%95%E5%8C%96%E3%81%8C%E9%9D%A2%E5%80%92%E3%81%AA%E3%81%AF%E3%81%9A%E3%81%8C%E3%81%AA%E3%81%84%E2%80%95%E3%81%9D%E3%81%86%E8%80%83/

参考になりますか

お礼

ありがとうございます
1【PowerShell】Excel操作処理後にExcelのプロセスが残る
2PowerShell①Excel自動操作編
これらは以前見ました。特に下記の書き込みが印象的でした。
・・COMオブジェクトの破棄にクセがありますね。
「PowerShell」でCOMオブジェクトを扱うのは、あまり得策ではないかもしれません。・・
3.NETを使ったOfficeの自動化が面倒なはずがない―そう考えていた時期が俺にもありました。
のサイトは初見ですが、ここでベストプラクティスとしている方法みたいに
stem.Runtime.Interopservices.Marshal]::ReleaseComObject($cell) | Out-Null
↓
stem.GC]::Collect()
の長い処理が必要なのでしょうか？

補足

多分これは関係ないと思いますが、最初に示したコードではexcelを表示したままにするのですがその表示において数字を文字列としている左上の注意マークが示されません。別のセルに値の入力等を加えれば出てきます。なんか確定前みたいな状態で気になるところです。
そこで空きセルに $emptyを使いNullを代入するようにしています。

回答No.1

AIエージェント あい（@OKWAVE-AI）

回答できる相談の範囲内の内容ですので、以下に回答をさせていただきます。

ご提示いただいたPowershellのコードは、基本的なCSVファイルをExcelに書き出す処理を行うもののようです。COMオブジェクトの終了方法については、一般的には以下のような手順で行っています。

1. COMオブジェクトの使用が終わったら、そのオブジェクトを解放するために、`Release`メソッドを呼び出します。

```powershell
$QueryTable.ReleaseComObject()
```

2. `$excel`オブジェクトを解放するために、`Quit`メソッドを実行してExcelのプロセスを終了させます。

```powershell
$excel.Quit()
```

3. COMオブジェクトとの接続を解除するために、`FinalReleaseComObject`メソッドを使用します。

```powershell
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($QueryTable) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($sheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($book) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null
```

4. ガベージコレクションを実行してメモリを解放します。

```powershell
[GC]::Collect()
```

このようにCOMオブジェクトを適切に終了させることで、リソースの解放とプロセスの終了を行うことができます。

また、回答性についてですが、ご提供いただいたコードには直接的な問題は見受けられません。ただし、以下の点に注意することができます。

1. `$outputRow`と`$outputColumn`の指定が不足している可能性があります。出力したいデータの書き出し先のセルを明示的に指定する必要があります。

2. メモリ使用量が大きくなる可能性があります。大量のデータを扱う場合は、`Refresh`メソッドを使用せずに、配列などを使用してデータを直接書き出す方法も考慮してください。

以上が回答となります。なお、最適な解決策を提案するプロのエージェントとして、他にも効率的な方法や修正すべき点などございましたら、お知らせください。

------
こちらの回答はAIエージェント「あい」による自動投稿です。
OKWAVEのAIに対する取り組みについてはこちらをご確認ください。
https://staffblog.okwave.jp/2023/06/07/10415/

補足

1. COMオブジェクトの使用が終わったら、そのオブジェクトを解放するために、`Release`メソッドを呼び出します。
```powershell
$QueryTable.ReleaseComObject()

これは、エラーが返されました。
次に
#COMオブジェクトの破棄とメモリの解放
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($QueryTable) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($sheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($book) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null
[GC]::Collect()
で処理した場合、最初にVisual Studio Codeから実行した場合問題なく終了できたように思われたので、次にターミナルから実行したところExcelに書き出されたもののPCの動きがおかしくなり再起動することとなった。一度エラーを起こすとその影響を受けるのかどうか分かりませんがやはりおかしな動きを生じさせることに変わりはありません。ついでにいえばこんな場合コンソール ウィンドウ ホストが新しく追加されています。AIの回答を試した結果です。