Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

format dates in write.xlsx of xlsx package

Tags:

r

xlsx

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

enter image description here

like image 932
Eric Green Avatar asked Jul 19 '14 13:07

Eric Green


1 Answers

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

like image 169
digEmAll Avatar answered Oct 06 '22 00:10

digEmAll