はじめに
業務で使用していた検索ツール(Excelマクロ)の処理時間が非常に長かったため、
作業内容によってはかなり原始的なやり方で仕事をしていました。
ネットワークに接続できないクラウド環境のため、他製品のインストールができない制約がありましたが、
幸いPowerShellは入っていたため、勉強もかねて作成してみました。
検索処理は圧倒的に早くなったので、
同じような状況の人(Windows環境のデフォルトのツールのみでやりくりせざるを得ない)の助けになれば幸いです。
ツール概要
PowerShellで一度テキスト変換し、出力されたテキストを検索するツール
※テキスト変換処理は一度に複数のExcelファイルの指定が可能、
検索文字を複数指定すると、指定した検索文字ごとに結果を出力します。
検索結果は"Excelファイル名_シート名 + ,(カンマ) + 値"の形式で出力されます。
フォルダ構成
./
├─result #Excelの各シートがテキストファイルとして格納されるフォルダ
├─targetExcel #変換対象のExcelファイルを格納するフォルダ
├─01_Excel変換 #Excel変換実行用ショートカット
├─02_searchWords.txt #検索文字を記載するファイル
├─03_text検索 #グレップ検索実行用ショートカット
├─99_readme.txt #ReadMe
├─convertText.ps1 #Excel変換ps1ファイル
└─searchText.ps1 #グレップ検索ps1ファイル
使い方
<バージョン>
v1.0
<概要>
Excelファイルをテキストに変換後、GREP検索を行うツールです。
<手順>
①targetExcelフォルダに検索を行いたいファイル(.xlsxまたは.xlsファイル)を格納
※再帰的に該当フォルダを検索するため、階層構造のあるフォルダをそのまま入れても問題ありません。
②「01_excel変換」ショートカットをクリック
→resultフォルダ配下に.txtファイルが生成されます。
※実行にはそこそこ時間がかかります。(90シートで10分ぐらい)
③「02_searchWords.txt」に検索を行いたい項目を記載し、保存
※改行で複数項目の検索が可能です。
④「03_text検索」ショートカットをクリック
→「04_検索結果.txt」が生成されます。
※実行は一瞬で終わります。
※検索条件を変えて再実行したい場合は、手順③から実行してください。
※「04_検索結果.txt」はファイル名・値がカンマ区切りで出力されます。excelに張り付けると見やすいです。
<注意>
・手順②実行時、対象のエクセルに非表示シートが存在するとエラーが発生します。
・手順④で検索する.txtファイルの文字コードはunicodeである必要があります。
そもそもPowerShellとは?
PowerShell（パワーシェル）は、マイクロソフトが開発した拡張可能なコマンドラインインターフェース (CLI) シェルおよびスクリプト言語である。オブジェクト指向に基づいて設計されており、.NET Frameworkと.NET Coreを基盤としている。
wikiより
特徴
要はwindows向けのCLIツールです。
windows標準のアプリケーションを操作できるコマンドが多数用意されており、
シェルスクリプトのような一連の処理を実装することができます。
また、オブジェクト指向で設計されているため、
各コマンドの戻り値はオブジェクトが返却されます。
そのため、オブジェクトのプロパティやメソッドを呼び出すことで、必要な情報を取得するという流れになります。
基礎構文
・コマンドレット
PowerShellでは、コマンド(メソッド)のことをコマンドレットと呼びます。
基本的に"<動詞> + -(ハイフン) + <名詞>"の命名規則に従っています。
ex)Split-Path(パスを分割するコマンドレット)
・変数
変数の宣言は$を使用します。
数値や文字列など、データ型を区別しないため一律この表記になります。
※キャストや型指定の仕組みも用意されてますが、今回は割愛します。
ex)
$hoge
${hoge hoge}("{}"で括ることでスペースを入れることが可能です。)
・繰り返し処理
for文やwhile文など基本的なループ方法は備わっています。
(他言語やったことがある人はさらりと書けるはずなので省略)
"|"(パイプ)で受け取った配列データに対してループ処理を行えるForEach-Objectについて触れます。
※Javaの拡張for文的な使い方ができるforeachステートメントというものもあります。
${配列オブジェクト} | ForEach-Object{ #処理 }の書き方が基本になります。
また、"ForEach-Object"を省略して、"%"と記載することも可能です。
ex)${配列オブジェクト} | %{ #処理 }
ループ処理内部では、配列データは$_変数に格納されています。
ex)${配列オブジェクト} | %{ echo $_ }
※配列の内容を順次取り出しを行い、コンソールに出力する例
・関数
もちろん、関数化の機能も備えています。
function 関数名 (引数1, 引数2, ...引数n) {
#処理
}
・デバッグ
「PowerShell ISE」を使用したデバッグが基本となりますが、
変数の型や内容が知りたい場合は、以下を使用しましょう。
・echo ${変数}
コンソールに引数の内容を出力
・${変数}.GetType().FullName
変数の型を取得
・help コマンドレット
指定したコマンドレットのヘルプを確認
convertText.ps1(Excel変換処理コード内容)
#実行パス取得
${dir} = Split-Path $MyInvocation.MyCommand.Path -Parent
#デバッグ用
echo "検索起点フォルダ:${dir}"
#EXCEL操作用オブジェクト取得
${excel} = New-Object -ComObject Excel.Application
#EXCELの確認ダイアログを非表示にする
${excel}.DisplayAlerts = $false
#検索起点フォルダ配下のEXCELファイルを順次処理する
#※Includeパラメータを追加する事で、"*.xlsm"ファイルも含めることができます。
Get-ChildItem "${dir}" -Include "*.xlsx","*.xls" -Recurse -Name | %{
#検索起点フォルダからの相対パスを取得
${childPath} = $_
echo ${childPath}
#EXCELファイルを開く
${wb} = ${excel}.Workbooks.Open("${dir}\${childPath}")
#シートに対して順次検索処理を行う
${wb}.Worksheets | %{
#Worksheetオブジェクトの取得
${ws} = $_
#新規のExcelファイルを作成
${book} = ${excel}.Workbooks.add()
#作成したExcelファイルにシートを対象のコピー
${ws}.copy(${book}.worksheets.item(1))
#Textファイル名を"Excelファイル名_シート名"にするための、文字列変換処理
#相対パスを"/"で区切りで分けた配列データに
${childPathCsvList} = (${childPath}.ToString() -split"\\")
#配列の末データ取得
${childPathCsvName} = ${childPathCsvList}[$(${childPathCsvList}.Length)-1]
#".xlsx"・".xls"拡張子を削除
${childPathCsvName} = ${childPathCsvName}.ToString() -replace ".xlsx",""
${childPathCsvName} = ${childPathCsvName}.ToString() -replace ".xls",""
echo "${dir}\result\${childPathCsvName}_$(${ws}.Name).txt"
#新規で作成したExcelファイルには"sheet1"が含まれるため削除
#※複数シートがある場合、text保存した場合文字化けします
${book}.Worksheets.item(2).delete()
#Unicode(第２引数の"42"で指定)ファイルとして別名保存
${book}.saveAs("${dir}\result\${childPathCsvName}_$(${ws}.Name).txt",42)
}
#次のExcelファイルを開くために、現在のファイルをクローズする
${wb}.Close(0)
}
#Excelプロセスを終了させるため、メモリの解放を行う
#※これを実行しないと、毎回タスクマネージャーからプロセスの終了を行う事になります
${excel}.Quit()
${dir} = $null
${excel} = $null
[System.GC]::Collect([System.GC]::MaxGeneration)
searchText.ps1(ファイル検索処理コード内容)
#実行パス取得
${dir} = Split-Path $MyInvocation.MyCommand.Path -Parent
#検索結果格納ファイル生成
${fullPath} = "${dir}\04_検索結果.txt"
${file} = New-Object System.IO.StreamWriter($fullPath,$false)
#デバッグ用
echo "検索起点フォルダ:${dir}"
#読み込んだファイルをstring型の配列として順次処理する
(Get-Content .\02_searchWords.txt) -as [string[]] | %{
#検索対象文字列
${word} = $_
#ファイル書き込み
${file}.writeline("==== 検索開始(${word})====")
#検索対象フォルダのファイルに対して順次処理を行う
#"Select-String"を使用してファイルの中身を検索する
Get-ChildItem "${dir}\result" -Include "*.txt" -Recurse | Select-String ${word} | %{
#検索結果は":"区切りの文字列のため、必要情報を分割して取得する
#ファイル名はパス付きで1つ目の配列に格納されている
${resFile} = $($_ -split":")[1]
${resFile} = ${resFile}.ToString() -replace " ",""
#"\"でパスを配列化し、配列の末尾を取得(\は特殊文字のためエスケープ)
${resFileList} = (${resFile}.ToString() -split"\\")
${resFileList} = (${resFileList} -split"\\")[$(${resFileList}.Length)-1]
#検索結果は配列の3つ目に格納されている
${resStr} = $($_ -split":")[3]
#Excelのセルの数だけタブが設定されているため、取り除く
${resStr} = ${resStr}.ToString() -replace "'t",""
#結果の書き込み
${file}.writeline("${resFileList},${resStr}")
}
}
#ファイルをクローズし、メモリの解放を行う
${file}.Close()
${file} = $null
[System.GC]::Collect([System.GC]::MaxGeneration)
ショートカット
作成したスクリプトはそのまま実行しても良いが、実行者の環境によってはポリシーの変更を求められる。
一時的に特定ファイルのみ実行許可を行う場合は、ショートカットを作成し、
そちらをクリック・実行してもらうという方法を手順に加えてしまうのがおすすめです。
(セキュリティ的にもおすすめ)
赤線の部分を修正する。
作業フォルダは記入なし
リンク先は以下のように記載する。
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy RemoteSigned -File .\convertText.ps1
※powershell.exeの格納場所は適宜書き換えてください。
まとめ
今まで、たった36項目を検索するのに1日かかっていたものが、
このスクリプトによって5秒ほどに短縮できました！(環境が最弱すぎなのも問題かも。。。)
「一度テキストに変換して、検索してしまえば早いんじゃね？」という安易な発想が上手く行ってとても満足です。
Excelは高機能なため、検索対象の情報元として使用するにはあまりにも不要なものが多すぎだっ担だと思います。
これからも、やりたい事に対して適切なツールを検討し、試行錯誤し続けねば！