R语言操作Excel工作簿：如何将空白单元格识别为NA而非忽略？

问题背景 #

我需要编写函数编辑通过loadWorkbook导入的Excel工作簿，但遇到空白单元格被忽略而非识别为NA的问题。演示代码如下：

df <- data.frame(x = c(1, 2, 3, 4, NA, 6), y = c(7, 8, 9, 10, NA, 12))
xlsx::write.xlsx(df, file = 'testdf.xlsx', showNA = FALSE, row.names = FALSE)
testwb <- loadWorkbook(file = 'testdf.xlsx')

sheetnames <- c('Sheet1')
sheets <- lapply(sheetnames, function(name) getSheets(testwb)[[name]])
rows <- getRows(sheets[[1]], rowIndex=1:7)
cells <- getCells(rows, colIndex=1:2)
values <- sapply(unlist(cells), getCellValue)
values
length(values)

初始运行时能正确包含NA，但手动删除Excel文件中的第一行数据后，再次运行代码会丢失空白单元格对应的项，导致对比不同工作表时因值数量不一致出现问题。

我尝试用openxlsx包的createWorkbook和writeData的keepNA参数重建工作簿，代码如下：

testwb2 <- openxlsx::createWorkbook()
addWorksheet(testwb2, 'Sheet1')
writeData(testwb2, 'Sheet1', read_xlsx('testdf.xlsx', sheet = 1), keepNA = TRUE)

sheetnames <- c('Sheet1')
sheets <- lapply(sheetnames, function(name) getSheets(testwb2)[[name]])
rows <- getRows(sheets[[1]], rowIndex=1:7)
cells <- getCells(rows, colIndex=1:2)
values <- sapply(unlist(cells), getCellValue)
values
length(values)

却出现错误：

Error in envRefInferField(x, what, getClass(class(x)), selfEnv) :
‘getNumberOfSheets’ is not a valid field or method name for reference class “Workbook”

解决方案 #

方案1：统一使用xlsx包，手动填充空白单元格为NA #

getCells会跳过无内容的单元格，因此可以先构建指定行列的所有单元格坐标组合，逐个提取值，不存在的单元格直接设为NA：

library(xlsx)

# 读取工作簿
testwb <- loadWorkbook('testdf.xlsx')
sheet <- getSheets(testwb)[['Sheet1']]

# 指定要读取的行和列范围
target_rows <- 1:7
target_cols <- 1:2

# 生成所有目标单元格的坐标
cell_coords <- expand.grid(row = target_rows, col = target_cols)

# 逐个提取值，空白单元格返回NA
values <- mapply(function(r, c) {
  cell <- getCells(sheet, rowIndex = r, colIndex = c)[[1]]
  if (is.null(cell)) NA else getCellValue(cell)
}, cell_coords$row, cell_coords$col)

# 整理成矩阵方便查看（可选）
values_matrix <- matrix(values, nrow = length(target_rows), byrow = TRUE)
colnames(values_matrix) <- c('x', 'y')

values_matrix
length(values) # 结果为14，与指定行列数一致

方案2：统一使用openxlsx包，避免跨包混用错误 #

之前的错误是因为混用了xlsx和openxlsx的Workbook操作函数，两个包的Workbook结构完全不兼容。如果用openxlsx，直接用它的原生方法处理：

library(openxlsx)

# 读取Excel并将空白识别为NA
df <- read.xlsx('testdf.xlsx', sheet = 1, na.strings = "")

# 创建工作簿并写入数据（保留NA）
testwb2 <- createWorkbook()
addWorksheet(testwb2, 'Sheet1')
writeData(testwb2, 'Sheet1', df, keepNA = TRUE)

# 提取指定行列的值，补全不足的行
target_rows <- 1:7
target_cols <- 1:2
df_expanded <- df[target_rows, target_cols, drop = FALSE]
# 补全超出原数据行数的空白行
df_expanded <- rbind(df_expanded, matrix(NA, nrow = max(0, length(target_rows) - nrow(df_expanded)), ncol = ncol(df_expanded)))
rownames(df_expanded) <- NULL

values <- unlist(df_expanded)
values
length(values) # 结果为14，符合预期

方案3：用readxl直接读取指定范围并补全NA #

如果仅需获取指定行列的所有值（含空白），可以用readxl指定读取范围，自动将空白转为NA：

library(readxl)

# 读取A1:B7范围，空白单元格识别为NA
df <- read_xlsx('testdf.xlsx', range = "A1:B7", na = "")
# 确保行数符合目标要求
df <- df[1:7, ]

values <- unlist(df)
values
length(values)

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