CODEWITHSUNDEEP
rexcelxlconnect
user6763357
user6763357

Reputation:

R XLConnect - filtering columns based on column color

In the XLconnect package (or any other package in R), is it possible to read an Excel sheet with colors in their headers and filtering them in R based on those colors?

For example is column headers for A, C & E are filled with green, and after being read in R, is it possible to filter them based on that color?

Thanks

Upvotes: 1

Views: 546

Answers (1)

BlooB
BlooB

Reputation: 965

yes i believe it is: Read to R, using xlsx package and extract:

library(xlsx)
wb     <- loadWorkbook("test.xlsx")
sheet1 <- getSheets(wb)[[1]]

than get the rows and cells:

# get all rows
rows  <- getRows(sheet1)
cells <- getCells(rows)
# quick look at the values
sapply(cells, getCellValue)
#  1.1  2.1  3.1  4.1  5.1  6.1  7.1  8.1  9.1 10.1 11.1 
#  "x"  "1"  "2"  "3"  "4"  "5"  "6"  "7"  "8"  "9" "10"

now where is the color info? It is in the cell style:

styles <- sapply(cells, getCellStyle)

now a function to give you the cells RGB value:

NOTE: the following line give the cells background color style$getFillForegroundXSSFColor()

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

want more info? go here

Upvotes: 1

Related Questions