概要
Excelの数式欄にかなりの長さの数式が埋まっており、読み解くにも修正するにもかなりの苦労が必要になってしまったので、ひとまず抜き出して解析を試みた話。
データの抜き出しには Openpyxl を利用しました。
こんな状況でした
Excelの数式に入れていい文字列は75文字までとか誰か決めてくれ・・・ｗ
（計算結果の途中をどっかのセルに吐き出すとか・・・ね。）
特に Excel の IF は読みにくい
=IF(A1="これこれ", "xxxx", IF(B1="あれこれ", "yyyy", "zzzz"))
こんな書き方、良くしますよね・・・。
if (A1 === "これこれ") {
cell = "xxxx";
} elseif (B1 === "あれこれ") {
cell = "yyyy";
} else {
cell = "zzzz";
}
という感じなんですけど、独自関数定義するとマクロが・・・って話になりますし、リファクタリングも難しいです。なので、インデントしたりしてどうにか可読性をあげるしかないですね。
ちなみに Excel数式におけるインデントに、N（）関数を加えてコメントっぽく書くことができたり、配列数式を使ってコメントっぽく書く方法もあるようです・・・
今回は値の取り出し〜インデントを加えるところをやってみます
もちろんマクロを使って解決する方法もありますね。
→ ExcelでネストしたIf関数をVBAでインデントして分析しやすくする
Python でやってみる
今回、Openpyxlを使ってやってみます。
ずばり、数式の解析は Parsing Formula を使ってできるようです。
とりあえず書いてあるのを試してみましょう。
>>> from openpyxl.formula import Tokenizer
>>> tok = Tokenizer("""=IF(A1="これこれ", "xxxx", IF(B1="あれこれ", "yyyy", "zzzz"))""")
>>> print("\n".join("%12s%11s%9s" % (t.value, t.type, t.subtype) for t in tok.items))
IF( FUNC OPEN
A1 OPERAND RANGE
=OPERATOR-INFIX
"これこれ" OPERAND TEXT
, SEP ARG
WHITE-SPACE
"xxxx" OPERAND TEXT
, SEP ARG
WHITE-SPACE
IF( FUNC OPEN
B1 OPERAND RANGE
=OPERATOR-INFIX
"あれこれ" OPERAND TEXT
, SEP ARG
WHITE-SPACE
"yyyy" OPERAND TEXT
, SEP ARG
WHITE-SPACE
"zzzz" OPERAND TEXT
) FUNC CLOSE
) FUNC CLOSE
なるほど。先頭からそれぞれのパーツが関数（開始・終了）なのか、変数なのか等々に分解してくれるようだ。
ということは関数が開始したら改行して、次の行にはインデントをつけ、関数が終了するときにインデントを減らしてから改行していけばよいということですね。
わかりやすさのためにインデントを可視化して書いてみる
from openpyxl.formula import Tokenizer
# あとでindent_charはもちろんスペースに直すよ
def parse(formula, indent_char = "_"):
# 最終的に文字列として結合して返す
result = ""
tok = Tokenizer(formula)
# インデント数
n = 0
# 直前で処理したトークン
last = None
for t in tok.items:
if t.type == "FUNC":
if t.subtype == "OPEN":
if last is not None and (last.type == "FUNC" and last.subtype == "OPEN"):
n += 2
result += indent_char * n + t.value
else:
result += t.value
result += "\n"
else:
# function close。インデント数を戻す
n -= 2
result += "\n" + indent_char * n + t.value
else:
# それ以外は単純結合。直前が関数の場合はインデント
if last is not None and (last.type == "FUNC" and last.subtype == "OPEN"):
n += 2
result += indent_char * n + t.value
else:
result += t.value
last = t
return result
s = """=IF(A1="これこれ", "xxxx", IF(B1="あれこれ", "yyyy", "zzzz"))"""
print(parse(s))
IF(
__A1="これこれ", "xxxx", IF(
____B1="あれこれ", "yyyy", "zzzz"
__)
)
うん。これで良さそう。
あとはExcelの指定したセル範囲とかを渡して
wb = load_workbook(filename, data_only = False)
sheet_name = "Sheet1"
cell_range = "A2:C2"
for line in wb[sheet_name][cell_range]:
for cell in line:
# formula のみを抽出
if cell.data_type == "f":
f = parse(cell.value)
# cell.coordinateには「A1」などの表現が入る
with open(cell.coordinate + ".txt", mode='w') as fl:
fl.write(f)
こんな感じにすれば、数式が埋まっているセルだけ、A2.txt, C2.txtのようにインデント済のテキストとして吐き出せます。