CODEWITHSUNDEEP
ropenxlsx
ks ramana
ks ramana

Reputation: 133

Write dates to excel sheet as date format with custom styles using openxlsx

enter image description hereMy code for writing in excel sheet is as follows:

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)

Expected output is 'mm/dd/yyyy"

But R is writing as below (refer- screen print)

Please help

screen print of excel sheet which in written by R:

Upvotes: 4

Views: 3456

Answers (2)

vegatroz
vegatroz

Reputation: 44

I have tried the accepted solution. It worked, but in my case, the highlight style can't entirely cover my rows (date cell is still not colored).

I found that by writing data first i.e. (writeData( )), then adding addStyle(stack = True ) we can add the highlight style to the entire rows (including the date) without overwriting previous styles.

Upvotes: 1

stefan
stefan

Reputation: 125398

The issue is that the date format is overwritten when you add your styles. To solve this issue, add the styles first, then write the data to the workbook:

Adapting the default example from 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)

enter image description here

Upvotes: 4

Related Questions