是否setDataFormatForType（）在所有的工作中XLConnect日期？ [英] Does setDataFormatForType() work at all for Dates in XLConnect?

问题描述

我最近尝试各种格式化参数上的功能

I recently tried all sorts of formatting arguments on the function

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

例如格式=如上图所示，除了许多其他D / M /日。

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

这则是通过

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

然后我写了一个工作表，并保存workook。
格式调整的任何形式似乎工作。
当我惹的setDataFormatForType命令任何格式的结果是数字时间值在Excel工作簿中的日期栏，我保存以后显示出来
即对于2013年11月6日= 41584。
如果我不与任何DataFormats那么标准（POSIX）格式会保存，但是当你在Excel的结果看，它有一定的自定义XLConnect格式分配给它，所以它会显示错误的干扰:-(
- 这意味着美国的符号（领先一个月后的一天），但我想要的是Eurepean（领先的一天之后的一个月）。

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).

如果任何人有在XLConnect设置这些DataFormats（尤其是'日期'），然后分享一些想法和智慧将是非常美联社preciated一些经验。

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

谢谢，沃尔特

推荐答案

有一个新的风格的动作 XLC $STYLE_ACTION.DATATYPE在XLConnect版本从GitHub在 https://github.com/miraisolutions/xlconnect 。在数据类型风格的动作可以用它可以使用设置一个特定的单元格样式可以用于特定类型的细胞风格 setCellStyleForType 。请看下面的例子：

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)

这篇关于是否setDataFormatForType（）在所有的工作中XLConnect日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！