rformattingxlsxhighlightconditional-formatting

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


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


Solution

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

    enter image description here

    Created on 2024-04-18 with reprex v2.0.2