使用openxlsx导出R数据框至Excel时的数字格式配置问题

问题 #

使用R的openxlsx包导出包含文本列和数值列的数据框到Excel时，数值列同时包含整数和小数（数据类型为numeric），希望Excel中数字显示与R中完全一致，但现有numFmt配置无法满足需求：

• 设置numFmt = "######.######"时，整数后会带小数点（如12345显示为12345.），小数前缺失0（如0.0001显示为.0001）
• 设置numFmt = "#.0"时，所有数字会被强制保留一位小数，不符合需求

使用环境：R 4.3.1、RStudio 2023.06.0 Build 421、openxlsx 4.2.5.2

原代码示例：

library("openxlsx")  

df <- data.frame(ID = c("a","b","c"), Value = c(12345, 12.52, 0.0001))

# Create workbook & sheet:
wb    <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")

# Create the cell style
textstyle <- openxlsx::createStyle(numFmt = "@")
numericstyle <- openxlsx::createStyle(numFmt = "######.######")

# Assign df to workbook
openxlsx::writeDataTable(wb = wb, sheet = "test", x = df)

# First identify the range of the 'text cells':
textcells <- expand.grid(row = c(1:(nrow(df)+1)), col = c(1))
numericcells <- expand.grid(row=c(1:(nrow(df)+1)), col = c(2))

# Then assign 'textstyle' to the 'textcells-range':
openxlsx::addStyle(wb = wb, sheet = "test", 
                   rows = textcells$row, cols = textcells$col, 
                   style = textstyle) 

openxlsx::addStyle(wb = wb, sheet = "test", 
                   rows = numericcells$row, cols = numericcells$col, 
                   style = numericstyle) 

# Save the workbook
openXL(wb)

解决方案 #

要实现与R中一致的数字显示效果，需将数值列的numFmt设置为"0.##########"，该自定义格式的逻辑是：

• 0表示必须显示的数字位，确保小数前的0不会缺失，同时整数部分至少显示1位
• .作为小数点分隔符，仅当数字有小数部分时才会显示
• ##########表示最多显示10位小数（可根据实际需求调整位数），整数则不会显示小数点及后续内容

修改后的代码如下：

library("openxlsx")  

df <- data.frame(ID = c("a","b","c"), Value = c(12345, 12.52, 0.0001))

# Create workbook & sheet:
wb    <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")

# Create the cell style
textstyle <- openxlsx::createStyle(numFmt = "@")
# 修改这里的numFmt配置
numericstyle <- openxlsx::createStyle(numFmt = "0.##########")

# Assign df to workbook
openxlsx::writeDataTable(wb = wb, sheet = "test", x = df)

# First identify the range of the 'text cells':
textcells <- expand.grid(row = c(1:(nrow(df)+1)), col = c(1))
numericcells <- expand.grid(row=c(1:(nrow(df)+1)), col = c(2))

# Then assign 'textstyle' to the 'textcells-range':
openxlsx::addStyle(wb = wb, sheet = "test", 
                   rows = textcells$row, cols = textcells$col, 
                   style = textstyle) 

openxlsx::addStyle(wb = wb, sheet = "test", 
                   rows = numericcells$row, cols = numericcells$col, 
                   style = numericstyle) 

# Save the workbook
openXL(wb)

另外，也可以简化代码，直接在writeDataTable中通过style参数指定列样式，避免手动设置单元格范围：

library("openxlsx")  

df <- data.frame(ID = c("a","b","c"), Value = c(12345, 12.52, 0.0001))

wb <- openxlsx::createWorkbook()
sheet <- openxlsx::addWorksheet(wb, "test")

# 定义列样式
col_styles <- list(
  createStyle(numFmt = "@"),  # 第一列文本样式
  createStyle(numFmt = "0.##########")  # 第二列数值样式
)

# 直接写入数据并应用列样式
openxlsx::writeDataTable(wb = wb, sheet = "test", x = df, style = col_styles)

openXL(wb)

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