如何在openxlsx2的数据表中写入公式？遇#REF!错误求助

问题原因 #

直接用add_formula把公式写入数据表外部区域时，Excel不会自动将该区域纳入数据表范围，导致引用表内字段触发#REF!错误；即便预添加空列，若公式位置或数据表关联逻辑有误，同样会失效。

解决方案 #

1. 预添加空列到数据框：创建Excel数据表前，先给原始数据框新增空列，确保数据表创建时包含该列。
2. 精准定位公式写入位置：公式的起始行、列必须对应数据表内新增列的位置，让Excel识别公式为数据表的一部分。
3. 使用数据表结构化引用：公式里用表名[@列名]的结构化引用方式，保证引用有效性。

修正后的代码 #

library(dplyr)
library(openxlsx2)

# 预添加空列到数据框，用于存放公式结果
mtcars_model <- mtcars %>%
  mutate(model = rownames(.),
         model_prefix = NA)

temp_path <- file.path(tempdir(), paste0(format(Sys.time(), "%m-%d-%H%M-%S"),"_mtcars_openxlsx2_test.xlsx"))

wb <- wb_workbook() %>%
  wb_add_worksheet("Data") 

# 创建包含空列的数据表
wb$add_data_table(sheet = "Data",
                  x = mtcars_model,
                  tableName = "mtcars")

# 向数据表内的空列写入公式
wb$add_formula(sheet = "Data",
               x = '=LEFT(mtcars[@model],FIND(" ",mtcars[@model],1))',
               array = TRUE,
               startRow = 2:(nrow(mtcars_model) + 1),
               startCol = ncol(mtcars_model))  # 直接定位到数据框最后一列

wb$save(path = temp_path)
shell.exec(temp_path)

关键说明 #

• 预添加的空列会被纳入Excel数据表，后续写入的公式会被识别为数据表的计算列，不会出现#REF!错误。
• 用ncol(mtcars_model)定位新增列，避免手动计算列索引时出错。
• 结构化引用mtcars[@model]能保证数据表扩展、修改时引用依然有效。

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