Search code examples
rexceldatexlconnect

Exporting date data to .xlsx file with XLConnect (R) bug?

UPDATE 2: Example that is producing problem.

Example Excel File: is a blank .xlsx with a sheet name Sheet1. Example R Code:

library(chron)
library(rJava)
library(xlsxjars)
library(xlsx)
library(XLConnect)

setwd("C:/")

Day<-as.Date('2014-01-01')
Data<-as.data.frame(rep(Day,35))

name<-paste("Example.xls")
Master<-loadWorkbook(name, create=TRUE)
writeWorksheet(object=Master, data=Data,sheet="Sheet1", startRow=2, startCol=1,header=FALSE)
saveWorkbook(Master)

UPDATE: The numeric value in the data frame for "12/31/2013" is 16070. The last date is "3/25/2014" with a value of 16154.

This is expected. However, when I look at the numbers written to the sheet, "12/31/2013" is 41638.75 and should be 41639. "3/25/2014" is 41722.79 and should be 41723.

Am I missing something about how an R data frame interprets a whole date? Does the Java Routine subtract a certain fraction of a whole number?

ORIGINAL: I am attempting to export many data frames to different sheets of an .xlsx file. My data frame has date only data information in a data frame. (i.e. "2013-12-31" )

I use the following code to export it to my excel file.

Temp<-data
name<-paste("Master.xlsx") 
Master<-loadWorkbook(name, create=TRUE)
writeWorksheet(object=Master,data=Temp,sheet="Ft. Wayne",startRow = 2, startCol = 1,   header=FALSE)
saveWorkbook(Master)

When I open the .xlsx file, the date comes in as:

12/30/2013 18:00:00

How do I get to to export with the format

"12/31/2013"

And maintain "date" cell number formatting the.xlsx file?

FYI: If I change the format in the dataframe, it exports as a "general" value.

Solution

  • Reason for this behavior is that objects of class Date are converted internally to POSIXct and are assumed to represent a date at midnight UTC. US CST is UTC - 6 hours so that's why you experience an offset. I would suggest you use POSIXct directly. If you want to display dates without time components in Excel, you can use cell styles and data formats.

    Example:

    library(XLConnect)

data = data.frame(Day = rep(as.POSIXct('2014-01-01'), 35))

wb = loadWorkbook("example.xls", create = TRUE)

setStyleAction(wb, XLC$"STYLE_ACTION.DATATYPE")
cs = createCellStyle(wb, name = "myDateStyle")
setDataFormat(cs, format = "yyyy-mm-dd")
setCellStyleForType(wb, style = cs, type = XLC$"DATA_TYPE.DATETIME")

createSheet(wb, name = "test")
writeWorksheet(wb, data = data, sheet = "test", startRow = 2, startCol = 1, header = FALSE)

saveWorkbook(wb)