library(tidyverse)
library(tidyxl)
library(unpivotr)

#1 load in data
(my_data <- xlsx_cells("demo.xlsx"))

#2 filter to focus on the relevant worksheet colors are in column 2
(sheet3 <- my_data |> 
  filter(sheet == "Sheet3"))

#### if you are confident that your columns with color have no other formatting, 
#### you could simply rely on the local_format_id column in the tibble 
#### returned from xlsx_cells()
#### and treat that like a factor 

#3 extract formats from workbook
(sheets_formats <- xlsx_formats("demo.xlsx"))

# you can explore the nested format lists using the following
View(sheets_formats)

#4 extract codes for color fill from the workbook
(fill <- sheets_formats$local$fill$patternFill$bgColor$rgb)

# detour to look at the local formats within our worksheet
(formats <- sheet3[1:12,"local_format_id"])
# the important thing to notice is that the inclusion of a bold character
# in column 2 adds an additional format
# each number does not correspond 1:1 to a color!!!
formats[formats>1]


#5 select our relevant columns 
(correct_colors <- sheet3 |> 
  select(row, 
         col,
         data_type,
         numeric, 
         character,
         local_format_id)) 

#6 behead() to extract column names
(correct_colors2 <- correct_colors |> 
  behead("up", column_name))

#7 associate color with formats
(correct_colors3 <- correct_colors2 |> 
  mutate(
    #### important part associating the correct color with the local format ID
    color_code = map_chr(local_format_id, \(x)fill[x]),
    ####
    ### a little cleanup of the above so that we can extract something we can visualize)
    correct_color_code= if_else(is.na(color_code), NA,
      paste("#", str_sub(start = 3, color_code), sep = ""))))

#8 prepare our data to spatter()
(correct_colors4 <- correct_colors3 |> 
  distinct(row,
           numeric,
           # fudging by changing a name so that we can spatter() tibble long to wide
           character = correct_color_code,
           column_name) |> 
  # a little more fudging to spatter()
  mutate(data_type = c("numeric", "character"), .by = row))

#9 finalize data
(correct_colors5 <- correct_colors4 |> 
  spatter(column_name))
  
# lastly: a way to print our color codes
scales::show_col(correct_colors5$favorite_color |> 
                   unique())

# see if this matches our eyes
table(correct_colors5$favorite_color)

# looks good
