使用openxlsx的dataValidation设置下拉列表时出现sprintf错误

核心问题 #

1. 工作表名称不匹配：代码中创建的隐藏工作表是Drop_down_values2，但dataValidation里引用的是Drop_down_values（少了末尾的2），Excel找不到对应数据源，导致文件损坏无法打开。
2. 工作表存在性判断逻辑错误：判断是否添加工作表时，用的是原始文件existing_file的工作表列表，而非已加载到内存的工作簿wb的列表，可能导致重复添加或漏加。
3. sprintf警告：本质是引用的工作表不存在导致的连锁问题，修复名称后警告会自动消失。

修正后的代码 #

wb <- loadWorkbook(existing_file)

# 检查并添加"Dependent"工作表（基于已加载的wb判断）
if(!"Dependent" %in% getSheetNames(wb)){
  addWorksheet(wb, "Dependent")
}

# 检查并添加隐藏的"Drop_down_values2"工作表（基于已加载的wb判断）
if(!"Drop_down_values2" %in% getSheetNames(wb)){
  addWorksheet(wb, "Drop_down_values2", visible = FALSE)
}

# 提取数据
items <- dependent_table$Item
value <- dependent_table$Value

# 写入主表数据
myList_df <- data.frame("Items" = items, "Value" = value)
writeData(wb, sheet = "Dependent", x = myList_df, startCol = 1)

# 写入下拉数据源
myList_dropdown <- data.frame("Values" = value)
writeData(wb, sheet = "Drop_down_values2", x = myList_dropdown, startCol = 1)

# 计算数据行数（避免空值报错）
length_rows <- max(2, length(value) + 1)  # 至少保留第2行的下拉范围

# 添加数据验证（修正工作表名称为Drop_down_values2）
dataValidation(wb, 
               sheet = "Dependent", 
               cols = 2, 
               rows = 2:length_rows, 
               type = "list", 
               value = sprintf("'Drop_down_values2'!$A$2:$A$%d", length_rows))

# 保存工作簿
saveWorkbook(wb, "PeterExcel.xlsx", overwrite = TRUE)

关键修改说明 #

• 把工作表存在性判断的getSheetNames(existing_file)改成getSheetNames(wb)，确保基于当前内存中的工作簿状态判断，避免重复操作。
• 修正dataValidation中的工作表名称为Drop_down_values2，和创建的隐藏表名称完全一致。
• 给length_rows加了max(2, ...)处理，防止value为空时出现无效的行范围（比如2:1）。

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