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

My 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:

Solution

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)