ExcelファイルをGrepする

業務をこなすなかで、時には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

 

【放送大学】1年目第1学期の所感【情報学コース】

4月から放送大学の情報コースに入学しました。

動機は以下の通り

①自分が知らない技術・世界を知る
仕事で触れる技術がレガシーがち＆同じような言語・Webフレームワークで視野が広がらない。
＝＞情報学の広範な分野に触れておいて自分の軸にしたいものを見つける

②キャリアのため
会社を辞めて他の会社に行くときの実績になるかもしれない。
情報学で修士～博士までのキャリアを狙いたくなった時の足掛かり。
自分のバックグラウンドとしての人文学に情報学をかけ合わせておきたい。

③暇つぶし
仕事がつまらないため。

3年次編入扱いで72単位認定してもらったので、残り72単位取得すれば学士(教養)の学位で卒業できます。
フルタイム(7.5時間)*週5で働きながら1学期12単位取得を目指します。つまり3年での卒業を狙っていきます。

今回の記事では1学期に受けた授業の所感を書いておきます。
同じような境遇の人の参考になれば幸いです。

ちなみに自分の境遇

文系大学院修了・入社時プログラミング未経験・業務2年目

 

【受けた講義】

①初歩からの数学('18)

②情報理論とデジタル表現('19)

③日常生活のデジタルメディア('22)

④情報学へのとびら('22)

⑤情報ネットワーク('18)

⑥演習初歩からの数学('20)*

⑦プログラミング入門Python('24)*

①【初歩からの数学('18)】

本当に初歩から数学的な思考・概念を教えてくれる。
テキストは定理・公式の証明が非常に丁寧で好感がもてた。
高校生レベルである程度数学がてきた人なら受けなくてよいかもしれない。
講義動画は見なかった。

②【情報理論とデジタル表現('19)】

ノイマンの情報量、エントロピー、マルコフ連鎖などの情報理論、動画や画像の圧縮技術を学ぶ。
条件付きエントロピーの概念が難しく躓きがちと思われる。自分の力量には余った。最終評価もCだった。
試験は教科書の内容を理解していれば解ける内容。

③【日常生活のデジタルメディア('22)】

日常生活にあふれるデジタルメディアを様々な切り口で紹介する。
メディア論であり、眼前のデジタルメディアを分析するための概念を頭に入れていく科目。
自分でプロダクトを作りたい人は、視野を広げたり、自分が作るものの社会的・理論的な位置づけを考えるために履修しておくと良いと感じました。
試験は教科書の内容だけでなく講義動画内でしか言及されていない事項も出題される。
講義動画に毎回出てくるゲストが結構豪華。

④【情報学へのとびら('22)】
情報学の裾野を概観する導入科目。おおむね基本情報技術者試験に内包された内容。
途中で導入科目の割に衒学的な言い回しをする科目があるが気にしすぎないほうが吉。

「電子メディアは、ボードリヤールの『消費社会の神話と構造』で指摘されているように、モノから記号へと消費構造が変化し、システム化されてしまった社会のシフト現象は、新しいブランドと結びついた新製品が、従来の形態との交換可能性を生じてくることを言い、それに伴って時代遅れとなってしまう特許権パテントや著作権コピーライト、色あせてしまう商標トレードマークと同様に、グッドウィル、顧客吸引力の形態も、償却資産の意味しか有さない、と説いています」

「その電子的エクリチュールは、コンピュータの会話のテクスト的効果によって、同一性それ自体を、根源的に疑問に付すような形で、同意性の再布置、再びおおいかくすような状態へ移行する、と説いています」

前者であれば、ポストモダン以降は消費行動が表層的な記号消費にすり替わる、ということを言っているわけですが、このくだりを導入科目でいきなり話し始めるのはいかがなものかと思う。

⑤【情報ネットワーク('18)】
TCP/IPなどの基本的なネットワークプロトコルについて学ぶ。低レイヤのことを知りたかったので履修しました。
ネットワークの講義としては基礎的だが割とボリュームはある。
レポートは気になったトピックを調査してまとめるもの。自分はSSLの証明書で書きましたが、それほど凝ったものでなくても満点レポートでした。
難易度は低い科目。

⑥【演習初歩からの数学('20)*】
初歩からの数学の演習版。問題をひたすら解いていく。
難易度は高校数学程度。最終課題が1問しかなくて驚いた。
難易度:低

⑦【プログラミング入門Python('24)*】
Pythonの入門でもありプログラミング入門でもある。
ほかの言語を既習であれば苦労しないと思う。
難易度は低いが最終レポートはそれなりの取り組みが必要。
ただし難易度が3段階に分かれているので、プログラミング完全未経験でも単位の修得は問題ないように配慮されている。

最終レポートではpythonの強みを聞かれるところもあり沼にはまったがインタプリタの特性など一般的なことを書いておけばよいと感じた。


【全体的な感想】

オンライン授業と放送授業は試験のスケジュールが若干ずれているので、受講する講義をオンライン授業と放送授業で分散させておくと試験対策が少し楽になりそうです。
1学期はフルタイム+残業ほぼなし+1日1.5h~2hは個人開発、という感じでしたが一応こなせました。
専門科目は割とハードなので、今後履修する際は自分の時間と相談になりそうです。
また、シラバスを見ていると演習科目が少ないのがやや気になりました。