Convert cell coloration in Excel to written text in R

General
1

I have an excel spreadsheet that is encoded with cell color fill as opposed to machine readable text. I would like to add text to each cell that denotes what color it was colored in. The text can be anything, as long as its unique to the color (I was thinking the RGB value since its already used by excel). So for example, a cell in red would have #FF0000 entered into that cell to make it machine readable. Here is the code that I have already written to partially address this:

library(openxlsx)#

Load your Excel file wb <- loadWorkbook("test.xlsx")

# Get cell stylesstyles <- getStyles(wb)

# Access a specific cell's stylecell_style <- styles[["Sheet1"]][[1,1]] # For cell A1 in Sheet1

# Check the properties of the styleprint(cell_style$fill$patternFill$fgColor$rgb)

# Print the fill color of the cellView(styles)print(styles)

# Assuming you want to access the fill foreground color of the first element in stylesfill_fg_color <- styles[[1]]$fill$fgColor# Print the fill foreground colorprint(fill_fg_color)

All of this works and it retrieves the formatting of the code fine, but I can't seem to extract the value and get it into its corresponding cell in dataframe. How can I proceed?

2

Hi @nong

library(openxlsx)

wb <- loadWorkbook("test.xlsx")

getStyleFgColor <- function(styleObjects, sheet, row, col){
  temp <- lapply(styleObjects, function(x){
      if(x$sheet == sheet && (length(intersect(which(x$rows == row), which(x$cols == col))) == 1)){
        return(x$style$fill$fillFg)
      } 
      return(NA)
  })
  temp <- unlist(temp)
  if(sum(is.na(temp)) == length(temp)){
    return(NA)
  }
  return(temp[!is.na(temp)])
}

styleObjects <- wb$styleObjects
sheet <- "Sheet1"
row <- 1
col <- 1

getStyleFgColor(styleObjects = styleObjects, sheet = sheet, row = row, col = col)

if it works how i intended it to :smile: the function getStyleFgColor should give back the color based on your inputs - if there is a style applied - otherwise NA.

If this does what you need i will post a better explanation of what the function does step by step (if need at all).