使用openxlsx写入多列XLOOKUP公式时出现意外@或{}符号问题

核心解决方案 #

创建工作簿时禁用动态数组支持，避免工具自动添加@符号或数组大括号，让公式直接被Excel正常解析。

原因说明 #

openxlsx/openxlsx2默认启用Excel 365+的动态数组特性，当公式中包含多单元格区域拼接（如Source!A$2:A$4 & "|" & Source!B$2:B$4）这类数组运算时，工具会自动插入@溢出运算符，导致公式返回#NAME?错误；若设置array=TRUE，则会添加数组大括号，需要手动确认才能生效。禁用动态数组后，公式会被当作传统数组公式处理，无需额外操作即可正常运行。

修改后的代码（openxlsx版本） #

library(openxlsx)

# 创建工作簿时禁用动态数组
wb <- createWorkbook(dynamicArray = FALSE)
addWorksheet(wb, "Source")
addWorksheet(wb, "Target")

# 写入Source数据
Source <- data.frame(
  Col1 = c("A1","A1","A2"),
  Col2 = c("B1","B2","B1"),
  Col3 = c(1,1,2),
  Value1 = c(100,200,300),
  Value2 = c(10,20,30)
)
writeData(wb, "Source", Source)

# 写入Target数据
Target <- data.frame(
  Col1 = c("A1","A2"),
  Col2 = c("B1","B1"),
  Col3 = c(1,2)
)
writeData(wb, "Target", Target)

# 写入XLOOKUP公式（无需设置array=TRUE）
writeFormula(wb, "Target", 
             x = '=XLOOKUP(A2 & "|" & B2 & "|" & C2, Source!A$2:A$4 & "|" & Source!B$2:B$4 & "|" & Source!C$2:C$4, Source!D$2:D$4, "not found")', 
             startCol = 4, startRow = 2)
writeFormula(wb, "Target", 
             x = '=XLOOKUP(A2 & "|" & B2 & "|" & C2, Source!A$2:A$4 & "|" & Source!B$2:B$4 & "|" & Source!C$2:C$4, Source!E$2:E$4, "not found")', 
             startCol = 5, startRow = 2)

saveWorkbook(wb, "fixed_xlookup_openxlsx.xlsx", overwrite = TRUE)

修改后的代码（openxlsx2版本） #

library(openxlsx2)

# 创建工作簿时禁用动态数组
wb <- wb_workbook(dynamic_array = FALSE)
wb$add_worksheet("Source")
wb$add_worksheet("Target")

# 写入Source数据
Source <- data.frame(
  Col1 = c("A1","A1","A2"),
  Col2 = c("B1","B2","B1"),
  Col3 = c(1,1,2),
  Value1 = c(100,200,300),
  Value2 = c(10,20,30)
)
wb$add_data("Source", Source)

# 写入Target数据
Target <- data.frame(
  Col1 = c("A1","A2"),
  Col2 = c("B1","B1"),
  Col3 = c(1,2)
)
wb$add_data("Target", Target)

# 写入XLOOKUP公式
wb$add_formula(
  sheet = "Target",
  x = '=XLOOKUP(A2 & "|" & B2 & "|" & C2, Source!A$2:A$4 & "|" & Source!B$2:B$4 & "|" & Source!C$2:C$4, Source!D$2:D$4, "not found")',
  dims = "D2"
)
wb$add_formula(
  sheet = "Target",
  x = '=XLOOKUP(A2 & "|" & B2 & "|" & C2, Source!A$2:A$4 & "|" & Source!B$2:B$4 & "|" & Source!C$2:C$4, Source!E$2:E$4, "not found")',
  dims = "E2"
)

wb$save("fixed_xlookup_openxlsx2.xlsx", overwrite = TRUE)

验证结果 #

生成的Excel文件中，Target工作表的公式不会包含@符号或数组大括号，打开后直接显示正确的匹配结果，无需手动调整。

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