Formatting xlsx in R: Highlight cell in column based on values with column names

Question

I'm using R for my analysis to list a bunch of counts per year and species; years in columns and species across rows. I have a small dummy dataset below. The last column of the dataset (year_highlighted) shows the year in which the species was officially listed. I want to highlight the cell that corresponds to the column (year) mentioned in 'year_highlighted'.

I've seen several posts (like this) on conditional formatting, but those format the cells with the value itself. Other posts are over a decade old and I assume there are better ways now (like this). For me, I have the reference to column where the cell needs to be formatted. I want to do this all in R so that I don't have to highlight the cell manually in Excel.

Here's a small example dataset

species_year <- structure(list(fruit = c("apple", "apricot", "avocado"), `2022` = c(9.676, 
9.285, 9.557), `2023` = c(10.891, 
10.097, 9.359), `2024` = c(10.031, 
8.983, 11.389), year_highlight = c(2023, 
2024, 2023)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", 
"data.frame"))

Here's my expected output:

Answer 1

You can do this with openxlsx.

There is a more recent package openxlsx2 which may offer an alternative approach, but I've not yet made the move from its predecessor.

library(openxlsx)

# generate a vectors for column and row coordinates (cell references) for cells which need background colour
# nothing sophisticated about this approach due to the example data

row_vec <- seq_len(nrow(species_year)) + 1
col_vec <- species_year$year_highlight - 2020




wb <- createWorkbook()
addWorksheet(wb, sheetName = "fruits")

writeData(wb, sheet = 1, x = species_year)

# create style for background colour
s1 <- createStyle(fgFill = "gold")

addStyle(wb, 
         sheet = 1,
         style = s1, 
         rows =  row_vec, 
         cols = col_vec)


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

Which results in:

^{Created on 2024-04-18 with reprex v2.0.2}