用openpyxl在Excel表格中插入动态数组公式

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.formula import Tokenizer, Token

# 创建一个新的Excel文件
wb = Workbook()
ws = wb.active

# 写入一些数据
for row in range(1, 6):
    for col in range(1, 6):
        cell = ws.cell(row=row, column=col)
        cell.value = col

# 插入动态数组公式
formula = "=SORT(A1:E5)"
tokenizer = Tokenizer(formula)
tokens = list(tokenizer.items())
for token in tokens:
    if token[0] == Token.RANGE:
        start_cell = token[1][0]
        end_cell = token[1][1]
        start_row = start_cell.row
        start_col = start_cell.column
        end_row = end_cell.row
        end_col = end_cell.column
        formula_range = f"{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}"
        ws[start_cell.coordinate:end_cell.coordinate].formula = f"{token[0]}({formula_range})"
    else:
        ws[token[1].coordinate].formula = token[0]

# 保存Excel文件
wb.save("example.xlsx")