【Python】openpyxlでセルの書式が混在するエクセルファイルから日付を取得する方法

2020年3月7日2021年6月12日

Pythonではopenpyxlライブラリを使うことでエクセルのセルの値を取得できるのですが、日付は注意が必要です。

エクセルでセルの書式設定の表示形式が「日付」になっていればPythonにセルの値を読み込むと日付型で読み込まれるのですが、表示形式が「標準」「ユーザー定義」になっている場合はPythonにはシリアル値で読み込まれてしまいます。

上の画像のB列はA列の表示形式で入力されています。このB列のセルの値をPythonに読み込んだ結果が下の画像になります。

読み込みに使ったコードは以下

import openpyxl
from openpyxl import load_workbook, utils

excel_path = './日付.xlsx'
# エクセルファイルを読み込み専用で読み込み
wb = load_workbook(filename = excel_path, read_only = True)
# エクセルファイルのシートを変数sheet_orderに代入
ws = wb['Sheet1']

print('B2')
print(ws['B2'].number_format)
print(ws['B2'].value)
print('\n')

print('B3')
print(ws['B3'].number_format)
print(ws['B3'].value)
print('\n')

print('B4')
print(ws['B4'].number_format)
print(ws['B4'].value)
print('\n')

print('B5')
print(ws['B5'].number_format)
print(ws['B5'].value)
print('\n')

print('B6')
print(ws['B6'].number_format)
print(ws['B6'].value)

1行目はセル番地を直接入力して表示。

2行目はopenpyxlで読み込んだセル番地に「.number_format」をつけることでセルの表示形式を取得しています。

3行目はopenpyxlで読み込んだセル番地に「.value」をつけることでセルの値を取得しています。

4行目はコマンドプロンプト上の表示を見やすくするための改行。

B5セルとB6セルはセルの表示形式がユーザー定義になっているため、取得したセルの表示形式が「General」、セルの値が「43891」というシリアル値になってしまっています。

このシリアル値はutilsモジュールの「utils.datetime.from_excel()」を使うと日付型に変換することができます。

以下のようなコードを実行すると、上の例のB6セルのように元々シリアル値だった場合には変換できていることが分かります。

B6_valueは読み込んだセルの値をそのまま。（コマンドプロンプト画面の3行目）
B6_value_2はB6_valueの値をutils.datetime.from_excel()で変換したもの。（コマンドプロンプト画面の4行目）

print('B6')
print(ws['B6'].number_format)
B6_value = ws['B6'].value
print(B6_value)
B6_value_2 = utils.datetime.from_excel(B6_value)
print(B6_value_2)

print('B3')
print(ws['B3'].number_format)
B3_value = ws['B3'].value
print(B3_value)
B3_value_2 = utils.datetime.from_excel(B3_value)
print(B3_value_2)

しかしこの変換はシリアル値→日付型に変換することしかできないため、B3セルのように元々日付型だった場合に適用しようとするとエラーになってしまいます。

※コマンドプロンプト画面の7行目=スクリプトのprint(B3_value)までは問題なく表示されていますが、その次の行はTraceback ～というエラーが表示されています。

そのため「日付」「標準」「ユーザー定義」が混在する場合に、読み込んだセルの値全てにutils.datetime.from_excel()を適用させて日付型に変換するということはできません。

元がどちらでも適用できるようにするには、以下のような日付型の値はそのまま、General型の値は日付型に変換するif文を使うことで解決できます。

B6_value = ws['B6'].value
if ws['B6'].number_format == 'General':
    B6_value = utils.datetime.from_excel(B6_value)

まず1行目でB6セルの値をB6_valueに代入。

2～3行目でセルの表示形式がGeneralだった場合にutils.datetime.from_excel()を適用。

その後B6_valueの値を取得すると、元々の表示形式が「日付」の場合はif以下が適用されないそのままの値、元々の表示形式が「標準」「ユーザー定義」の場合はif以下が適用されて変換された値を取得することができます。

先ほどのB6セル、B3セルにこのif文を使った場合の結果が以下になります。

print('B6')
print(ws['B6'].number_format)
B6_value = ws['B6'].value
print(B6_value)
if ws['B6'].number_format == 'General':
    B6_value = utils.datetime.from_excel(B6_value)
print(B6_value)

print('\n')

print('B3')
print(ws['B3'].number_format)
B3_value = ws['B3'].value
print(B3_value)
if ws['B3'].number_format == 'General':
    B3_value = utils.datetime.from_excel(B3_value)
print(B3_value)