setDataFormatForType() 对 XLConnect 中的日期是否正常工作? [英] Does setDataFormatForType() work correctly for Dates in XLConnect?

问题描述

我最近在函数上尝试了各种格式化参数

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

例如 format="d/m/yy" 如上所示，还有许多其他的.

接下来是

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

然后我写一个工作表并保存工作簿.任何形式的格式调整似乎都不起作用.一旦我弄乱了 setDataFormatForType 命令中的任何格式，结果就是数字时间值显示在我稍后保存的 Excel 工作簿的日期列中即 2013 年 11 月 6 日 = 41584.如果我不干扰任何数据格式，那么标准 (POSIX) 格式将被保存，但是当您在生成的 Excel 中查看该格式时，它分配了一些自定义的XLConnect 格式"，因此显示为错误":-(- 这意味着美国符号(前导月后接日)但我想要的是欧式(前导日后接月).

如果有人在 XLConnect 中设置这些数据格式(尤其是日期")方面有一些经验，那么分享一些想法或智慧将不胜感激.

谢谢，沃尔特

解决方案

在 XLConnect 版本中有一个新的样式操作 XLC$"STYLE_ACTION.DATATYPE" 可从 github 的 https://github.com/miraisolutions/xlconnect.数据类型"样式操作可用于使用可使用 setCellStyleForType 设置的特定单元格样式来设置特定类型单元格的样式.请参见以下示例:

<前>需要(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, "数据")writeWorksheet(wb, data = data, sheet = "data")保存工作簿(wb)

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

解决方案

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屋！