フォルダーの中のエクセル請求書ファイルを合計する

日本語のヘルプファイルを読みたい

UNIXでは慣例的にコマンドのマニュアルを読むための「man」コマンドが用意されている。だが、BUW(Bash on Ubuntu on Windows)のベースとなるUbuntu 14.04 LTS(いい加減16.04 LTSに更新されないものかと思うのだが……)には、ベーシックなパッケージはインストールされているものの、日本語化されたドキュメントは未インストールだ。

Ubuntuでは、利用者向け日本語版マニュアルページパッケージ「manpages-ja」と、開発者向け日本語版マニュアルパッケージ「manpages-ja-dev」が用意されている。これらをインストールするため、「sudo apt-get install manpages-ja manpages-ja-dev -y」を実行すれば、至極簡単に日本語のマニュアルを閲覧できる。もちろん英語の方がマニュアルの鮮度もよく、Linuxを使うのであれば簡単な英語は使えるべきだ、という意見もあるだろう。だが、日本語に慣れた方なら、やはり日本語で読めた方が安心だ。必ずしも推奨することではないが、日本語マニュアルを必要とする読者諸氏は試してほしい。

Bashから請求書フォーマットにアクセスする

さて、複数のExcelブックファイルに分散した数値をまとめて拾いたいというシチュエーションは少なくない。シート単位でまとめておけば、Excel内部の参照で完結できるし、外部ファイルを参照する方法もある。筆者も異なるExcelブックファイル間で数値を参照しあう仕組みで管理しているが、ファイルの場所を変更するだけでダメになってしまうため、いつか抜本的な解決をしなければならないと思案中だ。

それはさておき、シェルスクリプトは面倒な作業を簡潔に済ませるのが主な目的である。日常業務を自動化し、空いた時間で他の作業を行うというアプローチは、現在のITを取り巻く環境に類似するのではないだろうか。前置きが長くなってしまったが、例えばフォルダー内にある請求書など決まったフォーマットのExcelブックから、特定のセルに入力した数値を取り出し、その合算を出力できれば便利だろう。このような前提で作成したのが今回のシェルスクリプトである。いつもどおりお使いの環境に合わせて変数の値を変更し、シェルスクリプトに実行権限を与えてからお試し頂きたい。

 #!/bin/bash

 BaseDir=/mnt/c/Users/kaz/Desktop
 FileDir=$BaseDir/Test
 TmpDir=$BaseDir/_TMP
 TmpFile=$BaseDir/_num.txt
 OutputFile=$BaseDir/Output.txt

 if [ ! -d $TmpDir ]; then
    mkdir $TmpDir
 fi

 cd $FileDir
 for File in $(ls *.xlsx); do
    unzip -qq $File -d $TmpDir
    cat $TmpDir/xl/worksheets/sheet2.xml | perl -pe 's/></>\n</g;' | grep -A 2 -e r=\"F49\" | tail -n 1 | perl -pe 's/<[^>]*>//g' >> $TmpFile
    rm -rf $TmpDir
 done

 cat $TmpFile | awk 'NF>0 {n+=$1} END {printf "%f",n/NR}'
 echo
 rm $TmpFile

前回と同じようにExcelブックを展開し、セルの内容を読み取るというものだが、今回のターゲットは「xl/sharedStrings.xml」ではなく「xl/worksheets/sheet2.xml」である。Excelブックは前者のファイルに文字列参照番号と文字列を格納し、シート上の数値は呼応者のファイルに格納しているようだ。下図は元となるExcelブックファイルの1つだが、ご覧のように演算結果の数値も「sheet2.xml」ファイルに格納されていた。ただし、下図では「132000」という数値だが、ファイル内では「131999.76」と記録されている。この辺りはExcelブックの仕様話になるので今回は割愛する。

今回は14～18行目にあるfor文のループで、Excelブックの展開と指定したセルの数値をgrepで抽出。tailコマンドとperlコマンドで不要な情報を削除した結果を変数「TmpFile」に記録している。その結果をfor文のループが終わってからawkコマンドで計算を行うのが20行目だ。なお17行目と22行目のrmコマンドは展開したExcelブックの残骸や一時ファイルを削除するために使用している。このシェルスクリプトを実行すると、セルF49の数値を参照するセルC18の内容を抽出し、その合算を表示する。

今回awkを使用したのは応用が利くからだ。例えば合計ではなく平均値が必要な場合は「n/NR」に変更すれば、簡単に求められる。awkの演算方法に関しては専門の書籍などを参照してほしい。

阿久津良和(Cactus)