如何仅使用xlconnect检索时间? [英] How can I retrive the time only with xlconnect?

问题描述

我将数据保存在.xlsx文件中，并将日期和时间列分隔在数据中.但是，我输入了代码；

data1< -readWorksheetFromFile("file.xlsx"，sheet ="Sheet1")

以将数据检索到R中.因此，我在R环境中获得了time列，并附带了废话日期.所以R中的时间列看起来像 1899-12-31 16:07:11 ，在这种情况下，我只想将时间作为 16:07:11 .那怎么办?

解决方案

XLConnect使用POSIXct表示data.frame中的日期时间值.如果只想提取时间分量，则可以使用strftime(your_column, format = "%H:%M:%S")(表示为character).

关于您为何获得1899-12-31的一些背景: Excel将日期时间值存储为十进制数字，代表自1900-01-00以来的天数(是的，第00天！-不存在).小数天代表小时，分钟和秒.因此，十进制数字1.0代表1900-01-01，数字0.0代表1900-01-00.由于后者不存在，因此在XLConnect中，它实际上表示为第一天的前一天，即1899-12-31. 在Excel中，仅时间值表示为十进制数字0.xxx.按照上述说明，在XLConnect中导入时，结果为1899-12-31 HH:MM:SS.

可以在此处找到有关Excel日期时间值的其他信息... >

I have data saved in .xlsx file, and in the data the columns date and time separated. However, I type the code;

data1<-readWorksheetFromFile("file.xlsx", sheet="Sheet1")

to retrive data into R. As a consequence, I am getting the time column in the R environment, with nonsense date attached to it. So the time column in R looks like 1899-12-31 16:07:11, what I want only the take the time as 16:07:11 in this case. How can that be done ?

解决方案

XLConnect uses POSIXct to represent date-time values in a data.frame. If you only want to extract the time component you may use strftime(your_column, format = "%H:%M:%S") (representation as character).

Some background as to why you get 1899-12-31: Excel stores date-time values as decimal numbers representing the number of days since 1900-01-00 (yes, day 00! - which doesn't exist). Fractional days represent hours, minutes and seconds. As such the decimal number 1.0 represents 1900-01-01 and the number 0.0 represents 1900-01-00. Since the latter doesn't exist, in XLConnect it's actually represented as the day before day 1 which is 1899-12-31. Time-only values are represented as decimal numbers 0.xxx in Excel. In accordance with the above, when imported in XLConnect this results in 1899-12-31 HH:MM:SS.

Additional information on Excel date-time values can be found here.

这篇关于如何仅使用xlconnect检索时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！