Excel Cell Coloring using xlsx

Question

Initial code:

Lets assume we're using this command to create the dummy data:

Data <- data.frame(
    X = paste(c(sample(1:10),sample(1:10)), collapse=";"),
    Y = sample(c("yes", "no"), 10, replace = TRUE)
)

Output:

                                           X   Y
1  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
2  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
3  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
4  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
5  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
6  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
7  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
8  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
9  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
10 10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes

Question:

Using xlsx package I can output the X column data to an excel file colored.

Is there a way where I can color lets say the values bigger then 5 to red and lesser then 5 to blue and put in the same cell everything. Basically I just write this table to an excel but some values are colored.

Thank you in advance,

Answer 1

I doubt it's possible to change Excel's conditional formatting directly from R. So, first open the Excel workbook and set the conditional formatting for the column "X" is going into to whatever color vs value conditions you want. Then when you write from R into the workbook, the coloring will happen.

Answer 2

I am essentially copying code from this question and my answer there and making some adjustments for this use case. I'm not sure about the etiquette on this, but I just wanted to show that this can be done! Anyone, let me know if I have done something I shouldn't do in reusing the code in the linked question for this answer. If this is seen as a duplicate now that the other question is answered, I'm fine with that. Just trying to help!

First, reformat the data a little.

# split the X column so there will be one numeric entry per cell 
d <- matrix(as.numeric(unlist(strsplit(as.character(Data$X), ";"))), 
           ncol = 20, byrow = TRUE)

d <- data.frame(d, Data$Y)
cols <- length(d[1, ]) # number of columns, we'll use this later

Second, we can use functions in xlsx to create a workbook, and then get at the cell values.

library(xlsx)

# exporting data.frame to excel is easy with xlsx package
sheetname <- "mysheet"
write.xlsx(d, "mydata.xlsx", sheetName=sheetname)
file <- "mydata.xlsx"
# but we want to highlight cells if value greater than or equal to 5
wb <- loadWorkbook(file)              # load workbook
fo1 <- Fill(foregroundColor="blue")   # create fill object # 1
cs1 <- CellStyle(wb, fill=fo1)        # create cell style # 1
fo2 <- Fill(foregroundColor="red")    # create fill object # 2
cs2 <- CellStyle(wb, fill=fo2)        # create cell style # 2 
sheets <- getSheets(wb)               # get all sheets
sheet <- sheets[[sheetname]]          # get specific sheet
rows <- getRows(sheet, rowIndex=2:(nrow(d)+1))     # get rows
                                                   # 1st row is headers
cells <- getCells(rows, colIndex = 2:cols)         # get cells

# in the wb I import with loadWorkbook, numeric data starts in column 2
# The first column is row numbers.  The last column is "yes" and "no" entries, so
# we do not include them, thus we use colIndex = 2:cols

values <- lapply(cells, getCellValue) # extract the cell values

Next we find the cells that need to be formatted according to the criteria.

# find cells meeting conditional criteria > 5
highlightblue <- NULL
for (i in names(values)) {
  x <- as.numeric(values[i])
  if (x > 5 && !is.na(x)) {
    highlightblue <- c(highlightblue, i)
  }    
}

# find cells meeting conditional criteria < 5
highlightred <- NULL
for (i in names(values)) {
  x <- as.numeric(values[i])
  if (x < 5 && !is.na(x)) {
    highlightred <- c(highlightred, i)
  }    
}

Finally, apply the formatting and save the workbook.

lapply(names(cells[highlightblue]),
       function(ii) setCellStyle(cells[[ii]], cs1))

lapply(names(cells[highlightred]),
       function(ii) setCellStyle(cells[[ii]], cs2))

saveWorkbook(wb, file)

Answer 3

Old question, but for people that still research this topic:

In the package openxlsx, there is a function that makes this much easier- conditionalFormatting()

Below is an example:

#Load needed package
if (!require("pacman")
) install.packages("pacman")
pacman::p_load(
  #add list of libraries here
  openxlsx
)

##Create workbook and write in sample data
wb <- createWorkbook()
addWorksheet(wb, "Moving Row")
writeData(wb, "Moving Row", -5:5)
writeData(wb, "Moving Row", LETTERS[1:11], startCol = 2)

##Define how you want the cells to be formatted
negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")

## highlight row dependent on first cell in row
conditionalFormatting(wb, "Moving Row",
                      cols = 1:2,
                      rows = 1:11, rule = "$A1<0", style = negStyle
)
conditionalFormatting(wb, "Moving Row",
                      cols = 1:2,
                      rows = 1:11, rule = "$A1>0", style = posStyle
)

##Save workbook in default location
saveWorkbook(wb, "conditionalFormattingExample.xlsx", TRUE)

you can read about it here and see many other types of conditional highlighting it can do: https://www.rdocumentation.org/packages/openxlsx/versions/4.2.5/topics/conditionalFormatting