【問題と解答】第26回シェル芸勉強会及びエクシェル芸勉強会

Pocket
LINEで送る

問題のみのページはコチラ

問題で使うファイル等

GitHubにあります。ファイルは

https://github.com/ryuichiueda/ShellGeiData/tree/master/vol.26

にあります。

クローンは以下のようにお願いします。

$ git clone https://github.com/ryuichiueda/ShellGeiData.git

環境

シェル芸を行うのはUbuntu Linux 16.04です。確認はMacのExcelやWord, PowerPointで行いました。今回は特にワンライナーにこだわる必要はありません。シェルスクリプトにしても構いません。もちろん、一般解にこだわる必要もありません。

イントロ

第26回シェル芸勉強会及びエクシェル芸勉強会

Q1

.xlsxや.docx、.pptxファイルはzipファイルです。リポジトリの中のxlsx,docx,pptxを展開し、中にどんなファイルがあるか見て、再び戻して再び.xlsx,.docx,.pptxファイルとして開いてみてください。

解答

解凍すると、一つのディレクトリに収まってなく、その場にいくつかのファイルが展開されるので厄介です。ファイルの置き場所と展開する場所を分けましょう。

$ mkdir ~/tmp
$ cd ~/tmp
###tmpの下に置かずに解凍###
$ unzip ~/ShellGeiData/vol.26/graph.xlsx 
ueda@remote:~/tmp$ ls
[Content_Types].xml  _rels  docProps  xl
ueda@remote:~/tmp$ tree
.
├── [Content_Types].xml
├── _rels
├── docProps
│   ├── app.xml
│   ├── core.xml
│   └── thumbnail.jpeg
└── xl
    ├── _rels
    │   └── workbook.xml.rels
    ├── calcChain.xml
    ├── charts
    │   └── chart1.xml
    ├── drawings
    │   ├── _rels
    │   │   └── drawing1.xml.rels
    │   └── drawing1.xml
    ├── styles.xml
    ├── theme
    │   └── theme1.xml
    ├── workbook.xml
    └── worksheets
        ├── _rels
        │   └── sheet1.xml.rels
        └── sheet1.xml

10 directories, 14 files
###再圧縮したファイルも別のディレクトリに作ると事故が少ない###
ueda@remote:~/tmp$ zip -r ../hoge.xlsx ./

Q2

20141019OSC_LT.pptxのスライドに何回「危険」という単語が出てくるか数えてください。画像になっているものは除きます。

解答

$ unzip ~/ShellGeiData/vol.26/20141019OSC_LT.pptx ;
grep -o 危険 ppt/slides/slide* | wc -l
17

unzipは-pオプションで必要なファイルだけcatすることができます。ということで次のようにすると完全にワンライナーになります。

$ unzip -p ~/ShellGeiData/vol.26/20141019OSC_LT.pptx 'ppt/slides/slide*' |
 grep -o 危険 | wc -l
17

Q3

20141019OSC_LT.pptxのスライドから画像を抽出して、一つのディレクトリにまとめてzipで固めてください。

解答

$ unzip ~/ShellGeiData/vol.26/20141019OSC_LT.pptx ;
 zip -r media.zip ./ppt/media/

Q4

20141019OSC_LT.pptxのスライドの7ページ目のテキストをスクレイピングしましょう。以下が出力の例です。

戦果(?)
初日だけで見知らぬ方のマシン3台轟沈
その他自爆者多数
Docker上で試したらホストマシン沈黙の報告
自分の本がサイト経由で1冊だけ売れた
フォロワーが1人減った
(以下、フッタ等の文字列が混ざっても可とします)

解答

$ unzip -p ~/ShellGeiData/vol.26/20141019OSC_LT.pptx ppt/slides/slide7.xml |
 xmllint --format - | grep '<a:[pt]>' | sed 's;</.*;;' |
 sed 's;<.*>;;' | awk 'NF==0{print "@@@"}{print}' |
 xargs | sed 's/@@*/\n/g' | awk 'NF' | tr -d ' '
戦果(?)
初日だけで見知らぬ方のマシン3台轟沈
その他自爆者多数
Docker上で試したらホストマシン沈黙の報告
自分の本がサイト経由で1冊だけ売れた
フォロワーが1人減った
2014/10/19
OSCTokyo/Fall2014
7

Q5

graph.xlsxの2列の数字を抜き出して端末にSSV形式のデータ(CSVのカンマがスペースになったもの)、あるいはセルの番号と値のリストとして抜き出してください。

解答

###スペース区切り###
$ unzip ~/ShellGeiData/vol.26/graph.xlsx;
 cat xl/worksheets/sheet1.xml |
 sed 's;</row>;\n;g' | sed 's;</v>.*<v>; ;' |
 sed 's;.*<v>;;' | sed 's;</v>.*;;' | grep -v "^<"
###スペース区切り・ワンライナーバージョン###
$ unzip -p ~/ShellGeiData/vol.26/graph.xlsx xl/worksheets/sheet1.xml |
 sed 's;</row>;\n;g' | sed 's;</v>.*<v>; ;' |
 sed 's;.*<v>;;' | sed 's;</v>.*;;' | grep -v "^<"

もっとスマートに行うには、html-xml-utilsとlibxml2-utilsをインストールしてhxselectコマンドやxmllintを使います。

$ sudo apt install html-xml-utils
$ sudo apt install libxml2-utils
###番号と値のリスト###
$ unzip -p ~/ShellGeiData/vol.26/graph.xlsx xl/worksheets/sheet1.xml |
 hxselect c -s '\n' | sed 's;[^=]*=";;' |
 sed 's;".*<v>; ;' | sed 's;<.*;;'
$ unzip -p ~/ShellGeiData/vol.26/graph.xlsx xl/worksheets/sheet1.xml |
 xmllint --format - | grep -e '<c r=' -e '<v>' | xargs |
 sed 's;</v>;\n;g' | sed 's/.*=//' | sed 's/>.*>/ /'

もっと便利なツールもあるという噂ですが、とりあえず私からこれくらいで・・・

Q6

hanshin.xlsxのシートについてQ2と同様SSV形式か、セルの番号と値のリストとして端末上に出力してください。日付のセルについては何を出力しても良いことにします。

解答

文字列は展開したファイルのxl/sharedStrings.xmlに順番に入っています。

$ unzip ~/GIT/ShellGeiData/vol.26/hanshin.xlsx 
$ xmllint --format xl/sharedStrings.xml  | head
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="18" uniqueCount="15">
  <si>
    <t>真弓</t>
    <rPh sb="0" eb="2">
      <t>マユミ</t>
    </rPh>
    <phoneticPr fontId="1"/>
  </si>
  <si>

読みのデータが邪魔なので、消去した上でリストを作っておきます。

$ unzip -p ~/GIT/ShellGeiData/vol.26/hanshin.xlsx xl/sharedStrings.xml |
 hxselect si -s '\n' | awk -F'[<>]' '{print NR-1,$5}' > strings
$ head -n 3 strings 
0 真弓
1 弘田
2 バース

次に、シートのデータを加工していきます。文字列のセルには「t=”s”」という属性があるので、これで文字列のセルと数字のセルを分けます。文字列のセルのv要素にある数字は、sharedStrings.xmlの何番目の文字列がこのセルに入るかを意味します。(sharedStrings.xmlはXMLファイルなのにデータの並び順で文字列を管理しているという・・・)

$ unzip -p ~/GIT/ShellGeiData/vol.26/hanshin.xlsx xl/worksheets/sheet1.xml |
  hxselect c -s '\n' | grep '<v>' |
 awk -F'[<> "]' '/t="s"/{print $4,"s",$(NF-4)}!/t="s"/{print $4,"n",$(NF-4)}'
B1 n 42522
C1 n 42561
A2 n 1
B2 s 0
C2 s 0
A3 n 2
B3 s 1
C3 s 9
A4 n 3
B4 s 2
...

ここまでできたら、awkで無理やり文字列のファイルとデータのファイルを混ぜて答えを出します。この例ではFILENAMEという変数を使っています。

$ unzip -p ~/GIT/ShellGeiData/vol.26/hanshin.xlsx xl/worksheets/sheet1.xml |
 hxselect c -s '\n' | grep '<v>' |
 awk -F'[<> "]' '/t="s"/{print $4,"s",$(NF-4)}!/t="s"/{print $4,"n",$(NF-4)}' |
 awk 'FILENAME=="strings"{s[$1]=$2}FILENAME=="-"&&
$2=="s"{print $1,s[$3]}$2=="n"{print $1,$3}' strings -
B1 42522
C1 42561
A2 1
B2 真弓
C2 真弓
A3 2
B3 弘田
C3 北村
A4 3
B4 バース
C4 バース
A5 4
B5 掛布
C5 掛布
A6 5
B6 岡田
C6 佐野
A7 6
B7 佐野
C7 木戸
A8 7
B8 平田
C8 平田
A9 8
B9 木戸
C9 永尾
A10 9
B10 ゲイル
C10 池田

Q7

certificate.docxファイルを開いて確認し、人の名前が入るところに好きな名前を入れてみましょう。

解答

ホームの下にtmp等の一時ディレクトリを作ってそこで試しましょう。-iオプションは、BSD系のsedの場合-i.bakと言うように拡張子をつけてバックアップファイルを作らなければなりませんが、その場合はバックアップファイルを消してから再圧縮します。

###~/tmp/で作業すると仮定します###
$ unzip ~/ShellGeiData/vol.26/certificate.docx ; 
sed -i 's/WINNER/しぇる芸のオッサン/' word/document.xml ; 
zip -r ../hoge.docx ./
###ワーニングが出ますが開けます###

Q8

Q7を応用し、次のリストlist.txtで、複数の表彰状を作ってみましょう。

$ cat list.txt 
シェル芸おじさん
シェル芸野郎
変態シェル芸豚野郎

解答

unzipに-o(overwrite)オプションをつけると便利です。

$ cat ~/ShellGeiData/vol.26/list.txt | 
while read name ; do unzip -o ~/ShellGeiData/vol.26/certificate.docx ;
 sed -i "s/WINNER/$name/" word/document.xml ;
 zip -r ../$name.docx ./ ; done
Pocket
LINEで送る

【問題のみ】第26回シェル芸勉強会及びエクシェル芸勉強会

Pocket
LINEで送る

解答はコチラ

問題で使うファイル等

GitHubにあります。ファイルは

https://github.com/ryuichiueda/ShellGeiData/tree/master/vol.26

にあります。

クローンは以下のようにお願いします。

$ git clone https://github.com/ryuichiueda/ShellGeiData.git

環境

シェル芸を行うのはUbuntu Linux 16.04です。確認はMacのExcelやWord, PowerPointで行いました。今回は特にワンライナーにこだわる必要はありません。シェルスクリプトにしても構いません。もちろん、一般解にこだわる必要もありません。

イントロ

第26回シェル芸勉強会及びエクシェル芸勉強会

Q1

.xlsxや.docx、.pptxファイルはzipファイルです。リポジトリの中のxlsx,docx,pptxを展開し、中にどんなファイルがあるか見て、再び戻して再び.xlsxファイルとして開いてみてください。

Q2

20141019OSC_LT.pptxのスライドに何回「危険」という単語が出てくるか数えてください。画像になっているものは除きます。

Q3

20141019OSC_LT.pptxのスライドから画像を抽出して、一つのディレクトリにまとめてzipで固めてください。

Q4

20141019OSC_LT.pptxのスライドの7ページ目のテキストをスクレイピングしましょう。以下が出力の例です。

戦果(?)
初日だけで見知らぬ方のマシン3台轟沈
その他自爆者多数
Docker上で試したらホストマシン沈黙の報告
自分の本がサイト経由で1冊だけ売れた
フォロワーが1人減った
(以下、フッタ等の文字列が混ざっても可とします)

Q5

graph.xlsxの2列の数字を抜き出して端末にSSV形式のデータ(CSVのカンマがスペースになったもの)、あるいはセルの番号と値のリストとして抜き出してください。

Q6

hanshin.xlsxのシートについてQ2と同様SSV形式か、セルの番号と値のリストとして端末上に出力してください。日付のセルについては何を出力しても良いことにします。

Q7

certificate.docxファイルを開いて確認し、人の名前が入るところに好きな名前を入れてみましょう。

Q8

Q7を応用し、次のリストlist.txtで、複数の表彰状を作ってみましょう。

$ cat list.txt 
シェル芸おじさん
シェル芸野郎
変態シェル芸豚野郎
Pocket
LINEで送る

Excel読み取りコマンドができた。ただしOpen usp Tukubai依存

Pocket
LINEで送る

小児科の待合室からこんにちは。

エクシェル芸の修行の一環であるオフィス用コマンドをこの前から作ってますが、とりあえずシートの数字の文字列を読み込んで標準出力にゴモゴモと出すコマンドが完成しました。

続きを読む Excel読み取りコマンドができた。ただしOpen usp Tukubai依存

Pocket
LINEで送る

エクセルのワークシートを読み込むコマンド

Pocket
LINEで送る

https://github.com/ryuichiueda/ShellOfficeTools
にエクセルのワークシートを読むコマンド「exread-sheet」を加えました。まだバグがあると思いますが。

続きを読む エクセルのワークシートを読み込むコマンド

Pocket
LINEで送る

Word(docx)用sedを改良してしもうた。

Pocket
LINEで送る

昨日からの続編です。Word版sedを作る話。

やっぱりsedはsedらしくということで、

  • 標準入力からファイルを読む
  • sedのコマンドがそのまま使えるようにする

というように改変しました。https://github.com/ryuichiueda/ShellOfficeToolsで公開しております。

続きを読む Word(docx)用sedを改良してしもうた。

Pocket
LINEで送る

Word(docx)用sedを作ってしもうた。

Pocket
LINEで送る

続編アリ(2014/4/23)

クリエイチビチーが止まりません。上田です。

お昼ごはんの蕎麦を食いながら昼休みの間に「ワードファイルの文字列を改ざんするコマンド」を作りましたので公開をば。本の一部にするので遊んでいるわけではない、遊んでいるわけではないと、自分に言い聞かせながら・・・。

続きを読む Word(docx)用sedを作ってしもうた。

Pocket
LINEで送る

ワードシェル芸?の方法。

Pocket
LINEで送る

もう、やり方だけ。環境はUbuntu。hxselectで要素を指定するときに、コロンをエスケープするというのでちょっとはまった。

###docxことzipファイルの中はこんな感じ。###
###文章の内容はword/document.xml###
###画像はjpegがそのまま入っている###
Archive:  self_introduction.docx
  Length      Date    Time    Name
---------  ---------- -----   ----
     1871  1980-01-01 00:00   [Content_Types].xml
      590  1980-01-01 00:00   _rels/.rels
     1484  1980-01-01 00:00   word/_rels/document.xml.rels
     4835  1980-01-01 00:00   word/document.xml
     1789  1980-01-01 00:00   word/footnotes.xml
     1783  1980-01-01 00:00   word/endnotes.xml
    21556  1980-01-01 00:00   word/media/image1.jpeg
     7561  1980-01-01 00:00   word/theme/theme1.xml
     4193  1980-01-01 00:00   word/settings.xml
    49341  1980-01-01 00:00   word/stylesWithEffects.xml
    48475  1980-01-01 00:00   word/styles.xml
     1021  1980-01-01 00:00   docProps/app.xml
     3484  1980-01-01 00:00   word/fontTable.xml
     8773  1980-01-01 00:00   word/numbering.xml
      871  1980-01-01 00:00   word/webSettings.xml
      713  1980-01-01 00:00   docProps/core.xml
---------                     -------
   158340                     16 files
###おりゃ###
ueda@remote:~$ unzip -p self_introduction.docx word/document.xml | hxselect 'w\:t' | sed 's;</w:t>;&\n;g'
<w:t>自己紹介</w:t>
<w:t xml:space="preserve"> </w:t>
<w:t>【氏名】漢字)上田 隆一</w:t>
<w:t xml:space="preserve">    ローマ字)</w:t>
<w:t>Ryuichi Ueda</w:t>
###画像はふつうに抽出###
ueda@remote:~$ unzip self_introduction.docx word/media/image1.jpeg
Archive:  self_introduction.docx
 extracting: word/media/image1.jpeg 

以上。わーどうしましょう。

Pocket
LINEで送る

unzip -p で真のエクシェル芸が完成したような気がする。

Pocket
LINEで送る

なんでこんなに眠いのか。

地味に好評なエクシェル芸ですが、肝心なことを忘れていました。

これまでのブログの記事では、エクセルを一旦解凍していましたが、unzipの-pを使うとその必要はありません。こんな感じにxlsxから標準出力にダイレクトにxmlを流すことができます。unzip -pの後にzipファイル(この場合はエクセルファイル)、その次にzipの中の見たいファイルを指定します。最初のシートなら大抵xl/worksheets/sheet1.xmlを指定すればよいことになります。

続きを読む unzip -p で真のエクシェル芸が完成したような気がする。

Pocket
LINEで送る

エクセル方眼紙の無駄をエクシェル芸で遠回しに指摘する。

Pocket
LINEで送る

上田です。カーネル読書会に潜入中です。

ここ2日、連続でExcelファイルをシェル芸でほじる記事を書きましたが、副会長から、エクシェル芸という名称をいただきました。使わせていただきます。有り難うございます。

Excelと言えば、最近、これとかこれとか、エクセル方眼紙が話題になっています。

続きを読む エクセル方眼紙の無駄をエクシェル芸で遠回しに指摘する。

Pocket
LINEで送る