导出到 Excel 时保留数字格式

我有这个数据框

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 <- 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   

我想在将 df2 提取到 excel 时保留此格式。我试过了

library(xlsx) 

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

但没有成功。有什么想法吗？

另一种方法是

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")

看起来这个方法有效，而且考虑到我拥有的数百万个单元格，这个方法非常快。这有意义吗？

我有 300000 行和 900 列