【Tips集】 Pandas,OpenPyXL を利用したエクセル読み書き

#Python

#pandas

shiraishi

2022.08.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

最近、Pythonにて、エクセルの読み書きを実装する機会がありました。
Pandas,OpenPyXLを初めて利用したのですが、その際に調べたことを書き残すと共に,ご紹介させていただきます。

環境

MacOS BigSur(M1) 11.6
Microsoft Excel for Mac 16.64 (Microsoft365サブスクリプション)
pandas 1.4.4
openpyxl 3.0.10

前提

以下のような、列に対して値を書き込んだ Microsoft Excel Workbook (.xlsx) を準備します。
セルの書式設定は変更しません。素直に書き込んだ時点で、セルの書式設定を確認すると、日付の表示形式は日付になり、メールアドレスはハイパーリンクになります。

調べたこと

[Pandas] dtypeを指定することで取得したデータをキャストできる

エクセルのデータを素直にpandasで取得します。dtypeは、日付 = datetime64[ns]、メールアドレス = object となります。

import pandas as pd

df = pd.read_excel('Book1.xlsx', sheet_name='Sheet1', usecols="A:E")

print(f"ID: {df['ID'].dtype}")
print(f"日付: {df['日付'].dtype}")
print(f"メールアドレス1: {df['メールアドレス1（必須）'].dtype}")
print(f"メールアドレス2: {df['メールアドレス2（任意）'].dtype}")
print(f"メールアドレス3: {df['メールアドレス3（任意）'].dtype}")

$ python3 xl_test.py
ID: int64
日付: datetime64[ns]
メールアドレス1: object
メールアドレス2: object
メールアドレス3: object

メールアドレスは、後続の処理でString型で扱いたいとします。
その場合は、 read_excel() の引数でdtypeを指定すればOKです。

import pandas as pd

df = pd.read_excel('Book1.xlsx', sheet_name='Sheet1', usecols="A:E", dtype={
    'メールアドレス1（必須）': 'string',
    'メールアドレス2（任意）': 'string',
    'メールアドレス3（任意）': 'string'
})

print(f"ID: {df['ID'].dtype}")
print(f"日付: {df['日付'].dtype}")
print(f"メールアドレス1: {df['メールアドレス1（必須）'].dtype}")
print(f"メールアドレス2: {df['メールアドレス2（任意）'].dtype}")
print(f"メールアドレス3: {df['メールアドレス3（任意）'].dtype}")

$ python3 xl_test.py
ID: int64
日付: datetime64[ns]
メールアドレス1: string
メールアドレス2: string
メールアドレス3: string

[OpenPyXL] None型を書き込みたい時は、そのまま書き込んでもヨシ、forループでcontinueしてもヨシ

メールアドレス2,3（任意）データを、プログラム上でNone型で作成したとします。
Noneが含まれたデータを、エクセルに書き込みたい場合は、単にfor文の中でcontinueすれば、該当のセルに対しての処理をSkipします。個人的には、これで良いと思います。
なお、補足となりますが、None自体を実際に書き込んだとしてもエラーにはなりませんし、エクセル上でも空白で表示されます。

import openpyxl
import datetime

data = [
    [11111, datetime.date(2022, 12, 31), 'shiraishi@example.com', None, None]
]

wb = openpyxl.load_workbook('Book1.xlsx')
ws = wb["Sheet1"]

for i, values in enumerate(data):
    for j, value in enumerate(values):
        if value is None:
            continue
        if type(value) is datetime.datetime:
            ws[f"B{j}"].number_format = openpyxl.styles.numbers.FORMAT_DATE_DDMMYY
        ws.cell(row=3+i, column=1+j, value=value)

wb.save('Book1.xlsx')

所感

まだまだ、Pandas/OpenPyXLについて、今後とも調査が必要なことが出てくると思います。悩む事に追記していく予定です。
以上、どなたかの参考になりましたら幸いです。

参考

dtypeについて
- https://pandas.pydata.org/pandas-docs/stable/user_guide/basics.html#dtypes
number format（書式設定について）
- https://openpyxl.readthedocs.io/en/stable/usage.html?highlight=number_format#using-number-formats