環境
- macOS Catalina バージョン10.15.7
- Microsoft Excel for Macバージョン16.41
- PyCharm Community 2020.2
- Python 3.8.5
- pandas 1.1.3
- formulas 1.0.0
- openpyxl 3.0.5
- xlrd 1.2.0
- xlwings 0.20.7

PythonのpanadasでとあるExcelファイルを読み込んだらデータが読めなかった。

他のプログラムでExcelファイルに関数を入れていた
ファイルは手動で保存されることなくやってきた
pandasで読み込んだら関数の結果値を読み込めなかった
ファイルを手動かVBAで開いて保存した後はpanadasで関数の結果値を読み込めた

調べたら何かプログラム的なものでExcelに関数をいれて手動で保存していないと関数の結果はなかなか読み込むのが大変らしい、が不可能ではないようなので頑張ってみた。

頑張ってみた

if __name__ == '__main__':
    excel_file = 'Book.xlsx'
    sheet_name = 'Sheet1'
    # 1. Excelファイルに関数を入れる
    write_openpyxl()
    # 2. Pythonで入れた関数の結果を読み込む
    print_cell()

1. Excelファイルに関数を入れる

やること : B4セルに関数=TIMEVALUE("11:45")を入れる

本当の時は、Pythonではない何かが使われたが今回はPythonで再現してみた。
panadasでExcelファイルを更新したところ書式が崩れたのでopenpyxlでやってみた。
参考 : 【Python】「OpenPyXL」で「Excel」のセルの値を取得・入力するなどセルを操作してみよう | zak-papa

def write_pandas():
    # pandasで書き込むと書式が崩れた
    sheet = pandas.read_excel(excel_file, sheet_name=sheet_name, header=None)
    sheet.iloc[3, 1] = '=TIMEVALUE("11:00")'
    sheet.to_excel(excel_file, sheet_name=sheet_name, index=False, header=False)


def write_openpyxl():
    """`B4`セルに関数`=TIMEVALUE("11:45")`を入れる"""
    book = openpyxl.load_workbook(excel_file)
    book[sheet_name]['B4'].value = '=TIMEVALUE("11:45")'
    book.save(excel_file)

2. Pythonで入れた関数の結果を読み込む

やること : B4セルを読み込む(関数の結果値がほしい)

def print_cell():
    update_formulas()
    read_pandas()
    read_openpyxl()
    read_xlrd()
    read_xlwings()

formulasで関数を計算して保存しても読み込めない

参考
- python - Calculating Excel sheets without opening them (openpyxl or xlwt) - Stack Overflow
- 7.8.3. ExcelModel — formulas 1.0.0 documentation

def update_formulas():
    """formulasで関数を計算して保存・・・した後に読み込んでも結果値が取得できない"""
    model = formulas.ExcelModel().loads(excel_file).finish()
    model.calculate()
    model.write()

pandasで読み込むと`nan`になった

def read_pandas():
    """pandasで読み込むと`nan`になった"""
    sheet = pandas.read_excel(excel_file, sheet_name=sheet_name, header=None)
    print(sheet.iloc[3, 1]) # >> nan

openpyxlで読み込むと`None`になった

def read_openpyxl():
    """openpyxlで読み込むと`None`になった"""
    # openpyxlで関数の結果を取得するには「data_only=True」を指定する.
    book = openpyxl.load_workbook(excel_file, data_only=True)
    sheet = book[sheet_name]
    print(sheet['B4'].value) # >> None
    # 手動保存でできるならopenpyxlで開いて保存したら結果値が読み込めるかと思ったけど、
    # 保存するとNoneが保存されて関数が消えてしまうだけだった・・・
    # book.save(excel_file)
    # print(sheet['B4'].value) # >> None

xlrdで読み込むと`empty:''`になった

def read_xlrd():
    """xlrdで読み込むと`empty:''`になった"""
    sheet = xlrd.open_workbook(excel_file).sheet_by_name(sheet_name)
    print(sheet.cell(3, 1)) # >> empty:''

xlwingsで読み込むと結果値が取れた

参考

def read_xlwings():
    """xlwingsで読み込むと結果値が取れた"""
    # 非表示でExcelアプリを開く
    app = xlwings.App(visible=False)
    book = app.books.open(excel_file)
    sheet = book.sheets[sheet_name]
    # 関数の結果値がシリアル値(24h = 1)で取れた
    print(sheet.range('B4').value) # >> 0.4895833333333333
    # シリアル値に24hを乗算して時間にする
    print(sheet.range('B4').value * 24) # >> 11.75
    # Excelファイルを閉じる
    book.close()
    # Excelアプリを終了する
    app.kill()

xlwingsはデータが読めたけどExcelが非表示にしたはずなのにぴょこぴょこ表示される・・・処理が重そう・・・ファイル数が多い場合は時間がかかりそう・・・

プログラムでExcelに埋め込んだ関数の結果を読み込めないかったからいろいろ試してみた