- 環境
- 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で関数を計算して保存しても読み込めない
- 参考
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が非表示にしたはずなのにぴょこぴょこ表示される・・・処理が重そう・・・ファイル数が多い場合は時間がかかりそう・・・
