業務をこなすなかで、時にはExcelで書かれた設計書の中から特定の文言を探し出すという、クリエイティビティあふれる作業をすることがあります。
しかし、手で一枚一枚エクセルで書かれた設計書を開いていくこの作業を、もっと効率化すれば、この素敵な時間がより生きがいに満ち満ちた時間になるに違いありません。
そこでExcelファイルを、Linuxで言うところのgrepコマンドを叩く風に対象文字列を一括で検索してみたいと思います。
ExcelGrepは、管見ではPowerShellから呼び出せる.Netの機能を使用する実装がありました。しかし、今回はExcelファイルの実体に沿って別のアプローチを試みます。
1. ExcelGrepのソース
excelGrep.ps1
function grepExcel {
param(
[Parameter(Mandatory=$true)]
$e,
[switch]
$nocache,
$out,
$path
)
#設定ファイル読み込み
$table = Get-Content -Path ./conf | ConvertFrom-StringData
#変数設定
#検索対象のパス
$searchRootPath
if([string]::IsNullOrEmpty($path))
{
$searchRootPath = $table.conf_root_path
}
else
{
$searchRootPath = $path
}
#対象フォルダのエクセルファイル取得
Get-ChildItem -Path $table.conf_root_path *.xlsx -Recurse -Name | ForEach-Object {
$fileName = $_ -replace ".xlsx", ""
$fileNameEx = $_
$org = $table.conf_root_path + '\' + $_
$dest = $table.conf_workspace + '\tmp\' + $fileName + '.zip'
# コピー先ディレクトリの作成とzipファイルとしてのコピー
$expandDist = $table.conf_workspace + '\tmp\' + $fileName
if ($nocache)
{
New-Item -Path $dest -ItemType File -Force
Copy-Item -Path $org -Destination $dest -Recurse -Force
Expand-Archive -Path $dest -Force -DestinationPath $expandDist
}
#sharedStrings読み込み
$file = $expandDist + '\xl\' + 'sharedStrings.xml'
$xmlSS = [xml]::new()
$xmlSS.load($file)
$nsmgrSS = New-Object -TypeName System.Xml.XmlNamespaceManager -ArgumentList $xmlSS.NameTable
$nsmgrSS.AddNamespace("msb", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
$si = $xmlSS.SelectNodes("//msb:si" , $nsmgrSS)
for ($i = 0 ; $i -lt $si.Count; $i++) {
if ($si[$i].InnerText -eq $e) {
#各シートを読み込んでtタグ=sのタグを配列に格納する
$sheetsPath = $expandDist + '\xl\worksheets\' + '*.xml'
$xmlSheet = [xml]::new()
$sheetName=''
$cellAddress=''
#シートを順次読み込み
(ls $sheetsPath).FullName | ForEach-Object {
$xmlSheet.load($_)
$nsmgr = New-Object -TypeName System.Xml.XmlNamespaceManager -ArgumentList $xmlSheet.NameTable
$nsmgr.AddNamespace("msb", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
$Script:cellAddress = $xmlSheet.SelectNodes([string]::Format("//msb:c[*='{0}']", $i), $nsmgr)
$Script:sheetName = $_ -replace '^.+\\', '' -replace '\.xml', ''
}
$cellAddress | ForEach-Object {
$ms = [string]::Format("Expression '{0}' found @ {1} : {2} : {3}", $e , $fileNameEx, $sheetName, $_.r)
echo $ms
}
}
}
}
}
conf
conf_root_path= #検索対象のフォルダパスを指定します。パラメータで指定可にするつもりでしたが飽きたのでやめ。
conf_char_code=utf-8 #検索時に使用する文字コード。飽きたので使っていません。
conf_workspace= #powershellのスクリプトを置く作業場。ここにファイルをコピー＆展開します。
conf_cache_valid=false #コマンドからキャッシュ使用有無を指定しない場合にデフォルトでキャッシュを使用するか否かの設定。飽きたので使っていません。
readme.txt
【概要】
.xlsxファイルのテキストを走査して指定の文字列が書き込まれているすべてのブック、シート名、セル番地を表示します。
【オプション】
-e …検索対象の文字列です。正規表現が可能か確かめていません。必須項目です。
-nocache…前回実行時のキャッシュを使用せずに検索します。ファイルの展開を行うため速度が低下します。デフォルトではキャッシュを使用します。
2.何をやっているか
Excel 2007の標準ファイル形式は、従来の「XLS」から「XLSX」に変った。この「XLSX」の実体は圧縮されたZIP形式ファイルで、中にはブックの情報がXML形式で保存されている。たとえば「Book1.XLSX」を保存したとき、エクスプローラでこのファイルを見るとExcelのアイコンで表示される。
『新しい標準ファイル形式「XML」（第2回）』
Excelの実体はZipファイルであり、内部のxmlファイルにはExcelに書き込まれた文字列やそのセル番地が格納されています。
解凍したExcelファイル内の、shareStrings.xmlには書き込まれた文字列が、そして、workSheetsフォルダのシート名のxmlにはshareString.xml内の文字列がある番地などの情報が格納されています。
従って、
①対象フォルダから.xlsxファイルを再帰的に検索
②すべて.zip形式に変更
③すべて解凍しxmlをパース
④パースしたxmlから文字列を抽出
⑤検索対象の文字列がヒットしたらシートのxmlを見に行きセル番地を取得する
という操作を行えばgrepに近いことを実現できるはずです。
ファイルの解凍はまあまあオーバーヘッドがかかる操作だと思うので、zipファイルから展開した既存のファイルがある場合はそれらをキャッシュとして使用するとよさそうです。
.Netのfindメソッド(シート内の文字列検索)はシート別に呼び出すようなので、文字列の検索にすべてのシートを走査する必要があるようです。
一方shareString.xmlに該当の文字列が無ければシートを走査する必要がないと考えれば、xmlパーサで先にsharedString.xmlを見るほうが効率が良いのでは、という思想です。
検索する文字列がレアなほど速くなる。
3.実行結果
PowerShellスクリプトの実行方法は下記参照。
https://www.vwnet.jp/windows/PowerShell/2016100401/UseFunctionInPsPrompt.htm
こんな感じのシートがあるファイルをいくつか15個くらい用意しました。
sheet1
sheet2
sheet2は大量のランダムな文字列を書き込んでるように見えますが、関数で生成しているのでsharedStringsには書き込まれません。ただしシート別のxmlとしてはかなりの量が書き込まれるので、どの程度xmlのパースに時間がかかるのかつかめるはずです。
この間55秒。まあバックグラウンドでやってくれる分にはそんなに気にならないけどなんか遅くね？というかシート名とセル番地表示されてないし。スコープ間違えてるのかな？
暇なときに直す＆もっと高速にしたいです。
てかExcelの実体がxmlでそれを頑張ってパースするぐらいなら初めから設計書をxmlのマークアップで書けばよくねﾜﾗ
ソース↓
https://gitlab.com/simulacre1/excelgrep.git
