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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With