xlsxwriter中write_dynamic_array_formula需手动回车生效问题咨询

我有一个包含坐标及关联标签A、B、C的表格，希望新增一列将标签转换为1、2、3。使用xlsxwriter编写了如下代码：

import xlsxwriter

# Create some example data
x = [1, 2, 3, 4, 5]
y = [2, 4, 6, 8, 10]
labels = ["A", "B", "C", "B", "A"]

# Create a new Excel file and add a worksheet
workbook = xlsxwriter.Workbook('scatter_plot.xlsx')
worksheet = workbook.add_worksheet('Data')

# write column headings
worksheet.write(0,0,'x')
worksheet.write(0,1,'y')
worksheet.write(0,2,'labels')


# Write the data to the worksheet
for i in range(len(x)):
    worksheet.write(i+1, 0, x[i])
    worksheet.write(i+1, 1, y[i])
    worksheet.write(i+1, 2, labels[i])

# Formula that writes a new column where A = 1 B = 2 C = 3    
worksheet.write_dynamic_array_formula('D2:D6', '=IFS(LEFT(C2:C6,1)="A",1,LEFT(C2:C6,1)="B",2,LEFT(C2:C6,1)="C",3,TRUE,NA())')    

# Add a scatter chart to the worksheet
chart = workbook.add_chart({'type': 'scatter'})
chart.add_series({
    'name':       'X vs Y',
    'categories': '=Data!$A$2:$A$6',
    'values':     '=Data!$B$2:$B$6',
    
})

# Insert the chart into the worksheet
worksheet.insert_chart("F1", chart)

# Save the Excel file
workbook.close()

运行后生成Excel文件，公式在Excel中无语法错误，但必须手动在单元格按回车才能生效，请问这是否应该自动完成？

原因 #

XlsxWriter写入动态数组公式后，Excel有时不会自动触发SPILL计算，首次打开文件时需要手动确认公式的数组范围才会执行计算。

推荐方案：直接在Python中计算转换值写入（最稳定） #

无需依赖Excel公式，提前在代码内完成标签到数值的转换，直接写入单元格，打开Excel即可看到结果，彻底避免公式触发问题。

修改后的代码如下：

import xlsxwriter

# Create some example data
x = [1, 2, 3, 4, 5]
y = [2, 4, 6, 8, 10]
labels = ["A", "B", "C", "B", "A"]
# 定义标签与数值的映射关系
label_map = {"A": 1, "B": 2, "C": 3}

# Create a new Excel file and add a worksheet
workbook = xlsxwriter.Workbook('scatter_plot.xlsx')
worksheet = workbook.add_worksheet('Data')

# write column headings
worksheet.write(0,0,'x')
worksheet.write(0,1,'y')
worksheet.write(0,2,'labels')
worksheet.write(0,3,'label_num')  # 新增转换列的标题

# Write the data to the worksheet
for i in range(len(x)):
    worksheet.write(i+1, 0, x[i])
    worksheet.write(i+1, 1, y[i])
    worksheet.write(i+1, 2, labels[i])
    # 直接写入转换后的数值
    worksheet.write(i+1, 3, label_map[labels[i]])

# Add a scatter chart to the worksheet
chart = workbook.add_chart({'type': 'scatter'})
chart.add_series({
    'name':       'X vs Y',
    'categories': '=Data!$A$2:$A$6',
    'values':     '=Data!$B$2:$B$6',
    
})

# Insert the chart into the worksheet
worksheet.insert_chart("F1", chart)

# Save the Excel file
workbook.close()

备选方案：确保Excel自动计算动态数组公式 #

如果坚持使用公式，可以尝试在创建工作簿时显式设置计算模式为自动（默认即为自动，但显式设置可避免异常情况）：

workbook = xlsxwriter.Workbook('scatter_plot.xlsx', {'calc_mode': 'auto'})

不过这种方式仍可能受Excel自身设置影响，可靠性不如直接计算值写入。

内容的提问来源于stack exchange，提问作者bigbomb