Export character column to Excel and add thousands separator to numbers

Question

I have a data frame with a column that contains both text and numbers. As a consequence, the type of this column is character. The numbers have a dot as a decimal separator and no thousands separator.

library(openxlsx2)

x <- data.frame(Test = c("Test", "2698.48", "Test", "6872340.48"))
wb <- write_xlsx(
  x = x,
  file = "C:\\Test.xlsx"
)

I would like to export this data frame to an Excel file and I want the numbers in this column to have a dot as a thousands separator and a comma as a decimal separator. I have used the openxlsx2 package so far, but it seems that wb_add_numfmt, which applies Excel number formats to cells, only works with columns whose type is numeric (even though I couldn't find that in the documentation). The output should look like this:

Column
Text
2.698,48
Text
6.872.340,48

This doesn't work

wb_add_numfmt(
  wb,
  sheet= 1,
  dims = wb_dims(rows = 2:5, cols = 1),
  numfmt = 4
  )
  wb_save(wb, "C:\\Test.xlsx")

How can I achieve this goal? I am not fixated on the openxlsx2 package, any other approach would work for me, too.

wb_add_numfmt is from the openxlsx2 package, but I can't add that as a tag since I don't have enough reputation.

Answer 1

If it is okay for you that the numbers are still treated as text in Excel, you can do this:

library(openxlsx2)
dat <- data.frame(Column = c("Text", 2698.48, "Text", 6872340.48))
dat$Column <- sapply(dat$Column, function (x) {
  if (!is.na(as.numeric(x))) {
    format(as.numeric(x), big.mark = ".", decimal.mark = ",")
  } else {
    x
  }
})
# Warning messages are okay.
write_xlsx(dat, "text.xlsx")

For each entry in $Column, this checks whether the entry can be converted to a number. If so, it formats the number with . as thousand separator and , as decimal mark.