Does setDataFormatForType() work at all for Dates

Question

I recently tried all sorts of formatting arguments on the function

setDataFormatForType(wb, type=XLC$DATA_TYPE.DATETIME, format="d/m/yy")

for example format="d/m/yy" as shown above, besides numerous others.

This then is followed up by

setStyleAction(wb, XLC$"STYLE_ACTION.DATA_FORMAT_ONLY")

and then I write a worksheet and save the workook. No form of format tweaking seems to work. As soon as I mess with any format in the setDataFormatForType command the result is that the numeric time value shows up in the date columns in Excel workbook that I save later on i.e. for Nov. 6th, 2013 = 41584. If I do not interfere with any DataFormats then Standard (POSIX) format gets saved but when you look at that in the resulting Excel it has some Custom "XLConnect format" assigned to it so it is displayed "wrong" :-( - which means American notation (leading month followed by day) but what I want is Eurepean (leading day followed by the month).

If anyone has some experience with setting these DataFormats (especially 'dates') in XLConnect, then sharing some thoughts or wisdom would be highly appreciated.

Thanks, Walter

Answer 1

You do need to have a named region called "Dates". I saved a copy of the template2.xslx file with such a region. The only think that worked for me was to write it out with the format.Date function:

Dates=seq(from=as.Date("2001-01-01"), to=as.Date("2013-01-01"), by=365)
file.copy(system.file("demoFiles/template2.xlsx", 
                      package = "XLConnect"),
          "dataformat.xlsx", overwrite = TRUE)
wb <- loadWorkbook("dataformat.xlsx")
setDataFormatForType(wb, type = XLC$"DATA_TYPE.DATETIME", 
                     format = "dd/mm/yyyy")
setStyleAction(wb, XLC$"STYLE_ACTION.DATA_FORMAT_ONLY")
createName(wb, name = "Dates", formula = "mtcars!$A$1")
writeNamedRegion(wb, format(Dates, "%d.%m.%Y"), name = "Dates")
saveWorkbook(wb)

Answer 2

There's a new style action XLC$"STYLE_ACTION.DATATYPE" in the XLConnect version available from github at https://github.com/miraisolutions/xlconnect. The "datatype" style action can be used to style cells of a specific type using a specific cell style which can be set using setCellStyleForType. See the following example:

require(XLConnect)
wb = loadWorkbook("test.xlsx", create = TRUE)
setStyleAction(wb, XLC$"STYLE_ACTION.DATATYPE")
cs = createCellStyle(wb, name = "mystyle")
setDataFormat(cs, format = "d/m/yy")
setCellStyleForType(wb, style = cs, type = XLC$"DATA_TYPE.DATETIME")
data = data.frame(A = 1:10, B = Sys.time() + 1:10)
createSheet(wb, "data")
writeWorksheet(wb, data = data, sheet = "data")
saveWorkbook(wb)