R语言使用openxlsx自定义样式将日期作为日期格式写入excel工作表

ndh0cuux 于 2023-10-13 发布在其他

关注(0)|答案(2)|浏览(145)

x1c 0d1x我在excel工作表中编写的代码如下：

wb <- createWorkbook("wb_Object1")

addWorksheet(wb, "Report_MicronV1", gridLines = TRUE)

writeData(wb, sheet = "Report_MicronV1", Report_Micron, rowNames = FALSE)

setColWidths(wb, "Report_MicronV1", cols = 1:length(Report_Micron[1,]), widths = 15)

headerS <- createStyle(
           fontSize = 12,
           textDecoration = "bold",
           fontColour = "#0F0E0E",
           halign = "left",
           valign = "center",
           fgFill = "#FCF707",
           border = "TopBottom",
           borderColour = "#22FC07",
           borderStyle = "thick",
           wrapText = TRUE)

bodyS <-  createStyle(
          fontSize = 9,
          textDecoration = "bold",
          fontColour = "#0F0E0E",
          halign = "left",
          border = "TopBottom",
          borderColour = "#d7dcde")

 addStyle(wb, sheet = 1, headerS, rows = 1, cols = 1:length(Report_Micron[1,]), gridExpand = 
          TRUE)

 addStyle(wb, sheet = 1, bodyS, rows = 2:nrow(Report_Micron), cols = 
          1:length(Report_Micron[1,]), gridExpand = TRUE)

 saveWorkbook(wb, "Report_MicronV1.xlsx", overwrite = TRUE)

预期输出为“mm/dd/yyyy”
但是R写的是下面的（参考-屏幕打印）
请帮
由R编写的Excel表格的屏幕打印：

来源：https://stackoverflow.com/questions/68140289/write-dates-to-excel-sheet-as-date-format-with-custom-styles-using-openxlsx

2条答案

按热度按时间

inb24sb21#

问题是当你添加样式时日期格式会被覆盖。要解决此问题，请先添加样式，然后将数据写入工作簿：
修改openxlsx的默认示例：

library(openxlsx)

dates <- data.frame("d1" = Sys.Date() - 0:4)
for(i in 1:3) dates <- cbind(dates, dates)
names(dates) <- paste0("d", 1:8)
dates$d1 <- "Text"
dates$d2 <- 1:5

## Date Formatting
wb <- createWorkbook()

addWorksheet(wb, "Date Formatting", gridLines = FALSE)

setColWidths(wb, 1, cols = 1:length(dates[1,]), widths = 15)

headerS <- createStyle(
  fontSize = 12,
  textDecoration = "bold",
  fontColour = "#0F0E0E",
  halign = "left",
  valign = "center",
  fgFill = "#FCF707",
  border = "TopBottom",
  borderColour = "#22FC07",
  borderStyle = "thick",
  wrapText = TRUE)

bodyS <-  createStyle(
  fontSize = 9,
  textDecoration = "bold",
  fontColour = "#0F0E0E",
  halign = "left",
  border = "TopBottom",
  borderColour = "#d7dcde")

addStyle(wb, sheet = 1, headerS, rows = 1, cols = 1:length(dates[1,]), gridExpand = 
           TRUE)

addStyle(wb, sheet = 1, bodyS, rows = 2:(nrow(dates) + 1), cols = 
           1:length(dates[1,]), gridExpand = TRUE)

writeData(wb, 1, dates) ## write without styling

saveWorkbook(wb, "date.xlsx", overwrite = TRUE)

赞(0）回复(0）举报 2023-10-13

okxuctiv2#

我已经尝试了公认的解决方案。它工作了，但在我的情况下，突出显示样式不能完全覆盖我的行（日期单元格仍然没有着色）。
我发现，通过先写数据，即。（writeData( )），然后添加addStyle(stack = True )，我们可以将突出显示样式添加到整个行（包括日期），而无需重复以前的样式。

赞(0）回复(0）举报 2023-10-13

我来回答

R语言使用openxlsx自定义样式将日期作为日期格式写入excel工作表

2条答案

相关问题

热门标签

最新问答

R语言 使用openxlsx自定义样式将日期作为日期格式写入excel工作表

2条答案

相关问题

热门标签

最新问答

R语言使用openxlsx自定义样式将日期作为日期格式写入excel工作表