openpyxlでハイパーリンクを設定するとりあえずの方法

■ 二通りの手段、どちらを採るべきか

openpyxl(2.4.9)には、出力Excel上にハイパーリンクを作成する方法が二通りある。一つはopenpyxl.worksheet.hyperlinkモジュールを使う方法で、もう一つはExcelのHYPERLINK関数をそのまま書き下す方法である。これらを使うと外部Excelファイルの所望のセルにリンクを貼ることができ、たとえばある数値の元データがどのExcelのどのセルなのかを明示できる。

ただ前者は、リンク先ファイルを絶対パスで指定するとなぜか機能しない(相対パス指定ならOK)。というわけで後者を採ることになる。

 

というかなんで同じ機能を実現するのに二通りの方法が用意されているんだ。Python的には整理した方がいいんじゃないのか。

ハイパーリンクに見えるような書式設定方法

ハイパーリンクを作成すると、一般的には参照名の文字列に「未アクセスのリンク先は青色、アクセス済みのリンク先は紫色」といった色分けがなされる。当該文字列に下線が引かれることもある。また当然、当該文字列のフォントや文字サイズは自由に設定できる。

ところがopenpyxlでHYPERLINK関数を作らせると、これがなかなかうまくいかない。先ほど却下したhyperlinkモジュールを使えば、色分けはされるもののフォント関連のスタイル指定ができない。あろうことかMSゴシックとかになる。というわけで試行錯誤。

- ビルトインスタイルを指定してみる→NG

openpyxlにはビルトインスタイルがあって、一般的な書式は'Title'だとか'Accent1'だとかのキーワードを使って

% target_cell = ws.cell(row=row_num, column=column_num)     # 行列座標指定

% target_cell.style = 'Accent1'

といった按配で指定できる。で、このキーワードの中に'Hyperlink'というのがあるので使ってみたところ、色分けはできたがフォント指定が結局できなかった。NGである。

- セルのフォント設定を細かく指定する→OK

ビルトインスタイル探しにちょっと眺めたopenpyxl.stlyesモジュールと関連ソースコードをたどっていくと、リンク風味の色分けはフォントカラーの"theme"として設定できるようである。そこでメイリオUIの10ptでハイパーリンクを作成するには以下のように指定する。

from openpyxl.styles import Font
from openpyxl.styles.colors import Color

target_cell.value = '=HYPERLINK("[linked_file.xlsx]!A1", "参照名")'    # A1セルにリンク
target_cell.font  = Font(name="Meiryo UI", size=10, color=Color(rgb=None, indexed=None, auto=None, theme=10, tint=0.0, tpye="theme"))

 

やれやれ。 ようやくOKである。

■ 雑感

openpyxl、軽いノリで周囲におすすめするにはまだ早いな。影響大きそうなworksheet.cell()のパラメータの渡し方が2.5.0で変わるなど、安定的とも言えない気がする。現段階でがんばって関連コードを量産しても、早々に負債化する恐れ大なので、まあ手元でちまちまいじるにとどめておくのが無難なように思える。

ローカルストレージからNASへのファイル転送(Windows7)

■ 概要

ファイルのタイムスタンプによる写真データ管理はNASと相性が悪いからいつの日か乗り換えないとダメだ。

■ 経緯

ディレクトリのタイムスタンプを維持するため、ローカルストレージからNASへのファイル転送にはrobocopyを使っている。

ただ、いったんコピーした後、コピー元で書き込み禁止属性のみ変更した場合、デフォルトのオプション設定では当該変更が反映されない(大事な写真だけreadonlyに設定する場合など)。というわけで、そういう場合は/ISオプションを併用して下記のように指定することで全件上書きコピーできる模様。

robocopy c:\DATA\DigitalCamera\* z:\ /E /DCOPY:T /COPY:DAT /FFT /ETA /IS

オプションについては以下のサイトが詳しい。

Robocopy - DOS コマンド一覧 - Programming Field

 

ただ、全ファイル属性込みでの比較になるため、コピー時間はそれなりに増えてしまうのがちょっと悩ましい。素直に最初からNASでオリジナルファイルを管理すればいいのだが、なかなかその文化に移行しきれないのが悲しい現状…。

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

■ 概要

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

■ 前置き

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

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

qiita.com

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にしたいし、それが無理でもコーディングパターン的ノウハウを探して対処していきたい。

製麺用包丁あれこれ

■ 製麺用の包丁は切れ味大事

私が最近買ったのは「貝印 kai 関孫六 麺切り 包丁 AG-5021」とかいうので、通販向け写真を見ても実用品としてどうかと思ったが、同価格帯で思い切れる品が他に無かったのでやむなく買った。

商品到着後、とりあえずその辺のティッシュペーパーを丸めて試し切りしてみたが、ようやく傷がつくかどうかという有様。普通の包丁はさっくり切り込めるのに…。

というわけで、仕方ないので手持ちの砥石を持ち出して刃つけと仕上げをしてから使った。

いつも使っている麺切り台に比べて重さも切れ味もだいぶ違って、うどんを細麺には仕上げられなかったが、まあ形にはなった気がする(良く切れる牛刀とどっちがいいかは、時間の都合もあって確認できなかった)。

 

■ 安物でがんばるには

市価2000円程度の麺切り包丁でうどんを切るには、こま板がどうこうよりも、まず包丁にしかるべく刃をつける必要があるように思えた。どうせ包丁自体の重量(300gとか)では切れないので、刃先に頼った方が良かろう。その際、中砥ではどうしても限界があるので、荒砥から使う必要がありそうだ。手元に荒砥がないので、とりあえず今後の課題である。ただ中砥でも、何もやらないよりはずっと良い。

それから、包丁が凡庸なら、麺を切るときに一工夫すれば多少はマシになりそうだ。すなわち麺を折りたたんで切る際、折りたたみ回数を極力減らして、全体として薄い状態で切りに行けば、切り口の崩れも少なくて済む。というわけで、安物でがんばるなら、なるべく刃渡りが長いものを選んで、その刃渡りに極力合わせた長さの生地を打ってやればよい気がする。

第1回 製麺会結果

 大人8人、子供4人、小麦粉が6kg+α出て、6000円の収入(自分と風邪で参加できなかった妻子分除く)。収支とんとんであった。製麺初心者や小さなお子様が参加する場合に必要となる時間とエネルギーの見積もりが甘かった。会場で試食に供したのはあらかじめ仕込んでおいた1kg分だけだった。

なお会費・食材費別とし、会費は大人1人500円(乳幼児無料)・小麦粉は500円/1kgに設定した。 またもし余れば、小麦粉は500円/1kg・取り粉は100円/250gで参加者に分けることにした。

 

結構前の話を振り返って書いているせいか、まあこんなものではないかという気がする。

製麺会準備 その2

■ 概要

 製麺会の会場を押さえ、小麦粉と運搬用品の発注を完了し、麺打板と麺打棒の目当てをつけた。外堀は埋まった。ただ相変わらず費用は気になる。

■ 会場の確定

自宅近くの文化センターに手狭ながら調理室があったので、そこを借りることにした。費用は予約時前払いで1300円だった。ちょうど誰も使っていなかったので下見させていただき、下記を確認した。設備にはやや年季が入っているものの、まず問題なさそうである。

  • 車道から調理室まで段差なしでアクセス可能(エレベータあり)
  • ガス台×3、流し×3、冷蔵庫×1が壁際および講師机にある
  • 調理台は講師用を除いて6台。麺打ちに耐える強度と、麺打板を置ける広さがある
  • 一般的食器類・調理用具があり、特に寸胴が備品としてあるのがポイント高い
  • ホワイトボードあり(書けるボードマーカーがあるかは見忘れた…)
  • うどん踏み用スペースはないが、机を動かせるので、部屋の隅にござやビニールを敷けばOK

しかし予約のためには直接窓口まで行く必要がある、というのは正直面倒であった。あと駐車場がないので、自動車利用の参加者が多いと厳しいかも。

■ 小麦粉の調達

日清製粉のWebサイトによれば、小麦粉は500gで大体1リットルとのこと。打ち粉含めて14kg買うと28リットルになる。適当に買う塩一袋とあわせてコンテナ一個くらいで、まあ屋内保存できそう。

というわけで岡坂商店宛に赤丸金魚5kg×2、打ち粉澱粉 二番2kg×2と、ついでに鎌田醤油 だし醤油 500mlを発注した。発送方法として「常温商品限定おまかせ配送」にすると送料を数百円ほどけちれるようなので、それを選択した。合計5300円だった。 

■ 機材の調達

巻き込んだ先輩と相談したところ、麺打板・麺打棒は新調となった。近所のホームセンターで見繕ったところ、板は910*910*4.5mmで1200円 、丸棒は910*30mmΦで800円くらいだった。板については、ラワン材ベニヤを使うと生地にケバが混じってしまうことがあるので、シナ合板を選ぶことにした。一方、自宅内での保管の都合で、板の高さは890mmに抑える必要がある。2cmだけ自分で切り取るとか面倒すぎるので、これは買うときの有料オプションで切ってもらうことにする。

一方、台車と折りたたみコンテナ×2として、Amazonで安く売られていた中山トラスコのを買った。合計7200円だった。

www.amazon.co.jp

www.amazon.co.jp

台車のハンドルを立てた状態で折りたたみコンテナを載せると、コンテナの角が微妙にはみ出すが許容範囲。高さ方向に余裕があるのでコンテナの少なくとも片方は50Lタイプでも良かったかもしれないが、自宅内で他の用途に使っているコンテナと寸法を合わせておきたいという事情もあり、ひとまずこれで良しとした。

なお台車はたたんで専用カバーを被せてみたら、玄関先にほどよくまとまった。

■ どんぶり勘定

経費と設備をあわせて、ざっくり1万6千円を支払った。これから小物を買い足していくとして、当初見積もりの2万円は良い線かもしれない。

ただ設備分をどう費用負担するかは製麺会が軌道に乗ったら決めようと思う。初回参加者で全部払うのはフェアじゃない一方、趣味とはいえ赤字運営では長続きしないので…。

製麺会準備

■ 概要

十年来、手打ちうどんを趣味で作る会に参加してきたが、主催者の転勤に伴ってしばらく休止していた。しかし日々製麺欲が高まってきて抑えられなくなったので自分で主催することにし、会場・食材・機材の準備に着手。とりあえず数家族程度の小規模開催には差し支えなさそうな見通しが立った。見積費用は二万円。思ったより高かったが、実行に移したい。

ただ参加者に費用負担をお願いするからには、忘れ物防止のためチェックリストを作らないとダメそう。小物多すぎる。

■ 会場選び

自分と参加者の両方の都合から会場は都内に設定する。しかし都区内のレンタルキッチンは一回15000円とかが相場らしい(参考)。恐ろしい。食材価格に比べて桁で高く、話にならない。というかその値段だったらホテルの一室でやった方が安いのでは…。ほら製麺だけなら火を使わないし。

それはそれとして、行政管理の文化センターの類にある調理室を狙うことにする。区にもよるが、予約作業が繁雑(Webはもとより電話でも予約受付不可だったり…)な代わりに一回2000円程度で済むようだ。ひとまず全参加者に対する区内在住/在勤メンバの割合について厳しいルールのないところを絞り込んでおく。

【ざっくり見積:3000円】

■ 食材選び

うどんの基本は小麦粉と塩、水であって、他に必要なのは打ち粉だけである(参考:手打ちうどんの作り方)。

小麦粉はこれまでの主催者が用意してくださっていた中でも標準的な「金魚」を使うことにする。打ち粉と合わせて、発注先はこれまた前・主催者御用達の「岡坂商店」。香川まで買い出ししなくて済む世の中は大変便利である。あ、塩はその辺のスーパーで一番安いのを買う。

www.udon2ban.com

分量は参加予定者の数と自宅収納スペースとに鑑みて、小麦粉10kg+打ち粉4kgと仮定する。卵やネギなどのトッピングや、うどん醤油など調味料の類は後で考える。

【ざっくり見積:5000円】

■ 機材選び

まず製麺に直接必要な機材は以下の通り。

  • 製麺包丁(福井工作所の手切包丁が理想)
  • 麺打板(1m×1m×0.5cmくらいのベニヤ板)
  • 麺打棒(1m×2cmΦくらいの木の棒)
  • ビニール袋(破けないよう厚手の45リットル程度のサイズ)
  • 料理秤(2kgまで対応できること)

手切包丁を新品で買うと七万円くらいする。以前はもう少し安かった印象があるが…まあ需要と供給の大人の都合もあるのだろう、仕方ない。そこで大昔に当該包丁とともに活躍した先輩に声をかけて貸していただけないかご相談。ありがたいことに包丁・板・棒のセットを参加者として持って来ていただけそうとのお返事。これで一番の山を乗り越えられたといえよう。もっとも厚手のビニール袋は見つけにくいので地道に近所の店屋を回る必要はある。

 

次に製麺会に必要またはあると便利なのは以下の通り。

  • 運搬用台車・搬送用折りたたみコンテナ
  • ゴミ袋
  • 麺入れタッパー(屋台で多用されるような薄手のプラパックでも可)
  • キッチンタイマー
  • 打ち粉用粉ふるい・ちりとり・受け皿
  • 麺洗い用のザル(プラスチック製で受け皿とセットならベター)
  • 布巾
  • 滑り防止ゴムマット(ベニヤの麺打板が机の上で暴れないように)
  • 爪楊枝(生地に空気が入ったときに穴を開けて逃がす)
  • 輪ゴム
  • 鉄砲ザル
  • 寸胴と、それにあわせた長めの菜箸
  • 手洗い石けん・食器洗剤

とにかく安くあげることを重視するので、都区内の地の利を活かして、借りる料理室にありそうなものとか、その近くの店で買えそうなものは当日調達する作戦を採る。大体百均で揃えるとして、台車とコンテナさえあれば企画が当日全焼することもないだろう。都区内は車事情が厳しいことから、台車をはじめとした運搬用具には多少お金をかけても引き合うと睨んで品定めする(大物なので家族の承認待ち)。

しかし台車高いなあ…。使用頻度は低いので収納性さえ良ければ助かるのだが、そういう品はあまりないようだ。

【ざっくり見積:10000円】

■ どんぶり勘定

経費が8000円、設備が10000円で、切り上げて合計二万円。包丁のあてはあったものの、正直思ったより高くなった。

これで大人が10人参加するとして、参加費は二千円となる。経験によれば一桁高い。切りよく千円くらいに収めたい。というかこれまで参加してきた会は500円くらいだった。経費だけでも800円なのに、なんだあれ魔術か。

 

 

まだまだ修行が必要である。