謎テクノロジー入門

この記事は ハンズラボ Advent Calendar 2017 21日目の記事です。

皆様こんにちは。KENT（@tone-kikuchi）と申します。
普段は主にシェル芸おじさんをやっており、ハンズラボには2017年の1月から務めています。
ハンズラボには多彩なバックエンドを持ったエンジニアが集まっており、それについてはアドベントカレンダーの内容の統一性のなさからもお分かりいただけるかと思います。

自分はと言いますと、ハンズラボは３社目なのですが、不思議な事にラボを含めてこれまでの会社全てでシェルスクリプトをゴリゴリ書いておりました。
（シェルスクリプト以外も書いていましたが）
そこで新しい技術の話は他のメンバーに任せるとして、シェルスクリプトに関するいにしえの技術の話をしたいと思います。

謎テクノロジーとは

まずここでタイトルの「謎テクノロジー」とはなんぞや？について触れておきたいのですが、
結論から言うと「ユニケージ開発手法」のことです。
（以降「ユニケージ開発手法」を「ユニケージ」と呼びます。）
ユニケージはシェルスクリプティングの上で成り立っている技術です。
Googleなどで「謎テクノロジー」で検索して頂けると弊社のエンジニアブログがヒットするので、
私は勉強会の場などではよく「弊社のことは謎テクノロジーで検索してください」とお話させていただいております。

謎テクノロジーの詳細については該当記事に委ねたいと思います。
ハンズラボが採用しているユニケージという謎テクノロジーについて 第1回

では何を説明するのか

ユニケージでは、表形式フラットファイルを用います。
元からユニケージを意識してデータ設計をすればよいのですが、既にデータソースがある場合にはデータ移行が必要になります。
DBからのデータダンプによる移行やExcelファイルをCSVエクスポートして移行など方法は色々ありますが、今回は、書くネタがなかったのでExcelファイルそのものからシェルスクリプトを用いてフラットファイルを作成していきたいと思います。

前提：
以降のコマンドの実行環境は下記のとおりです。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.13.1
BuildVersion:   17B1003
$ bash --version
GNU bash, version 3.2.57(1)-release (x86_64-apple-darwin17)
Copyright (C) 2007 Free Software Foundation, Inc.

また、今回の記事で用いるExcelファイルは下記の内容の.xlsx形式ファイルとなっております。

*	A	B	C
1	tanaka	M	1000
2	suzuki	M	2000
3	sasaki	F	3000

データに特に意味はありません。

Excelファイルについて

Excelファイルに対してfileコマンドを実行すると以下の結果が返ってくると思います。

$ file test.xlsx 
test.xlsx: Microsoft Excel 2007+

この通り、Excelファイルと認識されていますが、実態はZip圧縮されたXMLファイルの集合です。
試しにunzipコマンドを実行してみましょう。

$ unzip -d testdir test.xlsx 
Archive:  test.xlsx
  inflating: testdir/[Content_Types].xml  
  inflating: testdir/_rels/.rels     
  inflating: testdir/xl/_rels/workbook.xml.rels  
  inflating: testdir/xl/workbook.xml  
  inflating: testdir/xl/sharedStrings.xml  
  inflating: testdir/xl/theme/theme1.xml  
  inflating: testdir/xl/styles.xml   
  inflating: testdir/xl/worksheets/sheet1.xml  
 extracting: testdir/docProps/thumbnail.jpeg  
  inflating: testdir/docProps/core.xml  
  inflating: testdir/docProps/app.xml  

-dは出力先ディレクトリを指定するオプションです。
上記の通り、様々なXMLファイルが展開されました。

では、どこに値が格納されているのか？という問題になりますが、それっぽい名前のsheet1.xmlを見てみましょう。

$ xmllint --format testdir/xl/worksheets/sheet1.xml 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">
  <dimension ref="A1:C3"/>
  <sheetViews>
    <sheetView tabSelected="1" workbookViewId="0">
      <selection activeCell="A4" sqref="A4"/>
    </sheetView>
  </sheetViews>
  <sheetFormatPr baseColWidth="12" defaultRowHeight="20" x14ac:dyDescent="0.3"/>
  <sheetData>
    <row r="1" spans="1:3" x14ac:dyDescent="0.3">
      <c r="A1" t="s">
        <v>2</v>
      </c>
      <c r="B1" t="s">
        <v>1</v>
      </c>
      <c r="C1">
        <v>1000</v>
      </c>
    </row>
    <row r="2" spans="1:3" x14ac:dyDescent="0.3">
      <c r="A2" t="s">
        <v>0</v>
      </c>
      <c r="B2" t="s">
        <v>1</v>
      </c>
      <c r="C2">
        <v>2000</v>
      </c>
    </row>
    <row r="3" spans="1:3" x14ac:dyDescent="0.3">
      <c r="A3" t="s">
        <v>3</v>
      </c>
      <c r="B3" t="s">
        <v>4</v>
      </c>
      <c r="C3">
        <v>3000</v>
      </c>
    </row>
  </sheetData>
  <phoneticPr fontId="1"/>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

何やら、色々な情報が載っていますが、セルアドレスの様な文字と値のような文字があるのが確認できます。
そこに絞ってデータを見てみましょう。
（コマンド中のsedはデフォルトのネームスペースを無効化しています。）

$ echo "cat /worksheet/sheetData/row/c"|xmllint --shell <(sed -e "s/xmlns/ignore/" testdir/xl/worksheets/sheet1.xml) 
/ >  -------
<c r="A1" t="s">
  <v>2</v>
</c>
 -------
<c r="B1" t="s">
  <v>1</v>
</c>
 -------
<c r="C1">
  <v>1000</v>
</c>
 -------
<c r="A2" t="s">
  <v>0</v>
</c>
 -------
<c r="B2" t="s">
  <v>1</v>
</c>
 -------
<c r="C2">
  <v>2000</v>
</c>
 -------
<c r="A3" t="s">
  <v>3</v>
</c>
 -------
<c r="B3" t="s">
  <v>4</v>
</c>
 -------
<c r="C3">
  <v>3000</v>
</c>
/ > 

どうやら、c要素のr属性の値がセルアドレス、c要素の子要素のv要素が値のようです。（今回は数値と文字列のみの話とします）
しかし、数値のセルは値が入っているように見えますが、文字列のセルには見覚えのない数字が入っています。

この数字を紐解く鍵はsharedStrings.xmlという別なXMLファイルにあります。早速見てみましょう。

$ xmllint --format testdir/xl/sharedStrings.xml 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="6" uniqueCount="5">
  <si>
    <t>suzuki</t>
    <phoneticPr fontId="1"/>
  </si>
  <si>
    <t>M</t>
    <phoneticPr fontId="1"/>
  </si>
  <si>
    <t>tanaka</t>
    <phoneticPr fontId="1"/>
  </si>
  <si>
    <t>sasaki</t>
    <phoneticPr fontId="1"/>
  </si>
  <si>
    <t>F</t>
    <phoneticPr fontId="1"/>
  </si>
</sst>

見覚えのある文字列が出てきましたね。
実は、先程のxl/worksheets/sheet1.xmlファイル内でt属性がsのもの、即ち文字列型であるセルについては別なファイルに文字列が格納されており、そのv要素はxl/sharedStrings.xml内でのインデックスでありました。

まとめると、

• 　数値型：アドレス＝c要素のr属性、値＝c要素の子要素のv要素
• 文字列型：アドレス＝c要素のr属性、値＝xl/sharedStrings.xml内の[c要素の子要素のv要素]番目のsi要素の子要素のt要素

となります。分かりにくいですね。

しかし、ここまで情報が出揃ったので、後はテキスト処理はシェルスクリプトの得意分野。XMLファイルから必要な情報を抜き出すことでフラットファイルを作成していきましょう。
（今回の記事のために突貫で書いたのでスクリプトの汚さはご容赦ください）

# 文字列データ定義ファイルから文字列情報を抜き出す。
echo "cat /sst/si/t"                                                              |
xmllint --shell <(sed -e 's/xmlns/ignore/' testdir/xl/sharedStrings.xml)          |   
# t要素のある行のみ抽出
grep '<t>.*</t>'                                                                  |   
# t要素の値を抽出
sed -e 's/.*<t>\(.*\)<\/t>/\1/g'                                                  |   
# 上から順に0始まりのインデックスを付与。
awk '{print NR-1,$0}'                                                             |   
# 後に結合する都合上、インデックスを文字列としてソート。
LANG=C sort -k1,1                                                                 > index-string-file

# アドレス定義ファイルからアドレス情報を抜き出す。 
echo "cat worksheet/sheetData/row/c"                                                      |
xmllint --shell <(sed -e 's/xmlns/ignore/' testdir/xl/worksheets/sheet1.xml)              |   
# 改行コードを削除。
tr -d "\n"                                                                                |   
# c要素のブロック毎に引かれていたラインを改行コードに置換。
sed -e 's/-------/\'$'\n/g'                                                               |   
# タグで始まる行のみを抽出。
grep "^<"                                                                                  > all-address-file

# 値結合ファイルを作成（文字列型）
cat all-address-file                                                                |   
# 文字列型の行のみ取得。
grep 't="s"'                                                                        |   
# 1.インデックス 2.セルアドレス（英字部） 3.セルアドレス（数値部）として抽出。
sed -e 's/^.*r=\"\([A-Z]*\)\([0-9]*\)\".*<v>\(.*\)<\/v>.*$/\3 \1 \2/g' |
# インデックス順にソート。
LANG=C sort -k1,1                                                                   |   
# 上で作成した文字列情報ファイルと、インデックスをキーに結合
join index-string-file -                                                            |   
# インデックスはもう不要なので、セルアドレスと文字列情報のみ出力
awk '{print $4,$3,$2}'                                                              > string-file

# 値結合ファイルを作成（数字型）
cat all-address-file                                                              |   
# 文字列型でない行を取得
grep -v 't="s"'                                                                   |   
# セルアドレスと文字列情報を出力
sed -e 's/^.*r=\"\([A-Z]*\)\([0-9]*\)\".*<v>\(.*\)<\/v>.*$/\2 \1 \3/g'  > numeric-file

# それぞれのファイルをユニオンして、アドレス順にソート
cat string-file numeric-file |
LANG=C sort -k1,2    

出力結果

1 A tanaka
1 B M
1 C 1000
2 A suzuki
2 B M
2 C 2000
3 A sasaki
3 B F
3 C 3000

今回はスクリプトは割愛しますが、１列目、2列目に対して２重ループでマッピングしていくことで以下のようなファイルが得られます。（今回はわかりやすいように各行、列にセルアドレスを付与しています。）

* A B C
1 tanaka M 1000
2 suzuki M 2000
3 sasaki F 3000

元のExcelの情報が復元できましたね。
この形式になってしまえば、集計や分析等はユニケージを用いて非常に簡単に行なえます。
CSVエクスポートどころか、手でコピペしたほうが早いですね。

まとめ

ここまで長々と説明しましたが、「エクシェル芸」辺りでググってもらうとより詳細な情報が得られるかと思います。

決して最新の技術でもなければ汎用性のある技術でもないですが、これはこれでなかなかつらみ深みがあって面白いです。
興味を持って頂けたら、ぜひ調べて見てください。

明日は、同期入社の@szkdskさんの記事です。