PowerShell でエクセルファイルを作る
◆ C# と同じようにエクセル操作できる
エクセルファイルを自動で作りたかったのですが Excel そのものを使わずにエクセルファイルを作るライブラリだと完全に互換性がないです
罫線やセル内の一部のテキストのみに設定したスタイルが失われたり 図形等が保持できなかったりです
Windows だし Python の xlwings でいいかと思ったけど インストールはあまりしたくないです
C# で作っておいた exe を持ってくればいいかと思ったけど バイナリコピーも手間があるなと思って PowerShell でやることにしました
いくつか詰まる部分はあったもののすごく簡単です
と並んでるテンプレートに B 列を追加して
にして保存するシンプルなものを作ってみます
$type が null ならエクセルが使える環境じゃないということです
デフォルトだとそのユーザのドキュメントフォルダのようです
同じフォルダのファイルをテンプレートにしようとしてファイル名だけ指定すると開けませんでしたとエラーになります
なので PowerShell 側で絶対パスにして指定する必要があります
PowerShell だと絶対パス化するには convert-path が使えます
テンプレートの方はこれでもいいのですが出力先の方では問題がありました
convert-path はファイルの存在をチェックするのでファイルが存在しないパスには使えません
.NET の機能で 「[IO.Path]::GetFullPath」 を使えばファイルの存在チェックなしでフルパス化できるのですが ここでも問題がありました
PowerShell 内で .NET 機能を使うときのカレントディレクトリは PowerShell 上のカレントディレクトリとは異なります
PowerShell の起動時は一緒ですが PowerShell 内で cd などを使って移動すると PowerShell 上はカレントディレクトリが変更されますが .NET 機能で参照するカレントディレクトリは変更されません
カレントディレクトリを PowerShell と揃えるためにカレントディレクトリをセットする処理が必要です
ですが PowerShell だとエラーになって () を使って関数呼び出し形式にすることで動きました
関数呼び出しの結果に代入するようでなんか変な感じがしますが問題なく動きます
構文的に PowerShell だとメソッドの返り値に代入してもエラーにはならないみたいです
これでエラーは起きません
かといってこれで $str の文字列が書き換わったりはしてません
COM を解放すれば良いのですが .NET では内部的に管理されているので GC されれば自動で解放されます
スクリプトファイルの実行なので終わった時点で変数には残っていないのですが PowerShell ではあまり積極的に GC されてないようで解放されずにエクセルプロセスが残ってしまいます
GC のタイミングで消えるので無視してもいいのですが 気持ち悪いのなら実行後に
を実行すればすぐに終了できます
どうせやるならスクリプト内でやったほうが使う側的には楽です
スクリプト内部だと quit 後でもたぶん $book などに参照が残っていて 単純に GC を呼び出しても消えないです
quit のあとにそれぞれの変数に null を入れてもいいですが ひとつひとつに null 代入なんて面倒ですし 変数を追加したら漏れが出る気しかしません
try の範囲を関数化すれば実行後にはスコープを抜けて変数は残ってないはずなので 関数化して実行してから GC で良いと思います
しかし ps1 ファイルを別の PowerShell プロセスで実行してれば終わったらプロセスが終了になって即解放されるはずです
新しいプロセスなら .NET のカレントディレクトリとのずれも存在しないので SetCurrentDirectory も不要になります
何もインストールしてない環境でスクリプトのテキストのコピーのみで実行させるつもりなら古い PowerShell で実行ポリシー制限もされてるので許可するついでにもなります
罫線やセル内の一部のテキストのみに設定したスタイルが失われたり 図形等が保持できなかったりです
Windows だし Python の xlwings でいいかと思ったけど インストールはあまりしたくないです
C# で作っておいた exe を持ってくればいいかと思ったけど バイナリコピーも手間があるなと思って PowerShell でやることにしました
いくつか詰まる部分はあったもののすごく簡単です
例
A 列にA
B
C
と並んでるテンプレートに B 列を追加して
A a
B b
C c
にして保存するシンプルなものを作ってみます
$type = [Type]::GetTypeFromProgID("Excel.Application")
if ($type -eq $null) {
echo "エクセルが見つかりません"
exit
}
[IO.Directory]::SetCurrentDirectory((Get-Location))
$tpl = [IO.Path]::GetFullPath("template.xlsx")
$out = [IO.Path]::GetFullPath("result.xlsx")
try {
$app = [Activator]::CreateInstance($type)
$book = $app.WorkBooks.Open($tpl)
$sheet = $book.WorkSheets(1)
$sheet.Range("B1") = "a"
$sheet.Range("B2") = "b"
$sheet.Range("B3") = "c"
$book.SaveAs($out)
} finally {
$app.quit()
}
Type
エクセルのインスタンスを作る方法はいくつかありますが 「[Type]::GetTypeFromProgID」 を使って Type を取得してそこからインスタンスを作る方法にしています$type が null ならエクセルが使える環境じゃないということです
カレントディレクトリ
エクセルでは PowerShell 側のカレントディレクトリではなく独自にカレントディレクトリを持ってるみたいですデフォルトだとそのユーザのドキュメントフォルダのようです
同じフォルダのファイルをテンプレートにしようとしてファイル名だけ指定すると開けませんでしたとエラーになります
なので PowerShell 側で絶対パスにして指定する必要があります
PowerShell だと絶対パス化するには convert-path が使えます
テンプレートの方はこれでもいいのですが出力先の方では問題がありました
convert-path はファイルの存在をチェックするのでファイルが存在しないパスには使えません
.NET の機能で 「[IO.Path]::GetFullPath」 を使えばファイルの存在チェックなしでフルパス化できるのですが ここでも問題がありました
PowerShell 内で .NET 機能を使うときのカレントディレクトリは PowerShell 上のカレントディレクトリとは異なります
PowerShell の起動時は一緒ですが PowerShell 内で cd などを使って移動すると PowerShell 上はカレントディレクトリが変更されますが .NET 機能で参照するカレントディレクトリは変更されません
カレントディレクトリを PowerShell と揃えるためにカレントディレクトリをセットする処理が必要です
Excel 操作
C# でやると WorkSheets や Range のアクセスのところは [] でインデックスアクセスですsheet.Range["B1"] = "a";
ですが PowerShell だとエラーになって () を使って関数呼び出し形式にすることで動きました
関数呼び出しの結果に代入するようでなんか変な感じがしますが問題なく動きます
構文的に PowerShell だとメソッドの返り値に代入してもエラーにはならないみたいです
PS C:\Users\user0\Desktop> $str = "123"
PS C:\Users\user0\Desktop> $str.Substring(1) = "a"
これでエラーは起きません
かといってこれで $str の文字列が書き換わったりはしてません
Excel プロセスが残る
PowerShell で上の ps1 ファイルを実行後にタスクマネージャーで見ると Excel.exe が残っていますCOM を解放すれば良いのですが .NET では内部的に管理されているので GC されれば自動で解放されます
スクリプトファイルの実行なので終わった時点で変数には残っていないのですが PowerShell ではあまり積極的に GC されてないようで解放されずにエクセルプロセスが残ってしまいます
GC のタイミングで消えるので無視してもいいのですが 気持ち悪いのなら実行後に
[GC]::Collect()
を実行すればすぐに終了できます
どうせやるならスクリプト内でやったほうが使う側的には楽です
スクリプト内部だと quit 後でもたぶん $book などに参照が残っていて 単純に GC を呼び出しても消えないです
quit のあとにそれぞれの変数に null を入れてもいいですが ひとつひとつに null 代入なんて面倒ですし 変数を追加したら漏れが出る気しかしません
try の範囲を関数化すれば実行後にはスコープを抜けて変数は残ってないはずなので 関数化して実行してから GC で良いと思います
しかし ps1 ファイルを別の PowerShell プロセスで実行してれば終わったらプロセスが終了になって即解放されるはずです
新しいプロセスなら .NET のカレントディレクトリとのずれも存在しないので SetCurrentDirectory も不要になります
何もインストールしてない環境でスクリプトのテキストのコピーのみで実行させるつもりなら古い PowerShell で実行ポリシー制限もされてるので許可するついでにもなります
powershell -ExecutionPolicy RemoteSigned -File ".\mkxlsx.ps1"