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"))
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