Openxlsx: How can I use an external variable to conditionally format cell background colors in an excel table?

Openxlsx: How can I use an external variable to conditionally format cell background colors in an excel table?

Dear all, I am designing an excel table and I would like to condition the background colours of the cells to the values of a variable that is not to be displayed in the table. To the best of my understanding of openxlsx documentation, it is possible to (a) conditionally format colours according to properties of the table’s element (e.g. the first row, column x,…); (b) conditionally format colors according to values displayed in the cell (e.g. mark red if negative…). I could not find any references to conditionally formatting according to an external value for the same dataset. I would be grateful if you could point me to possible ways to do this, or any workarounds. Many thanks for your attention.

Teresio

An example (curent task I am engaged in)

# I have to set-up an excel file with ranking data
# Main information to be displayed is the position of various entities in given
# ranking excercise:

# My example dataset
Entity <- c("Entity_1", "Entity_2", "Entity_3", "Entity_4", "Entity_5")
Exercise1 <- c(2, 4, NA, 32, 26)
Exercise2 <- c(27, NA, NA, 12, 3)

ranking_DF <- data.frame(Entity, Exercise1, Exercise2)


# The ranking position in itself - however - may be misguiding in assessing 
# entities performances, because of the different number of competing entities in each
# exercise. I.e. being 2nd out of 100 competing entities has - in my case - a 
# different substantive meaning that being 2nd out of 4 competing entities.
# So I would like to display the quartile position, too, using background colours: 
# values in Q4 of the exercise would have a green background, the ones in Q3 would 
# have a yellow one... etc 
# NA and label cells should have a white one

# Based on quartile position of my entities and other criteria above, I have set up 
# a "mirror dataframe", mapping colours to be used. Ideally, I would like to pass openxlsx 
# functions the information in the latter dataframe, but I am open to other ways to handle
# my problem 

# So, the corresponding quartiles/colours dataframe:
Entity <- c("white", "white", "white", "white", "white")
Exercise1 <- c("green", "green", "white", "orange", "orange")
Exercise2 <- c("red", "white", "white", "yellow", "green")

bgcolours_DF <- data.frame(Entity, Exercise1, Exercise2)

I would like to get an excel spreadsheet like the one in the picture below:

Coloured table I would like to get to

Solution

library(openxlsx)

# I have to set-up an excel file with ranking data
# Main information to be displayed is the position of various entities in given
# ranking excercise:

# My example dataset
Entity <- c("Entity_1", "Entity_2", "Entity_3", "Entity_4", "Entity_5")
Exercise1 <- c(2, 4, NA, 32, 26)
Exercise2 <- c(27, NA, NA, 12, 3)

ranking_DF <- data.frame(Entity, Exercise1, Exercise2)

# The ranking position in itself - however - may be misguiding in assessing 
# entities performances, because of the different number of competing entities in each
# exercise. I.e. being 2nd out of 100 competing entities has - in my case - a 
# different substantive meaning that being 2nd out of 4 competing entities.
# So I would like to display the quartile position, too, using background colours: 
# values in Q4 of the exercise would have a green background, the ones in Q3 would 
# have a yellow one... etc 
# NA and label cells should have a white one

# Based on quartile position of my entities and other criteria above, I have set up 
# a "mirror dataframe", mapping colours to be used. Ideally, I would like to pass openxlsx 
# functions the information in the latter dataframe, but I am open to other ways to handle
# my problem 

# So, the corresponding quartiles/colours dataframe:
Entity <- c("white", "white", "white", "white", "white")
Exercise1 <- c("green", "green", "white", "orange", "orange")
Exercise2 <- c("red", "white", "white", "yellow", "green")

bgcolours_DF <- data.frame(Entity, Exercise1, Exercise2)

wb <- createWorkbook("Test Workbook")
addWorksheet(wb, "Ranking Data", gridLines = FALSE)
writeData(wb, sheet = 1, ranking_DF, rowNames = FALSE)

color_cells <- function(wb, rows, cols, color) {
  cellStyle <- createStyle(fgFill = color)
  addStyle(wb, sheet = 1, cellStyle, rows = rows, cols = cols, gridExpand = TRUE)
  wb
}

lapply(2:(nrow(bgcolours_DF) + 1), function(x) {
  wb <- color_cells(wb, rows = x, cols = 1, color = bgcolours_DF[ x - 1, 'Entity' ])
  wb <- color_cells(wb, rows = x, cols = 2, color = bgcolours_DF[ x - 1, 'Exercise1' ])
  wb <- color_cells(wb, rows = x, cols = 3, color = bgcolours_DF[ x - 1, 'Exercise2' ])
})

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