r - 在R中打开带有日期和时间的excel文件

Question

如果已经在其他地方问过这个问题，请提前道歉，但我尝试了不同的尝试，但到目前为止没有任何效果。

我希望使用以下命令打开一个大 Excel 文件(> 21000 行):

    myData <- read.xlsx("....xlsx", sheet = 1, colNames = TRUE)

我有两列需要以“01-01-2019”格式而不是“43000”格式转换的日期。因此我使用:

    myData$Begin.Date <- convertToDate(myData$Begin.Date)
    myData$End.Date <- convertToDate(myData$End.Date)

然后，我在 Excel 文件中也有两列时间(小时:分钟，范围从 00:00 到 23:59)。但是，当我在 R 中读取 Excel 文件时，所有小时都转换为从 0 到 0,99 的值。如何转换这两个列以保持“小时:分钟”格式？如果我使用 convertToDate 函数，现在显示为 0 的值 00:00 将转换为“1899-12-30”

    myData$Begin.Time <- ??(myData$Begin.Time)
    myData$End.Time <- ??(myData$End.Time)

这是我到目前为止所获得的:

    > myData <- read.xlsx("....xlsx", sheet = 1, colNames = TRUE)
    > myData$Begin.Date <- with(myData, convertToDateTime(Begin.Date))
    > myData$End.Date <- with(myData, convertToDateTime(End.Date))
    > myData$Begin.Time <- with(myData, convertToDateTime(Begin.Time))
    > myData$End.Time <- with(myData, convertToDateTime(End.Time))
    > head(myData, 2)
             xxxxx yyyyy zzzzz aaaaa bbbbb nnnnnn qqqqq ssssss
    1 xxx     yyy  zzz   aaa   yyyyyy tttttt  B  rrr
    2 xxx     yyy  zzz   aaa   yyyyyy tttttt  B  rrr
      kkkkkk mmmmmm ooooo Begin.Date Begin.Time   End.Date   End.Time 
    1 u       yyy     y   2019-01-01 1899-12-30 2019-01-29 1899-12-30      
    2 u       yyy     y   2019-01-01 1899-12-30 2019-01-29 1899-12-30


    dput(head(myData,3))
    structure(list(Sample_ID = c("...", "...", "..."), Locality.Name = c("...", "...", "..."), Code = c("...", "...", "..."), Catchment =  c("...", 
    "...", "..."), Decimal.Latitude = c(..., ..., ...), Decimal.Longitude = c(..., ..., ...), Sample.type = c("...", 
    "...", "..."), Sample.Treatment = c(".", ".", "."), Chemicals = c("...", "...", "..."), Apparatus.Type = c(".", ".", "."), 
    Begin.Date = c(43466, 43466, 43466), Begin.Time = c(0, 0, 0), End.Date = c(43494, 43494, 43494), End.Time = c(0, 0, 0), Value = c(..., ..., .), Value.Type = c("A", "A", "A"), Measuring.Unit = c("...", "...", "..."), Uncertainty.Value = c(..., .., ..),   Uncertainty.Type = c(".", ".","."), Uncertainty.Unit = c("...", "...", "..."), Laboratory = c("...", "...", "..."), class = "data.frame")

Answer 1

@Parfait 明白了:

openxlsx::convertToDateTime(myData$Begin.Date+myData$Begin.Time)

例如 :

myData <- data.frame(Begin.Date = 43000:43010,
                     Begin.Time = seq(0,1,0.1)) 
openxlsx::convertToDateTime(myData$Begin.Date+myData$Begin.Time)

 [1] "2017-09-22 00:00:00 CEST" "2017-09-23 02:24:00 CEST" "2017-09-24 04:48:00 CEST" "2017-09-25 07:12:00 CEST"
 [5] "2017-09-26 09:36:00 CEST" "2017-09-27 12:00:00 CEST" "2017-09-28 14:24:00 CEST" "2017-09-29 16:48:00 CEST"
 [9] "2017-09-30 19:12:00 CEST" "2017-10-01 21:36:00 CEST" "2017-10-03 00:00:00 CEST"

什么 openxlsx::convertToDateTime做的是转换 Begin.Date以 POSIXct 取 Excel Origin 即“1899-12-30”，乘以小数部分 Begin.Time (介于 0 和 1 之间)按一天中的秒数(84600)，并考虑时区(因为 as.Date 强制 UTC):

myData <- data.frame(Begin.Date = 43000:43010,
                     Begin.Time = seq(0,1,0.1)) 
as.POSIXct(as.Date(myData$Begin.Date,origin='1899-12-30'))+myData$Begin.Time*86400-3600*2

 [1] "2017-09-22 00:00:00 CEST" "2017-09-23 02:24:00 CEST" "2017-09-24 04:48:00 CEST" "2017-09-25 07:12:00 CEST" "2017-09-26 09:36:00 CEST"
 [6] "2017-09-27 12:00:00 CEST" "2017-09-28 14:24:00 CEST" "2017-09-29 16:48:00 CEST" "2017-09-30 19:12:00 CEST" "2017-10-01 21:36:00 CEST"
[11] "2017-10-03 00:00:00 CEST"