Exporting color coded data from R to Excel in a wide format

library(tidyr)

# Create a data frame in long format
long_data <- data.frame(
  digits = rep(1:5, each = 3),
  category = rep(c("A", "B", "C"), times = 5),
  value = c(10, 20, 30, 15, 25, 35, 12, 22, 32, 18, 28, 38, 14, 24, 34),
  color_code = c("red", "red", "red", "red", "blue", "blue", "blue", "blue", "blue", "green", "green", "green", "green", "green", "green")
)

I have a data_frame in a long format where each value is color_coded. I found it possible to color code this output in Excel using openxlsx library by applying conditional formatting based on color_code. However, I cannot figure out how to do it if I need the final Excel output to be in the wide format but still color-coded.

I wasn't able to find a solution for this. Please help.

Note: with this code you can specify more specific hexadecimal color values. For example "#00FF00" instead of "green".

library(purrr)
library(openxlsx)
library(tidyr)

wide <- long_data |>
  pivot_wider(id_cols = digits, names_from = category, values_from = value)

colors <- long_data |>
  pivot_wider(id_cols = digits, names_from = category, values_from = color_code)

color_loc <- map(set_names(unique(long_data$color_code)), ~ as.data.frame(which(colors == .x, arr.ind = TRUE)))
color_style <- imap(color_loc, ~ createStyle(fontColour = .y))

wb <- write.xlsx(wide, "output.xlsx")
iwalk(color_loc, ~ addStyle(wb, 1, color_style[[.y]], rows = .x$row + 1, cols = .x$col))                    
saveWorkbook(wb, "output.xlsx", overwrite = TRUE)

1. wide is the wide format data and colors is the same structured data but with the cell-level color as values.

> wide
#   digits     A     B     C
#    <int> <dbl> <dbl> <dbl>
# 1      1    10    20    30
# 2      2    15    25    35
# 3      3    12    22    32
# 4      4    18    28    38
# 5      5    14    24    34

> colors
#   digits A     B     C    
#    <int> <chr> <chr> <chr>
# 1      1 red   red   red  
# 2      2 red   blue  blue 
# 3      3 blue  blue  blue 
# 4      4 green green green
# 5      5 green green green

2. For each unique color, we get the row and column index location of all cells for that color. So, this code will work if you change the colors, add colors, and/or remove colors:

> color_loc
# $red
#   row col
# 1   1   2
# 2   2   2
# 3   1   3
# 4   1   4
# 
# $blue
#   row col
# 1   3   2
# 2   2   3
# 3   3   3
# 4   2   4
# 5   3   4
# 
# $green
#   row col
# 1   4   2
# 2   5   2
# 3   4   3
# 4   5   3
# 5   4   4
# 6   5   4

3. For each color we create a sheet style color_style.
4. Lastly, after creating a workbook object (wb), we apply the each style to the specific row and column index (row + 1 to account for the header row in Excel).

Output