为何Excel会移除R生成的条件格式？求解决方案

用R的openxlsx包给Excel文件添加条件底纹，代码运行无报错且成功生成文件，但用Microsoft 365 Apps 2306版本打开时，提示文件内容存在问题；选择恢复后，条件底纹消失，同时弹出提示“已移除功能：/xl/worksheets/sheet7.xml部分的条件格式”。

可能的解决思路 #

• 修正conditionalFormatting参数用法：openxlsx中使用between规则时，无需operator参数，直接将区间值传入rule即可。原代码错误使用operator=c(5,9)会生成不符合Excel规范的XML结构，导致Office无法识别。
• 保留数据数值类型：原代码将数据转为字符型（format(selected_data, nsmall=1)），但条件格式基于数值判断，字符型数据会让规则失效，还会干扰XML生成。
• 排查模板兼容性：若模板本身包含复杂格式或旧版Excel特性，可能与新添加的条件格式冲突，可尝试用空白工作簿测试。

修改后的代码 #

data_output_root = paste0(project_root, "/Output/2022/NI Table 3 SOC2/")
data_template_root = paste0(project_root, "/Template/2022/NI Table 3 SOC2/")

new_workbook <- loadWorkbook(paste0(data_template_root, "Occupation SOC20 (2) Table 3 (NI).1   Weekly pay - Gross 2022.xlsx"))

# 读取数据
data_from_xlsCV <- read_xls(paste0(data_input_root, "Work Region Occupation SOC20 (2) NI Table 3 (NI).1b   Weekly pay - Gross 2022 CV.xls"), sheet = "Male")
selected_data <- data_from_xlsCV[6:41, 2:17]
selected_data <- sapply(selected_data, as.numeric)
# 保留数值类型，通过样式的numFmt控制显示格式，注释掉字符转换步骤
# selected_data <- format(selected_data, nsmall= 1)

# 写入数据
writeData(new_workbook, sheet = "Table3.1b Male CV",
          x = selected_data,
          startRow = 10, startCol = 2,
          colNames = FALSE)

# 创建样式
cvFive <- createStyle(bgFill = "#5bc0de",
                      numFmt = "#,###.#",
                      halign = "right",
                      fontSize = 12)

cvTen <- createStyle(bgFill = "#428bca",
                     numFmt = "#,###.#",
                     halign = "right",
                     fontSize = 12)

# 修正条件格式参数：明确类型为cellIs，comparison设为between，rule传入区间值
conditionalFormatting(new_workbook, sheet = "Table3.1b Male CV",
                      rows = 10:45, cols = 3:17,
                      type = "cellIs",
                      comparison = "between",
                      rule = c(5,9),
                      style = cvFive)

conditionalFormatting(new_workbook, sheet = "Table3.1b Male CV",
                      rows = 10:45, cols = 3:17,
                      type = "cellIs",
                      comparison = "between",
                      rule = c(10,20),
                      style = cvTen)

# 保存工作簿
saveWorkbook(new_workbook, paste0(data_output_root,"Occupation SOC20 (2) Table 3 (NI).1   Weekly pay - Gross 2022.xlsx"), overwrite = TRUE)

补充说明 #

• 数值类型数据是Excel正确解析条件规则的基础，无需提前转成字符型，样式中的numFmt可直接控制显示格式。
• openxlsx的conditionalFormatting函数对于between规则的标准用法是type="cellIs" + comparison="between" + rule=c(min, max)，原代码的参数错误是导致XML格式不兼容的核心原因。
• 若问题仍存在，可尝试用createWorkbook()新建空白工作簿写入数据和格式，排除模板文件的干扰。

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