使用openxlsx2创建Excel公式时遇非法字符错误求助

1. 列名生成逻辑错误导致非法字符 #

你的column手动拼接逻辑存在索引错误：

• R的letters向量索引从1开始，当i=7时，floor((i+2)/26) = floor(9/26) = 0，letters[0]会返回NA，最终拼接出NAI这类无效列名，直接导致公式中出现非法的NA字符。
• 直接用openxlsx2内置的int2col()函数替代手动计算，能自动生成正确的Excel列名：

  column = int2col(i + 2) # 将列号转为标准Excel列名
  

2. Excel IF函数语法错误 #

原公式的IF结构违反Excel语法规则：

• Excel的IF函数仅支持3个参数：IF(条件, 真值, 假值)，但你生成的公式里写了4个参数（IF(..., Gx, -Hx, IFERROR(...))），这会被Excel判定为非法公式结构。
• 需修正嵌套逻辑，确保IF参数数量正确，示例调整后的结构：

  paste0("IF(", column, "$1=\"elim\", G", row_num, ", -H", row_num, " + IFERROR(...))")
  

3. 公式括号/引号不匹配 #

观察你的公式末尾存在多余闭合括号，且部分嵌套结构可能存在括号数量不对的情况，Excel解析时会报错。建议先手动生成单个行的公式，复制到Excel中测试通过后，再批量生成。

4. 先验证单个公式有效性 #

在循环中打印生成的单条公式字符串，检查是否存在：

• 无效的列名/单元格引用
• 引号、括号不匹配
• 函数参数数量错误
  比如先取i=7时生成的第一条公式，直接在Excel中测试，确认能正常解析后再批量处理。

修复后的简化示例代码 #

library(openxlsx2)
wb = wb_workbook()
wb$add_worksheet('IS_Rec')

row_count = 2847
isRecFormula2 = data.frame(matrix(ncol = 0, nrow = row_count))
row_nums = 1:row_count + 5 # 提前计算行号，避免重复计算

for (i in 7:38){
  # 使用内置函数生成正确的Excel列名
  column = int2col(i + 2)
  
  # 修正IF函数语法，确保参数数量正确
  formula_str = paste0(
    "IF(", column, "$1=\"elim\", G", row_nums, 
    ", -H", row_nums, " + IFERROR(VLOOKUP($B", row_nums, 
    ", INDIRECT(", column, "$3&\"!$A$1:$QQ$4000\"), MATCH(", column, 
    "$4, INDIRECT(", column, "$3&\"!$A$1:$QQ$1\"), 0), FALSE), 0) + IFERROR(VLOOKUP($B", row_nums, 
    ", INDIRECT(", column, "$3&\"!$A$1:$QQ$4000\"), MATCH(", column, 
    "$5, INDIRECT(", column, "$3&\"!$A$1:$QQ$1\"), 0), FALSE), 0)"
  )
  
  isRecFormula2[,i-6] = formula_str
  class(isRecFormula2[,i-6]) = c(class(isRecFormula2[,i-6]), "formula")
}

wb$add_data(sheet='IS_Rec', dims = "I6", x= isRecFormula2, col_names = F, na.strings ='')
wb_save(wb, file = "test.xlsx", overwrite = T)

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