如何使用R的openxlsx包创建溢出数组公式？

问题描述 #

尝试用R的openxlsx包创建动态溢出数组，希望用户修改第1行内容后，导出文件中的UNIQUE公式能自动动态增减溢出结果。但使用writeFormula时，无论array参数设为TRUE还是FALSE都无法达到预期：

• 设为FALSE时，公式被强制添加@符号，无法触发溢出
• 设为TRUE时，公式被强制添加旧版数组公式的{}括号，同样无法实现动态溢出

示例代码：

library(openxlsx)

testwb <- createWorkbook()
addWorksheet(testwb, "Sheet1")

df <- data.frame(c("one"),
                 c("two"),
                 c("three"),
                 c("two"),
                 c("four"))

writeData(testwb, sheet = "Sheet1", x = df, startCol = 1, startRow = 1, colNames = FALSE)

writeFormula(testwb, sheet = "Sheet1", x = "TRANSPOSE(UNIQUE(A1:E1, TRUE))", array = FALSE,
             startCol = 1, startRow = 3)

saveWorkbook(testwb, file = "test_output.xlsx", overwrite = TRUE)

openXL(testwb)

解决方案 #

问题根源是openxlsx默认会对公式进行兼容性转换，自动添加@或{}。要写入原生的Excel动态数组公式，只需在writeFormula中添加local = TRUE参数，让函数直接写入原始公式，不做自动转换：

修改后的代码：

library(openxlsx)

testwb <- createWorkbook()
addWorksheet(testwb, "Sheet1")

df <- data.frame(c("one"),
                 c("two"),
                 c("three"),
                 c("two"),
                 c("four"))

writeData(testwb, sheet = "Sheet1", x = df, startCol = 1, startRow = 1, colNames = FALSE)

# 添加local=TRUE，保留原生动态数组公式
writeFormula(testwb, sheet = "Sheet1", x = "TRANSPOSE(UNIQUE(A1:E1, TRUE))", 
             array = FALSE, startCol = 1, startRow = 3, local = TRUE)

saveWorkbook(testwb, file = "test_output.xlsx", overwrite = TRUE)

openXL(testwb)

说明 #

• local = TRUE参数会让openxlsx跳过公式的自动转换逻辑，直接将你输入的公式字符串写入Excel单元格
• 写入后的公式是原生的动态数组公式，无需手动添加任何符号，Excel会自动识别并触发溢出效果
• 修改第1行的内容后，UNIQUE公式的结果会自动更新，溢出范围也会随结果数量增减而动态调整
• 确保你的openxlsx版本是较新的（建议>=4.2.5），旧版本可能对动态数组公式的支持不完善

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