setDataFormatForType()对于XLConnect中的日期是否可以正常工作? [英] Does setDataFormatForType() work correctly 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")

例如上述所示的format ="d/m/yy"以及许多其他格式.

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

然后是

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

，然后我编写一个工作表并保存工作钩. 似乎没有格式调整的任何形式. 一旦我弄乱了setDataFormatForType命令中的任何格式，结果就是数字时间值显示在Excel工作簿的日期列中，我稍后将其保存 即2013年11月6日= 41584. 如果我不干扰任何数据格式，那么将保存标准(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(特别是日期")的经验，那么分享一些想法或智慧将不胜感激.

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

谢谢沃尔特

推荐答案

XLConnect版本中有一个新样式的操作XLC$"STYLE_ACTION.DATATYPE"，可从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屋！