この記事は公開されてから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