[Python知识库]【openpyxl】使用公式、复制（翻译）公式

1.可用公式

当然我演示的openpyxl版本是3.0.9，一共支持352个公式，公式保存在一个frozenset类型的集合了，我们可以通过python的in语法判断是否支持某个公式，记住每个公式都是大写的

from openpyxl.utils import FORMULAE

print(FORMULAE)  # frozenset({'ODD', 'VDB', 'RANK', 'LOGEST', 'ISNONTEXT', 'COUNTA'...
print(len(FORMULAE))  # 352

# 判断是否支持某个公式，公式名区分大小写
print("SUM" in FORMULAE)  # True
print("PI" in FORMULAE)  # True
print("sum" in FORMULAE)  # False

2.使用公式

使用公式很简单，你只要记得公式名和用法，直接像在Excel那样输入即可，例如，下面的求和、求平均值

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.append(["价格1", "价格2", "总和", "平均值"])
ws.append([22, 63])
ws.append([11, 88])
ws.append([15, 68])

ws["c2"] = "=SUM(A2,B2)"  # 求和
ws["d2"] = "=AVERAGE(A2:B2)"  # 求平均值
wb.save("test.xlsx")

结果如下图

3.翻译公式

用过Excel的同学都知道，当某个单元格使用了公式，可以通过拖动填充柄的方式快速复制上一个公式进行填充，在openpyxl做法如下

from openpyxl import Workbook
from openpyxl.formula.translate import Translator

wb = Workbook()
ws = wb.active

ws.append(["价格1", "价格2", "总和", "平均值"])
ws.append([22, 63])
ws.append([11, 88])
ws.append([15, 68])

ws["c2"] = "=SUM(A2,B2)"
ws["d2"] = "=AVERAGE(A2:B2)"
# C3、C4使用上面的C2的求和公式
ws["C3"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C3")
ws["C4"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C4")

wb.save("test.xlsx")

结果如下图

当然，既然是重复操作，我们要使用优雅的循环写法

from openpyxl import Workbook
from openpyxl.formula.translate import Translator
....
ws["c2"] = "=SUM(A2,B2)"
ws["d2"] = "=AVERAGE(A2:B2)"
# C3、C4使用上面的C2的求和公式
for cell in ws["C3:C4"]:
    # ws["C3"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C3")
    cell[0].value = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula(cell[0].coordinate)
wb.save("test.xlsx")