LoginSignup
0

More than 1 year has passed since last update.

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

Last updated at Posted at 2020-10-09
  • 環境
    • 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ファイルに関数を入れていた
  2. ファイルは手動で保存されることなくやってきた
  3. pandasで読み込んだら関数の結果値を読み込めなかった
  4. ファイルを手動か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で関数を計算して保存しても読み込めない

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が非表示にしたはずなのにぴょこぴょこ表示される・・・処理が重そう・・・ファイル数が多い場合は時間がかかりそう・・・
Untitled.gif

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
0