名著『退屈なことは Pythonにやらせよう』で有名になった Excelのデータを操作する Pythonモジュールの OpenPyXlですが、やはり Excelに引っ張られてか、インターネット上で見られる使用例は大体クライアント PC上での話のようです。
しかし私の経験では、OpenPyXlはサーバーで使ってこそ華という気がします。
OpenPyXlをご存知ない方のために、まずは本家サイトから。
OpenPyXl
2022年7月5日時点での最新バージョンは、3.0.10です。
OpenPyXlはよく「Excel作業を自動化!!」という触れ込みをされていますが、それだと VBAマクロとの違いがよく分からないので「何でわざわざ Excel単品でできることを Pythonでやらにゃいかんの？」と思う人もいるでしょう。
私もそう思います。
ですけど、ユーザーがお休みの日や退勤した後の夜間に VBAマクロを実行できるともっと良いと思います。
しかしこれをクライアントPCでやるとなると、意外と難しい…。
そもそも退勤後もクライアント PCに電気入れっ放しのログオンしっ放しがセキュリティ上 NGなことが最近では多いですよね。
「じゃサーバーでやらせれば？」となりますが、Linuxのサーバーにはそもそも Excelがインストールできません。
「アホか。Windowsサーバーでやれ。」と言われても Excel自体がユーザーログオンありきの GUI操作ありきなソフトで、マイクロソフトもサーバーでのバッチ実行に Excelを供するのは推奨していません。
何かやり方がありそうではあるけど、有人前提の Excelを無人で回すという考え方に無理があるように思われます。
そんな中で、Excelがないところで Excelのファイルをいじくるツールの OpenPyXlは解決策の一つになると考えています。
Pythonの簡便な記述で Excelデータを何かといじることができ、大変優秀なツールなのでこれを使わない手はありません。
バージョンが上がる度にできることが増えて行ってますし、万能になりつつありますしね。
ただ実務的な事を言うと、どうかな〜と思うところはあります。
1つはシートのデザインに関してで、わざわざコードでやることなのか？という疑問があります。
視覚的なデザインをコードで表現するのは大変な手間なのに比べて、Excelよりも Excelのシートのデザインに長けたツールは他にありません。
もう1つは、例えば消費税額の計算など、Excel自体が優秀な計算機なのに、Excelの外で計算させる必要あるのかということです。
これらを鑑みた場合、OpenPyXlにやらせるのは変動するデータを Excelに貼り付けるだけにしておき、残りは全部 Excelにやらせるという方法が良いと考えています。
昔のワープロで言う「差し込み印刷」みたいなイメージです。
ということで前置きが非常に長くなりましたが、このコンセプトで OpenPyXlを使って Excelデータをイジってみようと思います。
実験では MySQLからデータを取り込んで Excelに貼ることにします。
実験環境は以下の通りです。
- Ubuntu Server 22.04
- MySQL8
- Python 3.10.4
- OpenPyXl 3.0.10
- Libre Office Community 7.3.4.2(のCalc) (私は Excelを持っていないためw)
まず環境を整えていきます。
Ubuntu Server 22.04にはデフォルト状態では Pythonがありませんでしたのでインストールします。
subro@UbuntuServer2204:~$ sudo apt install python3 python3-pip
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています... 完了
〜〜〜 中略 〜〜〜
No user sessions are running outdated binaries.
No VM guests are running outdated hypervisor (qemu) binaries on this host.
インストールできました。
OpenPyXlをインストールします。
subro@UbuntuServer2204:~$ pip install OpenPyXl
Defaulting to user installation because normal site-packages is not writeable
Collecting OpenPyXl
Downloading openpyxl-3.0.10-py2.py3-none-any.whl (242 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 242.1/242.1 KB 4.5 MB/s eta 0:00:00
Collecting et-xmlfile
Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, OpenPyXl
Successfully installed OpenPyXl-3.0.10 et-xmlfile-1.1.0
インストールできました。
それから MySQLを使うんでドライバをインストールしておきます。
subro@UbuntuServer2204:~$ pip install mysql-connector-python
Defaulting to user installation because normal site-packages is not writeable
Collecting mysql-connector-python
Downloading mysql_connector_python-8.0.29-cp310-cp310-manylinux1_x86_64.whl (25.2 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 25.2/25.2 MB 7.1 MB/s eta 0:00:00
Collecting protobuf>=3.0.0
Downloading protobuf-4.21.2-cp37-abi3-manylinux2014_x86_64.whl (407 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 407.8/407.8 KB 9.3 MB/s eta 0:00:00
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.29 protobuf-4.21.2
インストールできました。
先にデータを貼りつける Excelブックの元になるファイルを作っておきます。
OpenPyXlだけでこのようなカッコイイフォーマットを作るのは骨ですので、先にこうして Excel(本当は Libre Office Calc)で作っておくのでした。
C列とD列にデータベースから取ってきたデータを貼り付けたいわけです。
このExcelブックのファイルを Ubuntu Server 22.04の環境に持ってきました。
本当は Libre Office Calcで作ったのに、ちゃんと XLSXファイルになっていますw。
subro@UbuntuServer2204:~/work$ ls -l
合計 12
-rw-rw-r-- 1 subro subro 1849 7月 5 18:50 openpyxl_test.py
-rw-rw-r-- 1 subro subro 6044 7月 5 17:49 住所録テンプレート.xlsx
なお、データベースの方にはこんなデータが。
mysql> use subrotest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from testtable;
+--------------------+--------------------+
| name | address |
+--------------------+--------------------+
| subro | tokyo |
| じょーすたー | ニューヨーク |
| じょうたろう | 東京 |
| ぽるなれふ | パリ |
| かきょういん | 東京 |
| あぶどる | カイロ |
+--------------------+--------------------+
6 rows in set (0.00 sec)
そしてこれが Pythonのスクリプトです。
細かい説明はソースの中に書いています。
subro@UbuntuServer2204:~/work$ cat openpyxl_test.py
# MySQLを使うためのモジュールです
import mysql.connector
# OpenPyXlのモジュールです
from openpyxl import load_workbook
# MySQLへのセッションを保持する変数を初期化しています
cnx = None
try:
# MySQLへの接続をしています
cnx = mysql.connector.connect(
user='subro',
password='********',
host='192.168.1.107',
database='subrotest'
)
# MySQLのカーソルを宣言しています
cursor = cnx.cursor()
# SQLを実行しています
cursor.execute('select * from testtable')
# Excelシートにデータを貼る際のセルのズレです
offset_x = 3
offset_y = 5
# MySQLから取得したデータ数のカウンタを初期化しています
row_count = 0
# OpenPyXlでExcelファイルを開いています
wb = load_workbook('住所録テンプレート.xlsx')
# アクティブにするワークシートを選択しています
ws = wb['住所録']
# MySQLのデータ数だけ繰り返してExcelシートに貼り付けていきます
# A1というセル座標の指定の仕方もありますが、座標を計算して出す場合は、
# こちらの cell を使う方が良いと思います
for ( name, address ) in cursor:
ws.cell( row = offset_y + row_count , column = offset_x, value = name )
ws.cell( row = offset_y + row_count , column = offset_x + 1, value = address )
row_count += 1
# カーソルを閉じています
cursor.close()
except Exception as e:
print(f"Error Occurred: {e}")
finally:
# MySQLへのセッションを閉じています
if cnx is not None and cnx.is_connected():
cnx.close()
# Excelで言うところの、名前をかえて保存です
wb.save('住所録.xlsx')
それではスクリプトを実行してみましょう。
subro@UbuntuServer2204:~/work$ python3 openpyxl_test.py
subro@UbuntuServer2204:~/work$ ls -l
合計 20
-rw-rw-r-- 1 subro subro 1849 7月 5 18:50 openpyxl_test.py
-rw-rw-r-- 1 subro subro 6269 7月 5 18:56 住所録.xlsx
-rw-rw-r-- 1 subro subro 6044 7月 5 17:49 住所録テンプレート.xlsx
「住所録.xlsx」ファイルが新たにできあがりました。
相変わらず画面ショットは Libre Office Calcで撮っていますが。
本番で動かす Pythonスクリプトではもっとちゃんとエラーハンドルをしないといけませんが、DBからデータを取ってきて Excelのファイルに貼り付けるだけならこの程度の仕組みでできてしまいます。
今回はデータソースを DBにしましたけれども、スクレイピングによってインターネットからデータを拾ったり、パブリッククラウドのオブジェクトストレージからデータを拾ったりと、Pythonでは色々なデータにアクセスする手段が用意されています。
また Pythonの Webアプリケーションフレームワークである DJANGOの環境であれば、WEBシステムへの入力データを契機に何かしらのデータを生成することもありますよね。
そして OpenPyXlを使うことでこうして簡単に出力先を Excelブックにすることができます。
このようなスクリプトをサーバーで動かすことで、ユーザーから手離れした場所で Excelファイルを作ることができてしまいます。
できあがった Excelファイルは、SAMBAでユーザーに見せるなり、ユーザーが使っているファイルサーバーに転送するなりすると良いと思います。
以上、OpenPyXlをサーバーで実行する例をやってみました。
Excelをよく使うユーザーの所では、アイデア次第でかなり効果を上げられるものと思います。
それが当の Excelなしにできてしまうのが本当に素晴らしいです。
MySQLにアクセスする Pythonスクリプトは、以下のサイトのものをパク参考にさせていただきました。
Python で MySQL - データ取得 (select)
