format dates in write.xlsx of xlsx package

Question

I am trying to export a data frame with date columns to Excel using the xlsx package. The help file for write.xlsx() illustrates a method for formatting date columns. I tried to follow that method below, but the resulting Excel file does not show the correct format. I can manually change the cell format in Excel to be a custom date, but this is an extra step that makes it non-reproducible.

library(lubridate)
library(xlsx)
dat <- data.frame(dates=c("2014-07-16 15:03:16", "2014-07-16 14:52:03", "2014-07-16 16:50:38", "2014-07-12 00:00:00", "2014-07-12 00:00:00"))
dat$dates <- ymd_hms(dat$dates)

wb <- createWorkbook()
saveWorkbook(wb, "output.xlsx")
oldOpt <- options()
options(xlsx.date.format="yyyy-mm-dd HH:mm:ss") # change date format
write.xlsx(dat, 
           "output.xlsx",
           sheetName="output")
options(oldOpt)      # revert back to defaults

Answer 1

Since the dates columns type is POSIXct, you should use xlsx.datetime.format instead of xlsx.date.format (that is valid only for columns of type Date).

But still, it doesn't work because of a bug in class check inside the xlsx package. In fact, looking at the code, it performs this check:

indDT <- which(sapply(y, class) == "POSIXct")

that fails because POSIXct also inherits from POSIXt. Maybe the correct check should be:

indDT <- which(sapply(y, FUN=function(x) any(class(x) == "POSIXct")))

Anyway, you can work-around this problem by doing this:

dat$dates <- ymd_hms(dat$dates)
class(dat$dates) <- "POSIXct"

and of course setting xlsx.datetime.format instead of xlsx.date.format