R语言处理.xlsx文件时空白/NA单元格无法转为[x]的问题求助

问题分析 #

直接操作sheet_data$v处理NA无效，核心原因是openxlsx的工作表结构中，空白单元格可能未被纳入sheet_data$v的向量（仅记录有值单元格），且直接修改底层结构并非官方推荐操作，容易出现兼容性问题。

解决方案 #

推荐使用openxlsx官方提供的read.xlsx()和writeData()方法操作数据，能更可靠识别并处理空白/NA值：

修改后的代码 #

right_aligned_style <- createStyle(numFmt = "#,###.0",
                                   halign = "right",
                                   fontSize = 12)

Wblistreplace <- AllWorkbooks$Full_Output[AllWorkbooks$Year == current_year]

for (x in Wblistreplace) {
  wb_path <- paste0(project_root, x, ".xlsx")
  new_workbook <- loadWorkbook(wb_path)
  
  sheet_count <- length(new_workbook$worksheets)
  
  for (s in 4:sheet_count) {
    # 读取工作表，将空白单元格转为NA
    sheet_data <- read.xlsx(wb_path, sheet = s, na.strings = c("", "NA"))
    
    # 替换特定数值
    sheet_data[sheet_data == 9999] <- "[z]"
    sheet_data[sheet_data == 9998] <- "[d]"
    sheet_data[sheet_data == 9997] <- "[x]"
    sheet_data[sheet_data == 9996] <- "[u]"
    # 替换所有NA（含空白转换而来的）
    sheet_data[is.na(sheet_data)] <- "[x]"
    
    # 清空原表并写入修改后的数据
    deleteData(new_workbook, sheet = s, rows = 1:nrow(sheet_data), cols = 1:ncol(sheet_data))
    writeData(new_workbook, sheet = s, x = sheet_data, startRow = 1, startCol = 1)
    
    # 处理样式逻辑
    row_range <- if (AllWorkbooks[which(AllWorkbooks$Full_Output == x), 16] == "No") {
      10:(10 + as.numeric(AllWorkbooks[which(AllWorkbooks$Full_Output == x), 14]) - as.numeric(AllWorkbooks[which(AllWorkbooks$Full_Output == x), 13]))
    } else {
      10:(11 + as.numeric(AllWorkbooks[which(AllWorkbooks$Full_Output == x), 14]) - as.numeric(AllWorkbooks[which(AllWorkbooks$Full_Output == x), 13]))
    }
    
    addStyle(new_workbook, sheet = s,
             style = right_aligned_style,
             rows = row_range,
             cols = 3:17,
             gridExpand = TRUE)
  }
  
  saveWorkbook(new_workbook, wb_path, overwrite = TRUE)
}

关键调整点 #

• 用read.xlsx(na.strings = c("", "NA"))强制将空白单元格转为NA，确保is.na()能识别。
• 采用deleteData()+writeData()的官方流程修改单元格数据，避免底层结构不兼容问题。
• 简化样式判断逻辑，提升代码可读性。

备选方案（不推荐） #

若坚持直接修改底层结构，可补充空字符串判断：

values[values == "9999"] <- "[z]"
values[values == "9998"] <- "[d]"
values[values == "9997"] <- "[x]"
values[values == "9996"] <- "[u]"
# 同时处理NA和空字符串
values[is.na(values) | values == ""] <- "[x]"

此方式依赖openxlsx底层结构，版本更新可能导致失效。

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