导出至Excel时保留数字格式的R实现及替代方案验证

问题背景 #

现有如下数据框：

df <- data.frame(status=c('single', 'married', 'married', "fff"),
                 education=c(3456, 898989899, 23939004, NA),
                 income=c('34,5%', '88.456%', '92', NA))

通过自定义my_numbers类修改数字显示格式：

my_numbers <- function(x) structure(x, class = c('my_numbers', 'numeric'))  
format.my_numbers <- function(x,...)NextMethod(sci = FALSE, big.mark='.')
print.my_numbers <- function(x,...) print(format(x), quote = FALSE)    
'[.my_numbers' <- function(x,...,drop = FALSE)  my_numbers(NextMethod('['))
 
x <- my_numbers(df)

df <- lapply(df, my_numbers)
df2<- bind_rows(df)

生成的df2显示格式如下：

A tibble: 4 × 3
  status     education   income    
  <my_nmbrs> <my_nmbrs>  <my_nmbrs>
1 single           3.456 34,5%     
2 married    898.989.899 88.456%   
3 married     23.939.004 92   
4 fff                 NA NA   

具体问题 #

1. 使用xlsx包导出df2到Excel时，无法保留自定义my_numbers类的格式，代码如下，请问如何解决？

library(xlsx) 

wb <- createWorkbook() 
CellStyle(wb) 
sheet <- createSheet(wb, sheetName = "Sheet1") 
  
addDataFrame(df2, sheet)
saveWorkbook(wb = wb, file = "New_df.xlsx")

2. 尝试了如下替代方案，在处理百万级单元格（300000行、900列）时速度快且有效，请问该方案是否合理？

library(xlsx)
df1<-df %>%   mutate(across(where(is.numeric), ~formatC(., format="d", big.mark=".")))
df1[df1=='NA']<-NA

 
wb <- createWorkbook() 
CellStyle(wb) 
sheet <- createSheet(wb, sheetName = "Sheet1") 
addDataFrame(df1, sheet)
saveWorkbook(wb = wb, file = "New_df.xlsx")

解答 #

问题1：自定义类导出Excel保留格式的解决办法 #

xlsx包的addDataFrame函数无法自动识别自定义的my_numbers类，仅支持基础数据类型。要保留格式，需先将my_numbers类型列转换为格式化后的字符型再导出：

# 将df2所有列转换为带自定义格式的字符
df_export <- df2 %>%
  mutate(across(everything(), ~as.character(format(.x))))

# 执行导出
library(xlsx)
wb <- createWorkbook()
sheet <- createSheet(wb, sheetName = "Sheet1")
addDataFrame(df_export, sheet)
saveWorkbook(wb, file = "New_df_formatted.xlsx")

核心逻辑是利用自定义类已定义的format方法，直接生成带格式的字符串，转成字符型后导出即可保留预期显示效果。

问题2：替代方案的合理性分析 #

该方案合理且高效，原因如下：

• 逻辑严谨：直接通过formatC对数值型列做格式化，将数字转为带千位分隔符的字符串，同时把字符型"NA"替换为Excel可识别的空值，完全匹配需求。
• 性能优异：mutate(across(...))是向量化操作，处理百万级数据时效率远高于逐行处理；xlsx包导出字符型数据的速度也能支撑大规模数据场景。
• 兼容性强：转换后的字符型列无需依赖自定义类，Excel可直接显示为预期格式，避免了类识别的兼容性问题。

小提示：如果后续需要对这些列做数值计算，建议保留原始数值列的备份；若income列的百分比格式需要统一处理，需单独针对该列做格式调整（比如提取数值后再格式化）。

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