Export dataframe as excel sheet in r using openxlsx and recognize numeric cells as numbers (get rid of numbers formatted as text error)

Question

Is there a way to make sure the numbers are exported as numbers using openxlsx?

using the example below, the cells with numbers are exported as text. I believe part of it is that columns contain both text and numbers (ie read as character columns). if I change it to numeric, it will cause loss of data.

any suggestions? I tried to read through the package documentation but cannot find a specific argument that can do this function.

dff = data.frame(var1 = c("a", "b", "c", "1"), var2=c("1", "c", 2, "1/1/2020"))

wb22= createWorkbook()
addWorksheet(wb22, "sheet1")
writeData(wb22, sheet="sheet1", x=dff, startRow = 1,
          startCol = 1, colNames = TRUE)
saveWorkbook(
  wb22,
  file = "example22.xlsx",
  overwrite = TRUE
)

browseURL("example22.xlsx")

Answer 1

As per stefan's comment, a dataframe column can only have one datatype.

A workaround might be adding a dummy formula in Excel to convert numbers (that are stored as characters) back to numbers then leave everything else as is.

For example:

dff = data.frame(var1 = c("a", "b", "c", "1"), var2=c("1", "c", 2, "1/1/2020"))

wb22= createWorkbook()
addWorksheet(wb22, "sheet1")
writeData(wb22, sheet="sheet1", x=dff, startRow = 1,
          startCol = 1, colNames = TRUE)

## New column will be added with header called var2_new
writeData(wb22, sheet="sheet1", x= "var2_new", startRow = 1, startCol = 3)

## Excel formula to convert numbers back to numbers and everything else as is. 
## by multiplying by 1. If there is an error use the column value as is.
xl_formula <- paste0('IFERROR(B', 2:(nrow(dff) + 1), '*1, B', 2:(nrow(dff) + 1), ')')
writeFormula(wb22, sheet = "sheet1", xl_formula, startRow = 2,startCol = 3) 

saveWorkbook(
  wb22,
  file = "example22_new.xlsx",
  overwrite = TRUE
)