R tidyxl determine exact color

Question

I am loading an Excel file with shaded colors using the tidyxl R package, but I cannot figure out how to convert the octal decimal color to an rgb color.

Here is a basic example of my Excel spreadsheet:

After reading in this Excel file using tidyxl, I can see the fill color and tint, but how do I convert this to rgb? Currently, I am manually removing the first two "FF" and adding a pound sign to get this to be a hexadecimal color, but this doesn't seem to be the correct approach and not sure how to handle the tint.

filepath <- "C:/Users/pcalhoun/Filled colors.xlsx"

dat <- tidyxl::xlsx_cells(filepath)
formats <- tidyxl::xlsx_formats(filepath)
colors <- tidyxl::xlsx_color_theme(filepath)
    
#subset to only the columns that have any relevant data
dat <- dat[!is.na(dat$numeric)|!is.na(dat$character),]

dat$fillColor <- formats$local$fill$patternFill$fgColor$rgb[dat$local_format_id]
dat$fillTint <- formats$local$fill$patternFill$fgColor$tint[dat$local_format_id]

dat[c("character", "fillColor", "fillTint")]
   character fillColor   fillTint
1     Colors      <NA>         NA
2 Light Gray  FFFFFFFF -0.1499985
3 Light Blue  FF00B0F0         NA
4      Green  FF4EA72E         NA


# Here is an attempt where I convert this to hexadecimal and just ignore the first two letters,
# but not sure what to do with the "fillTint" column to make this light gray
paste0("#", substring(dat$fillColor[!is.na(dat$fillColor)], 3))
[1] "#FFFFFF" "#00B0F0" "#4EA72E"

Answer 1

There are two things going on here:

1. The Excel hex codes contain the alpha value at the start, so your light blue FF00B0F0 is the color 00B0F0 (red 0, green 176, blue 240) with alpha FF (255). R expects the alpha at the end i.e. 00B0F0FF.
2. The reason your FFFFFFFF (RGB and alpha all 255) is grey rather than white is because of fillTint. Excel calculates this by multiplying the RGB values by 1 + fillTint and rounding the result. This gives you get RGB values of 217 in this case (light grey).

We can write a function to reorder the alpha values, convert to RGB, adjust by the fill tint and then convert back to hex codes:

excel_to_hex_color <- function(fillColor, fillTint) {
    # move alpha to end
    colors <- paste0(
        substring(fillColor, 3, 8),
        substring(fillColor, 1, 2)
    )

    # calculate adjusted fill
    fill_adj <- 1 + ifelse(is.na(fillTint), 0, fillTint)

    # multiply rgb values by adjustment
    rgb_mat <- round(t(col2rgb(paste0("#", colors), alpha = TRUE)) * fill_adj)
    rgb(rgb_mat, maxColorValue = 255) # back from rgb to hex
}

We can then apply this to your colors:

fillColor <- c("FFFFFFFF", "FF00B0F0", "FF4EA72E")
fillTint <- c(-0.1499985, NA, NA)
excel_to_hex_color(fillColor, fillTint)
# [1] "#D9D9D9" "#00B0F0" "#4EA72E"

This returns the colors in your worksheet: