
openpyxlで数式の値が取れない原因と対処法|Excelに再計算させて読む
こんにちは、すきまログです。
Python の openpyxl で Excel の集計を自動化していて一番ヒヤッとしたのが、「見た目は正常に動いているのに、数字だけ静かにおかしくなる」パターンでした。今日はその中でも印象に残っている地味なハマりどころを紹介します。
原因は「openpyxlは数式を計算しない」こと
XLOOKUP や COUNTIFS の数式で埋まったシートを、openpyxl で読み込んで別シートに転記する処理を組んでいました。動作確認では問題なく数字が入っていたのに、後日同じファイルを開いたら一部の値がゼロや空欄になっていました。
原因はシンプルで、openpyxl は数式を評価しないことです。
import openpyxl
# data_only=True … 「Excelが最後に計算してキャッシュした値」を読む
wb = openpyxl.load_workbook(path, data_only=True)data_only=True で読めるのは計算結果そのものではなく、Excel が最後に開いたときにキャッシュした値です。だから、
openpyxl で数式を書き込んで保存したファイルには、キャッシュ値がまだ入っていない
そのファイルをもう一度 openpyxl で data_only=True で読むと、値は None になる
数式自体は残っているのに、値だけ抜けたように見える、という状態でした。
対処: Excel 本体に計算させてから読む
数式の中身(日付演算など)を Python 側で再現するのは事故りやすいので、実際に Excel を起動して計算させ、その結果を読む方式にしました。Windows なら win32com (pywin32)で操作できます。
import win32com.client as win32
excel = win32.gencache.EnsureDispatch("Excel.Application")
excel.Visible = False
wb = excel.Workbooks.Open(str(path))
excel.CalculateFullRebuild() # 完全再計算
wb.Save()
values = wb.Sheets("集計データ").UsedRange.Value # ここで初めて値が取れる
wb.Close(SaveChanges=False)
excel.Quit()遠回りに見えますが、「数式の仕様を自分で再実装しない」ぶん、後から壊れにくくなりました。
もう一つ、地味に厄介だった重複判定
日次のデータを追記していく処理で、重複行を弾くために日時をキーにして判定していました。ところが、ある日から急に同じデータが二重に入るようになりました。
調べてみると、マイクロ秒単位でわずかに異なるタイムスタンプが原因でした。人間が見れば「同じ時刻」に見えるデータでも、プログラムの目には別物として映っていたわけです。
対処したこと
openpyxl だけで完結させようとしない。 数式の値が要るなら、一度 Excel に計算させる工程を挟む
重複判定のキーは、必要な精度まで丸めてから比較する。秒単位で十分な場面まで細かい単位で比較していたのが原因だったので、キーの粒度を見直しました
転記した直後に、件数と合計値をログに出しておく。数字が消えたことに後から気づくのではなく、その場で異常に気づけるようにしました
この経験から思うこと
自動化のバグは、エラーで止まってくれるものより、こういう「静かに間違った結果を出し続ける」方が厄介だと感じています。処理が落ちないからこそ気づくのが遅れる。だからこそ、動いている最中の値を都度確認できる仕組みを、最初から組み込んでおく方がいいと今は思っています。