导出含文本数字的字符列至Excel并设置指定千位/小数分隔符

要实现将混合文本与数字的字符型列导出为Excel，并让数字显示为点作为千位分隔符、逗号作为小数分隔符的格式，核心问题是：Excel不会对文本类型的单元格应用数字格式，因此需要先将数字字符转为数值型，再搭配自定义数字格式实现需求。以下提供两种基于R包的可行方案：

方案一：使用openxlsx2包 #

library(openxlsx2)

x <- data.frame(Test = c("Test", "2698.48", "Test", "6872340.48"))

# 创建工作簿并写入初始数据
wb <- wb_workbook() %>%
  wb_add_worksheet("Sheet1") %>%
  wb_add_data(x, sheet = "Sheet1")

# 定义自定义数字格式：正数/负数用点做千分位、逗号做小数位，零显示为0，文本保持原样
custom_numfmt <- "#.##0,00;-#.##0,00;0;@"

# 为目标列设置自定义格式
wb <- wb_add_numfmt(wb, sheet = 1, dims = "A:A", numfmt = custom_numfmt)

# 遍历单元格，将数字字符转为数值型（文本内容保持不变）
for (row_idx in seq_len(nrow(x))) {
  cell_content <- x$Test[row_idx]
  # 尝试转换为数值，排除NA（即非数字内容）
  num_val <- suppressWarnings(as.numeric(cell_content))
  if (!is.na(num_val)) {
    wb <- wb_set_cell_value(wb, sheet = 1, row = row_idx + 1, col = 1, value = num_val)
  }
}

# 保存Excel文件
wb_save(wb, "C:\\Test.xlsx")

方案二：使用openxlsx包（旧版） #

如果你更习惯使用openxlsx包，代码逻辑类似：

library(openxlsx)

x <- data.frame(Test = c("Test", "2698.48", "Test", "6872340.48"))

wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
writeData(wb, "Sheet1", x)

# 定义相同的自定义数字格式
custom_numfmt <- "#.##0,00;-#.##0,00;0;@"
# 创建格式样式并应用到目标列
target_style <- createStyle(numFmt = custom_numfmt)
addStyle(wb, "Sheet1", style = target_style, rows = 2:(nrow(x)+1), cols = 1, gridExpand = TRUE)

# 转换数字字符为数值型
for (row_idx in seq_len(nrow(x))) {
  cell_content <- x$Test[row_idx]
  num_val <- suppressWarnings(as.numeric(cell_content))
  if (!is.na(num_val)) {
    writeData(wb, "Sheet1", num_val, startRow = row_idx + 1, startCol = 1, keepNA = FALSE)
  }
}

saveWorkbook(wb, "C:\\Test.xlsx", overwrite = TRUE)

关键说明 #

• 你之前的代码失效原因：wb_add_numfmt仅对数值型单元格生效，而原始数据中所有内容都是字符型，Excel不会为文本单元格应用数字格式。
• 自定义格式代码#.##0,00;-#.##0,00;0;@的含义：
  • #.##0,00：正数显示为千分位用点、小数用逗号的格式
  • -#.##0,00：负数在格式前加负号
  • 0：零显示为0
  • @：文本内容保持原样显示

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