I want to export data frames to Excel and highlight cells according to certain rules. I don't think this answer to a similar question is correct. I think it is possible, and I think I get close using the CellStyle
functions of the xlsx
package.
Below I outline what I've tried. Most of the ideas come from the package help files. I get all the way to the end and get an error when I try to apply the style I created to the cells that meet the criteria. I get the error: Error in .jcall(cell, "V", "setCellStyle", cellStyle$ref) : RcallMethod: invalid object parameter
.
library(xlsx)
# create data
cols <- sample(c(1:5), 1) # number of columns to vary to mimic this unknown
label <- rep(paste0("label ", seq(from=1, to=10)))
mydata <- data.frame(label)
for (i in 1:cols) {
mydata[,i+1] <- sample(c(1:10), 10)
}
# exporting data.frame to excel is easy with xlsx package
sheetname <- "mysheet"
write.xlsx(mydata, "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
fo <- Fill(backgroundColor="yellow") # create fill object
cs <- CellStyle(wb, fill=fo) # create cell style
sheets <- getSheets(wb) # get all sheets
sheet <- sheets[[sheetname]] # get specific sheet
rows <- getRows(sheet) # get rows
cells <- getCells(rows) # get cells
values <- lapply(cells, getCellValue) # extract the values
# find cells meeting conditional criteria
highlight <- "test"
for (i in names(values)) {
x <- as.numeric(values[i])
if (x>=5 & !is.na(x)) {
highlight <- c(highlight, i)
}
}
highlight <- highlight[-1]
# apply style to cells that meet criteria
if (length(highlight)>0) { # proceed if any cells meet criteria
setCellStyle(cells[highlight], cs) # DOES NOT WORK
}
# save
saveWorkbook(wb, file)
Update: I've also tried:
if (length(highlight)>0) { # proceed if any cells meet criteria
for (h in 1:length(highlight)) {
setCellStyle(cells[highlight[h]], cs) # DOES NOT WORK
}
}
But I get the error: Error in .jcall(cell, "V", "setCellStyle", cellStyle$ref) : RcallMethod: cannot determine object class
Try this out. I changed a few things, including the a slight change to the call to Fill
and limiting the cells included for consideration to those with numeric data. I used lapply
to apply the conditional formatting.
cols <- sample(c(1:5), 1) # number of columns to vary to mimic this unknown
label <- rep(paste0("label ", seq(from=1, to=10)))
mydata <- data.frame(label)
for (i in 1:cols) {
mydata[,i+1] <- sample(c(1:10), 10)
}
# exporting data.frame to excel is easy with xlsx package
sheetname <- "mysheet"
write.xlsx(mydata, "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
fo <- Fill(foregroundColor="yellow") # create fill object
cs <- CellStyle(wb, fill=fo) # create cell style
sheets <- getSheets(wb) # get all sheets
sheet <- sheets[[sheetname]] # get specific sheet
rows <- getRows(sheet, rowIndex=2:(nrow(mydata)+1) # get rows
# 1st row is headers
cells <- getCells(rows, colIndex = 3:(cols+3)) # get cells
# in the wb I import with loadWorkbook, numeric data starts in column 3
# and the first two columns are row number and label number
values <- lapply(cells, getCellValue) # extract the values
# find cells meeting conditional criteria
highlight <- "test"
for (i in names(values)) {
x <- as.numeric(values[i])
if (x>=5 & !is.na(x)) {
highlight <- c(highlight, i)
}
}
highlight <- highlight[-1]
lapply(names(cells[highlight]),
function(ii)setCellStyle(cells[[ii]],cs))
saveWorkbook(wb, file)
this is great. I'd like to propose one small change to make it more "reproducible". Since the number of columns will vary (which reflects my underlying use case), I'd suggest changing
rows <- getRows(sheet, rowIndex=2:11)
torows <- getRows(sheet, rowIndex=2:(nrow(mydata)+1)
. Other than that, works perfectly.