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は個人開発、という感じでしたが一応こなせました。
専門科目は割とハードなので、今後履修する際は自分の時間と相談になりそうです。
また、シラバスを見ていると演習科目が少ないのがやや気になりました。

 

PowerShellで文字列を機械的に埋め込んで出力する

インストールの許可がないためにPHPなど他言語でサクッとスクリプトを書くこともできないがエクセルで設計書を書くことを強いられるような過酷な状況の時のためのメモ書き

# 文字列を埋め込みたい場所に{0}を指定(複数埋め込みたい場合は都度数字をインクリメント)

$templates = @("テンプレ1({0})", "テンプレ2({0})" )

# 埋め込むための大量の文字列が1行ずつ書き込まれているファイルがあると想定

$attributes = (Get-Content -Encoding utf8 -Path C:\test.txt) -as [string[]]

#StringBuilderで効率よく結合

$formatedSb = [System.Text.StringBuilder]::new()

foreach ($att in $attributes) {

    foreach ($temp in $templates) {

        $formatedSb.AppendLine([string]::format($temp -f $att))

    }

}

#多分いらない $formatedSb.ToString() | Set-Content -Encoding utf8 -Path C:\result.txt で大丈夫

$encoder = [System.Text.Encoding]::UTF8.GetBytes($formatedSb.ToString())

#パイプで流し込む

[System.Text.Encoding]::UTF8.GetString($encoder) | Set-Content -Encoding utf8 -Path C:\result.txt

C:\result.txtに一行ずつフォーマットされた文字列が出力される。
VSCodeのPowerShell拡張から実行したらテンプレ部分が文字化けしてしまったけど、PowerShellのターミナルから実行したら文字化けしなかったので多分大丈夫。

aタグとPointerEvent

aタグをコンソールから押す方法についてやっと理解できたのでメモ。

1.やりたいこと

こんなHTML内のaタグをJavaScriptからクリックしたい。

<body>

    <a href="https://www.google.com" id="target">Google.com</a>

</body>

2.ダメな例

function log(id){

    const target = document.getElementById(id);

    console.log(e);

    target.dispatchEvent(new Event('click'));

}

element.dispatchEvent()を使ってみる。dispatchEventは引数にEventオブジェクトを取って、要素に任意のイベントを割り当てることができる。
上記の場合は、Eventオブジェクトのコンストラクタに'click'を指定して、aタグにクリックイベントを発生させようとしている。

が、画面遷移は発生しない。

3.これならできるけど

function log(id){

    const target = document.getElementById(id);

    console.log(e);

    target.click();

}

click()メソッドを実行して、クリック動作をシミュレーションすることができ、aタグによる遷移を発生させることができる。

4.DispatchEventにこだわるなら

aタグのhref属性を削除したうえで、イベントオブジェクトを確認してみる。

function log(id){

    const target = document.getElementById(id);

    target.addEventListener('click', function (e) {

        console.log(e);

    });

    target.click();

}

Eventオブジェクトではなく、PointerEventオブジェクトが割り当てられている模様。

PointerEvent インターフェイスは、接触点の形状、イベントを生成した機器の種類、接触面に加えられた圧力の量など、ポインターによって生成された DOM イベントの状態を表します。

-----mdc web docs

クラス図

PointerEventはEventオブジェクトをプロトタイプとして継承しているので(多分)、Eventオブジェクトで指定できる'click'イベントも指定可能。
PointerEventによる発火であればログ出力して画面遷移。

function log(id){

    const target = document.getElementById(id);

    target.addEventListener('click', function (e) {

        if(e.constructor.name === 'PointerEvent'){

            console.log('PointerEvent!!');

        }

    });

    target.dispatchEvent(new PointerEvent('click'));

}

🥰🥰🥰

読めたら　読んでおく

 

バイナリの制御文字と'BOM'でハマった

最近部屋の片づけをしており、大量の本やＣＤを整理しています。
折角だから捨てる前に全部ＣＤを取り込んでおこう…ということでアルバムを一つ一つasunderから取り込んでいるのですが、面倒な課題にぶつかってしまいました。

1. 曲・アルバム・作曲者の表記揺れが多すぎる問題

昔聞いていた曲には19世紀の古典的な曲が多く、再演・再奏された録音を収録している場合、アルバムが違うだけで「同じ曲なのにタイトルが違う」ということが頻発します。

J.Strauss Ⅱ Wo die Zitronen blühen

→ヨハンシュトラウス2世 シトロンの花咲くところ

→ヨハン・シュトラウスⅡ世 レモンの花咲くところ

→ヨハン・シュトラウス2世 シトロンの花咲く国

曲名が違うだけならそこまで神経質にならずともよいですが、作曲者名に揺れがあると「その作曲者の曲だけ聞きたい」という場合不便を極めます。

ということで、表記揺れを一括で直せるように楽曲ファイルのメタデータを編集できるようにしたいと思います。類似のMP3タグ編集ソフトで同等のことはできるのかもしれませんが、調べていません。どうせ勉強だし。

2. バイナリを読む

とりあえず、AACとかその他の音楽ファイルの形式は考えず、MP3のタグ(ID3V2)を編集することだけ考えます。
タグを編集する場合、バイナリ(0と1で構成された、テキスト以外のデータ)の読み書きを直接行うことになります。

ID3V2タグにはヘッダーも存在しますが、ここでは最初にタグの本体の中身を見ることにします。
バイナリエディタの左側のパネルの各セルには、1バイトの情報が表示されており、右側のパネルにはバイトに対応する文字がエンコードされて表示されています。

言わでものことかもしれませんが、1バイトはここでは16進表記されているので、'54'なら16 * 5 * 4で10進数の84となります。16進数をリテラル表記する際の'0x'という接頭辞をつけて表記するなら、0x54となります。
さて、上掲の画像の中で、エンコードされた文字列を見ていると、「TSSE」や「TRCK」、「TPE1」という大文字の文字列が目につきます。
この文字列からID3V2タグの「フレーム」と呼ばれる、楽曲のメタデータのより具体的な情報を格納する部分が開始します。「TSSE」などは各フレームの名称で、「TSSE」はエンコード設定に関する情報、「TRCK」はトラック番号、「TPE1」は主なるアーティストを指します。

今回は、「TRCK」について見ていきたいと思います。上掲の画像のうち、赤枠で囲った部分がフレーム名の「TRCK」を表す部分。
水色の枠で囲った部分が、フレームの本体サイズを表す部分で、今回は「00 00 00 05」なので、5バイトであることがわかります。本来はSyncsafe Integerという表記法に対応したサイズの計算が必要ですが、今回は割愛。
緑色の枠で囲った部分が、フラグを表す部分ですが、通常は使用されません。
そして、最後に、オレンジの枠で囲った部分が、フレームの本体で、ここにトラック番号が格納されています。サイズは先ほどフレームの本体サイズを表記していた箇所に従って5バイトとなります。

さて、フレームのサイズに従ってトラック番号を表す箇所を見てみると、「01 FF FE 31 00」と記されています。
31 = 0x31はUnicode(文字コードの標準規格)では'1'を指します。バイナリエディタで見ていた曲はアルバムの1曲目なので、正しそうですね。
ただ、0x31の前には、0x01,0xFF,0xFEというバイト列が並んでいます。0x01や0xFFや0xFEには対応する文字が無いようですが…。

3. 0x01,0xFFFEとは

Unicodeエンコーディング(UTF-16またはUTF-8)が使用されているデータ・ファイルには、ファイルの最初の数バイトにバイト順序マーク(BOM)が含まれている場合があります。キャラクタ・セットUTF-16が使用されているデータ・ファイルでは、ファイルの最初の2バイトの値{0xFE,0xFF}は、ファイルがビッグ・エンディアンのデータを含んでいることを示すBOMです。{0xFF,0xFE}という値は、ファイルにリトル・エンディアンのデータが含まれていることを示すBOMです。
https://docs.oracle.com/cd/E57425_01/121/SUTIL/GUID-CFEED713-D459-42F4-A777-7AAA654451AC.html

0xFF,0xFEが使用されている場合、続くデータはリトルエンディアン(=多バイトをメモリに格納する際の方式1つ。リトルエンディアンの場合下位バイトを先に格納する)ことを明示しています。このような記号をBOM(Byte Order Mark)と呼びます。
（ただし、UTF-8のBOMは0xEFBBBFで、これはUTF-8で書き込まれていることを明示するために使われるのであって、今見ているファイルがUTF-8で書き込まれているはずなのに0xFFEが書き込まれているのはなぜなのかはよくわかりません。）

せんずるに、0XFFFEは文字コードとしては定義されない値であり、今回はUTF-8のファイルとしてバイナリデータを扱うので、楽曲に関するメタデータとして考慮する必要はないということです。
ただし、読み込んだバイト列をエンコードする際に弾きだす必要はありますね。

0x01については、unicodeの制御文字のようで、意味としては'START OF HEADING'＝ヘッダ開始を表す符号にすぎないようで、これも文字列として扱わないように弾く必要あり。そのほかunicodeには0x000~0x001Fまでの制御文字があるようです。