openpyxlによる条件付き書式設定(365限定かも)

正規表現を使用して条件付き書式を設定したかった

セルに何某かの値が入っていて、パターンに一致したの場合にそのセルに色をつけるのに手間取った記録。

失敗パターン

駄目なコード概略

import openpyxl
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles.fills import PatternFill

color: PatternFill = PatternFill(start_color="ffff00", end_color="ffff00", fill_type="solid")
rule: FormulaRule = FormulaRule(formula=['REGEXTEST(A1,"reg")'], fill=color)
ws.conditional_formatting.add("A1:A5", rule)

なぜか着色をしてくれなかった。
場合によってはエラーだからということで、開くときに条件付き書式を消されてしまうこともあった。

代替候補1

rule: FormulaRule = FormulaRule(formula=['ISERROR(SEARCH("reg",A1))'], fill=color)

こっちの場合は着色してくれた。しかしsearchは正規表現ではないので要求が満たしきれない。

代替候補2

セルの値を読んでpythonのコードで正規表現と比較するのは簡単だけど、Excelにできることはやらせたいという手抜き精神でこれも没。

状況把握

どうしても条件付き書式の正規表現で作りたかったので、駄目なコードで作成したファイルをエクセルで開いて条件付き書式を確認してみた。
すると下図のように想定通りの数式が設定されているが着色が行われていない。
しかもこの数式を手で入力すると想定通りに動くという謎な状況だった。

着色される場合には何が設定されているの？

手動で作成し着色されたファイルと上述の駄目なファイルを開いて、どのような設定が行われているか確認するプログラムを作って覗いてみた。

wb = openpyxl.load_workbook("hoge.xlsx")
ws = book.active
for cond in ws.conditional_formatting:
    print(cond.rules)

駄目なファイル

[<openpyxl.formatting.rule.Rule object>
Parameters:
type='expression',
rank=None,
priority=1,
equalAverage=None,
operator=None,
aboveAverage=None,
dxfId=0,
stdDev=None,
stopIfTrue=None,
timePeriod=None,
text=None,
percent=None,
bottom=None,
colorScale=None,
dataBar=None,
iconSet=None,
formula=['REGEXTEST(A1,"reg")']]

着色されるファイル

[<openpyxl.formatting.rule.Rule object>
Parameters:
type='expression',
rank=None,
priority=1,
equalAverage=None,
operator=None,
aboveAverage=None,
dxfId=0,
stdDev=None,
stopIfTrue=None,
timePeriod=None,
text=None,
percent=None,
bottom=None,
colorScale=None,
dataBar=None,
iconSet=None,
formula=['_xlfn.REGEXTEST(A1,"reg")']]

最後に出てきたformulaの_xlfn.ってなんだ？

試してみる

よくわからないけどそのまま使ってみよう。
駄目なコードを下記に修正

rule: FormulaRule = FormulaRule(formula=['_xlfn.REGEXTEST(A1,"reg")'], fill=color)

色がついた!

で、何者？

_xlfn.はこのときに初めて見ました。どうやらExcelの互換性確保を目的に使われているもののようです。
どの関数で必要なのかは、現時点では調べる気は無いので悪しからず。
詳しく知りたい方はググってください。山盛り出てきます。