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


- 環境
    - 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ファイルを読み込んだらデータが読めなかった。

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

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

# 頑張ってみた
```py
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](https://zak-papa.com/python_openpyxl_cell_input_output#%E3%80%8C%E8%A4%87%E6%95%B0%E3%80%8D%E3%81%AE%E3%82%BB%E3%83%AB%E3%81%AE%E5%80%A4%E3%82%92%E3%80%8C%E8%A1%8C%E3%81%94%E3%81%A8%E5%8F%96%E5%BE%97%E3%80%8D%E3%81%99%E3%82%8B)

```py
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`セルを読み込む(関数の結果値がほしい)

```py
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](https://stackoverflow.com/questions/22451973/calculating-excel-sheets-without-opening-them-openpyxl-or-xlwt)
    - [7.8.3. ExcelModel &mdash; formulas 1.0.0 documentation](https://formulas.readthedocs.io/en/stable/_build/formulas/excel/formulas.excel.ExcelModel.html)

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

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

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

### xlwingsで読み込むと結果値が取れた
- 参考
    - [Python - openpyxlでexcelを開かず,計算結果を更新したい｜teratail](https://teratail.com/questions/252367)
    - [python - xlwingsはバックグラウンドでExcelを実行できますか？](https://stackoverrun.com/ja/q/12425544)
    - [Python API &mdash; xlwings dev ドキュメント](https://docs.xlwings.org/ja/latest/api.html)
    - [Python - (python,xlwings) xlwingsでexcelから「ユーザー定義」の値（時刻）を、そのままの形式で取得したいです｜teratail](https://teratail.com/questions/274756)

```py
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が非表示にしたはずなのにぴょこぴょこ表示される・・・処理が重そう・・・ファイル数が多い場合は時間がかかりそう・・・
![Untitled.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/159761/346e20c2-7bea-7659-92c8-70c8479c33c0.gif)