openpyxlでそれっぽいExcel帳票の体裁を作る

■ 概要

人には、Excel帳票から逃れられないときがある。そんなとき、同じ帳票を作るにしても、人手でやるよりなるべく機械にやらせた方が良い…というのは建前で、何かしらコーディングに近いことをやっていた方が楽しいので、openpyxlをいろいろ試してみた。まだ今ひとつこなれていないが、まあまあ使いでがありそうな印象を受けた。

■ 前置き

多バイト文字列周りの処理が楽だったので、Python3系を便利に使っていた。データをcsvで受けて、処理して、csvで出し直してやれば、身の回りの些細なことは処理できていた。でもopenpyxlを見て、入力はともかく出力側は、偉い人向けにExcel帳票でそのまま出しても良いんじゃないかと思ったのだ。

というわけで、Python3.6とopenpyxl2.4.9を併用して色々試してみた結果を自分のために残しておこうと思う。なお基本的な使い方は公式ドキュメントに加え、下記いくつかの日本語サイトに教わった。

qiita.com

www.lisz-works.com

imabari.hateblo.jp

■ バージョン差の罠

openpyxlは自称「成長途中」なので、それはまあ良く挙動が変わる。しかも処理速度が桁で遅くなったりもする。というわけで、本稿ではバージョン2.4.9に限定してメモする。2.5.0ではメジャーバージョンアップと称し、行や列の挿入ができるようになった代わりに、（環境由来の問題かもしれないが）今まで一秒以下でできていた処理が数秒かかるようになったし、いまのところ2.4.9で事足りている。

■ データ読みだしの罠：セル結合内容推定と計算式実行結果取得、日時情報の扱い

既存Excelを読み出して何か処理をしようとするとき、まず間違いなく結合されたセルの扱いが面倒なことになる。結合セルの値は、もっとも左上の被結合セルのみから読み出せて、その他の被結合セルは値なし(None)になる。したがって、どのくらいの範囲が結合されているかにもよるが、値なしセルの内容を推定・補完する論理を作る必要がある。可読性のため、適当な関数にくくりだして対処する。

次に、既存Excelに数式やハイパーリンクが含まれていて、数式の計算結果やハイパーリンク先を読み取りたい場合。これはworkbookを読み出す際、"data_only True"を明示的に指定すればOK（参考）。

それから年月日といった日時情報をopenpyxlで読み出すと、データ型はdatetimeになる。日時同士の演算をやる予定がなければ、最初に文字列型に変換してしまって良いと思う（参考：日付フォーマット(datetime⇔文字列） | Python Snippets）。

なお今回、図形オブジェクトに処理対象データが含まれている場合は考えない。そういうときは、まず元帳票の書式をすり替えて、機械で読みやすくするところから始めたほうがいい。印刷した見た目が同じになるようにでもしておけば、書式改訂したからといって偉い人が怒鳴り込んでくる可能性は低いんじゃないかな。だといいな。

■ 数値と見た目設定を分ける

openpyxlだけの問題ではないが、Excelではいろいろな表現が使えるため、どうしても数値以外の何かに情報を持たせたくなってしまう心理が働く（注目すべきデータを赤字表記する、とか）。それはある程度やむを得ないけれど、そういうデータを機械処理する別の仕事が来たりすると、凡例の解釈にえらい苦労するのが目に見えている。ほら、各シートのタブに謎の色づけがされてて、その意味がわからないと帳票全体の解釈ができなくて途方に暮れた経験、皆さんあるでしょう。

とにかくPythonを使って何かしようとしているからには、まず大抵の場合、数値演算や文字列処理やそれら処理の制御やらが絡んでいるはずで、それらをやっているときにopenpyxlを混ぜない方がいい。Excel帳票に仕立てるなんてのは、入力データを処理し終えて所望の出力データを得た後で遅くない。極端な話、出力データをいったんcsvで作って、そのcsvの見た目を整えるためにopenpyxlでExcel形式に仕立て直してやる、くらいのアプローチをとっても損はない…気がする。

■ 列の幅を決める

csvで表現できる情報をわざわざExcelにする理由のひとつは、見た目を整えたいからである。とすると、まずは列の幅と行の高さをいじりたくなるだろう。横長ディスプレイが流行る昨今、前者が比較的大事になることが多そうだ。ではどうすればPythonからExcelの行幅を指定すれば良いか。

COL_WIDTH_RATIO = 1.2    # 調整係数
column_width_dic = {"A":3.0, "B":10.0}
for column_name in column_width_dic.keys():
    ws.column_dimensions[column_name].width = column_widht_dic[column_name] * COL_WIDTH_RATIO

などとすればいい。

ただしここでよくわからないのは、指定した幅が必ずしもExcel上の幅(pt)にならないようだということ。解像度周りが怪しそうだが、ともかく再現条件がよくわからないので、幅指定には適当な補正係数をかけておくと良さそうだ。

■ 色設定

セルの塗りつぶしや文字色を設定するとき、色の数値はARGBで指定する。openpyxlがあらかじめ定数として定義してある色もあるが、それらを使うのは将来バージョンで非推奨となるらしいし、それ以前に自分好みの色がない場合が多いので、適宜定数として設定して使うようにする。毎回直値で指定すると可読性が落ちるので避けたい。

■ セルの書式設定

フォントや罫線、表示位置の指定やセルの色づけなど様々な書式設定ができる。ただ公式ドキュメントを眺めても今ひとつ具体的な設定値がわからない場合が多い。そういうときはopenpyxlのソースコードを直接見て理解するか、所望のセルを一つ作ったサンプルExcelを読み込んで確認すると手間が省ける。

とりあえず一通りのサンプルを以下に付す。

from openpyxl.styles import Font
from openpyxl.styles.colors import Color
from openpyxl.styles import PatternFill
from openpyxl.styles import Alignment
from openpyxl.styles import Border, Side

################
BORDER_COLOR     = "FFD9D9D9"
TITLE_FONT_COLOR = "FFFFFFFF"
TITLE_CELL_COLOR = "FF595959"

ws["A1"].value     = "タイトル"
ws["A1"].fill      = PatternFill(patternType="solid", fgColor=TITLE_CELL_COLOR)
ws["A1"].font      = Font(name="Meiryo UI", size=10, color=TITLE_FONT_COLOR)
ws["A1"].alignment = Alignment(shrinkToFit=True, horizontal="center", vertical="center")
ws["A1"].border    = Border(outline=True, right  = Side(style="thin", color=BORDER_COLOR), \
                                          left   = Side(style="thin", color=BORDER_COLOR), \
                                          top    = Side(style="thin", color=BORDER_COLOR), \
                                          bottom = Side(style="thin", color=BORDER_COLOR)  \
                           )

■ セルに対するコメントの設定

注意すべきは、openpyxl 2.4.9では、コメントの設定に関する機能が未完成ということ。これは現時点の最新の2.5.1でも同様のようである。具体的には、コメント内部の書式設定やコメント欄の幅・高さの指定ができない。ぱっと見、openpyxlのCommentsモジュールのソースコードに関連していそうな記述を書き換えれば良さそうだが、所望の結果は得られないようだ。

というわけで、規定の大きさのコメントボックスに入りきらない文字数を設定しても全部は表示されない。コメント内容を懇切丁寧に、しかも改行を織り交ぜるなどして書いてしまっていると、あっという間にあふれる。簡易ポップアップGUIとして使えそうな期待はあるが、現時点では別の方法をとるのが賢明だ。

ちなみに現時点で使えそうなサンプルコードは以下の通り。

from openpyxl.comments import Comment
#中略
row_num = 1
column_num = 1
target_cell = ws.cell(row=row_num, column=column_num)
target_cell.commnet = Comment(test="コメント内容", author="コメント記入者名")

なおコメント内容の読み出しも期待通り動かなさそうなので、コメント周りは今のところそんな期待してはいけないようだ。開発チケットを見てみるとissueとして報告されているが、対応優先度は「低」になっている。

…と思ってたら、コメントボックスのサイズ設定についてはバージョン2.5.2で直ったようだ。そのうち動作確認してみよう。

■ おわりに

いろいろサンプルスクリプトを書いてみて、所望のExcelをだいたいは作れるようにはなってきたが、コードを見返すとどうしても可読性が低い。もっとシンプルに書けないと、まじめにメンテナンスする羽目になったときにコトである。できればLint freeにしたいし、それが無理でもコーディングパターン的ノウハウを探して対処していきたい。