Python + openpyxlで、 0:00 という値を持つセルが正しく保存されない

openpyxl Python

JSL (日本システム技研) Advent Calendar 2018 - Qiita の5日目の記事です。

　
Excel (*.xlsx) ファイルをPythonで作成するため、openpyxlを使いました。
https://bitbucket.org/openpyxl/openpyxl/src

　
そんな中、 0:00 という値を持つセルが正しく保存されなかったため、原因調査と対応を行ったときのメモです。

　
目次

　

環境

　

現象

Excel上で A1 セルに 0:00 、A2セルに 0:01 を入力したシートを用意します。

f:id:thinkAmi:20181205212715p:plain:w450

　
表示形式は以下の通りです。

f:id:thinkAmi:20181205212735p:plain:w450

　
このようなファイルに対し、openpyxlで開いて別名で保存をします。

BEFORE = "zero_time.xlsx"
AFTER = "zero_time_after.xlsx"

wb = openpyxl.load_workbook(BEFORE)
wb.save(AFTER)

　
別名で保存したファイルを開くと、 0:00 を設定したセルが変更されてしまっています。

f:id:thinkAmi:20181205212913p:plain

　

原因

openpyxlのissueを見ると、似たような事例がありました。
openpyxl / openpyxl / issues / #1043 - Time '0:00' is not saved correctly after loading and saving a file — Bitbucket

いわゆる1900年うるう日問題が原因のようです。

　
どこかでこのあたりを見たなと思ったら、過去に書いていました。
C#とAccessにおける、日付型(DateTime)のデフォルト値の差について - メモ的な思考的な

　
では、実際に値を見てみます。

def print_before_and_after(before, after):
    print('--- before ---')
    print_a1_a2(before)

    print('--- after ---')
    print_a1_a2(after)


def print_a1_a2(path):
    wb = openpyxl.load_workbook(path)
    sheet = wb.active
    a1_value = sheet['A1'].value
    a2_value = sheet['A2'].value
    print(f'A1: {a1_value}({type(a1_value)}), A2: {a2_value}({type(a2_value)})')
    wb.close()


print_before_and_after(BEFORE, AFTER)
# =>
# --- before ---
# A1: 1899-12-30 00:00:00(<class 'datetime.datetime'>), A2: 00:01:00(<class 'datetime.time'>)
# --- after ---
# A1: 1899-12-29 00:00:00(<class 'datetime.datetime'>), A2: 00:01:00(<class 'datetime.time'>)

　
気になる点は、

  • 1899/12/30から1899/12/29へと値が変更されていた
  • 0:00は datetime.datetime 型、0:01は datetime.date 型と、違いがある

です。

Excel上でも -1 と数式欄に表示されていたのも、この影響のように見えます。

　

対応

【NG】iso_dates=True を設定

まずは、issueに記載されていたように、 workbook.iso_dates=True を設定してみます。

wb = openpyxl.load_workbook(BEFORE)
wb.iso_dates = True
wb.save(ISO)

print_before_and_after(BEFORE, ISO)
# =>
# --- before ---
# A1: 1899-12-30 00:00:00(<class 'datetime.datetime'>), A2: 00:01:00(<class 'datetime.time'>)
# --- after ---
# A1: 1899-12-30 00:00:00(<class 'datetime.datetime'>), A2: 00:01:00(<class 'datetime.time'>)

データ上は良さそうです。

ただ、実際のファイルを開いてみたところ、 #VALUE となっていました。

f:id:thinkAmi:20181205214224p:plain:w450

　

Excelのゼロ日付 (1899/12/30) の時だけ特別処理

0:00 の時は日付が -1 されてしまうため、ゼロ日付の時は日付を1加算するロジックを追加してみました。

FIXED = "zero_time_fix.xlsx"
EXCEL_ZERO_DATE = datetime(1899, 12, 30, 0, 0, 0)

wb = openpyxl.load_workbook(BEFORE)
wb = fix_default_date(wb, "A1")
wb = fix_default_date(wb, "A2")
wb.save(FIXED)


def fix_default_date(workbook, cell):
    sheet = workbook.active
    value = sheet[cell].value
    if value == EXCEL_DEFAULT_DATE:
        sheet[cell].value = EXCEL_ZERO_DATE + timedelta(days=1)
    return workbook

　
データを確認してみましたが、大丈夫そうです。

print_before_and_after(BEFORE, FIXED)
# =>
# --- before ---
# A1: 1899-12-30 00:00:00(<class 'datetime.datetime'>), A2: 00:01:00(<class 'datetime.time'>)
# --- after ---
# A1: 1899-12-30 00:00:00(<class 'datetime.datetime'>), A2: 00:01:00(<class 'datetime.time'>)

　
表示上も良さそうでした。

f:id:thinkAmi:20181205214746p:plain

　

ソースコード

GitHubに上げました。 openpyxl/zero_time ディレクトリが今回のものです。
https://github.com/thinkAmi-sandbox/python_excel_libraries-sample