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
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)