概要
複数のExcelファイルに対して単語検索を行うCUIツールをPowerShellでつくってみました。（Windows専用）
フォルダを指定するとその配下に存在する全Excel1つ1つに対して単語検索をかけて、ヒットしたファイル・セル座標・セル内の文字列の一覧をCSVとして保存します。
ダウンロードは以下から可能です。
本記事ではこのツールを作った経緯や処理内容の解説なんかを述べたいと思います。
はじめに
自分は仕事の関係で大量のExcelを扱う場面が多く、「あの項目ってどのブックに書かれてたっけ？」というときに各ファイルを総当たりする必要がありました。
これではさすがに非効率的すぎるので、ひとまず「Excel 複数ファイル 検索」でググると以下のような方法が見つかりました。
- VBAで作成
- Pythonで作成
- PowerShellで作成
今回はPythonが使えない環境であること・macを考慮しなくてよいこと・自分がPowerShellに慣れていたことから3つ目の方針で進めることにしました。
（まぁ今思うと作り勝手はともかく使い勝手は1番目が最善ですかね、macでも使えそうですし）
PowerShellで複数ファイルを検索する方法の記事から必要なスクリプトはわかったので自作の起動バッチテンプレートと組み合わせて検索ツールとしてまとめてみました。
解説
バッチアプリもろもろの話はこっちの記事で話しているので、ここではスクリプト本体について解説しておきます。
コードの大半はコンソールでの対話インターフェース処理なので本質的な部分は少ないです。
Excelオブジェクトを読み込む
$EXCELAPP = New-Object -ComObject Excel.Application
PowerShell上でExcelアプリを扱うために必要な処理です。macで使えるPowerShellだとComObjectプロパティが存在しないのでこの部分でエラーになります。
Excelブックを開く
# 1: ファイルパス
# 2: 0ならシート内の外部参照を更新しない
# 3: Trueなら読み取り専用で開く
# 4: テキストファイルを開く場合の区切り文字、不要なのでMissingでスキップ
# 5: パスワードがかかっている場合に試すパスワード
$wb = $EXCELAPP.Workbooks.Open($file, 0, $True, [Type]::Missing, $password)
Excelブック（ファイル）を開く処理です。Openの各引数の意味はコメントに書いてある通りで、今回は検索ツールなので読み取り専用で開くようにしています。（ちなみにOpenの引数は全部で10個あるので気になる方はこちらを参照してください）
※Excelにパスワードがかかっている場合
パスワード引数の指定なしだと、ファイルを開いたときにパスワードを聞くウィンドウが表示されて処理が止まります。
パスワード引数の指定ありだと、パスワードが間違っていた場合にExceptionが投げられます。
※対象のExcelをすでに開いている場合
この処理で開こうとしたブックをすでに開いている場合はExceptionが投げられます。（n敗）
あらかじめExcelを閉じてからツールを実行することをおすすめします。
セルを検索する
$first = $found = $ws.Cells.Find($keyword)
シートに対して検索を書けます。検索時にキーワードの大文字小文字は区別されないですが、おそらくFindに何かプロパティを指定すればその辺も細かく指定できると思います。（たぶんVBAと同じっぽいのでMacthCase的なプロパティかな？）
セルの座標を取得する
function R1C1_to_A1([int] $n) {
$rest = $n % 26
if($rest -eq 0) { $rest = 26; $n -= 1 }
$over = [Math]::Truncate($n / 26)
if($over -gt 0) {
$pre = "$(R1C1_to_A1($over))"
} else {
$pre = ''
}
$az = [System.Convert]::ToChar($rest+64)
return "${pre}${az}"
}
# $result.Pos = "$($found.Row),$($found.Column)" # R1C1形式 (4,2)
$result.Pos = "$(R1C1_to_A1($found.Column))$($found.Row)" # A1形式 (B4)
見つかったセルの座標はR1C1形式（(n行,m列)の形式）で取得できます。ただExcelのデフォルトは列名がアルファベットなので自作の変換関数R1C1_to_A1を使って変換することにしました。
ちなみに後から気づいたことですが、$found.AddressでA1形式でも取得できます。ただし絶対座標という扱いで$がつくっぽいのでreplaceで取り除く必要がありそうです。
Excelの終了
$EXCELAPP.Quit()
$ws = $null
$wb = $null
$EXCELAPP = $null
[System.GC]::Collect()
正常終了・異常終了にかかわらず最後にExcelオブジェクトを捨てておかないとプロセスが残り続けてしまうので要注意です。
デバッグしていたときに何回かエラーで落ちた時、Excelのプロセスが残り続けてCPU負荷が爆上がりしていました。（タスクマネージャから強制終了させました）
検索結果の保存
$results | Export-Csv -Path $CSV -Encoding UTF8 -NoTypeInformation
検索結果をツールのフォルダのresults内にCSV形式で保存します。ちなみに-Encoding UTF8といっていますが、PowerShell 5.1までだとBOM付きUTF-8になります。この辺は有名なのでググればすぐにわかると思います。
さいごに
今後は以下のような機能もあるといいのかなと思いつつ、現状は今の仕様で十分なのでぽつぽつ更新していこうかなと思っています。
- パスワードの複数指定（指定されたパスワードをすべて試す）
- PowerShell 6.0以上への対応
- FormやDialog等を使ったGUI操作の追加
あと、今回の開発を経てPowerShell+Excelのポテンシャルが思ったより高いことと、そもそもVBAでできることが割と多いことに気づいたので他にもいろいろ作ってみようかなと思いました。