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

2022.08.31

最近、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