how to fill color while write to excel

February 28, 2023

I have a df like the one on the left. I would like to export it to an excel file with some formatting based on the Name value. What should I do? the rows filled with grey color every other unique names.

df<-structure(list(Name = c("Tom", "Tom", "Tom", "Jerry", "Jerry", 
"Amy", "Amy", "Amy", "Amy", "Ema", "Ema", "Ema"), Subject = c("PE", 
"ART", "ELA", "ELA", "MATH", "ELA", "MATH", "ART", "PE", "ART", 
"MATH", "ELA"), Score = c(98, 86, 75, 88, 100, 90, 86, 95, 78, 
88, 68, 95)), row.names = c(NA, -12L), class = c("tbl_df", "tbl", 
"data.frame"))

wb <- createWorkbook()
addWorksheet(wb, sheetName = "TEST")
writeData(wb, sheet = "TEST", x = df, startRow = 1)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

>Solution :

You could conditionally add a fill color using addStyle. Note that to do so we have to loop over the rows or the column to which the style should be applied:

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, sheetName = "TEST")
writeData(wb, sheet = "TEST", x = df, startRow = 1)
rows <- which(df$Name %in% c("Jerry", "Ema"))
style <- createStyle(
  fgFill = "grey85"
)
for (col in 1:3) addStyle(wb, "TEST", rows = rows + 1, cols = col, style = style)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)