如何用R的openxlsx包导出Excel并将数字单元格设为数值格式

当数据列同时包含文本和数字时，R会自动将列识别为字符型，openxlsx默认按字符格式导出，导致数字在Excel里显示为文本。直接强制转数值列会丢失文本数据，这里提供两种可行的解决方案：

方法1：逐个单元格判断并设置格式 #

先写入原始数据，再遍历每个单元格，尝试将可转换的字符转为数值或日期，同时设置对应单元格格式：

library(openxlsx)

dff = data.frame(var1 = c("a", "b", "c", "1"), var2=c("1", "c", 2, "1/1/2020"), stringsAsFactors = FALSE)

wb22 <- createWorkbook()
addWorksheet(wb22, "sheet1")
# 写入原始数据
writeData(wb22, sheet="sheet1", x=dff, startRow = 1, startCol = 1, colNames = TRUE)

# 创建数值和日期样式
num_style <- createStyle(numFmt = "0")
date_style <- createStyle(numFmt = "yyyy-mm-dd")

# 遍历数据行（跳过表头行）
for (row in 2:(nrow(dff)+1)) {
  # 处理var1列
  cell_val <- dff[row-1, "var1"]
  num_val <- suppressWarnings(as.numeric(cell_val))
  if (!is.na(num_val)) {
    writeData(wb22, sheet="sheet1", x=num_val, startRow=row, startCol=1, colNames=FALSE)
    addStyle(wb22, sheet="sheet1", style=num_style, rows=row, cols=1)
  }
  
  # 处理var2列（优先转日期，再转数值）
  cell_val <- dff[row-1, "var2"]
  date_val <- suppressWarnings(as.Date(cell_val, format="%m/%d/%Y"))
  if (!is.na(date_val)) {
    writeData(wb22, sheet="sheet1", x=date_val, startRow=row, startCol=2, colNames=FALSE)
    addStyle(wb22, sheet="sheet1", style=date_style, rows=row, cols=2)
  } else {
    num_val <- suppressWarnings(as.numeric(cell_val))
    if (!is.na(num_val)) {
      writeData(wb22, sheet="sheet1", x=num_val, startRow=row, startCol=2, colNames=FALSE)
      addStyle(wb22, sheet="sheet1", style=num_style, rows=row, cols=2)
    }
  }
}

saveWorkbook(wb22, file = "example22_fixed.xlsx", overwrite = TRUE)
browseURL("example22_fixed.xlsx")

方法2：预处理数据为列表型，保留元素类型 #

将数据列转换为列表，对每个元素尝试转成数值/日期，无法转换的保持字符，openxlsx会自动根据元素类型设置Excel单元格格式：

library(openxlsx)

dff = data.frame(var1 = c("a", "b", "c", "1"), var2=c("1", "c", 2, "1/1/2020"), stringsAsFactors = FALSE)

# 定义转换函数：优先转日期，再转数值，失败留原字符
convert_cell <- function(x) {
  date_val <- suppressWarnings(as.Date(x, format="%m/%d/%Y"))
  if (!is.na(date_val)) return(date_val)
  
  num_val <- suppressWarnings(as.numeric(x))
  if (!is.na(num_val)) return(num_val)
  
  return(x)
}

# 把每列转为元素类型混合的列表
dff[] <- lapply(dff, function(col) lapply(col, convert_cell))

wb22 <- createWorkbook()
addWorksheet(wb22, "sheet1")
writeData(wb22, sheet="sheet1", x=dff, startRow = 1, startCol = 1, colNames = TRUE)

saveWorkbook(wb22, file = "example22_fixed.xlsx", overwrite = TRUE)
browseURL("example22_fixed.xlsx")

说明 #

• 方法1适合需要精准控制单元格格式的场景，比如自定义数值小数位数、日期格式；
• 方法2代码更简洁，适合批量处理多列混合类型数据；
• suppressWarnings()用来屏蔽转换失败时的警告，不影响正常运行。

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