はじめに

この記事は Ver 3.0.2 以前のものを対象としています。OpenPyXL は日々改良されていますので、今後、ここで書かれた問題が解決されていることもあります。

OpenPyXL(2.6, 3.0) の問題について

OpenPyXL は、まっさらの新規ブックの状態から Python コードだけで Excel シートを力技で作成することもできますが、一から作成するとコードの記述が増えて見づらくなりますし、コードとデザインを分離しているほうがメンテナンス上も有利ですので、ある程度デザインされた雛形 Excel ブックを OpenPyXL で読み取んで、必要なデータを設定していくのが定石かと思います。

しかし、OpenPyXL は Excel とは全く異なる仕組みで動作するものですので、Excel VBA では簡単にできるのに OpenPyXL ではできない・難しいこともあります。

以下の条件を満たすもの以外については、雛形 Excel ブックを使った処理は現在の OpenPyXL だけでは実装するのは難しいことが多いです。

画像やシェイプを持たないもの
ワークシートのコピーが必要ないもの
結合セルを多用していないもの
ワークシートの途中に行や列を追加・削除しないもの

OpenPyXL での処理が難しい理由

画像やシェイプを持つもの

OpenPyXL では画像やシェイプ情報はブックオープン時に無視されてしまいます。 OpenPyXL で画像やシェイプを扱いたい場合は、ブックオープン後に OpenPyXL の処理で設定する必要があります。

ワークシートをコピーする必要があるもの

ワークシートをコピーする関数 workbook.copy_worksheet() は、Excel のそれ (Worksheets.Copy) とは挙動が異なり、条件付き書式などコピーされない情報があります。
OpenPyXL のソースコードを書き換えることである程度対応できますが、それでも Excel ほど完璧にコピーできません。

結合セルを多用しているもの

OpenPyXL では結合セル自体は扱うことができますが、罫線の描画や書式の設定などで制約があるものが多くあります。(トラブルの原因になりやすいです)
OpenPyXL で操作される部分についてはデザインを工夫するなどして極力結合セルを使用しないようにするか、OpenPyXL の動作に問題のないことを確認してから使用する必要があります。

ワークシートの途中に行や列を追加したりしないもの

行・列を挿入する worksheet.insert_rows() や worksheet.insert_col() は Excel のそれ (row().Insert, col().Insert) とはかなり挙動が異なります。
上記関数を実行しても、セルに付与された属性情報（定義された名前、書式、条件付き書式）・結合セル・セル上に配置されたイメージ情報・関数式などが追随しないため、現状のバージョンのものは 実質的にほとんど使い物になりません。
ワークシートの途中でなく末尾に行や列を追加(上書き)のであれば、問題は比較的少ないはずです。

問題の解決案

OpenPyXL ではオープン時の引数に keep_vba=True を付けることで、VBA マクロを含んだ雛形 Excel ブック (xlsm) を扱うことが可能です。
そこで、OpenPyXL では難しい処理を Excel VBA マクロで行わせることで、OpenPyXL では難しい・不可能な処理を実現することができます。
(社内的な事情などによって Excel VBA マクロの実行が許可されていない環境では残念ながらできません)

OpenPyXL で行う処理と Excel VBA マクロで行う処理を整理し、Python, Excel VBA それぞれにコード処理や設定を行っていきます。
例えばワークシートのコピーやワークシート途中の行列の追加・削除については、Excel VBA マクロで行います。

Excel VBA マクロで必要となるデータは、雛形ワークシートとは別にデータ用ワークシートを作成して、そこに OpenPyXL でデータを設定します。

OpenPyXL 側でしか作成できない画像を取り扱う必要がある場合なども同様にデータ用ワークシートを作成してそこに OpenPyXL で画像を配置します。
OpenPyXL の add_image() で作成される画像には識別名を設定することができず、複数の画像を渡す必要がある場合 Excel VBA マクロ側で画像の区別ができないので、画像の区別ができるような工夫も必要になります。

サンプル例

長々と説明するよりもサンプルを見て頂いたほうが早いかと思います。
コードは Python, Excel VBA いずれも主要処理は 100 行程度です。
サンプルの実行には、Python に OpenPyXL パッケージのほか、画像を編集するための Pillow パッケージが必要となります。
（エラーが発生した場合は、必要に応じてパッケージをインストールしてください）

サンプルの公開場所

Github(https://github.com/umazular/openpyxl) で公開しています。

CentOS 7 上の標準の Python 2.7.5 + OpenPyXL 2.6.4
Raspbian(Debian 10.2) 上の Python 3.7.3 + OpenPyXL 3.0.2
Windows10 にインストールした Python 3.7.2 + OpenPyXL 3.0.0

にて確認しています。(極力環境に依存しないようにしています)

サンプルは、領収証の出力をイメージして作成してみました。１つ以上の CSV 領収証データ＋画像を読み込んで、Excel に結果を出力します。
サンプルでは 2 シート分の CSV ファイル・画像をあらかじめ作成しています。
サンプルのデフォルトの CSV ファイルは Shift-JIS になっていますので、環境に応じて UTF-8 に置き換えてください。

雛形のレポートシート("領収証")には、OpenPyXL で難しそうな設定をしてみました。

明細行に行ごとに背景色を変更する条件付き書式
金額に数量と単価の積を設定する関数式
金額の総合計を設定する関数式
シート印刷時の書式(余白やすべての列を印刷する、などの設定)

Excel VBA は、Excel のメニューより「開発」→「Visual Basic」で開けます。
「開発」タブが表示されていない場合は、ファイル→オプションで Excel のオプション画面を開き、リボンのユーザー設定のメインタブの「開発」にチェックを入れると表示されるようになります。

サンプルの実行方法

python プログラムを実行すると、データが設定された Excel ブック (sampleoutput.xlsm) が生成されます。

$ ls *.xlsm
sample.xlsm
$ python sample.py
$ ls *.xlsm
sample.xlsm  sampleoutput.xlsm
$

出力された Excel ブックをマクロの実行が許可された環境で開くと、初回のみデータ設定のマクロ処理が実行されて結果が作成されます。

参考資料など

公式ドキュメント(英語)
https://openpyxl.readthedocs.io/
pythonのopenpyxlの使い方メモ
https://qiita.com/sky_jokerxx/items/dc9d8827d946b467ba4b

OpenPyXL の問題点と解決策について(Ver 3.0 版)