how to fill color while write to excel

Question

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)

stefan · Accepted Answer · 2023-02-28 14:17:59Z

4

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)

answered Feb 28, 2023 at 14:17

stefan

130k6 gold badges42 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stataq Over a year ago

what if I have larger number of Name? Is it possible to do so without listing each Name?

stefan Over a year ago

In general no. You want to fill each second name? In that case you could e.g. do names_to_fill <- unique(df$Name)[seq_along(unique(df$Name)) %% 2 == 0]; rows <- which(names_to_fill).

stefan Over a year ago

Of course it should be rows <- which(df$Name %in% names_to_fill)

Collectives™ on Stack Overflow

how to fill color while write to excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related