R identify highlighted rows in Excel spreadsheet

Question

I'm currently reading an Excel file in R, and I am trying to apply a function to all the gray (grey) highlighted cells. Is it possible to read a workbook with R and detect these highlighted cells?

Answer 1

Try using the xlsx package. For example:

library(xlsx)
df <- loadWorkbook("test.xlsx")
sheet1 <- getSheets(df)[[1]]
rows  <- getRows(sheet1)
cells <- getCells(rows)

styles <- sapply(cells, getCellStyle)

cellColor <- function(style) {
  fg  <- style$getFillForegroundXSSFColor()
  rgb <- tryCatch(fg$getRgb(), error = function(e) NULL)
  rgb <- paste(rgb, collapse = "")
  return(rgb)

}

myCellColors <- sapply(styles, cellColor). 

Edited from here: https://www.r-bloggers.com/when-life-gives-you-coloured-cells-make-categories/